Hi Mark, Thanks for your reply. > Guillaume Cottenceau wrote: [...] > > Btw, I use postgres 7.4.5 with -B 1000 -N 500 and all > > postgresql.conf default values except timezone = 'UTC', on an > > ext3 partition with data=ordered, and run Linux 2.6.12. > > I didn't see any mention of how much memory is on your server, but > provided you have say 1G, and are using the box solely for a database > server, I would increase both shared_buffers and effective_cache size. This test machine has 1G of (real) memory, servers often have 2G or 4G. The thing is that the application runs on the same machine, and as it is a java application, it takes up a little memory too (we can say half of it should go to java and half to postgres, I guess). Determining the best memory "plan" is not so easy, though your information is priceless and will help a lot! > shared_buffer = 12000 > effective_cache_size = 25000 > > This would mean you are reserving 100M for Postgres to cache relation > pages, and informing the planner that it can expect ~200M available > from the disk buffer cache. To give a better recommendation, we need Ok, thanks. I wanted to investigate this field, but as the application is multithreaded and uses a lot of postgres clients, I wanted to make sure the shared_buffers values is globally for postgres, not just per (TCP) connection to postgres, before increasing the value, fearing to take the whole server down. On a server with 235 connections and -N 512 -B 1024, reading http://virtualthreads.blogspot.com/2006/02/understanding-memory-usage-on-linux.html I came up with the following figure: for i in `pidof postmaster`; do pmap -d $i | grep -i writeable ; done | perl -MMDK::Common -ne 'do { push @a, $1; $tot += $1 } if /writeable.private: (\d+)K/; END { print "total postgres private memory: ${tot}K\nmin: " . min(@a) . "K\nmax: " . max(@a) . "K\n"; }' total postgres private memory: 432080K min: 936K max: 4216K As the server has 2G of memory, I was reluctant to increase the amount of shared memory since overall postgres memory use seems already quite high - though 100M more would not kill the server, obviously. Btw, can you comment on the upper figures? > to know more about your server and workload (e.g server memory > configuration and usage plus how close you get to 500 connections). Depending on the server, it can have 200, up to around 400 connections open. As of workload, I am not sure what metrics are suitable. Typically postgres can be seen in the top processes but most queries are quick and average load average reported by the linux kernel is nearly always below 0.3, and often 0.1. These are single or dual xeon 2.8 GHz machines with hardware raid (megaraid or percraid driver) with reasonable performance. -- Guillaume Cottenceau
