Re: Hardware tuning (Was: Performance question)

Hi Andreas,

Good to hear this has been of benefit.  From reading your email, you
haven't altered the value of sort_mem, just shared_buffers.

It might be worthwhile checking things out with sort_mem at different
levels too before homing in on the "best" value(s) for your application
& setup.

:-)

Regards and best wisehs,

Justin Clift


"Tille, Andreas" wrote:
>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
"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