Thread

RE: Slowdown problem when writing 1.7million records

Creager, Robert S <creagrs@louisville.stortek.com> — 2001-03-02T19:38:03Z

I just joined this list, so pardon if this has been suggested.

Have you tried 'COPY expafh FROM stdin', rather than inserting each record?
I'm managing a 2.5 million record import, creating a btree index on two
columns, and then vacuuming the db in 36 minutes (on an Ultra 5 - similar to
a AMD K6-2 500).  The data is being read from a 600Mb file.

I'm also using 7.1beta5 with the -F flag on the backend (prevents db server
from flushing after each transaction - can be dangerous, but the server is
faster).  I've attached a Perl script I use - the key being the putline
command.

Note that when using COPY, default values, sequences, etc. are not used.  If
you have a SERIAL field, you have to put in the incrementing values
yourself, and then use 'setval' to get thing correct again.

I apologize for the Perl script - it's not commented.  If you have trouble
understanding it, let me know and I'll spruce it up.

Robert Creager
Senior Software Engineer
Client Server Library
303.673.2365 V
303.661.5379 F
888.912.4458 P
StorageTek
INFORMATION made POWERFUL



> -----Original Message-----
> From: Stephen Livesey [mailto:ste@exact3ex.co.uk]
> Sent: Wednesday, February 28, 2001 2:20 AM
> To: Tom Lane
> Cc: pgsql-general@postgresql.org
> Subject: RE: [GENERAL] Slowdown problem when writing 
> 1.7million records
> 
> 
> 
> 
> > No, it's not.  Do you have any triggers or rules on this table that
> > you haven't shown us?  How about other tables referencing this one
> > as foreign keys?  (Probably not, if you're running an identical test
> > on MySQL, but I just want to be sure that I'm not missing 
> something.)
> 
> I have no triggers or rules.  I have only created 1 table and 
> their are no
> foreign keys.
> 
> >
> > How exactly are you writing the records?
> 
> First I read the data from a 'Powerflex' file and hold this 
> in a record set.
>      pfxstmt = pfxconn.createStatement();
>      pfxrs = pfxstmt.executeQuery("SELECT * from expafh");
> 
> I then perform a loop which writes the data to my 'Postgresql' file as
> follows:
>       stmt = conn.createStatement();
> 	while (pfxrs.next()) {
>         cmd = "INSERT INTO expafh VALUES ";
>         cmd = cmd +
> "('"+pfxrs.getString(2)+"',"+pfxrs.getString(3)+",'"+pfxrs.get
> String(4)+"','
> "+pfxrs.getString(5)+"')";
>         stmt.executeUpdate(cmd);
>         }
> 
> >
> > I have a suspicion that the slowdown must be on the client 
> side (perhaps
> > some inefficiency in the JDBC code?) but that's only a guess at this
> > point.
> >
> 
> I have used identical code for all of my testing, the only 
> changes being
> which drivers I use to access the data.
> 
> 
> Thanks
> Stephen Livesey
> 
> Legal Disclaimer:
> Internet communications are not secure and therefore Exact 
> Abacus does 
> not accept legal responsibility for the contents of this 
> message.  Any views
> or opinions presented are solely those of the author and do 
> not necessarily
> represent those of Exact Abacus unless otherwise specifically stated.
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to 
> majordomo@postgresql.org)
>