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?
regards, tom lane
#! /bin/sh
# Expected postmaster options: -N 100 -B 1024 -c checkpoint_timeout=1800
# Recommended pgbench setup: pgbench -i -s 10 bench
for del in 0 ; do
for sib in 1 ; do
for cli in 1 10 20 30 40 50 ; do
echo "commit_delay = $del"
echo "commit_siblings = $sib"
psql -c "vacuum branches; vacuum tellers; delete from history; vacuum history; checkpoint;" bench
PGOPTIONS="-c commit_delay=$del -c commit_siblings=$sib" \pgbench -c $cli -t 100 -n bench
done
done
done
for del in 10000 30000 50000 100000 ; do
for sib in 1 5 10 20 ; do
for cli in 1 10 20 30 40 50 ; do
echo "commit_delay = $del"
echo "commit_siblings = $sib"
psql -c "vacuum branches; vacuum tellers; delete from history; vacuum history; checkpoint;" bench
PGOPTIONS="-c commit_delay=$del -c commit_siblings=$sib" \pgbench -c $cli -t 100 -n bench
done
done
done