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 78A9832A791 for ; Wed, 20 Oct 2004 18:12:07 +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 53445-06 for ; Wed, 20 Oct 2004 17:11:58 +0000 (GMT) Received: from rambo.iniquinet.com (rambo.iniquinet.com [69.39.89.10]) by svr1.postgresql.org (Postfix) with ESMTP id 7176E32A1BB for ; Wed, 20 Oct 2004 18:11:54 +0100 (BST) Received: (qmail 6077 invoked by uid 1010); 20 Oct 2004 12:45:37 -0400 Received: from Robert_Creager@LogicalChaos.org by rambo.iniquinet.com by uid 1002 with qmail-scanner-1.20 (clamscan: 0.70. spamassassin: 2.64. Clear:RC:0(63.147.78.131):SA:0(-4.6/6.0):. Processed in 1.281667 secs); 20 Oct 2004 16:45:37 -0000 Received: from unknown (HELO thunder.mshome.net) (perl?test@logicalchaos.org@63.147.78.131) by rambo.iniquinet.com with AES256-SHA encrypted SMTP; 20 Oct 2004 12:45:36 -0400 Received: from localhost.localdomain (thunder.mshome.net [192.168.0.250]) by thunder.mshome.net (Postfix) with ESMTP id C1C2639C9; Wed, 20 Oct 2004 10:45:31 -0600 (MDT) Received: from logicalchaos.org (thunder.mshome.net [192.168.0.250]) by thunder.mshome.net (Postfix) with SMTP id 4303931CF; Wed, 20 Oct 2004 10:45:28 -0600 (MDT) Date: Wed, 20 Oct 2004 10:45:27 -0600 From: Robert Creager To: Rod Taylor Cc: Brock Henry , Postgresql Performance Subject: Re: Insert performance, what should I expect? Message-ID: <20041020104527.14927c06@thunder.mshome.net> In-Reply-To: <1098238348.747.20.camel@home> References: <97b3fe2041019185337a8c3d8@mail.gmail.com> <1098238348.747.20.camel@home> Organization: Starlight Vision, LLC. X-Mailer: Sylpheed-Claws 0.9.12a (GTK+ 1.2.10; i586-mandrake-linux-gnu) Mime-Version: 1.0 Content-Type: multipart/signed; protocol="application/pgp-signature"; micalg="pgp-sha1"; boundary="Signature=_Wed__20_Oct_2004_10_45_27_-0600_FSwak.CH9e/r+FHK" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/323 X-Sequence-Number: 8799 --Signature=_Wed__20_Oct_2004_10_45_27_-0600_FSwak.CH9e/r+FHK Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline Content-Transfer-Encoding: 7bit When grilled further on (Tue, 19 Oct 2004 22:12:28 -0400), Rod Taylor confessed: > > I've done some manual benchmarking running my script 'time script.pl' > > I realise my script uses some of the time, bench marking shows that > > %50 of the time is spent in dbd:execute. > > > 1) Drop DBD::Pg and switch to the Pg driver for Perl instead (non-DBI > compliant) which has functions similar to putline() that allow COPY to > be used. COPY can be used with DBD::Pg, per a script I use: $dbh->do( "COPY temp_obs_$band ( $col_list ) FROM stdin" ); $dbh->func( join ( "\t", @data ) . "\n", 'putline' ); $dbh->func( "\\.\n", 'putline' ); $dbh->func( 'endcopy' ); With sets of data from 1000 to 8000 records, my COPY performance is consistent at ~10000 records per second. Cheers, Rob -- 10:39:31 up 2 days, 16:25, 2 users, load average: 2.15, 2.77, 3.06 Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004 --Signature=_Wed__20_Oct_2004_10_45_27_-0600_FSwak.CH9e/r+FHK Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux) iEYEARECAAYFAkF2licACgkQLQ/DKuwDYzkCTQCfTAdnx0jDOUE3N2PyTesJcIgQ AvIAnitYXOsWZpmcQJGXM1rOFCJ+4p8y =ysQM -----END PGP SIGNATURE----- --Signature=_Wed__20_Oct_2004_10_45_27_-0600_FSwak.CH9e/r+FHK--