"Robert John Shepherd" <robert@reviewer.co.uk> writes:
> Nothing really. But I have noticed some very odd behaviour, the website
> seems very happy for long periods, with no more than 8-10 postgresql
> processes running. Then for no apparent reason, the number of processes
> increases dramatically to the maximum and they seem to all be either
> idle or sleeping, none are consuming any cpu at all.
Two plausible theories:
1. Something wacko in client-side connection pooling logic, causing
new connections to be opened when not really necessary.
2. Client transactions are all waiting on a lock held by some
transaction that's not doing anything but not releasing the lock either.
It's rather difficult to debug case #2 in current releases, because
there's no easy way to look at the locking status. (In 7.3 there's
a new pg_locks system view that allows you to determine exactly who's
waiting for whom and why. Dunno if you'd like to try your site on
7.3rc1, but the option is there.) One thing you can look at is "ps"
status: if there is one guy who's "idle in transaction" and the others
are all "SELECT waiting" (or any-operation waiting), then it's a good
bet that the first one has got the lock the others are waiting for.
regards, tom lane
