Re: Performance question (stripped down the problem)

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