From: Kevin White <kwhite@digital-ics.com>
To: scott.marlowe <scott.marlowe@ihs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Really bad insert performance: what did I do wrong?
Date: Fri, 21 Feb 2003 14:18:12 -0500
Message-ID: <3E567B74.5030807@digital-ics.com> (raw)
In-Reply-To: <Pine.LNX.4.33.0302211049090.17876-100000@css120.ihs.com>
References: <Pine.LNX.4.33.0302211049090.17876-100000@css120.ihs.com>
> 1: sun's performance on IDE hardware is abysmal.
OK, good to know. This box won't always be the production server: an
x86 Dell server with Linux will happen...thanks for the info.
> 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.
Wow...
> 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.
My original test was 700,000 at once...for today's, I realized that was,
um, dumb :) so I fixed it...it commits every 1,000 now...
> 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
The problem isn't inserting the dupes, but at times I need to update the
data, rather than load a new batch of it...and the rows have a "rank"
(by price)...so when one group of say 5 gets an updated row, it could
change the rank of the other 4 so all 5 need updated...so I have to do
the select first to find the other values so I can calculate the rank.
In THIS specific case, with the table empty, I don't need to do that,
but the code had been changed to do it, because normally, my table won't
be empty. This is just the initial setup of a new server...
Thanks for all the help...
It looks like the load finished...I might try turning the sync off.
Kevin
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: kwhite@digital-ics.com, scott.marlowe@ihs.com
Subject: Re: Really bad insert performance: what did I do wrong?
In-Reply-To: <3E567B74.5030807@digital-ics.com>
References: <Pine.LNX.4.33.0302211049090.17876-100000@css120.ihs.com> <3E567B74.5030807@digital-ics.com>
* 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