Re: checkpoint patches

On Wed, Mar 21, 2012 at 3:38 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> It looks like I neglected to record that information for the last set
> of runs.  But I can try another set of runs and gather that
> information.

OK. On further review, my previous test script contained several
bugs. So you should probably ignore the previous set of results. I
did a new set of runs, and this time bumped up checkpoint_segments a
bit more, in the hopes of giving the cache a bit more time to fill up
with dirty data between checkpoints. Here's the full settings I used:

shared_buffers = 8GB
maintenance_work_mem = 1GB
synchronous_commit = off
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_writer_delay = 20ms
log_line_prefix = '%t [%p] '
log_checkpoints='on'
checkpoint_segments='1000'
checkpoint_sync_pause='3' # for the checkpoint-sync-pause-v1 branch only

With that change, each of the 6 tests (3 per branch) involved exactly
2 checkpoints, all triggered by time rather than by xlog. The tps
results are:

checkpoint-sync-pause-v1: 2613.439217, 2498.874628, 2477.282015
master: 2479.955432, 2489.480892, 2458.600233
writeback-v1: 2386.394628, 2457.038789, 2410.833487

The 90th percentile latency results are:

checkpoint-sync-pause-v1: 1481, 1490, 1488
master: 1516, 1499, 1483
writeback-v1: 1497, 1502, 1491

However, looking at this a bit more, I think the
checkpoint-sync-pause-v1 patch contains an obvious bug - the GUC is
supposedly represented in seconds (though not marked with GUC_UNIT_S,
oops) but what the sleep implements is actually *tenths of a second*.
So I think I'd better rerun these tests with checkpoint_sync_pause=30
so that I get a three-second delay rather than a
three-tenths-of-a-second delay between each fsync.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company