Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id C058A9FBEBB for ; Fri, 16 Mar 2007 11:50:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 02374-01 for ; Fri, 16 Mar 2007 11:49:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 54C249FBFBA for ; Fri, 16 Mar 2007 11:49:37 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l2GEnPGQ006523; Fri, 16 Mar 2007 10:49:25 -0400 (EDT) To: Jeff Frost cc: pgsql-admin@postgresql.org Subject: Re: pl/pgsql function spikes CPU 100% In-reply-to: References: Comments: In-reply-to Jeff Frost message dated "Fri, 16 Mar 2007 01:52:59 -0700" Date: Fri, 16 Mar 2007 10:49:25 -0400 Message-ID: <6522.1174056565@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/101 X-Sequence-Number: 24925 Jeff Frost writes: > ... Interestingly, when you > strace the backend, it doesn't appear to be doing too much...here's some > sample output: > select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout) > semop(3932217, 0x7fbfffd150, 1) = 0 > semop(3932217, 0x7fbfffd150, 1) = 0 > semop(3932217, 0x7fbfffd150, 1) = 0 > semop(3932217, 0x7fbfffd150, 1) = 0 > semop(3932217, 0x7fbfffd150, 1) = 0 > select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout) > semop(3997755, 0x7fbfffd170, 1) = 0 > semop(3932217, 0x7fbfffd150, 1) = 0 This looks suspiciously like the sort of trace we saw in the various "context swap storm" threads. The test cases for those generally involved really tight indexscan loops, ie, the backends were spending all their time trying to access shared buffers. If you haven't changed the function or the data, then I concur with the nearby worry about autovacuuming (large buildup of dead tuples could result in this symptom). Or maybe you've got an old open transaction that is blocking cleanup? regards, tom lane