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