Hi guys.

Yesterday I was trying to figure out what is the cause of my problem. And I've noticed that two months ago my colleague added one trigger on this table. The trigger is:

SET TERM ^ ;
ALTER TRIGGER CLIENT_LM INACTIVE
AFTER INSERT OR UPDATE POSITION 1
AS
BEGIN
   UPDATE OR INSERT INTO LAST_MODIFICATION (TABLE_NAME, RECORD_TIME)
   VALUES ('CLIENT', NEW.RECORD_TIME)
   MATCHING (TABLE_NAME);
END^
SET TERM ; ^

So, after each insert or update this trigger was saving last modification time to a different table. On each table in the system such trigger exists. After dissabling the trigger, performance get back to normal.

Results for gstat are strange:

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              228629442
        Page size               16384
        ODS version             11.2
        Oldest transaction      219409373
        Oldest active           219409374
        Oldest snapshot         219409374
        Next transaction        227560937
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      1068492
        Implementation ID       24
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Aug 9, 2013 23:34:51
        Attributes              force write

    Variable header data:
        Sweep interval:         20000
        *END*

I see the big difference between oldest transaction and the current transaction. Is it possible that this trigger is caused this? BTW my colleague has reported to me that he had a number of deadlocks becuase of this mentioned trigger.

---In firebird-support@yahoogroups.com, <iblist@...> wrote:

Em 15/1/2014 16:30, brucedickinson@... escreveu:

Hi guys.

I have one table that currently has 100 000 000 records. PageSize is 16384.
Index statistics for primary key are 0.000000 (I've recomputed them).

Over a few days inserting and updating this table slowed down significantly. When I try to update one row:

UPDATE TABLE SET VALUE=X WHERE PK_COLUMN_ID =12335343

update executes in 0.250 miliseconds.

What is surprising for me that just few days ago everything was fine.. how could I speed thing up? What could happen over these few days?

Thanks in advance.

It's hard to tell without more information.

But the first thing I would look out is the transaction counters.

Perhaps you have some open transaction that is preventing the garbage collection process and acumulating record versions.

use GSTAT -h to get the header information of your database and post it back.

see you !