Re: Why do we still have commit_delay and commit_siblings?

On Sun, May 13, 2012 at 7:17 PM, Peter Geoghegan <peter(at)2ndquadrant(dot)com> wrote:
> Have I missed something? Why do we keep around this foot-gun that now
> appears to me to be at best useless and at worst harmful? I can see
> why the temptation to keep this setting around used to exist, since it
> probably wasn't too hard to get good numbers from extremely synthetic
> pgbench runs, but I cannot see why the new adaptive implementation
> wouldn't entirely shadow the old one even in that situation.

It seems that, with the new code, when there are a lot of people
trying to commit very frequently, they tend to divide themselves into
two gangs: everybody in one gang commits, then everyone in the other
gang commits, then everyone in the first gang commits again, and so
on. Assuming that the transactions themselves require negligible
processing time, this provides 50% of the theoretically optimum
throughput.

For example, with two backends, one transaction commits first, and the
other transaction must now wait for its WAL flush to complete before
initiating its own flush. And it will start its flush the very
instant the first transaction completes, before there is adequate time
for that backend to complete another transaction. Of course, by the
time it finishes its flush, the other transaction will be ready again,
so the flush will ping-pong back and forth between those two backends
forever, and they will never manage to group commit.

Now, potentially, these settings solve this problem, by letting the
first backend wait just a very little while for the second backend to
also be ready to commit, so that every head rotation commits a
transaction in each backend, rather than a transaction in one backend
or the other. I'm not sure if it can actually be made to work, but
I'm not willing to assume that it can't on the basis of a theoretical
argument not involving actual testing.

If we get to the point that the commit performance with 100 concurrent
clients is 100x the single-client performance rather than 50x the
single-client performance, then obviously these settings will have
outlived their usefulness. But we're not there yet, so we probably
only want to remove these settings if we can demonstrate that they are
useless.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company