Hardware tuning (Was: Performance question) - Mailing list pgsql-general
From | Tille, Andreas |
---|---|
Subject | Hardware tuning (Was: Performance question) |
Date | |
Msg-id | Pine.LNX.4.33.0109211022480.9092-100000@wr-linux02.rki.ivbb.bund.de Whole thread Raw |
In response to | Re: Performance question (stripped down the problem) (Justin Clift <justin@postgresql.org>) |
List | pgsql-general |
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.
pgsql-general by date: