Hello all,
I'm trying to speed up some insert statements. I have been tinkering
with the postmaster and DBI parameters I did some timings on my insert
and copy commands. Here is a sample insert query:
010430.18:31:18.199 [2604] query: insert into log values
(0,0,lower('blah.blah.mydomain.com'),lower('foo'),lower('bar'),lower('blah'),upper('Me'),
upper('Myself'), upper('I'), upper('INFO'), 'String Here', '20010430
16:00:00')
Pretty straightforward. Table log looks like:
Table "log"
Attribute | Type | Modifier
--------------+-----------+----------
site_id | bigint |
host_id | bigint |
fqdn | varchar() | not null
site | varchar() | not null
region | varchar() | not null
hostname | varchar() | not null
product | varchar() | not null
class | varchar() | not null
subclass | varchar() | not null
status | varchar() | not null
msg | varchar() | not null
remote_stamp | timestamp | not null
tstamp | timestamp | not null
Here are my non-scientific timings:
with AutoCommit on, using DBI across TCP/IP: 1.3 INSERTS/second
with AutoCommit off, DBI, TCP/IP, committing after every 100: 1.6
INSERTS/second
using psql -h host -U user -c "copy log from stdin" dbname < datafile
1.73 rows/second
using COPY LOG FROM 'filename' on the db machine itself: 1.73
rows/second
Another crucial piece of information is that each insert kicks off a
trigger. I did not write the trigger, and do not know how to write
triggers, but I think that might be the contributing factor to the
slowness. Here is the text file used to create the trigger:
drop function update_host_table();
drop trigger incoming_trigger on incoming ;
create function update_host_table()
returns opaque
as 'declare
myrec record;
new_hostid int4;
begin
new.timestamp := now() ;
/* check to see if we have see this machine before */
select * into myrec
from knownhosts k
where k.fqdn = new.fqdn and
k.hostname = new.hostname ;
/* -- if we have not found the machine name we are going to
insert a new record into the knownhosts table and set the init_contact
to now
*/
if not found
then
insert into knownhosts
values (new.fqdn,new.hostname,new.timestamp,new.timestamp) ;
else
update knownhosts
set last_contact = new.timestamp
where knownhosts.fqdn = new.fqdn ;
end if ;
/* now we are going to update the status table with the new record */
select * into myrec
from status s where
s.fqdn = new.fqdn and s.hostname=new.hostname
and s.class=new.class and s.sub_class=new.sub_class ;
if not found
then
insert into status
values (new.fqdn,new.hostname,new.class,
new.sub_class,new.level,new.msg,new.timestamp) ;
else
update status
set level = new.level,
timestamp = new.timestamp
where fqdn=new.fqdn and hostname=new.hostname and
class = new.class and sub_class = new.sub_class ;
end if;
return new;
end ;'
language 'plpgsql';
create trigger incoming_trigger
before insert on incoming
for each row
execute procedure update_host_table();
1.73 INSERTS/second seems awfully slow, but maybe I have set my
expectations too high. Now that you all can see the table and the kind
of data I am trying to put into it, do you have any suggestions? The
hardware specs of the database machine are: Pentium III 733Mhz, 512
megs memory, 7 gigs free on the partition. Seems like I should be
getting a lot more horsepower. I really need to speed this up somehow.
Does anyone see anything in the trigger or otherwise that would cause
this to be so slow?
Thank you very much,
Fran
