Re: Postgres DB crashing

Alan Hodgson <ahodgson(at)simkin(dot)ca> writes:
> On Thursday, June 20, 2013 07:52:21 AM Merlin Moncure wrote:
>> OP needs to explore use of connection pooler, in particular pgbouncer.
>> Anyways none of this explains why the server is actually crashing.

> It might be hitting file descriptor limits. I didn't dig into the earlier part
> of this thread much, though.

The disturbing part of the original report was this:

>>> 2013-06-11 16:54:14 GMT [22226]: [1-1]PANIC: stuck spinlock (0x2aaab54279d4) detected at bufmgr.c:1239

which implies that something was holding a buffer header spinlock for an
unreasonably long time (roughly 2 minutes, when no operation that holds
such a lock should take more than a few nanoseconds). But if you were
running a load test that absolutely mashed the machine into the ground,
as the OP seems to have been doing, maybe that could happen --- perhaps
some unlucky backend got interrupted and then swapped out during the
narrow window where it held such a lock, and the machine was too
overloaded to give that process any more cycles for a very long time.

As has been noted already, this test setup seems to have overloaded the
machine by at least two orders of magnitude compared to useful settings
for the available hardware. The "stuck spinlock" error would only come
out if a lock had been held for quite a lot more than two orders of
magnitude more time than expected, though. So I'm not entirely sure
that I buy this theory; but it's hard to see another one. (I discount
the obvious other theory that there's a software bug, because I just
looked through 9.2's bufmgr.c very carefully, and there are no code
paths where it fails to release a buffer header lock within a very few
instructions from where it took the lock.)

regards, tom lane