pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Why is the number of dead tuples causing the performance of deferred triggers to degrading so rapidly (exponentionally)?
5+ messages / 3 participants
[nested] [flat]

* Why is the number of dead tuples causing the performance of deferred triggers to degrading so rapidly (exponentionally)?
@ 2004-08-17 21:29  Frank van Vugt <ftm.van.vugt@foxi.nl>
  0 siblings, 1 reply; 5+ messages in thread

From: Frank van Vugt @ 2004-08-17 21:29 UTC (permalink / raw)
  To: pgsql-performance

Hi,

I'm seeing the following behaviour with the table and functions given below:

db=# insert into f select * from full_sequence(1, 1000);
INSERT 0 1000
Time: 197,507 ms
db=# insert into f select * from full_sequence(1, 1000);
INSERT 0 1000
Time: 341,880 ms
db=# insert into f select * from full_sequence(1, 1000);
INSERT 0 1000
Time: 692,603 ms
db=# insert into f select * from full_sequence(1, 1000);
INSERT 0 1000
Time: 985,253 ms
db=# insert into f select * from full_sequence(1, 1000);
INSERT 0 1000
Time: 1241,334 ms

Or even worse (fresh drop/create of the table and functions):

db=# insert into f select id from full_sequence(1, 10000);
INSERT 0 10000
Time: 22255,767 ms
db=# insert into f select id from full_sequence(1, 10000);
INSERT 0 10000
Time: 45398,433 ms
db=# insert into f select id from full_sequence(1, 10000);
INSERT 0 10000
Time: 67993,476 ms

Wrapping the commands in a transaction only accumulates the penalty at commit.

It seems in this case the time needed for a single deferred trigger somehow 
depends on the number of dead tuples in the table, because a vacuum of the 
table will 'reset' the query-times. However, even if I wanted to, vacuum is 
not allowed from within a function.

What is happening here? And more importantly, what can I do to prevent this?

NB. My real-world application 'collects' id's in need for deferred work, but 
this work is both costly and only needed once per base record. So I use an 
'update' table whose content I join with the actual tables in order to do the 
work for _all_ the base records involved upon the first execution of the 
deferred trigger. At the end of the trigger, this 'update' table is emptied 
so any additional deferred triggers on the same table will hardly lose any 
time. Or at least, that was the intention....

*********** demo script ***********
drop table f cascade;
drop function tr_f_def() cascade;
drop function full_sequence(integer, integer);
drop type full_sequence_type;

create table f (id int);
create function tr_f_def() RETURNS trigger LANGUAGE 'plpgsql' STABLE STRICT
SECURITY INVOKER AS '
        DECLARE
        BEGIN
		-- do stuff with all the ids in the table

		-- delete the contents
--		delete from f;
		IF EXISTS (SELECT 1 FROM f) THEN
			DELETE FROM F;
			VACUUM F;
		END IF;

                RETURN NULL;
        END;';
create type full_sequence_type as (id int);
create function full_sequence(integer, integer)
	RETURNS SETOF full_sequence_type
	LANGUAGE 'plpgsql'
	IMMUTABLE
	STRICT
	SECURITY INVOKER
	AS '	DECLARE
			my_from ALIAS FOR $1;
			my_to ALIAS FOR $2;
			result full_sequence_type%ROWTYPE;
		BEGIN
			-- just loop
			FOR i IN my_from..my_to LOOP
				result.id = i;
				RETURN NEXT result;
			END LOOP;

			-- finish
			RETURN;
		END;';
CREATE CONSTRAINT TRIGGER f_def AFTER INSERT ON f DEFERRABLE INITIALLY 
DEFERRED FOR EACH ROW EXECUTE PROCEDURE tr_f_def();
*********** demo script ***********

db=# select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66





-- 
Best,




Frank.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Why is the number of dead tuples causing the performance of deferred triggers to degrading so rapidly (exponentionally)?
@ 2004-08-17 21:33  Frank van Vugt <ftm.van.vugt@foxi.nl>
  0 siblings, 0 replies; 5+ messages in thread

From: Frank van Vugt @ 2004-08-17 21:33 UTC (permalink / raw)
  To: pgsql-performance

Obviously,

this part of tr_f_def():

******************************
		-- delete the contents
--		delete from f;
		IF EXISTS (SELECT 1 FROM f) THEN
			DELETE FROM F;
			VACUUM F;
		END IF;
******************************


should simply read:

******************************
		-- delete the contents
		delete from f;
******************************



--
Best,




Frank.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Why is the number of dead tuples causing the performance of deferred triggers to degrading so rapidly (exponentionally)?
@ 2004-08-17 23:03  Josh Berkus <josh@agliodbs.com>
  parent: Frank van Vugt <ftm.van.vugt@foxi.nl>
  0 siblings, 1 reply; 5+ messages in thread

From: Josh Berkus @ 2004-08-17 23:03 UTC (permalink / raw)
  To: Frank van Vugt <ftm.van.vugt@foxi.nl>; +Cc: pgsql-performance

Frank,

> It seems in this case the time needed for a single deferred trigger somehow
> depends on the number of dead tuples in the table, because a vacuum of the
> table will 'reset' the query-times. However, even if I wanted to, vacuum is
> not allowed from within a function.
>
> What is happening here? And more importantly, what can I do to prevent
> this?

I'm not clear on all of the work you're doing in the trigger.  However, it 
seems obvious that you're deleting and/or updating a large number of rows.  
The escalating execution times would be consistent with that.

> NB. My real-world application 'collects' id's in need for deferred work,
> but this work is both costly and only needed once per base record. So I use
> an 'update' table whose content I join with the actual tables in order to
> do the work for _all_ the base records involved upon the first execution of
> the deferred trigger. At the end of the trigger, this 'update' table is
> emptied so any additional deferred triggers on the same table will hardly
> lose any time. Or at least, that was the intention....

I think you're doing a lot more than is wise to do in triggers.   Deferrable 
triggers aren't really intended for running long procedures with the creation 
of types and temporary tables (your post got a bit garbled, so pardon me if 
I'm misreading it).   I'd suggest reconsidering your approach to this 
application problem.

At the very least, increase max_fsm_relations to some high value, which may 
help (or not). 

-Josh

-- 
__Aglio Database Solutions_______________
Josh Berkus		       Consultant
josh@agliodbs.com	 www.agliodbs.com
Ph: 415-752-2500	Fax: 415-752-2387
2166 Hayes Suite 200	San Francisco, CA



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Why is the number of dead tuples causing the performance of deferred triggers to degrade so rapidly (exponentionally)?
@ 2004-08-18 09:24  Frank van Vugt <ftm.van.vugt@foxi.nl>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Frank van Vugt @ 2004-08-18 09:24 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Hi Josh,

> > It seems in this case the time needed for a single deferred trigger
> > somehow depends on the number of dead tuples in the table

After further investigation I think I have a better grasp of what's going on.

The thing biting me here is indeed the 'delete from' on a table with a number 
of dead rows, possibly made worse in some cases where not everything can be 
handled in memory.

> I'm not clear on all of the work you're doing in the trigger.
> > NB. My real-world application 'collects' id's in need for deferred work
> I think you're doing a lot more than is wise to do in triggers.

I probably wasn't clear enough on this. I'm not creating types and/or 
temporary tables or anything of that kind.

The ratio is probably explained better by this example:

- the database has knowledge on 'parts' and 'sets', the sets have a few fields 
whose content depend on the parts, but the proper value for these fields can 
only be determined by looking at all the parts of the particular set together 
(i.e. it's not a plain 'part-count' that one could update by a trigger on the 
part)

- during a transaction, a number of things will happen to various parts of 
various sets, so I have after triggers on the parts that will insert the ids 
of the sets that need an update into a set_update holding table; in turn, 
this set_update table has a deferred trigger

- upon execution of the deferred triggers, I now know that all the work on the 
parts is finished, so the deferred trigger initiates an update for the sets 
whose ids are in the update table and it will delete these ids afterwards

Now, because multiple updates to parts of the same set will result in multiple 
inserts in the update table, I want to avoid doing the set-update more that 
once. 

Obviously, it would be better to be able to 'cancel' the rest of the calls to 
the deferred trigger after it has been executed for the first time, but that 
doesn't seem possible.

Even better would be to use a 'for each statement' trigger on the set_update 
holding table instead, but it is not possible to create a deferred 'for each 
statement' trigger..... ;(

So, I seem to be a bit between a rock and a hard place here, I must use 
deferred triggers in order to avoid a costly set update on each part update, 
but in such a deferred trigger I cannot avoid doing the update multiple 
times....(due to the growing cost of a 'delete from' in the trigger)

Mmm, it seems that by hacking pg_trigger I am able to create a for each 
statement trigger that is 'deferrable initially deferred'.

This probably solves my problem, I will ask on 'general' whether this has any 
unforseen side effects and whether or not a 'regular' deferrable for each 
statement trigger is incorporated in v8.0.

Thanks for you reply!



-- 
Best,




Frank.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Why is the number of dead tuples causing the performance
@ 2004-08-18 23:02  DeJuan Jackson <djackson@speedfc.com>
  parent: Frank van Vugt <ftm.van.vugt@foxi.nl>
  0 siblings, 0 replies; 5+ messages in thread

From: DeJuan Jackson @ 2004-08-18 23:02 UTC (permalink / raw)
  To: Frank van Vugt <ftm.van.vugt@foxi.nl>; +Cc: josh@agliodbs.com; pgsql-performance

Since you are updating all of the sets with the specified part number 
why not just ensure that a transaction never inserts the same part 
number more than once (an INSERT  ...SELECT ... WHERE NOT EXISTS(...) 
comes to mind), then delete the row before the end of transaction.

Frank van Vugt wrote:

>Hi Josh,
>
>  
>
>>>It seems in this case the time needed for a single deferred trigger
>>>somehow depends on the number of dead tuples in the table
>>>      
>>>
>
>After further investigation I think I have a better grasp of what's going on.
>
>The thing biting me here is indeed the 'delete from' on a table with a number 
>of dead rows, possibly made worse in some cases where not everything can be 
>handled in memory.
>
>  
>
>>I'm not clear on all of the work you're doing in the trigger.
>>    
>>
>>>NB. My real-world application 'collects' id's in need for deferred work
>>>      
>>>
>>I think you're doing a lot more than is wise to do in triggers.
>>    
>>
>
>I probably wasn't clear enough on this. I'm not creating types and/or 
>temporary tables or anything of that kind.
>
>The ratio is probably explained better by this example:
>
>- the database has knowledge on 'parts' and 'sets', the sets have a few fields 
>whose content depend on the parts, but the proper value for these fields can 
>only be determined by looking at all the parts of the particular set together 
>(i.e. it's not a plain 'part-count' that one could update by a trigger on the 
>part)
>
>- during a transaction, a number of things will happen to various parts of 
>various sets, so I have after triggers on the parts that will insert the ids 
>of the sets that need an update into a set_update holding table; in turn, 
>this set_update table has a deferred trigger
>
>- upon execution of the deferred triggers, I now know that all the work on the 
>parts is finished, so the deferred trigger initiates an update for the sets 
>whose ids are in the update table and it will delete these ids afterwards
>
>Now, because multiple updates to parts of the same set will result in multiple 
>inserts in the update table, I want to avoid doing the set-update more that 
>once. 
>
>Obviously, it would be better to be able to 'cancel' the rest of the calls to 
>the deferred trigger after it has been executed for the first time, but that 
>doesn't seem possible.
>
>Even better would be to use a 'for each statement' trigger on the set_update 
>holding table instead, but it is not possible to create a deferred 'for each 
>statement' trigger..... ;(
>
>So, I seem to be a bit between a rock and a hard place here, I must use 
>deferred triggers in order to avoid a costly set update on each part update, 
>but in such a deferred trigger I cannot avoid doing the update multiple 
>times....(due to the growing cost of a 'delete from' in the trigger)
>
>Mmm, it seems that by hacking pg_trigger I am able to create a for each 
>statement trigger that is 'deferrable initially deferred'.
>
>This probably solves my problem, I will ask on 'general' whether this has any 
>unforseen side effects and whether or not a 'regular' deferrable for each 
>statement trigger is incorporated in v8.0.
>
>Thanks for you reply!
>
>
>
>  
>




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-08-18 23:02 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-17 21:29 Why is the number of dead tuples causing the performance of deferred triggers to degrading so rapidly (exponentionally)? Frank van Vugt <ftm.van.vugt@foxi.nl>
2004-08-17 23:03 ` Josh Berkus <josh@agliodbs.com>
2004-08-18 09:24   ` Re: Why is the number of dead tuples causing the performance of deferred triggers to degrade so rapidly (exponentionally)? Frank van Vugt <ftm.van.vugt@foxi.nl>
2004-08-18 23:02     ` Re: Why is the number of dead tuples causing the performance DeJuan Jackson <djackson@speedfc.com>
2004-08-17 21:33 Why is the number of dead tuples causing the performance of deferred triggers to degrading so rapidly (exponentionally)? Frank van Vugt <ftm.van.vugt@foxi.nl>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox