Re: postgresql latency & bgwriter not doing its job

> off:
>
> $ pgbench -p 5440 -h /tmp postgres -M prepared -c 16 -j16 -T 120 -R 180 -L 200
> number of skipped transactions: 1345 (6.246 %)
>
> on:
>
> $ pgbench -p 5440 -h /tmp postgres -M prepared -c 16 -j16 -T 120 -R 180 -L 200
> number of skipped transactions: 1 (0.005 %)

> That machine is far from idle right now, so the noise is pretty high.

What is the OS and FS? Could it be XFS?

> But rather nice initial results.

Indeed, I can confirm:

I did 5000s 25tps tests: - Off: 8002 transactions lost (6.3%) - On: 158 transactions "lost" (0.12%).

Although it is still 13 times larger than the 12 (0.01%) lost with my 
every 0.2s CHECKPOINT hack, it is nevertheless much much better than 
before!

The bad news, under pgbench unthrottled load, the tps is divided by 2 (300 
-> 150, could have been worse), *BUT* is also much smoother, the tps is 
not going to 0, but stay in 50-100 range before the next spike.

I'm wondering about he order of operations. It seems to me that you sync 
just after giving back a buffer. Maybe it would be better to pipeline it, 
that is something like:
  round 0:    send buffers 0    sleep?
  round N:    sync buffers N-1    send buffers N    sleep?
  final N sync:    sync buffer N

I have not found how to control the checkpoint pacing interval, if there 
is such a thing. With a 200ms lag limit on pgbench, it would be nice if it 
is less than 200ms.

I found this old thread "Add basic checkpoint sync spreading" by Greg 
Smith and Simons Riggs, dating from 2010: 
http://www.postgresql.org/message-id/4CE07548.4030709@2ndquadrant.com 
https://commitfest.postgresql.org/action/patch_view?id=431 which ends up 
"returned with feedback".

-- 
Fabien.