Skip site navigation (1) Skip section navigation (2)

Re: Producer/Consumer Issues in the COPY across network

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Producer/Consumer Issues in the COPY across network
Date: 2008-02-26 11:29:53
Message-ID: 20080226112953.GC14945@svana.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2008-02-26 11:31:38
Subject: Re: pg_dump additional options for performance
Previous:From: Simon RiggsDate: 2008-02-26 11:22:56
Subject: Re: pg_dump additional options for performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group