Re: backend suddenly becomes slow, then remains slow

On 12/26/2012 11:03 PM, Jeff Janes wrote:
> On Fri, Dec 14, 2012 at 10:40 AM, Andrew Dunstan
> <andrew.dunstan@pgexperts.com> wrote:
> > One of my clients has an odd problem. Every so often a backend will
> suddenly
> > become very slow. The odd thing is that once this has happened it
> remains
> > slowed down, for all subsequent queries. Zone reclaim is off. There
> is no IO
> > or CPU spike, no checkpoint issues or stats timeouts, no other
> symptom that
> > we can see.
>
> By "no spike", do you mean that the system as a whole is not using an
> unusual amount of IO or CPU, or that this specific slow back-end is
> not using an unusual amount?


both, really.

>
> Could you strace is and see what it is doing?


Not very easily, because it's a pool connection and we've lowered the
pool session lifetime as part of the amelioration :-) So it's not
happening very much any more.

>
> > The problem was a lot worse that it is now, but two steps have
> > alleviated it mostly, but not completely: much less aggressive
> autovacuuming
> > and reducing the maximum lifetime of backends in the connection
> pooler to 30
> > minutes.
>
> Do you have a huge number of tables?  Maybe over the course of a
> long-lived connection, it touches enough tables to bloat the relcache
> / syscache.  I don't know how the autovac would be involved in that,
> though.
>
>

Yes, we do indeed have a huge number of tables. This seems a plausible
thesis.

cheers

andrew