Re: Rather large LA - Mailing list pgsql-performance

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
thebottom 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
CPUpower which is nowhere near sufficient to allow them to all actually achieve something all at once. You'll have a
lotof 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
requriresone connection per webserver thread - then the situation isn't so bad. They're still costing you backend RAM
andvarious 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
quitehappy 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
backendto 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"
butof 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
lotof debate about whether pooling is even the core DB server's job and if it is, which of the several possible
approachesis the most appropriate. Then there's the issue of whether in-server connection pooling is even appropriate
withoutadmission control - which brings up the "admission control is insanely hard" problem. So for now, pooling lives
outsidethe server in projects like PgPool-II and PgBouncer. 
>
> --
> Craig Ringer