Обсуждение: Trigger performance problem

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);

"Manuel Wenger" <manuel.wenger@ticinocom.com> writes:
> 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.

You need to figure out exactly which operation(s) inside the triggers
is so expensive.  You could try removing commands one at a time and
timing the modified triggers.

Just on general principles, I'd guess that this might be the problem:

>     delete from currentip where ip is null;

Since an IS NULL test isn't indexable by a normal index, this is going
to cause a full scan of the currentip table every time.  I don't really
understand why you need that executed every time anyway ... why is it
this trigger's responsibility to clean out null IPs?  But if you really
do need to make that run quickly, you could create a partial index with
a WHERE clause of "ip is null".

            regards, tom lane