I've done another set of tests, and effectively it seems that, with
unlogged tables, the checkpoint_completion_target does not influence
the final results.
I've increased the test duration in order to include several
checkpoints within each run.
First of all, initialization of the database:
% pgbench -i -s 300 -F 100 --foreign-keys --unlogged-tables -h
127.0.0.1 -U luca pgbench
Then the test I ran, six time after a restart between a batch and the other:
% pgbench -T 720 -j 4 -c 4 -h 127.0.0.1 -U luca pgbench
The average tps results always around 795, so I believe in the
previous batch of tests I was misleaded by a few wrong numbers that
made the average floating up and down.
The following is a detail about the runs. Note that this has been run
with the same external database activity as in the previous bunch of
tests.
# First batch
name | setting
------------------------------+---------
checkpoint_completion_target | 0.5
checkpoint_timeout | 300
shared_buffers | 131072
runs = 765, 807, 781, 799, 822, 796
avg = 795 tps
Sample checkpoint log:
08:43:06 LOG: checkpoint starting: time
08:43:07 LOG: checkpoint complete: wrote 13 buffers (0.0%); 0 WAL
file(s) added, 0 removed, 0 recycled; write=1.304 s, sync=0.032 s,
total=1.368 s; sync files=17, longest=0.006 s, average=0.001 s;
distance=9426 kB, estimate=9907 kB
# Second batch
name | setting
------------------------------+---------
checkpoint_completion_target | 0.1
checkpoint_timeout | 300
shared_buffers | 131072
runs = 810, 777, 808, 774, 806, 798
avg = 795 tps
Sample checkpoint log:
09:34:54 LOG: checkpoint starting: time
09:34:55 LOG: checkpoint complete: wrote 12 buffers (0.0%); 0 WAL
file(s) added, 1 removed, 0 recycled; write=1.204 s, sync=0.105 s,
total=1.363 s; sync files=13, longest=0.049 s, average=0.008 s;
distance=9431 kB, estimate=9716 kB
# Third batch
name | setting
------------------------------+---------
checkpoint_completion_target | 0.9
checkpoint_timeout | 300
shared_buffers | 131072
runs = 809, 801, 772, 797, 789, 795
avg = 793 tps
Sample checkpoint log:
12:17:28 LOG: checkpoint starting: time
12:17:32 LOG: checkpoint complete: wrote 39 buffers (0.0%); 0 WAL
file(s) added, 0 removed, 0 recycled; write=3.914 s, sync=0.004 s,
total=3.927 s; sync files=11, longest=0.001 s, average=0.000 s;
distance=393 kB, estimate=7606 kB