Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id A1F141337B56 for ; Wed, 29 Dec 2010 10:59:42 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 58672-06 for ; Wed, 29 Dec 2010 14:59:35 +0000 (UTC) X-Greylist: delayed 00:30:58.969321 by SQLgrey-1.7.6 Received: from mx.openit.de (mx.openit.de [217.69.65.200]) by mail.postgresql.org (Postfix) with ESMTP id C55A31337B49 for ; Wed, 29 Dec 2010 10:59:34 -0400 (AST) Received: from warpcore.openit.de (unknown [IPv6:2001:aa8:fffd::2]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mx.openit.de (Postfix) with ESMTP id D9569100077 for ; Wed, 29 Dec 2010 15:28:34 +0100 (CET) Received: from warpcore.openit.de (localhost.localdomain [127.0.0.1]) by warpcore.openit.de (8.13.8/8.13.8) with ESMTP id oBTESYan002994 for ; Wed, 29 Dec 2010 15:28:34 +0100 Received: (from julian@localhost) by warpcore.openit.de (8.13.8/8.13.8/Submit) id oBTESYFM002993; Wed, 29 Dec 2010 15:28:34 +0100 X-Authentication-Warning: warpcore.i.openit.de: julian set sender to bock@openit.de using -f From: bock@openit.de (Julian v. Bock) To: pgsql-performance@postgresql.org Subject: long wait times in ProcessCatchupEvent() Organization: OpenIT GmbH User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1.50 (gnu/linux) X-Face: $~?g2!-On|QWPz8qkxU3V^DL7_Tli#/R8{t\$SYTT,/`5"; d|]YB@q/9_|NP; |:tK&kx:n7f=%y:G)b0$[Y{}F_@"gb6WsI_LV8"%F7-|Kd="x6g18{t|/Gan9"&C!t_$6(HTf-1.}t/]RUW4k-; O6j5+FEyG29jrf,f?hKqa*l]*DVr; nhJN)+~H5&Xm&M\m_&rX/+WL Date: Wed, 29 Dec 2010 15:28:34 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.61 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_LOW=-0.7, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201012/326 X-Sequence-Number: 41778 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 --=20 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=C3=BCsseldorf http://www.openit.de ________________________________________________________________ HRB 38815 Amtsgericht D=C3=BCsseldorf USt-Id DE 812951861 Gesch=C3=A4ftsf=C3=BChrer: Oliver Haakert, Maurice Kemmann