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.4030...@2ndquadrant.com
https://commitfest.postgresql.org/action/patch_view?id=431 which ends up
"returned with feedback".
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers