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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
