Re: Write workload is causing severe slowdown in Production

On 22 Březen 2012, 13:38, Gnanakumar wrote:
>> There's a checkpoint_warning option. Set it to 3600 and you should get
>> messages in the log.
>
> I've a basic question about setting "checkpoint_warning" configuration.
> 8.2 doc
> (http://www.postgresql.org/docs/8.2/interactive/runtime-config-wal.html)
> says:
>
> "Write a message to the server log if checkpoints caused by the filling of
> checkpoint segment files happen closer together than this many seconds
> (which suggests that checkpoint_segments ought to be raised). The default
> is 30 seconds (30s)."
>
> How does increasing the default 30s to 3600s (which is 1 hour or 60
> minutes) print messages to the log? Even after reading the description
> from above doc, am not able to get this point clearly. Can you help me in
> understanding this?

A checkpoint may be triggered for two main reasons:

(1) all available WAL segments are filled (you do have 32 of them, i.e.
512MB of WAL data)

(2) the checkpoint_timeout runs out (by default 5 mins IIRC)

The checkpoint_warning should emmit a 'warning' message whenever the
checkpoint happens less than the specified number of seconds since the
last one, so setting it high enough should log all checkpoints.

The fact that this does not happen (no warning messages) suggests this is
not caused by checkpoints. It may be caused by the database, but it seems
unlikely it's checkpoints.

Tomas