From: Ron Johnson <ron.l.johnson@cox.net>
To: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: Re: Perfomance Tuning
Date: 13 Aug 2003 05:33:47 -0500
Message-ID: <1060770827.30778.130.camel@haggis> (raw)
In-Reply-To: <1f8901c36166$b50cf6b0$2800a8c0@mars>
References: <Pine.LNX.4.44.0308130819460.2191-100000@zigo.dhs.org>
<1f8901c36166$b50cf6b0$2800a8c0@mars>
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 |
+---------------------------------------------------------------+
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: ron.l.johnson@cox.net
Subject: Re: Perfomance Tuning
In-Reply-To: <1060770827.30778.130.camel@haggis>
References: <Pine.LNX.4.44.0308130819460.2191-100000@zigo.dhs.org> <1f8901c36166$b50cf6b0$2800a8c0@mars> <1060770827.30778.130.camel@haggis>
* 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