RE: CommitDelay performance improvement

> -----Original Message-----
> From: Tom Lane
>
> Attached are graphs from more thorough runs of pgbench with a commit
> delay that occurs only when at least N other backends are running active
> transactions.
>
> My initial try at this proved to be too noisy to tell much. The noise
> seems to be coming from WAL checkpoints that occur during a run and
> push down the reported TPS value for the particular case that's running.
> While we'd need to include WAL checkpoints to make an honest performance
> comparison against another RDBMS, I think they are best ignored for the
> purpose of figuring out what the commit-delay behavior ought to be.
> Accordingly, I modified my test script to minimize the occurrence of
> checkpoint activity during runs (see attached script). There are still
> some data points that are unexpectedly low compared to their neighbors;
> presumably these were affected by checkpoints or other system activity.
>
> It's not entirely clear what set of parameters is best, but it is
> absolutely clear that a flat zero-commit-delay policy is NOT best.
>
> The test conditions are postmaster options -N 100 -B 1024, pgbench scale
> factor 10, pgbench -t (transactions per client) 100. (Hence the results
> for a single client rely on only 100 transactions, and are pretty noisy.
> The noise level should decrease as the number of clients increases.)
>
> Comments anyone?
>

How about the case with scaling factor 1 ? i.e Could your
proposal detect lock conflicts in reality ? If so, I agree with
your proposal.

BTW there seems to be a misunderstanding about CommitDelay,
i.e

CommitDelay is completely a waste of time unless there's
an overlap of commit.

If other backends use the delay(cpu cycle) the delay is never
a waste of time totally.

Regards,
Hiroshi Inoue