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

On Tue, Mar 15, 2011 at 10:20:38AM -0400, Tom Lane wrote:
> 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.

while I understand it, and will pass this information to client, one
thing bugs me - one the problem happens only sometimes, with relatively
low count of backends?
we saw database running normally with 1200 connections, yet sometimes,
it has 500 connections, and then something happens and they all end up
locking in this parse mode.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/