Re: Optimize for insertions? - Mailing list pgsql-general

From wsheldah@lexmark.com
Subject Re: Optimize for insertions?
Date
Msg-id 200111262205.RAA15357@interlock2.lexmark.com
Whole thread Raw
In response to Optimize for insertions?  ("Gurupartap Davis" <partap@yahoo.com>)
List pgsql-general

Hi,

What you probably want to do is export the mysql data into delimited text files
and use Postgresql's COPY command. It's much faster than doing straight
INSERT's.

If you have to do inserts for some reason, it might be helpful to group them
into chunks of N insert operations each, and put each chunk in a transaction.
I'm not sure what a good value for N would be though, and this wouldn't be
nearly as fast as the COPY command in any case.

HTH,

Wes Sheldahl



"Gurupartap Davis" <partap%yahoo.com@interlock.lexmark.com> on 11/26/2001
04:17:00 PM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] Optimize for insertions?



Hi,

I'm trying to migrate a mysql database to  postgresql.  I've got the tables all
set up, but inserting the data from  the mysql dumps is taking forever.  I've
got about 200 million rows in the  big table (growing by about 1.5 million per
day), but at the 100  inserts/second I'm getting, this will take over 3 weeks.
MySQL on the  same machine averages about 1100-1200 inserts/second (no, I'm not
running them  both at the same time ;-)

Any thoughts on how to tweak the postmaster for  quick inserts?  I've got fsync
turned off, but that's about all I've tried  so far.

I'm running postgresql 7.1.3 on Linux (Redhat  7.2 with ext3) with a  700MHz
processor, 256MB RAM, and an 80GB IDE  HD

Thanks
-partap





pgsql-general by date:

Previous
From: David A Dickson
Date:
Subject: Pl/Pgsql triger procedure calls with parameters
Next
From: "Gregory Wood"
Date:
Subject: Re: Optimize for insertions?