On Sun, May 13, 2012 at 7:17 PM, Peter Geoghegan <pe...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
