Re: 9.1.3 backends getting stuck in 'startup'

On 04/27/12 17:30, Tom Lane wrote:
> Jeff Frost <jeff(at)pgexperts(dot)com> writes:
>> and I've got 81 more that do not contain bufmgr.c and are also not block on
>> LWLockAcquire.
> Hm ... no smoking gun in what you showed so far. I also took another
> look through 9.1 bufmgr.c, and I'm darned if I can see any code path
> there that holds a buffer mapping lock for any long interval.
>
> One possible theory is that you're using pg_buffercache_pages(), which
> does take all those locks. It tries its best to not hold them long,
> but with a sufficiently large buffer cache there could be an issue.
> (What is the shared_buffers setting on this installation, anyway?)
>

Tom, what does the startup code path read? The list of relations or
something? Just wondering what the contention would be.

Oh, good idea! Looks like pg_buffercache is installed in this DB. Customer
reports that it has been installed since the server has existed (and on the
previous server) but is not currently being used, though the issue with the
hanging startups did not start until this morning. Could it still cause
contention even if it's not being executed?

shared_buffers is 8GB on this machine which has 128GB of RAM.

It seems the pooling software that's being used has a max connection lifetime
of 1hr which is what makes this so painful.

--
Jeff Frost <jeff(at)pgexperts(dot)com>
CTO, PostgreSQL Experts, Inc.
Phone: 1-888-PG-EXPRT x506
FAX: 415-762-5122
http://www.pgexperts.com/