Re: postgresql latency & bgwriter not doing its job

On 2014-08-26 10:49:31 +0200, Fabien COELHO wrote:
>
> >What are the other settings here? checkpoint_segments,
> >checkpoint_timeout, wal_buffers?
>
> They simply are the defaults:
>
> checkpoint_segments = 3
> checkpoint_timeout = 5min
> wal_buffers = -1
>
> I did some test checkpoint_segments = 1, the problem is just more frequent
> but shorter. I also reduced wal_segsize down to 1MB, which also made it even
> more frequent but much shorter, so the overall result was an improvement
> with 5% to 3% of transactions lost instead of 10-14%, if I recall correctly.
> I have found no solution on this path.

Uh. I'm not surprised you're facing utterly horrible performance with
this. Did you try using a *large* checkpoints_segments setting? To
achieve high performance you likely will have to make checkpoint_timeout
*longer* and increase checkpoint_segments until *all* checkpoints are
started because of "time".

There's three reasons:
a) if checkpoint_timeout + completion_target is large and the checkpoint
isn't executed prematurely, most of the dirty data has been written out
by the kernel's background flush processes.

b) The amount of WAL written with less frequent checkpoints is often
*significantly* lower because fewer full page writes need to be
done. I've seen production reduction of *more* than a factor of 4.

c) If checkpoint's are infrequent enough, the penalty of them causing
problems, especially if not using ext4, plays less of a role overall.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services