Trigger performance problem - Mailing list pgsql-performance
From | Manuel Wenger |
---|---|
Subject | Trigger performance problem |
Date | |
Msg-id | 5A838EC3CE99804991EF9D37875C5A6F3343@exchange.ticinocom.com Whole thread Raw |
Responses |
Re: Trigger performance problem
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-performance |
We're having a performance problem with PostgresQL 8.0.2 running on RHEL3 Update 4. There is a frequently updated table logging all our ADSL customer logins which has 2 related triggers. An INSERT on that table, "calls", takes about 300ms to execute according to the logs, and the process takes up to 30% of the server CPU. When removing the triggers it drops to 10-20ms. I am posting the table structure of all the tables involved, the triggers and the indexes. This also happens when the "calls" table is empty. The "currentip" and "basicbytes" tables contain about 8000 records each. The "newest" table is always being emptied by a cron process. I am vacuuming the database daily. I really don't understand what I am missing here - what else can be optimized or indexed? Is it normal that the INSERT is taking so long? We're running PostgreSQL on a pretty fast server, so it's not a problem of old/slow hardware either. As you can see, this is pretty basic stuff when compared to what others are doing, so it shouldn't cause such an issue. Apparently I'm really missing something here... :-) Thank you everyone for your help -Manuel CREATE TABLE calls ( nasidentifier varchar(16) NOT NULL, nasport int4 NOT NULL, acctsessionid varchar(10) NOT NULL, acctstatustype int2 NOT NULL, username varchar(32) NOT NULL, acctdelaytime int4, acctsessiontime int4, framedaddress varchar(16), acctterminatecause int2, accountid int4, serverid int4, callerid varchar(15), connectinfo varchar(32), acctinputoctets int4, acctoutputoctets int4, ascendfilter varchar(50), ascendtelnetprofile varchar(15), framedprotocol int2, acctauthentic int2, ciscoavpair varchar(50), userservice int2, "class" varchar(15), nasportdnis varchar(255), nasporttype int2, cisconasport varchar(50), acctinputpackets int4, acctoutputpackets int4, calldate timestamp ) CREATE INDEX i_ip ON calls USING btree (framedaddress); CREATE INDEX i_username ON calls USING btree (username); CREATE TRIGGER trigger_update_bytes AFTER INSERT ON calls FOR EACH ROW EXECUTE PROCEDURE update_basic_bytes(); CREATE OR REPLACE FUNCTION update_basic_bytes() RETURNS "trigger" AS $BODY$ begin if (new.acctstatustype=2) then if exists(select username from basicbytes where username=new.username) then update basicbytes set inbytes=inbytes+new.acctinputoctets, outbytes=outbytes+new.acctoutputoctets, lastupdate=new.calldate where username=new.username; else insert into basicbytes (username,inbytes,outbytes,lastupdate) values (new.username,new.acctinputoctets,new.acctoutputoctets,new.calldate); end if; end if; return null; end $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER trigger_update_ip AFTER INSERT ON calls FOR EACH ROW EXECUTE PROCEDURE update_ip(); CREATE OR REPLACE FUNCTION update_ip() RETURNS "trigger" AS $BODY$ begin delete from currentip where ip is null; delete from currentip where ip=new.framedaddress; if (new.acctstatustype=1) then delete from currentip where username=new.username; delete from newest where username=new.username; insert into currentip (ip,username) values (new.framedaddress,new.username); insert into newest (ip,username) values (new.framedaddress,new.username); end if; return null; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE TABLE basicbytes ( username varchar(32) NOT NULL, inbytes int8, outbytes int8, lastupdate timestamp, lastreset timestamp ) CREATE INDEX i_basic_username ON basicbytes USING btree (username); CREATE TABLE currentip ( ip varchar(50), username varchar(50) ) CREATE INDEX i_currentip_username ON currentip USING btree (username); CREATE TABLE newest ( ip varchar(50), username varchar(50) ) CREATE INDEX i_newest_username ON newest USING btree (username);
pgsql-performance by date: