From: Jason E. Stewart <jason@openinformatics.com>
To: David Duff <dduff@sockeye.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: DBD::Pg timings
Date: 23 Nov 2002 03:01:43 -0700
Message-ID: <87of8g4naw.fsf@openinformatics.com> (raw)
In-Reply-To: <KDELIMLFNCKLNDCMBNIIEEMACFAA.dduff@sockeye.com>
References: <KDELIMLFNCKLNDCMBNIIEEMACFAA.dduff@sockeye.com>
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.
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 using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: jason@openinformatics.com, dduff@sockeye.com
Subject: Re: DBD::Pg timings
In-Reply-To: <87of8g4naw.fsf@openinformatics.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