Re: Adding more memory = hugh cpu load

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