On Mon, Dec 6, 2010 at 4:40 AM, Rob Wultsch <wultsch@xxxxxxxxx> wrote: > Manual: http://www.postgresql.org/docs/9.0/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS > Recent discussion: > http://www.facebook.com/notes/mysql-at-facebook/group-commit-in-postgresql/465781235932 > > It is my understanding that group commit in PG works without the > commit_delay or commit_siblings being enabled. For many people coming > from other databases, the existence of these GUC seems to suggest that > group commit does not work without the being enabled. > > Are these setting useful, and if so how should they be tuned? > If they are generally are not useful, should these settings be removed? > > -- > Rob Wultsch > wultsch@xxxxxxxxx > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > Hey Rob, I think I can explain this with the bit that I understand. When you try to commit a transaction it will sync the WAL buffer to disk. Here an optimization is done that before it syncs the disks it tries to find all the WAL Records in the buffer completed and ready to sync and it absorbs all the syncs together and does the commit for all of them together. What the GUC parameter commit_delay adds that before it syncs it sleeps for that period and then does the sync. (NOTE: The transaction is not committed till it syncs so this adds that latency to all transactions) The benefit is that when number of transactions are high, while its sleeping someone who committs will also sync its records and when it awakes it doesnt have to do its own sync or if it does it helps others. The commit_siblings = 5 basically checks that it sleeps only when that many backends are active. This I think is a very expensive check and I would rather make commit_siblings=0 (which the current code does not support.. it only supports minimum of 1) The check is expensive irrespective of the settings .. But anyway here is the real kicker. In all the tests I did with recent verions 8.4 and version 9.0 , it seems that the default behavior handles the load well enough and one does not have to use commit_delay at all. Since when the load is very high all of them are basically in sync phase and the desired thing happens anyway. Infact using commit_delay will actually add the cost of doing commit_siblings check and can hurt the performance by increasing CPU consumption.. Doing commit_siblings check for every transaction is a killer since it does not return after meeting the minimum backends and goes through every backend to calculate the total number before comparing with the minimum. This is probably why most people see a drop in performance when using commit_delay compared to the default. Anyway I would recommended right now to stick with the default and not really use it. It does the sync absorbtion well if you have two many users (though not perfect). Regards, Jignesh -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
