Re: XLogInsert scaling, revisited

On 28.05.2013 20:48, Alvaro Herrera wrote:
> Heikki Linnakangas wrote:
>> I've been slowly continuing to work that I started last winder to
>> make XLogInsert scale better. I have tried quite a few different
>> approaches since then, and have settled on the attached. This is
>> similar but not exactly the same as what I did in the patches I
>> posted earlier.
>
> Did this go anywhere?

Thanks for asking :-). I've been fixing bitrot throughout the winter. I
just started cleaning it up again last week, and also continued with
performance testing.

Unfortunately I lost the 8-core box I used earlier to test this, to disk
failure, so I can't repeat the tests I ran earlier. However, I have
access to a new 32-core box, the attached results are from that.

I've ran a whole bunch of tests with this in various configurations, and
have no longer been able to find a case where the patch would perform
worse than what we have now. In cases where WALInsertLock is not a
bottleneck, it's a wash, but in general, write-heavy workloads benefit.
Here, I've attached the results from one test case. The test case is
inserting 10 rows in a transaction: "insert into foo select
generate_series(1,10)". I used pgbench-tools to run the tests. The data
directory was put on a RAM drive (tmpfs), to eliminate disk as the
bottleneck. The test runs were very small, only 30s each, but I don't
see much jitter in the resulting plots, so I think that's ok.

I should mention that I also had the attached spinlock patch applied in
all the runs, baseline and patched. I'm not sure how much difference it
made in this particular case, but it made a big difference in some
tests, see http://www.postgresql.org/message-id/519A938A.1070903@vmware.com.

See attached image, insert10-clients-sets.png. The red line,
"master-b142068-" is the baseline run with a 9.3 checkout of that
commitid. The other lines are with the attached patch (*), with
different numbers of "slots". Back in September, Tom was worried that I
only ran the tests with 7 slots, which happened to be almost the same as
the number of cores; this test shows the effect of the number of slots.
It's now controlled by a GUC, num_xloginsert_slots.

To my surpise, the patch seems to be beneficial in this test even with
num_xloginsert_slots=1. The green line, "xloginsert-scale-5421726-1" is
higher than the baseline between 8 - 24 clients. I did not expect that;
num_xloginsert_slots=1 doesn't allow any more concurrency than the
single WALInsertLock we have without the patch. I'm not sure what's
going on there - maybe the fact that WAL pages are now initialized by
WAL writer in the background helps - but can't complain I guess. In some
other test scenarios, I saw num_xloginsert_slots=1 to perform worse than
unpatched code, but num_xloginsert_slots=2 already shows a benefit in
all the scenarios I've tried.

The optimal setting on this box seems to be num_xloginsert_slots >= 8.
Smaller values perform worse, but raising it higher makes little difference.

Overall, I'm really pleased with the performance of this now. But I'd
appreciate it if others would run this on different hardware and
different workloads, to verify that I haven't missed something.

One little thing that's broken at the moment is commit_delay. The code
is safe as it stands, but you won't see any performance benefit from
commit_delay because it doesn't try to flush any more WAL after the
delay than before it. That shouldn't be hard to fix, just haven't gotten
around to it.

(*) actually, an older version of the patch, but I haven't changed
anything substantial since, at least not on purpose, just moved code
around and fixed comments.

- Heikki