Hardware tuning (Was: Performance question)

On Fri, 21 Sep 2001, Justin Clift wrote:

> 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.
220 is much less than I have set before I posted my stats yesterday.
I have set it to 2048. But adjusting
kernel.shmall = 134217728
kernel.shmmax = 134217728
gave me a speed up by nearly factor 2! That could be a nice start
for further increasing of memory. (Well, that machine has 2GB ;-) ... )

> This seems to have dropped my execution times, but I haven't seriously
> gotten around to tuning this system.
So yes, it has dropped my execution times from 20 times slower than
MS-SQL to 10 times slower, i.e. I have to continue tuning my setup.

> 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/
I´ve read both documents now and see no other parameter to adjust than
shared_buffers. I have to admit that I´m not really sure if this
parameter is responsible for the term "cache size" on page
http://www.ca.postgresql.org/docs/hw_performance/node8.html
(I would consider it to be helpfull if the relevant parameter would
be mentioned in the text, Bruce.)

I just post the parameters I changed on my system and the results I´ve got:

/etc/sysctl.conf
kernel.shmall = 134217728
kernel.shmmax = 134217728
fs.file-max = 16384

/etc/postgresql/postgresql.conf:
max_connections = 256
shared_buffers = 2048
sort_mem = 32768

This setup gave me a speed increase from 56s to 33s for a certain query
(the one which took the M$-SQL server 2.5s).

Now I tried to increase
sort_mem = 32768
shared_buffers = 4096

and got no real speed difference but I noticed an improved memory usage by
top. So I continued increasing shared_buffers by doubling the size step
by step. To enable a certain amount of shared_buffers I also had to adjust
kernel.shmall and kernel.shmmax (I got errors otherwise).

Here I post some parameter settings and corresponding memory usage
measured by top and times for the query:

kernel.shmall = 536870912
kernel.shmmax = 536870912

shared_buffers = 32768

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
769 postgres 16 0 78372 76M 52916 R 99.9 7.6 0:23 postmaster

real 0m33.591s
user 0m0.190s
sys 0m0.040s

kernel.shmall = 1073741824
kernel.shmmax = 1073741824

shared_buffers = 65536

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
865 postgres 17 0 80332 78M 54836 R 99.9 7.7 0:20 postmaster

real 0m32.861s
user 0m0.200s
sys 0m0.010s

kernel.shmall = 2147483648
kernel.shmmax = 2147483648

shared_buffers = 131072

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
1172 postgres 18 0 86572 84M 60748 R 99.9 8.3 0:22 postmaster

7.1.3: (I also tried PostgreSQL 7.1.3 all other values for 7.1.2)
2644 postgres 17 0 87088 84M 61264 R 99.9 8.4 0:29 postmaster

set enable_seqscan = off; (quite the same speed but other mem-usage)
1205 postgres 18 0 85500 83M 59676 R 99.9 8.2 0:22 postmaster

7.1.3:
2631 postgres 15 0 81972 79M 56148 R 99.9 7.9 0:28 postmaster

real 0m32.835s
user 0m0.210s
sys 0m0.050s

I noticed no real difference in speed in all this tests but I observed
an increased need of memory usage. There was no difference in query
speed if I enabled or disabled index scan and between PostgreSQL version
7.1.2 and 7.1.3.

Furthermore I wonder about the following fact: I see no real difference
in speed if I start the query immediately after restarting postmaster
and redoing the same query. In my opinion the first query should fill
the relevant tables into memory cache which should take some time but the
second query should be faster because the cache is just filled.

So I wonder if it makes sense if I continue increasing those values
until I observe this difference or if I don´t see any increase in
memory usage by top. I think I could spend some more memory on this
task currently because it is less than 10% memory usage and there
is no swap at all on the machine.

# vmstat
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
1 0 0 0 176216 21536 1576744 0 0 1 3 2 7 0 0 24

> If you want to be abye 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
Thanks. I don´t want to do *any* benchmark. Only my application is
relevant even if PostgreSQL outperforms other databases in any
benchmark.

> Hope this is of assistance Andreas.
It was of assistance for sure and I hope further tuning brings me near
the target. Thanks Justin.

Kind regards

Andreas.