Re: Group commit and commit delay/siblings

On Mon, Dec 6, 2010 at 4:40 AM, Rob Wultsch <wultsch@gmail.com> 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@gmail.com
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> 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