pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedbackend suddenly becomes slow, then remains slow
6+ messages / 4 participants
[nested] [flat]
* backend suddenly becomes slow, then remains slow
@ 2012-12-14 18:40 Andrew Dunstan <andrew.dunstan@pgexperts.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andrew Dunstan @ 2012-12-14 18:40 UTC (permalink / raw)
To: pgsql-performance
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. 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.
It's got us rather puzzled. Has anyone seen anything like this?
cheers
andrew
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backend suddenly becomes slow, then remains slow
@ 2012-12-14 19:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andrew Dunstan <andrew.dunstan@pgexperts.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2012-12-14 19:56 UTC (permalink / raw)
To: Andrew Dunstan <andrew.dunstan@pgexperts.com>; +Cc: pgsql-performance
Andrew Dunstan <andrew.dunstan@pgexperts.com> writes:
> 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. 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.
> It's got us rather puzzled. Has anyone seen anything like this?
Maybe the kernel is auto-nice'ing the process once it's accumulated X
amount of CPU time?
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backend suddenly becomes slow, then remains slow
@ 2012-12-14 20:16 Andrew Dunstan <andrew@dunslane.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Andrew Dunstan @ 2012-12-14 20:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 12/14/2012 02:56 PM, Tom Lane wrote:
> Andrew Dunstan <andrew.dunstan@pgexperts.com> writes:
>> 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. 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.
>> It's got us rather puzzled. Has anyone seen anything like this?
> Maybe the kernel is auto-nice'ing the process once it's accumulated X
> amount of CPU time?
>
>
That was my initial thought, but the client said not. We'll check again.
cheers
andrew
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* backend suddenly becomes slow, then remains slow
@ 2012-12-27 04:03 Jeff Janes <jeff.janes@gmail.com>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 6+ messages in thread
From: Jeff Janes @ 2012-12-27 04:03 UTC (permalink / raw)
To: Andrew Dunstan <andrew.dunstan@pgexperts.com>; +Cc: pgsql-performance
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?
Could you strace is and see what it is doing?
> 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.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backend suddenly becomes slow, then remains slow
@ 2012-12-27 17:43 Andrew Dunstan <andrew.dunstan@pgexperts.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andrew Dunstan @ 2012-12-27 17:43 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: backend suddenly becomes slow, then remains slow
@ 2012-12-28 00:11 Jeff Janes <jeff.janes@gmail.com>
parent: Andrew Dunstan <andrew.dunstan@pgexperts.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jeff Janes @ 2012-12-28 00:11 UTC (permalink / raw)
To: Andrew Dunstan <andrew.dunstan@pgexperts.com>; +Cc: pgsql-performance
On Thursday, December 27, 2012, Andrew Dunstan wrote:
> On 12/26/2012 11:03 PM, Jeff Janes wrote:
>
>>
>> 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.
>
All of the syscache things have compiled hard-coded numbers of buckets, at
most 2048, and once those are exceeded the resulting collision resolution
becomes essentially linear. It is not hard to exceed 2048 tables by a
substantial multiple, and even less hard to exceed 2048 columns (summed
over all tables).
I don't know why syscache doesn't use dynahash; whether it is older than
dynahash is and was never converted out of inertia, or if there are extra
features that don't fit the dynahash API. If the former, then converting
them to use dynahash should give automatic resizing for free. Maybe that
conversion should be a To Do item?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-12-28 00:11 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-12-14 18:40 backend suddenly becomes slow, then remains slow Andrew Dunstan <andrew.dunstan@pgexperts.com>
2012-12-14 19:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-12-14 20:16 ` Andrew Dunstan <andrew@dunslane.net>
2012-12-27 04:03 ` Jeff Janes <jeff.janes@gmail.com>
2012-12-27 17:43 ` Andrew Dunstan <andrew.dunstan@pgexperts.com>
2012-12-28 00:11 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox