Re: [HACKERS] Libpq COPY optimization

[Luke Lonergan]

Title: Re: [HACKERS] Libpq COPY optimization

A note on this – we see a huge performance benefit from this change on Solaris, so much so that it should be mandatory for that platform.  Solaris’ error handling is deeper, and so the repeated redundant interrupts that this patch avoids causes 60% of the CPU to be consumed during COPY.  After this patch, this work disappears from the profile and the COPY speed is much improved.

There are also big speed improvements on Linux, on the order of 40% at high speeds.  Using Bizgres MPP, this change allowed us to increase from 47 MB/s loading to 70+ MB/s loading speed (250GB/hour).

- Luke


On 1/8/06 6:00 AM, "Alon Goldshuv" <[EMAIL PROTECTED]> wrote:

The following is a suggestion for optimizing the libpq COPY FROM call for
better performance. I submitted a similar suggestion awhile ago, but it
wasn't safe enough. This one is better. It shows a pretty significant
improvement while maintaining deadlock prevention.

The change is localized to PQputCopyData, not requiring
an alternate version of pqPutMsgEnd.

The change is that before returning, PQputCopyData would process inbound
NOTICEs *only if* the buffer was flushed.  (and drop the other
PQconsumeInput/parseInput calls in PQputCopyData)

At the end of the copy operation, libpq's caller will call
PQputCopyEnd followed by PQgetResult.  PQputCopyEnd flushes
the buffer, but I don't think we need to add any PQconsumeInput/
parseInput there, because any remaining inbound NOTICEs will be
handled immediately afterward when libpq's caller calls PQgetResult.

Alon.