Re: Postgres Query Plan Live Lock

On Wed, Feb 5, 2014 at 4:47 PM, Pweaver (Paul Weaver)
<pweaver(at)panjiva(dot)com> wrote:
>> That is quite extreme. If a temporary load spike (like from the deletes
>> and the hinting needed after them) slows down the select queries and you
>> start more and more of them, soon you could tip the system over into kernel
>> scheduler insanity with high system time. Once in this mode, it will stay
>> there until the incoming stream of queries stops and the existing ones clear
>> out. But, if that is what is occurring, I don't know why queries on other
>> tables would still be fast.
>
> We probably want a connection pooler anyways, but in this particular case,
> the load average is fairly low on the machine running Postrgres.

Indeed, if lack of connection pooling was the cause, I'd expect a huge
load average (around 100).

Can you post the output of "vmstat 6 10" and "iostat -x -m -d 6 10"
while the server is overloaded? (try to run them at the same time so
results can be correlated).

Also, some details on the hardware wouldn't hurt, like amount of RAM,
number of processors, kind of processor, whether it's a virtual
machine or a bare metal one, number of disks and disk configuration,
etc...