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.