Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 9BB6E9FB77B for ; Fri, 16 Mar 2007 06:36:12 -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 54535-03 for ; Fri, 16 Mar 2007 06:36:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.243]) by postgresql.org (Postfix) with ESMTP id 69B009FB807 for ; Fri, 16 Mar 2007 06:36:08 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so502261ana for ; Fri, 16 Mar 2007 02:36:07 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=HKiYOEfQgI4g5+iRXisKhwTJafqcZEL+knvsqJfn+Utt9yXfosNp9FU4kYGqqOCmvmWsYZmPWi1DTwhyQ9Shwale9dw8sPTpDx8/FbFvTZyzbTRaoU40RjMN5sa2Oi809dkj/3JH1v170qMA/fNREEMiRK43cFbaGuhtHnGYpUU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=EO2SSC2KBlXkzrOIKJsxN1y90H5PLcwyxhr3kn8mIOBr46tSvEl9+0d6o0D4K7sY5L37gMsnLF5bM7V+C2ewfVyy8OgXtJ0nxmwNoSaM9EN7XIfh88yB1teh5pJg1+lD5ql2Ru143r0RzyLegeBotKt6vgp5J4/9qdG8xKO59I8= Received: by 10.100.47.6 with SMTP id u6mr1303898anu.1174037767301; Fri, 16 Mar 2007 02:36:07 -0700 (PDT) Received: by 10.100.38.6 with HTTP; Fri, 16 Mar 2007 02:36:07 -0700 (PDT) Message-ID: Date: Fri, 16 Mar 2007 14:36:07 +0500 From: "Shoaib Mir" To: "Jeff Frost" Subject: Re: pl/pgsql function spikes CPU 100% Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_62113_306616.1174037767243" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/97 X-Sequence-Number: 24921 ------=_Part_62113_306616.1174037767243 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Are the stat collector and autovacuum processes in good shape? -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/16/07, Jeff Frost wrote: > > I've got a client that has a function in a db which had been humming along > quite nicely on 2xOpteron 275, PG 8.1.5, 8GB of RAM. Now suddenly many of > the > functions in the DB if called will spike the CPU to 100%. These are > functions > that used to finish in 7ms, now run for 20-40 mins. 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 > > Any chance we've stumbled into some corner case bug? We actually moved > the DB > to a different server thinking perhaps we had gotten to the limit of slow > hardware, but in fact it happens on the other server as well. > > I don't see any ungranted locks in pg_locks, nor are there any other non > idle > queries this time of the night. > > I'll see if I can share the function code tomorrow when people are awake > again > in case we have something especially silly in there. > > > -- > Jeff Frost, Owner > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 650-780-7908 FAX: 650-649-1954 > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > ------=_Part_62113_306616.1174037767243 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Are the stat collector and autovacuum processes in good shape?

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 3/16/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
I've got a client that has a function in a db which had been humming along
quite nicely on 2xOpteron 275, PG 8.1.5, 8GB of RAM.  Now suddenly many of the
functions in the DB if called will spike the CPU to 100%.  These are functions
that used to finish in 7ms, now run for 20-40 mins.  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

Any chance we've stumbled into some corner case bug?  We actually moved the DB
to a different server thinking perhaps we had gotten to the limit of slow
hardware, but in fact it happens on the other server as well.

I don't see any ungranted locks in pg_locks, nor are there any other non idle
queries this time of the night.

I'll see if I can share the function code tomorrow when people are awake again
in case we have something especially silly in there.


--
Jeff Frost, Owner       < jeff@frostconsultingllc.com>
Frost Consulting, LLC   http://www.frostconsultingllc.com/
Phone: 650-780-7908     FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                 http://www.postgresql.org/about/donate

------=_Part_62113_306616.1174037767243--