Re: commit_delay, siblings

Hi,

> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > Group commit is a well-documented technique for improving performance,
>
> The issue here is not "is group commit a good idea in the abstract?".
> It is "is the commit_delay implementation of the idea worth a dime?"
> ... and the evidence we have all points to the answer "NO". We should
> not let theoretical arguments blind us to this.
>
> I posted an analysis some time ago showing that under heavy load,
> we already have the effect of ganged commits, without commit_delay:
> http://archives.postgresql.org/pgsql-hackers/2002-10/msg00331.php
>
> It's likely that there is more we can and should do, but that doesn't
> mean that commit_delay is the right answer. commit_delay doesn't do
> anything to encourage ganging of writes, it just inserts an arbitrary
> delay that's not synchronized to anything, and is probably an order
> of magnitude too large anyway on most platforms.
>
> > I would ask that we hold off on their execution, at least for the
> > complete 8.1 beta performance test cycle.
>
> I'm willing to wait a week while Tatsuo runs some fresh tests. I'm
> not willing to wait indefinitely for evidence that I'm privately
> certain will not be forthcoming.
>
> regards, tom lane

Here are the results from testings I did this morning.

Summary:
The effect of commit_delay cannot be ignored. I got almost 3 times
performance differnce among different commit_delay settings.

Details:

Xeon 2.8GHz x2, HT on, mem 2GB, Ultra 320 SCSI, 15000RPM, HT on
Redhat AS 3/kernel 2.4.21( 2.4.21-9.30AXsmp)
PostgreSQL current (July 2 12:18 JST)

FS:
/dev/cciss/c0d0p3 28G 2.1G 25G 8% /
/dev/cciss/c0d0p1 985M 28M 907M 3% /boot
/dev/cciss/c0d1p1 67G 1.7G 62G 3% /data1
/dev/cciss/c0d2p1 67G 33M 64G 1% /data2
/dev/cciss/c0d3p1 67G 33M 64G 1% /data3
none 1.3G 0 1.3G 0% /dev/shm

OS & PostgreSQL binaries are on /. data is on /data1.

All postgresql.conf directives are set to defaults except:

max_connections = 512
shared_buffers = 10000

Benchmarking is done using pgbench. The test database was initialized
by following commands:
pgbench -i -s 100 test (10,000,000 rows in accounts table)

case 1: commit_delay = 0
$ time pgbench -N -c 128 -t 100 test (128 concurrent uses)
starting vacuum...end.
transaction type: Update only accounts
scaling factor: 100
number of clients: 128
number of transactions per client: 100
number of transactions actually processed: 12800/12800
tps = 47.400291 (including connections establishing)
tps = 47.509689 (excluding connections establishing)

real 4m30.065s
user 0m3.530s
sys 0m11.210s

case 2: commit_delay = 10
starting vacuum...end.
transaction type: Update only accounts
scaling factor: 100
number of clients: 128
number of transactions per client: 100
number of transactions actually processed: 12800/12800
tps = 140.024294 (including connections establishing)
tps = 141.038901 (excluding connections establishing)

real 1m31.431s
user 0m2.340s
sys 0m5.850s

case 3: commit_delay = 50
starting vacuum...end.
transaction type: Update only accounts
scaling factor: 100
number of clients: 128
number of transactions per client: 100
number of transactions actually processed: 12800/12800
tps = 137.207500 (including connections establishing)
tps = 138.083489 (excluding connections establishing)

real 1m33.312s
user 0m2.790s
sys 0m6.490s

case 4: commit_delay = 100
starting vacuum...end.
transaction type: Update only accounts
scaling factor: 100
number of clients: 128
number of transactions per client: 100
number of transactions actually processed: 12800/12800
tps = 133.458149 (including connections establishing)
tps = 134.298841 (excluding connections establishing)

real 1m35.931s
user 0m2.750s
sys 0m7.030s

As you can see commit_delay = 10 outperforms commit_delay = 0 by 3
times.
--
Tatsuo Ishii