Re: COPY locking

I rebuilt with these changes and am seeing a 30-40% improvement in average performance for
my high-contention high-volume copy case. I still need to do some more testing, but this really
seems to help.

Thanks a lot Tom!

Tom Lane wrote:
>
> I have committed some changes into development sources that should
> reduce contention overhead when multiple backends are inserting into the
> same table. If you're interested in trying it out, you could pull the
> latest sources from our CVS server, or try back-patching the changes
> into 7.1.*. The relevant changes are in these files:
>
> 2001-05-12 15:58 tgl
>
> * src/: backend/access/heap/heapam.c, backend/access/heap/hio.c,
> backend/storage/buffer/bufmgr.c, backend/storage/buffer/localbuf.c,
> include/storage/bufmgr.h: Modify RelationGetBufferForTuple() so
> that we only do lseek and lock when we need to move to a new page;
> as long as we can insert the new tuple on the same page as before,
> we only need LockBuffer and not the expensive stuff. Also, twiddle
> bufmgr interfaces to avoid redundant lseeks in
> RelationGetBufferForTuple and BufferAlloc. Successive inserts now
> require one lseek per page added, rather than one per tuple with
> several additional ones at each page boundary as happened before.
> Lock contention when multiple backends are inserting in same table
> is also greatly reduced.
>
> 2001-05-10 16:38 tgl
>
> * src/: backend/commands/sequence.c,
> backend/storage/buffer/bufmgr.c, backend/storage/smgr/md.c,
> backend/storage/smgr/mm.c, backend/storage/smgr/smgr.c,
> include/storage/smgr.h: Avoid unnecessary lseek() calls by cleanups
> in md.c. mdfd_lstbcnt was not being consulted anywhere, so remove
> it and remove the _mdnblocks() calls that were used to set it.
> Change smgrextend interface to pass in the target block number (ie,
> current file length) --- the caller always knows this already,
> having already done smgrnblocks(), so it's silly to do it over
> again inside mdextend. Net result: extension of a file now takes
> one lseek(SEEK_END) and a write(), not three lseeks and a write.
>
> regards, tom lane

--
John Coers Intrinsity, Inc.
coers(at)intrinsity(dot)com Austin, Texas