Hi Craig, Apologies, I should have made that clearer, I am using PgBouncer 1.4.1 in front of Postgres and included the config at the bottom of my original mail
Regards Richard ......... On 5 Sep 2011, at 11:49, Craig Ringer wrote: > 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
