pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mischa Sandberg <mischa.sandberg@telus.net>
To: Greg Sabino Mullane <greg@turnstep.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: multi-line copy (was: Re: COPY Hacks)
Date: Thu,  7 Apr 2005 21:53:22 -0700
Message-ID: <1112936002.42560e420adf7@webmail.telus.net> (raw)
In-Reply-To: <067c42864e24cd39b83c335da6adc8b5@biglumber.com>
References: <067c42864e24cd39b83c335da6adc8b5@biglumber.com>


Quoting Greg Sabino Mullane <greg@turnstep.com>:

> > Using COPY ... FROM STDIN via the Perl DBI (DBD::Pg) interface,
> > I accidentally strung together several \n-terminated input lines,
> > and sent them to the server with a single "putline".
> ...
> > So. Is it a feechur? Worth stress-testing? Could be VERY cool.
> 
> As explained elsewhere, not really a feature, more of a side-effect.
> Keep in mind, however, that any network round-trip time saved has to
> be balanced against some additional overhead of constructing the
> combined strings in Perl before sending them over. Most times COPY
> is used to parse a newline-separated file anyway. If you have a slow
> network connection to the database, it *might* be a win, but my
> limited testing shows that it is not an advantage for a "normal"
> connection: I added 1 million rows via COPY using the normal way
> (1 million pg_putline calls), via pg_putline of 1000 rows at a
> time, and via 10,000 rows at a time. They all ran in 22 seconds,
> with no statistical difference between them. (This was the "real" time,
> the system time was actually much lower for the combined calls).
> 
> It can't hurt to test things out on your particular system and see
> if it makes a real difference: it certainly does no harm as long as
> you make sure the string you send always *end* in a newline.

Many thanks for digging into it.

For the app I'm working with, the time delay between rows being posted 
is /just/ enough to exceed the TCP Nagle delay, so every row goes across
in its own packet :-( Reducing the number of network roundtrips 
by a factor of 40 is enough to cut elapsed time in half.
The cost of join("",@FortyRows), which produces a 1-4K string, is what's
negligible in this case.

-- 
"Dreams come true, not free" -- S.Sondheim, ITW




Message-ID: <1112936002.42560e420adf7@webmail.telus.net>
Permalink:  ../1112936002.42560e420adf7@webmail.telus.net/
Also on:    postgresql.org/message-id/1112936002.42560e420adf7@webmail.telus.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mischa.sandberg@telus.net, greg@turnstep.com
  Subject: Re: multi-line copy (was: Re: COPY Hacks)
  In-Reply-To: <1112936002.42560e420adf7@webmail.telus.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox