Skip site navigation (1) Skip section navigation (2)

pl/pgsql function spikes CPU 100%

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: pl/pgsql function spikes CPU 100%
Date: 2007-03-16 08:52:59
Message-ID: Pine.LNX.4.64.0703160147310.11964@discord.home.frostconsultingllc.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954

Responses

pgsql-admin by date

Next:From: Shoaib MirDate: 2007-03-16 09:36:07
Subject: Re: pl/pgsql function spikes CPU 100%
Previous:From: gunartha.nyomanDate: 2007-03-16 08:31:43
Subject: how to add oids field

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group