Re: Producer/Consumer Issues in the COPY across network

On Tue, Feb 26, 2008 at 11:00:33AM +0000, Simon Riggs wrote:
> I'm looking at ways to reduce the number of network calls and/or the
> waiting time while we perform network COPY.
>
> The COPY calls in libpq allow asynchronous actions, yet are coded in a
> synchronous manner in pg_dump, Slony and psql \copy.

I don't think it's the synchronous/asynchronous mode that's making the
difference. Rather, usually the network stack will coalesce packets
into larger chunks to improve performance. I wonder whether it's COPY
interacting badly with the TCP_NODELAY option (which disables the
coalescing).

> When we're running a COPY over a high latency link then network time is
> going to become dominant, so potentially, running COPY asynchronously
> might help performance for loads or initial Slony configuration. This is
> potentially more important on Slony where we do both a PQgetCopyData()
> and PQputCopyData() in a tight loop.

When you check the packets being sent, are you showing only one record
being sent per packet? If so, there's your problem.

> I also note that PQgetCopyData always returns just one row. Is there an
> underlying buffering between the protocol (which always sends one
> message per row) and libpq (which is one call per row)? It seems
> possible for us to request a number of rows from the server up to a
> preferred total transfer size.

AIUI the server merely streams the rows to you, the client doesn't get
to say how many :)

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy