Re: 8.4 open item: copy performance regression?

On Fri, 2009-06-19 at 14:11 -0400, Tom Lane wrote:
> Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> writes:
> > ok after a bit of bisecting I'm happy to announce the winner of the contest:
> > http://archives.postgresql.org/pgsql-committers/2008-11/msg00054.php
>
> > this patch causes a 25-30% performance regression for WAL logged copy,
> > however in the WAL bypass case (maybe that was what got tested?) it
> > results in a 20% performance increase.
>
> Hmm. What that patch actually changes is that it prevents a bulk insert
> (ie COPY in) from trashing the entire shared-buffers arena. I think the
> reason for the WAL correlation is that once it's filled the ring buffer,
> creating new pages requires writing out old ones, and the
> WAL-before-data rule means that the copy process has to block waiting
> for WAL to go down to disk before it can write. When it's allowed to
> use the whole arena there is more chance for some of that writing to be
> done by the walwriter or bgwriter. But the details are going to depend
> on the platform's CPU vs I/O balance, which no doubt explains why some
> of us don't see it.
>
> I don't think we want to revert that patch --- not trashing the whole
> buffer arena seems like a Good Thing from a system-wide point of view,
> even if it makes individual COPY operations go slower. However, we
> could maybe play around with the tradeoffs a bit. In particular it
> seems like it would be useful to experiment with different ring buffer
> sizes. Could you try increasing the ring size allowed in
> src/backend/storage/buffer/freelist.c for the BULKWRITE case

Yes, that's definitely the cause. The ring buffer technique was
prototyped in 8.3 and a ring of 32 blocks was found not to be
sufficient, which was one reason we didn't try to commit that then.
At the time, I also proposed a "filled buffer list" change to bufmgr to
allow bgwriter to preferentially target COPY's filled blocks, which
would also help with this effect.

You'll get better benefit from decreasing wal_writer_delay also.

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