Hi Caroline,
It could be a chain of many record versions.
If you have such database handy, run
gstat -a -r <database> > stat.txt
and load it to HQbird Database Analyst (trial version will be enough),
then open tab Tables and sort on Max Versions column.
Check how many versions on the table you are updating and what is the
length of Max Versions (the longest chain of versions here).
If you will see a long chain of versions, it means that you are updating
the same record while some other writeable transaction is active, or
some old transaction was rollabacked with mark on TIP, so it makes
Firebird to preserve versions of updated record.
In general, avoid multiple updates of the same record - replace them
with INSERTs and some scheduled delete, right before sweep.
Regards,
Alexey Kovyazin
IBSurgeon
Since I began using Firebird, I have kept my transactions (type
concurrency) very short and then call COMMIT immediately afterward.
This has worked very well.
I recently had the need to perform more complex processing and what I
did was to keep everything short and modular. But I am now seeing
that my design in this specific case was flawed.
I am updating different parts of the same record repeatedly and I
believe that this is causing multiple back versions which causing
excessive disk write I/O and slowing things down terribly:
a) begin a transaction, update FIELD_1 of MYTABLE, COMMIT, and end
transaction.
b) begin a transaction, update FIELD_2 of MYTABLE, COMMIT, and end
transaction.
c) begin a transaction, update FIELD_3 of MYTABLE, COMMIT, and end
transaction.
d) begin a transaction, update FIELD_4 of MYTABLE, COMMIT, and end
transaction.
e) begin a transaction, update FIELD_5 of MYTABLE, COMMIT, and end
transaction.
Note: other tables are inserted and updated during during transactions
a-e but those tables are not presenting any problems. The problem is
with MYTABLE.
Of course, I normally update all fields in one transaction but in this
particular case, determining the contents of each field is a complex
process that requires manipulation and analysis of the the data
provided by a number of other Firebird SELECT queries to the database.
I am averaging about 300 transactions per minute during this process
that may last 12 hours and during that time, things get terribly slow.
So can someone confirm my suspicions, will each of the 5 transactions
above to the same row of data cause 5 new 'back versions'?
Like I said, I have always kept transactions very short. I am
thinking of something like this instead:
a) begin a transaction, update FIELD_1 of MYTABLE.
b) update FIELD_2 of MYTABLE.
c) update FIELD_3 of MYTABLE.
d) update FIELD_4 of MYTABLE.
e) update FIELD_5 of MYTABLE, COMMIT, and end transaction.
If something fails anywhere in between, I would ROLLBACK this single
transaction.
Keeping my transactions shorter and more modular as above is easier
from a development point of view but I have the back version
performance issue. Although the second method means a much longer
transaction, I won't have back versions to deal with. Do you think
that this approach would be better?
Thank you
P.S. Sweeping the database does not help with the performance
problem, the only temporary solution to regain performance is to
backup using GBAK and restore.
