X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9671C53603 for ; Fri, 8 Apr 2005 05:53:33 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43783-07 for ; Fri, 8 Apr 2005 04:53:23 +0000 (GMT) Received: from priv-edtnes56.telusplanet.net (outbound01.telus.net [199.185.220.220]) by svr1.postgresql.org (Postfix) with ESMTP id 4C3C9535C5 for ; Fri, 8 Apr 2005 05:53:22 +0100 (BST) Received: from localhost ([199.185.220.240]) by priv-edtnes56.telusplanet.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with ESMTP id <20050408045322.HQWY9708.priv-edtnes56.telusplanet.net@localhost>; Thu, 7 Apr 2005 22:53:22 -0600 Received: from 209.17.183.249 ( [209.17.183.249]) as user a3a18850@192.168.200.1 by webmail.telus.net with HTTP; Thu, 7 Apr 2005 21:53:22 -0700 Message-ID: <1112936002.42560e420adf7@webmail.telus.net> Date: Thu, 7 Apr 2005 21:53:22 -0700 From: Mischa Sandberg To: Greg Sabino Mullane Cc: pgsql-performance@postgresql.org Subject: Re: multi-line copy (was: Re: COPY Hacks) References: <067c42864e24cd39b83c335da6adc8b5@biglumber.com> In-Reply-To: <067c42864e24cd39b83c335da6adc8b5@biglumber.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.1-cvs X-Originating-IP: 209.17.183.249 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.082 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200504/177 X-Sequence-Number: 11574 Quoting Greg Sabino Mullane : > > 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