Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 2150A63358A for ; Fri, 25 Jun 2010 11:47:59 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 66448-02-9 for ; Fri, 25 Jun 2010 14:47:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 5E862636138 for ; Fri, 25 Jun 2010 11:47:13 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o5PElAF4002287; Fri, 25 Jun 2010 10:47:10 -0400 (EDT) To: Craig James cc: pgsql-performance@postgresql.org Subject: Re: Occasional giant spikes in CPU load In-reply-to: <4C24B3BB.3070901@emolecules.com> References: <4BBCFB12.1010306@emolecules.com> <4BBD3A17.90209@emolecules.com> <4C23FD52.40508@emolecules.com> <23596.1277438692@sss.pgh.pa.us> <4C24B3BB.3070901@emolecules.com> Comments: In-reply-to Craig James message dated "Fri, 25 Jun 2010 06:48:43 -0700" Date: Fri, 25 Jun 2010 10:47:10 -0400 Message-ID: <2286.1277477230@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201006/465 X-Sequence-Number: 39321 Craig James writes: > On 6/24/10 9:04 PM, Tom Lane wrote: >> sinval queue overflow comes to mind ... although that really shouldn't >> happen if there's "no real load" on the server. What PG version is >> this? > 8.3.10. Upgraded based on your advice when I first asked this question. Any chance of going to 8.4? If this is what I suspect, you really need this 8.4 fix: http://archives.postgresql.org/pgsql-committers/2008-06/msg00227.php which eliminated the thundering-herd behavior that previous releases exhibit when the sinval queue overflows. If you're stuck on 8.3 then you are going to have to modify your application's behavior to eliminate sinval overflows. If the overall system load isn't high then I would have to guess that the problem is some individual sessions sitting "idle in transaction" for long periods, long enough that a number of DDL operations happen elsewhere. You could also consider throwing memory at the problem by raising the sinval queue size. That'd require a custom build since it's not exposed as a configurable parameter, but it'd be a one-line patch I think. Or you could look at using connection pooling so you don't have quite so many backends ... regards, tom lane