Re: PostgreSQL 8.4 performance tuning questions

[Greg Smith <gsmith(at)gregsmith(dot)com>]

On Thu, 30 Jul 2009, Rauan Maemirov wrote:

> maintenance_work_mem = 1GB
> work_mem = 192MB
> shared_buffers = 7680MB
> max_connections = 80
> My box is Nehalem 2xQuad 2.8 with RAM 32Gb

While it looks like you sorted out your issue downthread, I wanted to 
point out that your setting for work_mem could be dangerously high here 
and contribute to problems with running out memory or using swap.  If each 
of your 80 clients was doing a sort at the same time, you'd be using 80 * 
192MB + 7680MB = 15360GB of RAM just for the server.  The problem is that 
each client could do multiple sorts, so usage might even got higher. 
Unless you have a big data warehouse setup, more common work_mem settings 
are in the 16-64MB range rather than going this high.  Just something to 
keep an eye on if you find a lot of memory is being used by the database 
processes.  I really need to refine the pgtune model to more carefully 
account for this particular problem, it's a bit too aggressive here for 
people who aren't proactively watching the server's RAM after changing the 
settings.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD