Skip site navigation (1) Skip section navigation (2)

Re: Really bad insert performance: what did I do wrong?

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Kevin White <kwhite(at)digital-ics(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Really bad insert performance: what did I do wrong?
Date: 2003-02-21 17:55:41
Message-ID: Pine.LNX.4.33.0302211049090.17876-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-performance
OK, I'm gonna make a couple of observations here that may help out.

1:  sun's performance on IDE hardware is abysmal.  Both Solaris X86 and 
Solaris Sparc are utter dogs at IDE, even when you do get DMA and prefetch 
setup and running.  Linux or BSD are much much better at handling IDE 
interfaces.

2:  Postgresql under Solaris on Sparc is about 1/2 as fast as Postgresql 
under Linux on Sparc, all other things being equal.  On 32 bith Sparc the 
chasm widens even more.

3:  Inserting ALL 700,000 rows in one transaction is probably not optimal.  
Try putting a test in every 1,000 or 10,000 rows to toss a "commit;begin;" 
pair at the database while loading.  Inserting all 700,000 rows at once 
means postgresql can't recycle the transaction logs, so you'll have 
700,000 rows worth of data in the transaction logs waiting for you to 
commit at the end.  That's a fair bit of space, and a large set of files 
to keep track of.  My experience has been that anything over 1,000 inserts 
in a transaction gains little.

4:  If you want to make sure you don't insert any duplicates, it's 
probably faster to use a unique multi-column key on all your columns 
(there's a limit on columns in an index depending on which flavor of 
postgresql you are running, but I think it's 16 on 7.2 and before and 32 
on 7.3 and up.  I could be off by a factor of two there.



In response to

Responses

pgsql-performance by date

Next:From: Kevin WhiteDate: 2003-02-21 19:18:12
Subject: Re: Really bad insert performance: what did I do wrong?
Previous:From: Andrew SullivanDate: 2003-02-21 17:37:19
Subject: Re: Really bad insert performance: what did I do wrong?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group