Re: commit_delay, siblings
От | Tatsuo Ishii |
---|---|
Тема | Re: commit_delay, siblings |
Дата | |
Msg-id | 20050702.151431.115906254.t-ishii@sra.co.jp обсуждение исходный текст |
Ответ на | Re: commit_delay, siblings (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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
В списке pgsql-hackers по дате отправления: