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.