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@intrinsity.com  Austin, Texas