Hi Andreas,
I'm running PostgreSQL 7.1.3 here on a PC with nearly a gig of ram, and
running Linux Mandrake 8.0
First thing I did was to increase the amount of shared memory and stuff
which Linux allows things to use :
echo "kernel.shmall = 134217728" >> /etc/sysctl.conf
echo "kernel.shmmax = 134217728" >> /etc/sysctl.conf
For my system, that'll raise the shared memory limits to 128MB at system
boot time.
btw, the "134217728" figure = 128MB (128 * 1024 * 1024)
Then I changed the limits for the running system (so no reboot is
necessary) :
echo 134217728 > /proc/sys/kernel/shmall
echo 134217728 > /proc/sys/kernel/shmmax
Then adjusted the postgresql.conf file with these values :
sort_mem = 32768
shared_buffers = 220
Now, that's a bunch of shared_buffers, but at the same time I also
raised the max_connections to 110.
This seems to have dropped my execution times, but I haven't seriously
gotten around to tuning this system.
The key thing I think you've missed is to update the shared memory,
etc. More info about it can be found at :
http://www.postgresql.org/idocs/index.php?kernel-resources.html
Bruce Momjian also put together some information about optimising things
with PostgreSQL at :
http://www.ca.postgresql.org/docs/hw_performance/
If you want to be able to benchmark things on your system, I use the
"Open Source Database Benchmark" (Linux only at present), running the
latest CVS version of it, and also tweaked to not use hash indices. A
tarball of working source code is available at :
http://techdocs.postgresql.org/techdocs/perftuningfigures.php
Hope this is of assistance Andreas.
Regards and best wishes,
Justin Clift
"Tille, Andreas" wrote:
>
> On Thu, 20 Sep 2001, Justin Clift wrote:
>
> > Sorry, I haven't seen the history of this thread. One question which
> > might be relevant is, have you adjusted the postgresql.conf file from
> > the default memory settings to be something better?
> I adjusted two parameters:
>
> shared_buffers = 2048
> (When I tried 4096 Iīve got a connection error. Donīt know what this
> means, but anyway increasing of this value did not changed anything.)
>
> sort_mem = 2048
> (After increasing this value (from default 512) to 1024 I got an
> increase in speed from 20s to 18s - not much but better than nothing.
> Further increase to 2048 did not change anything further so I stopped
> here.)
>
> > If these are the times you're getting from a default configuration, you
> > might be able to get far better results by doing performance tuning of
> > PostgreSQL and/or the server.
> Any other values which might help here?
>
> Kind regards
>
> Andreas.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
