Re: System overload / context switching / oom, 8.3

On Tue, 2 Feb 2010, Rob wrote:
> pg 8.3.9, Debian Etch, 8gb ram, quadcore xeon, megaraid (more details at end)
> ~240 active databases, 800+ db connections via tcp.

> Linux 2.6.18-6-686-bigmem #1 SMP Thu Nov 5 17:30:05 UTC 2009 i686
> GNU/Linux (Debian Etch)
>
> 8 MB RAM
> 4 Quad Core Intel(R) Xeon(R) CPU E5440 @ 2.83GHz stepping 06

My advice?

1. Switch to 64-bit operating system and Postgres. Debian provides that,
and it works a charm. You have a 64-bit system, so why not use it?

2. Buy more RAM. Think about it - you have 800 individual processes
running on your box, and they will all want their own process space. To be
honest, I'm impressed that the current machine works at all. You can get
an idea of how much RAM you might need by multiplying the number of
connections by (work_mem + about 3MB), and add on shared_buffers. So even
when the system is idle you're currently burning 3200MB just sustaining
800 processes - more if they are actually doing something.

3. Try to reduce the number of connections to the database server.

4. Think about your work_mem. Finding the correct value for you is going
to be a matter of testing. Smaller values will result in large queries
running slowly, but have the danger of driving the system to swap and OOM.

Matthew

--
A good programmer is one who looks both ways before crossing a one-way street.
Considering the quality and quantity of one-way streets in Cambridge, it
should be no surprise that there are so many good programmers there.