pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedlong wait times in ProcessCatchupEvent()
8+ messages / 5 participants
[nested] [flat]
* long wait times in ProcessCatchupEvent()
@ 2010-12-29 14:28 Julian v. Bock <bock@openit.de>
2010-12-29 15:18 ` Re: long wait times in ProcessCatchupEvent() Tom Lane <tgl@sss.pgh.pa.us>
2010-12-29 19:28 ` Re: long wait times in ProcessCatchupEvent() Craig James <craig_james@emolecules.com>
0 siblings, 2 replies; 8+ messages in thread
From: bock@openit.de @ 2010-12-29 14:28 UTC (permalink / raw)
To: pgsql-performance
Hi
I have the problem that on our servers it happens regularly under a
certain workload (several times per minute) that all backend processes
get a SIGUSR1 and spend several seconds in ProcessCatchupEvent(). At
100-200 connections (most of them idle) this causes the system load to
skyrocket. I am not really familiar with the code but my wild guess is
that the processes spend most of their time waiting for spinlocks.
We have reduced the number of connections as much as possible for now
but it still makes up for roughly 50% of the total CPU time. Has
anyone experienced a similar problem?
I can reproduce the issue on a test system with production data but it
is not so easy to pinpoint what exactly causes the problem. The queries
are basically tsearch2 full text searches over moderately big tables
(~35GB). The queries are performed by functions which aggregate data
from partitions in temporary tables, cache some data, and perform
calculations before returning it to the user.
The PostgreSQL version is 8.3.12, the test server has 8 amd64 cores
and 16GB of ram. I experimented with shared_buffers between 1GB and
4GB but it doesn't make much of a difference. Disk IO doesn't seem to
be an issue here.
Regards,
Julian v. Bock
--
Julian v. Bock Projektleitung Software-Entwicklung
OpenIT GmbH Tel +49 211 239 577-0
In der Steele 33a-41 Fax +49 211 239 577-10
D-40599 Düsseldorf http://www.openit.de
________________________________________________________________
HRB 38815 Amtsgericht Düsseldorf USt-Id DE 812951861
Geschäftsführer: Oliver Haakert, Maurice Kemmann
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long wait times in ProcessCatchupEvent()
2010-12-29 14:28 long wait times in ProcessCatchupEvent() Julian v. Bock <bock@openit.de>
@ 2010-12-29 15:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-12-30 15:03 ` Re: long wait times in ProcessCatchupEvent() Julian v. Bock <bock@openit.de>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2010-12-29 15:18 UTC (permalink / raw)
To: bock@openit.de; +Cc: pgsql-performance
bock@openit.de (Julian v. Bock) writes:
> I have the problem that on our servers it happens regularly under a
> certain workload (several times per minute) that all backend processes
> get a SIGUSR1 and spend several seconds in ProcessCatchupEvent().
This is fixed in 8.4 and up.
http://archives.postgresql.org/pgsql-committers/2008-06/msg00227.php
If you aren't willing to move off 8.3 you might be able to ameliorate
the problem by reducing the volume of catalog changes, but that can be
pretty hard if you're dependent on temp tables.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long wait times in ProcessCatchupEvent()
2010-12-29 14:28 long wait times in ProcessCatchupEvent() Julian v. Bock <bock@openit.de>
2010-12-29 15:18 ` Re: long wait times in ProcessCatchupEvent() Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-12-30 15:03 ` Julian v. Bock <bock@openit.de>
0 siblings, 0 replies; 8+ messages in thread
From: bock@openit.de @ 2010-12-30 15:03 UTC (permalink / raw)
To: pgsql-performance
Hi
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:
TL> bock@openit.de (Julian v. Bock) writes:
>> I have the problem that on our servers it happens regularly under a
>> certain workload (several times per minute) that all backend
>> processes get a SIGUSR1 and spend several seconds in
>> ProcessCatchupEvent().
TL> This is fixed in 8.4 and up.
TL> http://archives.postgresql.org/pgsql-committers/2008-06/msg00227.php
Thanks for the quick reply.
TL> If you aren't willing to move off 8.3 you might be able to
TL> ameliorate the problem by reducing the volume of catalog changes,
TL> but that can be pretty hard if you're dependent on temp tables.
Upgrading to 8.4 or 9.0 is not possible at the moment but if it is only
catalog changes I can probably work around that.
Regards,
Julian v. Bock
--
Julian v. Bock Projektleitung Software-Entwicklung
OpenIT GmbH Tel +49 211 239 577-0
In der Steele 33a-41 Fax +49 211 239 577-10
D-40599 Düsseldorf http://www.openit.de
________________________________________________________________
HRB 38815 Amtsgericht Düsseldorf USt-Id DE 812951861
Geschäftsführer: Oliver Haakert, Maurice Kemmann
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long wait times in ProcessCatchupEvent()
2010-12-29 14:28 long wait times in ProcessCatchupEvent() Julian v. Bock <bock@openit.de>
@ 2010-12-29 19:28 ` Craig James <craig_james@emolecules.com>
2010-12-29 19:58 ` Re: long wait times in ProcessCatchupEvent() Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 8+ messages in thread
From: Craig James @ 2010-12-29 19:28 UTC (permalink / raw)
To: pgsql-performance
On 12/29/10 6:28 AM, Julian v. Bock wrote:
> I have the problem that on our servers it happens regularly under a
> certain workload (several times per minute) that all backend processes
> get a SIGUSR1 and spend several seconds in ProcessCatchupEvent(). At
> 100-200 connections (most of them idle) this causes the system load to
> skyrocket. I am not really familiar with the code but my wild guess is
> that the processes spend most of their time waiting for spinlocks.
>
> We have reduced the number of connections as much as possible for now
> but it still makes up for roughly 50% of the total CPU time. Has
> anyone experienced a similar problem?
>
> I can reproduce the issue on a test system with production data but it
> is not so easy to pinpoint what exactly causes the problem. The queries
> are basically tsearch2 full text searches over moderately big tables
> (~35GB). The queries are performed by functions which aggregate data
> from partitions in temporary tables, cache some data, and perform
> calculations before returning it to the user.
>
> The PostgreSQL version is 8.3.12, the test server has 8 amd64 cores
> and 16GB of ram. I experimented with shared_buffers between 1GB and
> 4GB but it doesn't make much of a difference. Disk IO doesn't seem to
> be an issue here.
This sounds like the exact same problem I had on Postgres 8.3 and 8.4:
http://archives.postgresql.org/pgsql-performance/2010-04/msg00071.php
Updating to Postgres version 9 fixed it. Here is what appeared to be the best analysis of what was happening, but we never confirmed it.
http://archives.postgresql.org/pgsql-performance/2010-06/msg00464.php
Craig
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long wait times in ProcessCatchupEvent()
2010-12-29 14:28 long wait times in ProcessCatchupEvent() Julian v. Bock <bock@openit.de>
2010-12-29 19:28 ` Re: long wait times in ProcessCatchupEvent() Craig James <craig_james@emolecules.com>
@ 2010-12-29 19:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-12-29 23:53 ` Re: long wait times in ProcessCatchupEvent() Craig James <craig_james@emolecules.com>
2010-12-30 00:54 ` Re: long wait times in ProcessCatchupEvent() Mladen Gogala <mladen.gogala@vmsinfo.com>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2010-12-29 19:58 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> On 12/29/10 6:28 AM, Julian v. Bock wrote:
>> I have the problem that on our servers it happens regularly under a
>> certain workload (several times per minute) that all backend processes
>> get a SIGUSR1 and spend several seconds in ProcessCatchupEvent().
> This sounds like the exact same problem I had on Postgres 8.3 and 8.4:
> http://archives.postgresql.org/pgsql-performance/2010-04/msg00071.php
> Updating to Postgres version 9 fixed it. Here is what appeared to be the best analysis of what was happening, but we never confirmed it.
> http://archives.postgresql.org/pgsql-performance/2010-06/msg00464.php
It happened for you on 8.4 too? In that previous thread you were still
on 8.3. If you did see it on 8.4 then it wasn't sinval ...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long wait times in ProcessCatchupEvent()
2010-12-29 14:28 long wait times in ProcessCatchupEvent() Julian v. Bock <bock@openit.de>
2010-12-29 19:28 ` Re: long wait times in ProcessCatchupEvent() Craig James <craig_james@emolecules.com>
2010-12-29 19:58 ` Re: long wait times in ProcessCatchupEvent() Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-12-29 23:53 ` Craig James <craig_james@emolecules.com>
1 sibling, 0 replies; 8+ messages in thread
From: Craig James @ 2010-12-29 23:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 12/29/10 11:58 AM, Tom Lane wrote:
> Craig James<craig_james@emolecules.com> writes:
>> On 12/29/10 6:28 AM, Julian v. Bock wrote:
>>> I have the problem that on our servers it happens regularly under a
>>> certain workload (several times per minute) that all backend processes
>>> get a SIGUSR1 and spend several seconds in ProcessCatchupEvent().
>
>> This sounds like the exact same problem I had on Postgres 8.3 and 8.4:
>
>> http://archives.postgresql.org/pgsql-performance/2010-04/msg00071.php
>
>> Updating to Postgres version 9 fixed it. Here is what appeared to be the best analysis of what was happening, but we never confirmed it.
>
>> http://archives.postgresql.org/pgsql-performance/2010-06/msg00464.php
>
> It happened for you on 8.4 too? In that previous thread you were still
> on 8.3. If you did see it on 8.4 then it wasn't sinval ...
My mistake - it was only 8.3.
Craig
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long wait times in ProcessCatchupEvent()
2010-12-29 14:28 long wait times in ProcessCatchupEvent() Julian v. Bock <bock@openit.de>
2010-12-29 19:28 ` Re: long wait times in ProcessCatchupEvent() Craig James <craig_james@emolecules.com>
2010-12-29 19:58 ` Re: long wait times in ProcessCatchupEvent() Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-12-30 00:54 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-12-30 14:50 ` Re: long wait times in ProcessCatchupEvent() Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 8+ messages in thread
From: Mladen Gogala @ 2010-12-30 00:54 UTC (permalink / raw)
To: pgsql-performance
On 12/29/2010 2:58 PM, Tom Lane wrote:
> It happened for you on 8.4 too? In that previous thread you were still
> on 8.3. If you did see it on 8.4 then it wasn't sinval ...
>
> regards, tom lane
>
May I ask what exactly is "sinval"? I took a look at Craig's problem and
your description but I wasn't able to figure out what is sinval lock and
what does it lock? I apologize if the question is stupid.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: long wait times in ProcessCatchupEvent()
2010-12-29 14:28 long wait times in ProcessCatchupEvent() Julian v. Bock <bock@openit.de>
2010-12-29 19:28 ` Re: long wait times in ProcessCatchupEvent() Craig James <craig_james@emolecules.com>
2010-12-29 19:58 ` Re: long wait times in ProcessCatchupEvent() Tom Lane <tgl@sss.pgh.pa.us>
2010-12-30 00:54 ` Re: long wait times in ProcessCatchupEvent() Mladen Gogala <mladen.gogala@vmsinfo.com>
@ 2010-12-30 14:50 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 8+ messages in thread
From: Kevin Grittner @ 2010-12-30 14:50 UTC (permalink / raw)
To: Mladen Gogala <mladen.gogala@vmsinfo.com>; +Cc: pgsql-performance
Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
> May I ask what exactly is "sinval"? I took a look at Craig's
> problem and your description but I wasn't able to figure out what
> is sinval lock and what does it lock? I apologize if the question
> is stupid.
This area could probably use a README file, but you can get a good
idea from the comment starting on line 30 of this file:
http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/storage/ipc/sinvaladt.c;h=791...
It has to do with communication between processes regarding
invalidation of the shared cache.
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-12-30 15:03 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-29 14:28 long wait times in ProcessCatchupEvent() Julian v. Bock <bock@openit.de>
2010-12-29 15:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-12-30 15:03 ` Julian v. Bock <bock@openit.de>
2010-12-29 19:28 ` Craig James <craig_james@emolecules.com>
2010-12-29 19:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-12-29 23:53 ` Craig James <craig_james@emolecules.com>
2010-12-30 00:54 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-12-30 14:50 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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