Re: Huge spikes in number of connections doing "PARSE"

hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
> On Tue, Mar 15, 2011 at 12:13:21AM -0400, Noah Misch wrote:
>> What is a typical lifespan for a backend in this system? What sort of
>> connection pooling are you using, if any?

> quite long, but:

> we have n (~ 40 i think) web servers. each webserver has it's own
> pgbouncer (in session pooling).

> application connects to pgbouncer, issues (usually) single query, and
> disconnects.

> pgbouncers are set to close oder connections, if unused, after (i think
> 2 minutes. (i don't actually have access there).

> generally, through the day we see from 400 to 800 connections, mostly
> idle, but sometimes it goes much higher (like 1400), and then the
> connections are mostly parsing.

If the backends are "mostly idle" then it'd be a good idea to set a
smaller maximum limit on their number. Idle backends can be a
performance risk pre-8.4 because of the cache reset problem Noah pointed
to. You'd be better off having pgbouncer delay incoming queries until
there's a free backend to pass them to.

regards, tom lane