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(at)postgresql(dot)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