Re: Load spikes on 8.1.11

Andrew Sullivan <ajs(at)commandprompt(dot)com> writes:
> On Tue, Jul 22, 2008 at 02:41:55AM +0530, Gurjeet Singh wrote:
>> I am aware of the heavy locking involved with Slony, which should mean that
>> it blocks the application connections; that's be completely acceptable,
>> given all the warnings in the Slony docs. But what I am concerned about and
>> trying to hunt down is why <IDLE> backend processes are all consuming up all
>> of CPU (!!!) so much so that I am unable to fire up any new process!

> Ah, well, then, yes, the spinlock improvements probably will help
> you. But you should disabuse yourself of the idea that <IDLE>
> processes have no cost. You still have to talk to all those
> connections when doing schema changes.

Yeah. In fact this is sounding more and more like the known problem
with sinval message response causing a "thundering herd" effect: the
idle processes all sit idle until the sinval message queue gets long
enough to rouse alarm bells, and then they all get signaled at once
and all try to clean the message queue at once, leading to very
heavy contention for the SInvalLock. That code's been rewritten in
CVS HEAD to try to alleviate the problem, but no existing release
has the fix.

See thread here for prior report:
http://archives.postgresql.org/pgsql-performance/2008-01/msg00001.php

regards, tom lane