Re: commit_delay, siblings - Mailing list pgsql-hackers

Hi,

> Simon Riggs <simon@2ndquadrant.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