Re: [firebird-support] UPDATE to same record causing heavy disk I/O

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Caroline,

It could be a chain of many record versions.

If you have such database handy, run
gstat -a -r   > 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.

Re: [firebird-support] UPDATE to same record causing heavy disk I/O

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Wed, Dec 23, 2015 at 7:12 AM, Alexey Kovyazin a...@ib-aid.com
[firebird-support]  wrote:

>
> 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.
>

Updating a record will always create a back version, even if there are no
other users in the database.  Back versions function in three ways:

  1) They provide a non-blocking consistent view of data for concurrent
transactions.
  2) They allow Firebird to detect and prevent conflicting updates.
  3) They are the mechanism for verb, savepoint, and transaction rollback

The third function must be available even in single user applications.

Good luck,

Ann

>
>
>
> 
>

Re: [firebird-support] UPDATE to same record causing heavy disk I/O

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, Dec 22, 2015 at 2:17 PM, Caroline Beltran
caroline.d.belt...@gmail.com [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> 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.
>

Just curious, why do you say both "COMMIT" and "end transaction" - Commit
ends the
transaction.

Good luck,

Ann

> 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.
>
>
> 
>

Re: [firebird-support] UPDATE to same record causing heavy disk I/O

[Caroline Beltran caroline.d.belt...@gmail.com [firebird-support]]

Hi Ann,

>Just curious, why do you say both "COMMIT" and "end transaction" - Commit
ends the transaction.

Yes you are right.  I shouldn't have said "end transaction" but I will
answer your question why.  I use the IBPP library for C++ and you create a
pointer to the transaction.  Immediately after either a COMMIT or a
ROLLBACK, the transaction pointer is immediately destroyed.  In my mind, I
saw the destruction of the transaction pointer as the end of the
transaction's lifetime.



On Tue, Dec 22, 2015 at 2:39 PM, Ann Harrison aharri...@ibphoenix.com
[firebird-support]  wrote:

>
>
> On Tue, Dec 22, 2015 at 2:17 PM, Caroline Beltran
> caroline.d.belt...@gmail.com [firebird-support] <
> firebird-support@yahoogroups.com> wrote:
>
>>
>>
>> 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.
>>
>
> Just curious, why do you say both "COMMIT" and "end transaction" - Commit
> ends the
> transaction.
>
> Good luck,
>
> Ann
>
>> 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.
>>
>>
>>
> 
>

[firebird-support] UPDATE to same record causing heavy disk I/O

[Caroline Beltran caroline.d.belt...@gmail.com [firebird-support]]

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.

Re: [firebird-support] UPDATE to same record causing heavy disk I/O

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, Dec 22, 2015 at 2:17 PM, Caroline Beltran
caroline.d.belt...@gmail.com [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> 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.
>

Good, but that too can be overdone.  Each transaction start causes a change
to the header page and end causes changed pages including a transaction
inventory page to be written to disk.  There's some grouping of writes, but
as a rule, think that each transaction you create causes two extra page
writes beyond the data and indexes.

>
> I recently had the need to perform more complex processing and what I did
> was to keep everything short and modular.  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.
>

There are several problems with this.  One is the significant transaction
overhead you introduce. A second, as you've guessed is that you're creating
a back version for each update.   Another is that any transaction reading
your record between updates will see some field that have been changed and
others that haven't.  Another, and not insignificant, is the danger that
some other transaction will change your part or all of a record between
your transactions, leaving the record inconsistent.


> 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.
>

 Probably some information from the monitoring tables will let someone else
give you good advice.

>
> So can someone confirm my suspicions, will each of the 5 transactions
> above to the same row of data cause 5 new 'back versions'?
>

Absolutely.

>
> 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.
>

That's not going to work either.  Your first update will create a back
version that's just the difference between the old record state and the new
state.  The second (or maybe third) will create a back version that's the
whole record (IIRC) - much larger and possibly off page. Off page matters
because it doubles the page writes.

>
> 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?
>

No, just do all the computations in a single transaction and update the
record once with all the changes.

>
>
> 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.
>

That's why I suspect there's more to it than just back versions and would
like to see something about I/O, reads, writes, fetches, marks, etc.

Good luck,

Ann

>
>
> 
>