Re: [PERFORM] Trigger performance problem
---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Trigger performance problem
"Manuel Wenger" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] 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); ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Trigger performance
Hello try prepared statements, PQexecPrepared http://developer.postgresql.org/docs/postgres/libpq-exec.html Regards Pavel Stehule On Thu, 22 Jan 2004, pginfo wrote: > Hi, > > thanks for the answer. > It is very interest, because I readet many times that if I write the trigger > in "C" it will work faster. > In wich case will this trigger work faster if write it in "C"? > In all my triggres I have "select " or "insert into mytable select ..." > or "update mytable set ...where...". > I need this info because I have a table with ~1.5 M rows and if I start to > update 300 K from this rows it takes ~ 2h. > If I remove the trigger for this table all the time is ~ 1 min. > > regards, > ivan. > > Tom Lane wrote: > > > pginfo <[EMAIL PROTECTED]> writes: > > > I was supprised that the pgsql trigger take ~8 sec. to insert this rows > > > and the "C" trigger take ~ 17 sec. > > > > The reason is that plpgsql caches the plan for the invoked SELECT, > > whereas the way you coded the C function, it's re-planning that SELECT > > on every call. > > > > regards, tom lane > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Trigger performance
Ok, thanks. I will do it. regards, ivan. Tom Lane wrote: > pginfo <[EMAIL PROTECTED]> writes: > > In wich case will this trigger work faster if write it in "C"? > > Given that the dominant part of the time will be spent down inside SPI > in either case, I doubt you will be able to see much difference. You > need to think about how to optimize the invoked query, not waste your > time recoding the wrapper around it. > > regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Trigger performance
pginfo <[EMAIL PROTECTED]> writes: > In wich case will this trigger work faster if write it in "C"? Given that the dominant part of the time will be spent down inside SPI in either case, I doubt you will be able to see much difference. You need to think about how to optimize the invoked query, not waste your time recoding the wrapper around it. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Trigger performance
Hi, thanks for the answer. It is very interest, because I readet many times that if I write the trigger in "C" it will work faster. In wich case will this trigger work faster if write it in "C"? In all my triggres I have "select " or "insert into mytable select ..." or "update mytable set ...where...". I need this info because I have a table with ~1.5 M rows and if I start to update 300 K from this rows it takes ~ 2h. If I remove the trigger for this table all the time is ~ 1 min. regards, ivan. Tom Lane wrote: > pginfo <[EMAIL PROTECTED]> writes: > > I was supprised that the pgsql trigger take ~8 sec. to insert this rows > > and the "C" trigger take ~ 17 sec. > > The reason is that plpgsql caches the plan for the invoked SELECT, > whereas the way you coded the C function, it's re-planning that SELECT > on every call. > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Trigger performance
pginfo <[EMAIL PROTECTED]> writes: > I was supprised that the pgsql trigger take ~8 sec. to insert this rows > and the "C" trigger take ~ 17 sec. The reason is that plpgsql caches the plan for the invoked SELECT, whereas the way you coded the C function, it's re-planning that SELECT on every call. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] Trigger performance
Hi,
I need to speed up the triggers that we are using and I began to make
some tests to compare the "C" and pgSQL trigger performance.
I try to write two identical test triggers (sorry I do not know very
good the pgsql C interface and I got one of examples and werite it) and
attached it on insert of my test table.
After it I try to insert in thi stable ~ 160 K rows and compared the
speeds.
I was supprised that the pgsql trigger take ~8 sec. to insert this rows
and the "C" trigger take ~ 17 sec.
This are my triggers:
CREATE OR REPLACE FUNCTION trig1_t()
RETURNS trigger AS
'
DECLARE
my_rec RECORD;
BEGIN
select into my_rec count(*) from ttest;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
and this writen in "C":
#include "postgres.h"
#include "executor/spi.h" /* this is what you need to work with
SPI */
#include "commands/trigger.h" /* ... and triggers */
extern Datum trigf(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(trigf);
Datum
trigf(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
TupleDesc tupdesc;
HeapTuple rettuple;
char *when;
boolchecknull = false;
boolisnull;
int ret, i;
/* make sure it's called as a trigger at all */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "trigf: not called by trigger manager");
/* tuple to return to executor */
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
rettuple = trigdata->tg_newtuple;
else
rettuple = trigdata->tg_trigtuple;
/* check for null values */
if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
&& TRIGGER_FIRED_BEFORE(trigdata->tg_event))
checknull = true;
if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
when = "before";
else
when = "after ";
tupdesc = trigdata->tg_relation->rd_att;
/* connect to SPI manager */
if ((ret = SPI_connect()) < 0)
elog(INFO, "trigf (fired %s): SPI_connect returned %d", when,
ret);
/* get number of rows in table */
ret = SPI_exec("SELECT count(*) FROM ttest", 0);
if (ret < 0)
elog(NOTICE, "trigf (fired %s): SPI_exec returned %d", when,
ret);
SPI_finish();
if (checknull)
{
SPI_getbinval(rettuple, tupdesc, 1, &isnull);
if (isnull)
rettuple = NULL;
}
return PointerGetDatum(rettuple);
}
My question:
Can I do the "C" trigger to be faster that the pgSQL?
regards,
ivan.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
