Message-ID: <4E64AF9D.3060601@ringerc.id.au>
Date: Mon, 05 Sep 2011 19:16:45 +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>
	<4E64A94E.7000803@ringerc.id.au>
	<0894B018-542F-45B6-9A2E-A84DEA99AE0A@aggress.net>
In-Reply-To: <0894B018-542F-45B6-9A2E-A84DEA99AE0A@aggress.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 5/09/2011 6:55 PM, Richard Shaw wrote:
> 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
>
Ah, I see. The point still stands: your hardware can *not* efficiently 
do work for 1000 concurrent backend workers. Reduce the maximum number 
of workers by setting a lower cap on the pool size and a lower 
max_connections. This won't work (you'll run out of pooler connections) 
unless you also set PgBouncer to transaction pooling mode instead of the 
default session pooling mode, which you're currently using. It is 
*important* to read the documentation on this before doing it, as there 
are implications for apps that use extra-transactional features like 
HOLD cursors and advisory locks.

See: http://pgbouncer.projects.postgresql.org/doc/usage.html

It may also be necessary to set PgBouncer to block (wait) rather than 
report an error when there is no pooled connection available to start a 
new transaction on. I'm not sure what PgBouncer's default behavior for 
that is and didn't see anything immediately clear in the pgbouncer(5) 
ini file man page.

--
Craig Ringer