Message-ID: <4BBCFF04.2000508@2ndquadrant.com>
Date: Wed, 07 Apr 2010 17:54:12 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Craig James <craig_james@emolecules.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Occasional giant spikes in CPU load
References: <k2pda0294261004061437p9cd3810dqe9ce528b6ddb0873@mail.gmail.com>
	<m2m603c8f071004071413w1f2d2497hbbe07d9c6491848f@mail.gmail.com>
	<4BBCFB12.1010306@emolecules.com>
In-Reply-To: <4BBCFB12.1010306@emolecules.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Craig James wrote:
> I managed to capture one such event using top(1) with the "batch" 
> option as a background process.  See output below

You should add "-c" to your batch top capture, then you'll be able to 
see what the individual postmaster processes are actually doing when 
things get stuck.

> max_connections = 1000
> shared_buffers = 2000MB
> work_mem = 256MB
> Mem:   8194800k total,  8118688k used,    76112k free,       36k buffers
> Swap:  2031608k total,   169348k used,  1862260k free,  7313232k cached

These settings appear way too high for a server with 8GB of RAM.  I'm 
not sure if max_connections is too large, or if it's work_mem that's too 
big, but one or both of them may need to be tuned way down from where 
they are now to get your memory usage under control.  Your server might 
running out of RAM during the periods where it becomes 
unresponsive--that could be the system paging stuff out to swap, which 
isn't necessarily a high user of I/O but it will block things.  Not 
having any memory used for buffers is never a good sign.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us