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

Re: DBD::Pg timings

From: "Jason E(dot) Stewart" <jason(at)openinformatics(dot)com>
To: "David Duff" <dduff(at)sockeye(dot)com>
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: DBD::Pg timings
Date: 2002-11-23 10:01:43
Message-ID: 87of8g4naw.fsf@openinformatics.com (view raw or flat)
Thread:
Lists: 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(at)sockeye(dot)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.



pgsql-interfaces by date

Next:From: Artur TrzewikDate: 2002-11-23 15:44:59
Subject: pgtcl has no coresponding function to PQcmdTuples
Previous:From: Tom LaneDate: 2002-11-23 02:43:54
Subject: Re: Frontend/Backend protocol changes?

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