Re: PostgreSQL 8.4 performance tuning questions

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