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:
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.