Re: 9.1.3 backends getting stuck in 'startup'

Jeff Frost <jeff(at)pgexperts(dot)com> writes:
> On May 24, 2012, at 3:35 PM, Tom Lane wrote:
>> Jeff Frost <jeff(at)pgexperts(dot)com> writes:
>>> BTW, when I connected to it this time, I had a really long time before my psql was able to send a query, so it seems to be still broken at least.

>> Yeah, I was afraid that re-initdb would be at best a temporary solution.

> Oh, sorry, I wasn't clear on that. The currently running system is still happy, but the old data directory got stuck in 'startup' for a while when I connected via psql.

Hm. That means there is some contributing factor we haven't identified,
but what?

>> It would probably be useful to confirm the theory that relcache rebuild
>> is what makes it stall. You should be able to manually remove the
>> pg_internal.init file in the database's directory; then connect with
>> psql, and time how long it takes before the pg_internal.init file
>> reappears.

> So, you're thinking autovac invalidates the cache and causes it to be rebuilt, then a bunch of new connections get stalled as they all wait for the rebuild?

Well, it's worse than that: all the incoming sessions each try to
rebuild the cache file themselves. So I suspect there's some
"thundering herd" effect on your production system, in addition to
the fact that it's just plain not cheap. However, if you're seeing
significant stall with just a single incoming connection (the old
system isn't getting actively used, right?) then there is possibly
yet another effect that we need to identify.

As the next step, I'd suggest verifying that the stall is reproducible
if you remove pg_internal.init (and that it's not there otherwise), and
then strace'ing the single incoming connection to see what it's doing.

regards, tom lane