Re: synchronous commit vs. hint bits - Mailing list pgsql-hackers

From Robert Haas
Subject Re: synchronous commit vs. hint bits
Date
Msg-id CA+TgmobJZn5xjdKMXEZsxNdnEWoTHddr0eKLq=dyh1h_0oM-tQ@mail.gmail.com
Whole thread Raw
In response to Re: synchronous commit vs. hint bits  (Simon Riggs <simon@2ndQuadrant.com>)
List pgsql-hackers
On Tue, Nov 8, 2011 at 1:59 AM, Simon Riggs <simon@2ndquadrant.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


pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: proposal: psql concise mode
Next
From: Magnus Hagander
Date:
Subject: Re: Disable OpenSSL compression