Re: How to find the culprit in server load spikes?

On Wed, Jul 22, 2015 at 5:50 AM, Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it>
wrote:

> Hi everyone,
> I host a Postgresql server on Ubuntu 12.04 and I am facing server load
> spikes (if I run top, it goes up to 25-30 on a 4-core system)...
> In some cases, I have to restart potgresql service because users call us
> complaining of the slowness, but in some cases I can leave things on their
> way and I see that after a bunch of minutes (about 5-10) the situations
> drops to the normality (0.50-2 load).
> The problem is, as in the most cases, the I/O, but I need a small hand to
> know some methods or tools that can help me to investigate who or what is
> causing me these spikes.
>

I always run systems starting out with logging cranked up to at least these
settings:

log_checkpoints = on
log_lock_waits = on
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '10s'
track_io_timing = on
log_autovacuum_min_duration = 1000
log_min_duration_statement = 1000 ## or less

In particular, you would want to see what the reported "sync" time is for
the checkpoint, and whether the slowness (as shown by the frequency of
statement min duration log events) is occurring in a pattern around the
beginning and end of a checkpoint.

I'd also set up vmstat to run continuously capturing output to a logfile
with a timestamp, which can later be correlated to the postgres log file
entries.

Cheers,

Jeff