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.

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

[Dmitry Yemanov dim...@users.sourceforge.net [firebird-support]]

23.12.2015 01:36, Ann Harrison wrote:

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

Correct, but from another side third, fourth, etc updates will not 
create any new versions. May be worth checking whether a "heavy" second 
update could be better than creating a long version chain.


Dmitry

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

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Right. When we were working on InterBase 1.1 (I think) a friend of Jim's
> suggested using deltas for back versions to save space.  He's still a friend, 
> but
> that feature was a real trial to implement and debug, partly because we ran
> out of bits in the record header.

With today's unlimited availability of disk space and silly-low cost per GB for 
storage, would an argument to dispense with the delta and simply store a full 
copy of the record (not including BLOB) be worthy of discussion?

I wonder if the economics which required the creation of delta's has been 
overtaken and the cost of merging deltas is no longer worth it.

I know that Jim has mentioned that in his later db engine he has adopted a 
reverse approach which has the latest version stored in full and for 
transactions required back versions responsible processing the deltas.  In this 
way, the latest version of the row are always complete so that the back 
versions can be dropped very efficiently.


Sean

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] Re: UPDATE to same record causing heavy disk I/O

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

On Wed, Dec 23, 2015 at 12:01 PM, Dmitry Yemanov
dim...@users.sourceforge.net [firebird-support] <
firebird-support@yahoogroups.com> wrote:

> 23.12.2015 01:36, Ann Harrison wrote:
>
> >
> > ...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.
>
> Correct, but from another side third, fourth, etc updates will not
> create any new versions. May be worth checking whether a "heavy" second
> update could be better than creating a long version chain.
>

Right. When we were working on InterBase 1.1 (I think) a friend of Jim's
suggested using deltas for back versions to save space.  He's still a
friend, but that feature was a real trial to implement and debug, partly
because we ran out of bits in the record header.  There's one that says
"the version behind me is a delta", but not one that says "I am a delta."
 The first one is essential because it means that you've got to set up a
copy of the record on which to apply deltas.  The second would have been
very nice for detecting bugs that lead to having the first bit set when it
shouldn't be or not set when it should.

Once the deltas were working reasonably well, we discovered that some users
were updating a single record dozens of times in a transaction.  Not a use
case we'd considered.  I don't remember whether we decided to create a
complete back version after two or three updates in one transaction, though
I vaguely remember arguing that like Martinis, one or two was pretty
reasonable, but after three things are likely to go off in an unexpected
direction.  With small records, a complete back version saves space
compared with three deltas.  With big records and small changes... not so
much.

Cheers,

Ann

>
>
> Dmitry
>
>
>
>
> 
> Posted by: Dmitry Yemanov 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>