Re: Partitioned checkpointing

Hello Takashi-san,

> I've noticed that the behavior in 'checkpoint_partitions = 1' is not the
> same as that of original 9.5alpha2. Attached
> 'partitioned-checkpointing-v3.patch' fixed the bug, thus please use it.

I've done two sets of run on an old box (16 GB, 8 cores, RAID1 HDD)
with "pgbench -M prepared -N -j 2 -c 4 ..." and analysed per second traces
(-P 1) for 4 versions : sort+flush patch fully on, sort+flush patch full
off (should be equivalent to head), partition patch v3 with 1 partition
(should also be equivalent to head), partition patch v3 with 16
partitions.

I ran two configurations :

small:
shared_buffers = 2GB
checkpoint_timeout = 300s
checkpoint_completion_target = 0.8
pgbench's scale = 120, time = 4000

medium:
shared_buffers = 4GB
max_wal_size = 5GB
checkpoint_timeout = 30min
checkpoint_completion_target = 0.8
pgbench's scale = 300, time = 7500

* full speed run performance

average tps +- standard deviation (percent of under 10 tps seconds)

small medium
1. flush+sort : 751 +- 415 ( 0.2) 984 +- 500 ( 0.3)
2. no flush/sort : 188 +- 508 (83.6) 252 +- 551 (77.0)
3. 1 partition : 183 +- 518 (85.6) 232 +- 535 (78.3)
4. 16 partitions : 179 +- 462 (81.1) 196 +- 492 (80.9)

Although 2 & 3 should be equivalent, there seems to be a lower performance
with 1 partition, but it is pretty close and it may not be significant.

The 16 partitions seems to show significant lower tps performance,
especially for the medium case. Although the stddev is a little bit better
for the small case, as suggested by the lower off-line figure, but
relatively higher with the medium case (stddev = 2.5 * average).

There is no comparision with the flush & sort activated.

* throttled performance (-R 100/200 -L 100)

percent of late transactions - above 100 ms or not even started as the
system is much too behind schedule.

small-100 small-200 medium-100
1. flush+sort : 1.0 1.9 1.9
2. no flush/sort : 31.5 49.8 27.1
3. 1 partition : 32.3 49.0 27.0
4. 16 partitions : 32.9 48.0 31.5

2 & 3 seem pretty equivalent, as expected. The 16 partitions seem to
slightly degrade availability on average. Yet again, no comparison with
flush & sort activated.

From these runs, I would advise against applying the checkpoint
partitionning patch: there is no consistent benefit on the basic harware
I'm using on this test. I think that it make sense, because fsyncing
random I/O several times instead of one has little impact.

Now, once I/O are not random, that is with some kind of combined patch,
this is another question. I would rather go with Andres suggestion to
fsync once per file, when writing to a file is completed, because
partitionning as such would reduce the effectiveness of sorting buffers.

I think that it would be interesting if you could test the sort/flush
patch on the same high-end system that you used for testing your partition
patch.

--
Fabien.