Re: Rather large LA

On 5/09/2011 6:28 PM, Richard Shaw wrote:
> max_connections | 1000

Woah! No wonder you have "stampeding herd" problems after a DB or server
restart and are having performance issues.

When you have 1000 clients trying to do work at once, they'll all be
fighting over memory, disk I/O bandwidth, and CPU power which is nowhere
near sufficient to allow them to all actually achieve something all at
once. You'll have a lot of overhead as the OS tries to be fair and allow
each to make progress - at the expense of overall throughput.

If most of those connections are idle most of the time - say, they're
peristent connections from some webapp that requrires one connection per
webserver thread - then the situation isn't so bad. They're still
costing you backend RAM and various housekeeping overhead (including
task switching) related to lock management and shared memory, though.

Consider using a connection pooler like PgPool-II or PgBouncer if your
application is suitable. Most apps will be quite happy using pooled
connections; only a few things like advisory locking and HOLD cursors
work poorly with pooled connections. Using a pool allows you to reduce
the number of actively working and busy connections to the real Pg
backend to something your hardware can cope with, which should
dramatically increase performance and reduce startup load spikes. The
general very rough rule of thumb for number of active connections is
"number of CPU cores + number of HDDs" but of course this is only
incredibly rough and depends a lot on your workload and DB.

Ideally PostgreSQL would take care of this pooling inside the server,
breaking the "one connection = one worker backend" equivalence.
Unfortunately the server's process-based design makes that harder than
it could be. There's also a lot of debate about whether pooling is even
the core DB server's job and if it is, which of the several possible
approaches is the most appropriate. Then there's the issue of whether
in-server connection pooling is even appropriate without admission
control - which brings up the "admission control is insanely hard"
problem. So for now, pooling lives outside the server in projects like
PgPool-II and PgBouncer.

--
Craig Ringer