Re: postgresql latency & bgwriter not doing its job

On Monday, August 25, 2014, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:

>
>
> The culprit I found is "bgwriter", which is basically doing nothing to
> prevent the coming checkpoint IO storm, even though there would be ample
> time to write the accumulating dirty pages so that checkpoint would find a
> clean field and pass in a blink. Indeed, at the end of the 500 seconds
> throttled test, "pg_stat_bgwriter" says:
>

Are you doing pg_stat_reset_shared('bgwriter') after running pgbench -i?
You don't want your steady state stats polluted by the bulk load.

>
> buffers_checkpoint = 19046
> buffers_clean = 2995
>

Out of curiosity, what does buffers_backend show?

In any event, this almost certainly is a red herring. Whichever of the
three ways are being used to write out the buffers, it is the checkpointer
that is responsible for fsyncing them, and that is where your drama is
almost certainly occurring. Writing out with one path rather than a
different isn't going to change things, unless you change the fsync.

Also, are you familiar with checkpoint_completion_target, and what is it
set to?

Cheers,

Jeff