Re: Hardware tuning (Was: Performance question) - Mailing list pgsql-general
From | Justin Clift |
---|---|
Subject | Re: Hardware tuning (Was: Performance question) |
Date | |
Msg-id | 3BAC8C10.9FA872F2@postgresql.org Whole thread Raw |
In response to | Hardware tuning (Was: Performance question) ("Tille, Andreas" <TilleA@rki.de>) |
Responses |
Re: Hardware tuning (Was: Performance question)
|
List | pgsql-general |
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
pgsql-general by date: