Re: 8.4 open item: copy performance regression?

Tom Lane wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On Sat, 2009-06-20 at 13:15 +0200, Stefan Kaltenbrunner wrote:
>>> 8192 6m43.203s/6m48.293s
>>> 16384 6m24.980s/6m24.116s
>>> 32768 6m20.753s/6m22.083s
>>> 65536 6m22.913s/6m22.449s
>>> 1048576 6m23.765s/6m24.645s
>
>> The rest of the patch should have had a greater effect on tables with
>> thinner rows. Your results match my expectations, though I read from
>> them that we should use 16384, since that provides some gain, not just a
>> cancellation of the regression.
>
> +1 for using 16384 (ie, max ring buffer size 16MB). Maybe even more.
> It seems likely that other cases might have an even bigger issue than
> is exhibited in the couple of test cases we have here, so we should
> leave some margin for error. Also, there's code in there to limit the
> ring buffer to 1/8th of shared buffers, so we don't have to worry about
> trashing the whole buffer arena in small configurations. Any limitation
> at all is still a step forward over previous releases as far as not
> trashing the arena is concerned.

+1. You might get away with a smaller ring with narrow tables, where
writing 16MB of data produces more than 16MB of WAL, but I don't think
it can ever be the other way round. Leaving a little bit of room for
error doesn't seem like a bad idea, though.

IIRC we experimented with an auto-tuning ring size when we worked on the
original ring buffer patch. The idea is that you start with a small
ring, and enlarge it in StrategyRejectBuffer. But that seems too risky
for 8.4.

I wonder if using the small ring showed any benefit when the COPY is not
WAL-logged? In that scenario block-on-WAL-flush behavior doesn't happen,
so the small ring might have some L2 cache benefits.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com