X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2EAD0D1C4CD for ; Wed, 13 Aug 2003 10:33:56 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 00314-07 for ; Wed, 13 Aug 2003 07:32:23 -0300 (ADT) Received: from lakemtao01.cox.net (lakemtao01.cox.net [68.1.17.244]) by svr1.postgresql.org (Postfix) with ESMTP id 3A999D1C4CA for ; Wed, 13 Aug 2003 07:33:45 -0300 (ADT) Received: from lhosts ([68.11.66.83]) by lakemtao01.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030813103348.CILA7142.lakemtao01.cox.net@lhosts> for ; Wed, 13 Aug 2003 06:33:48 -0400 Subject: Re: Perfomance Tuning From: Ron Johnson To: PgSQL Performance ML In-Reply-To: <1f8901c36166$b50cf6b0$2800a8c0@mars> References: <1f8901c36166$b50cf6b0$2800a8c0@mars> Content-Type: text/plain Message-Id: <1060770827.30778.130.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.3 Date: 13 Aug 2003 05:33:47 -0500 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/230 X-Sequence-Number: 3047 On Wed, 2003-08-13 at 01:47, Christopher Kings-Lynne wrote: > > So, group the inserts in transactions with maybe 1000 commands each. It > > will go much faster. It can then cache the rows and in the end just make > > sure all 1000 have been written out on disk. > > More than that, he should be using COPY - it's 10x faster than even grouped > inserts. I have a table which has a foreign key reference to a properly indexed table, and needed to load 15GB of uncompressed data into that table. Since the machine is minimal (60GB 5400RPM IDE HDD, 1GB RAM, 1GHz Athlon), to save precious disk space, I had the data compressed into 22 files totaling 641GiB. The records are approximately 275 bytes in size. Also, because date transformations needed to be made, I had to 1st insert into a temp table, and insert from there into the main table. Thus, in essence, I had to insert each record twice. So, in 8:45 (not 8 minutes 45 seconds!, decompressed 641MiB worth of 96% compressed files, inserted 30M rows, and inserted 30M rows again, while doing foreign key checks to another table. And the data files plus database are all on the same disk. Pretty impressive: 1,920 inserts/second. for f in ltx_*unl.gz; do psql test1 -c "truncate table t_lane_tx2;" ; (zcat $f | sed "s/\"//g" | \ psql test1 -c "copy t_lane_tx2 from stdin delimiter ',';"); time psql -a -f sel_into_ltx.sql -d test1 ; done -- +---------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "Man, I'm pretty. Hoo Hah!" | | Johnny Bravo | +---------------------------------------------------------------+