pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: DBD::Pg timings
2+ messages / 1 participants
[nested] [flat]
* Re: DBD::Pg timings
@ 2002-11-21 17:06 Jason E. Stewart <jason@openinformatics.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jason E. Stewart @ 2002-11-21 17:06 UTC (permalink / raw)
To: Pete Leonard <pete@hero.com>; +Cc: dbi-dev@perl.org; pgsql-interfaces
Hey Pete,
Ah, dammit, thanks for the advice.
This is no longer a DBI question, so I appologize for posting it back
to the list (but I thought it would be nice to get this archived for
google's sake).
Isn't there a nicer way to turn off indexing during a big insert other
than dropping all the indexes?
Cheers,
jas.
"Pete Leonard" <pete@hero.com> writes:
> Remove all indices on the tables you're inserting on, and add them once
> you're done.
>
> I was in the same boat, inserting 1.5M records into a simple table - it
> was crawling along at 10 rows/sec before I did this, 100 rows/sec
> afterwards. And re-creating the indicies only takes a couple of minutes
> after the fact.
>
> On 21 Nov 2002, Jason E. Stewart wrote:
>
> > Hey all,
> >
> > I'd be grateful if someone could give me a reality check. I have 250k
> > rows I want to insert into Postgres using a simple Perl script and
> > it's taking *forever*. According to my simple timings, It seems to be
> > only capable of handling about 5,000 rows/hr!!! This seems
> > ridiculous. This is running on a pretty speedy dual processor P4, and
> > it doesn't seem to have any trouble at all with big selects.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: DBD::Pg timings
@ 2002-11-23 10:01 Jason E. Stewart <jason@openinformatics.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jason E. Stewart @ 2002-11-23 10:01 UTC (permalink / raw)
To: David Duff <dduff@sockeye.com>; +Cc: pgsql-interfaces
Hey All,
I'd like to start off by saying that I've happily resolved my insert
problems. The conclusion was extra baggage in un-needed constraints
and triggers, especially triggers.
So, thanks everyone (especially Tom Lane) for helping me figure this
out.
"David Duff" <dduff@sockeye.com> writes:
> i timed inserts of 100k records using the following three techniques:
>
> 1. row-at-a-time insert using a prepared insert statement.
I timed 250k records only using 1.
> results:
>
> 1. 1053 records per second
- 647 records per second
After I figured out that most of my problem was a trigger, and
whittled it down to the bare essentials.
> general conclusion:
Be careful of your triggers.
I removed a foreign key constraint from my table (kept the column,
removed the constraint as it was already satisfied by another trigger
I didn't need two triggers for the same job).
I also removed one of the two inserts from the trigger.
> details:
>
> this was postgres running on solaris.
mine was a dual 1.8G Athlon box running debian linux.
> table had six fields - three varchar(32)'s, two integers, and a float.
> string values inserted were short - ~5-8 bytes.
>
> there was one unique index defined on the table.
table had 21 fields but I was only inserting into the 5 not null
fields: 2 varchar(128)'s, 2 name's, and a bigint.
There are two unique indices on the table.
> autocommit was off. a single $dbh->commit was done in each test
yup.
Also, I had a little extra baggage:
* All inserts were done in a loop reading values out of a perl 2D
array. Before each insert the primary key value was selected out of a
sequence in the DB.
* Then there was the trigger on INSERT for each row that inserted a
timestamp into an audit table.
So given my extra unique index, the select on the sequence and the
trigger doing an extra INSERT, I'm pretty happy that I'm seeing 60% of
what you were getting.
Conclusions:
- watch your triggers.
This now seems idiotically obvious now that I've gone through all this
(and I feel dumb for potentially wasting a lot of people's time), but
I was getting what I thought was reasonable performance for small
inserts (<1000 rows) but when I tried inserting something really big
for a change everything seemed to fall apart.
I'd just like to repeat:
Thanks everyone for helping me figure this out.
Cheers,
jas.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-11-23 10:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-21 17:06 Re: DBD::Pg timings Jason E. Stewart <jason@openinformatics.com>
2002-11-23 10:01 Re: DBD::Pg timings Jason E. Stewart <jason@openinformatics.com>
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