Re: synchronous commit vs. hint bits

On Tue, Nov 8, 2011 at 1:59 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Please continue to expect that, I just haven't finished it yet...

OK.

So here's the deal: this is an effective, mostly automatic solution to
the performance problem noted in the original post. For example, at
32 clients, the original test case gives about 7800-8300 tps with
wal_writer_delay=200ms, and about 10100 tps with
wal_writer_delay=20ms. With wal_writer_delay=200ms but the patch
applied, median of three five minute pgbench runs is 9952 tps; all
three runs are under 10000 tps. So it's not quite as good as
adjusting wal_writer_delay downward, but it gives you roughly 90% of
the benefit automatically, without needing to adjust any settings.
That seems very worthwhile.

At 1 client, 8 clients, and 80 clients, the results were even better.
The patched code with wal_writer_delay=200ms slightly outperformed the
unpatched code with wal_writer_delay=20ms (and outperformed the
unpatched code with wal_writer_delay=200ms even more). It's possible
that some of that is random variation, but maybe not all of it - e.g.
at 1 client:

unpatched, wal_writer_delay = 200ms: 602, 604, 607 tps
unpatched, wal_writer_delay = 20ms: 614, 616, 616 tps
patched, wal_writer_delay = 200ms: 633, 634, 636 tps

The fact that those numbers aren't bouncing around much suggests that
it might be a real effect.

I have also reviewed the code and it seems OK.

So +1 from me for applying this.

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