On Fri, 2009-06-19 at 14:11 -0400, Tom Lane wrote: > Stefan Kaltenbrunner <ste...@kaltenbrunner.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
