Message-ID: <4E93E562.7080800@2ndQuadrant.com>
Date: Tue, 11 Oct 2011 02:42:42 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20111004 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Adding more memory = hugh cpu load
References: <4E92F294.9010405@gmail.com> <4E92FB87.1030703@peak6.com>
	<4E932BFC.7070007@gmail.com>
In-Reply-To: <4E932BFC.7070007@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/10/2011 01:31 PM, alexandre - aldeia digital wrote:
> I drop checkpoint_timeout to 1min and turn on log_checkpoint:
>
> <2011-10-10 14:18:48 BRT >LOG:  checkpoint complete: wrote 6885 
> buffers (1.1%); 0 transaction log file(s) added, 0 removed, 1 
> recycled; write=29.862 s, sync=28.466 s, total=58.651 s
> <2011-10-10 14:18:50 BRT >LOG:  checkpoint starting: time

Sync times that go to 20 seconds suggest there's a serious problem here 
somewhere.  But it would have been better to do these changes one at a 
time:  turn on log_checkpoints, collect some data, then try lowering 
checkpoint_timeout.  A checkpoint every minute is normally a bad idea, 
so that change may have caused this other issue.

> procs -------------------memory------------------ ---swap-- 
> -----io---- --system-- -----cpu-------
>  r  b       swpd       free       buff      cache   si   so    bi    
> bo   in   cs  us sy  id wa st
> 34  0       2696    8289288     117852   38432268    0    0     8  
> 2757 2502 4148  80 20   0  0  0
> 39  1       2696    8286128     117852   38432348    0    0    24   
> 622 2449 4008  80 20   0  0  0
> 41  0       2696    8291100     117852   38433792    0    0    64   
> 553 2487 3419  83 17   0  0  0
> ...Notice that we have no idle % in cpu column.

You also have no waiting for I/O!  This is just plain strange; 
checkpoint sync time spikes with no I/O waits I've never seen before. 
System time going to 20% isn't normal either.

I don't know what's going on with this server.  What I would normally do 
in this case is use "top -c" to see what processes are taking up so much 
runtime, and then look at what they are doing with pg_stat_activity.  
You might see the slow processes in the log files by setting 
log_min_duration_statement instead.  I'd be suspicious of Linux given 
your situation though.

I wonder if increasing the memory is a coincidence, and the real cause 
is something related to the fact that you had to reboot to install it.  
You might have switched to a newer kernel in the process too, for 
example; I'd have to put a kernel bug on the list of suspects with this 
unusual vmstat output.

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