Message-ID: <4E64A94E.7000803@ringerc.id.au>
Date: Mon, 05 Sep 2011 18:49:50 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:6.0.1) Gecko/20110830 Thunderbird/6.0.1
MIME-Version: 1.0
To: Richard Shaw <richard@aggress.net>
CC: pgsql-performance@postgresql.org
Subject: Re: Rather large LA
References: <F757E48F-2B4F-496D-951B-B04ED98F8FD6@aggress.net>
In-Reply-To: <F757E48F-2B4F-496D-951B-B04ED98F8FD6@aggress.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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