Re: 8.4 open item: copy performance regression?

On Sat, 2009-06-20 at 13:15 +0200, Stefan Kaltenbrunner wrote:
> >
> > 8.3.7: 0m39.266s 0m43.269s (alan: 36.2 - 39.2)
> >
> > 8192: 0m40.715s 0m42.480s
> > 16384: 0m41.318s 0m42.118s
> > 65536: 0m41.675s 0m42.955s
>
> hmm interesting - I just did a bunch of runs using the lineitem table
> from the DBT3 tests (loading 60M rows in each run) and the same config
> Alan used.
>
> 8.4(postpatch - not RC1 but that one seems to behave exactly the same way)
>
> lineitem1
> 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

> 8.3
>
> 6m45.650s/6m44.781s

> so on this workload the sweetspot seems to be much higher than on the
> one with the narrower rows.

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.

I would suggest that we leave it as a tunable parameter in this release
and remove it again once we have clear evidence of how to set it. We are
unlikely to cover conclusively how to do this before we release 8.4.

> >
> > In any case, a bump of the ring multiplier to either 4096 or 8192
> > eliminates the worst of the regression here, good improvement so far.
>
> yeah with the above numbers I would say that 8192 should remove most if
> not all of the regression. However it seems that we might have to make
> this more dynamic in the future since the behaviour seems to depend on a
> number of variables...

I would be inclined to repeat this with multiple concurrent COPYs. We
may be able to improve on the numbers there, as well as get a
perspective on how to set parameter in real world.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support