Page 1 of 1
Firebird transaction handling and performance
Posted: Thu 06 Dec 2012 09:41
by ralle1
We’ve got the problem that the Firebird Server becomes very slow performance in the course of a day. We’ve found out that the transactions cause this. In the database statistic, I can see that the different between oldest active Transaction and next transaction permanently increases. After reading in several forums, we thought that it’s correct to use the feature “UpdateTransaction”. But the increase is just the same. Please look at my example below. The database sweep interval is set to 10 but the values for “Oldest transaction” / “Oldest active” / “Oldest snapshot” are standing still and the “Next transaction” is running. Nothing else is working on this database.
Here the example:
Code: Select all
procedure TForm7.Button1Click(Sender: TObject);
var
id : integer;
IBCQuery : TIBCQuery;
DefaultTransaction,
UpdateTransaction : TIBCTransaction;
IBCCon : TIBCConnection;
begin
Cancel := false;
IBCCon := TIBCConnection.Create(nil);
IBCCon.LoginPrompt := false;
IBCCon.Database := '10.1.0.18/3050:c:\databases\data1.fdb';
IBCCon.Username := 'SYSDBA';
IBCCon.Password := 'masterkey';
IBCCon.Connected := true;
DefaultTransaction := TIBCTransaction.Create(Self);
DefaultTransaction.DefaultConnection := IBCCon;
UpdateTransaction := TIBCTransaction.Create(Self);
UpdateTransaction.DefaultConnection := IBCCon;
IBCQuery := TIBCQuery.Create(Self);
TRY
(* Simple Table for Inserts:
CREATE TABLE TESTTABLE (
ID INTEGER NOT NULL,
VAL INTEGER,
PRIMARY KEY (ID))
*)
id := 0;
IBCQuery.Connection := IBCCon;
IBCQuery.Transaction := DefaultTransaction;
IBCQuery.UpdateTransaction := UpdateTransaction;
IBCQuery.SQL.Text := 'SELECT MAX(ID) FROM TESTTABLE';
IBCQuery.Open;
id := IBCQuery.Fields[0].AsInteger;
IBCQuery.Close;
IBCQuery.SQL.Text := 'SELECT * FROM TESTTABLE';
IBCQuery.ReadOnly := false;
IBCQuery.Open;
repeat
inc(id);
IBCQuery.Insert;
IBCQuery.FieldByName('ID').AsInteger := id;
IBCQuery.Post;
Label1.Caption := IntToStr(id);
Application.ProcessMessages;
until (Cancel);
FINALLY
IBCQuery.Close;
IBCQuery.Free;
DefaultTransaction.Free;
UpdateTransaction.Free;
IBCCon.Close;
IBCCon.Free;
END;
end;
Can you please help me??
Thanks!
Re: Firebird transaction handling and performance
Posted: Thu 06 Dec 2012 10:10
by AndreyZ
The point is that if the TIBCQuery.Options.PrepareUpdateSQL property is set to True, IBDAC prepares statements that are executed in the context of the writing transaction. This is done to provide the further work with the prepared statement. To avoid the problem, you should set TIBCQuery.Options.PrepareUpdateSQL to False. In this case, IBDAC does not prepare statements of the writing transaction, and executes the Commit statement instead of CommitRetaining.
Please note that we have changed the default value of the TIBCQuery.Options.PrepareUpdateSQL property from True to False in IBDAC 4.2.7 for performance improving.
Re: Firebird transaction handling and performance
Posted: Thu 06 Dec 2012 10:52
by ralle1
Unfortunately this is not quite correct. I am using Delphi XE2 with IBDAC 4.5.9
Sorry for not communicate this information.
And I use the default value. I just checked it.
TIBCQuery.Options.PrepareUpdateSQL is set to False.
Re: Firebird transaction handling and performance
Posted: Thu 06 Dec 2012 17:13
by AndreyZ
I have checked your code using IBDAC 4.5.9, and IBDAC correctly calls the Commit method instead of CommitRetaining. You are right, when using your code, "Oldest transaction", "Oldest active", and "Oldest snapshot" do not change and "Next transaction" changes. It is a correct behaviour, because you have two transactions, one for reading data and one for writing. When you open IBCQuery, IBDAC starts DefaultTransaction to read data from the TESTTABLE table. "Oldest transaction", "Oldest active", and "Oldest snapshot" do not change because DefaultTransaction is active. When you add new records, IBDAC starts UpdateTransaction and commits it for each record, that is why the value of "Next transaction" grows. If you do not want this to happen, you can start UpdateTransaction manually before adding new records and commit all changes afterwards. Here is a code example:
Code: Select all
IBCQuery.SQL.Text := 'SELECT * FROM TESTTABLE';
IBCQuery.ReadOnly := false;
IBCQuery.Open;
IBCQuery.AutoCommit := False;
IBCQuery.UpdateTransaction.StartTransaction;
repeat
inc(id);
IBCQuery.Insert;
IBCQuery.FieldByName('ID').AsInteger := id;
IBCQuery.Post;
Label1.Caption := IntToStr(id);
Application.ProcessMessages;
until (Cancel);
IBCQuery.UpdateTransaction.Commit;
Re: Firebird transaction handling and performance
Posted: Fri 07 Dec 2012 07:30
by ralle1
Thank your for investing your time in my problem!
Your explanation seems to be logical. But if I always close the IBCQuery after each Insert, the DefaultTransaction will no longer be active and because of "WHERE ID IS NULL" none of the new records is selected. "Oldest transaction" etc. just the same.
Can you explain this behaviour please?
Here is a code example:
Code: Select all
IBCQuery.Close;
IBCQuery.SQL.Text := 'SELECT * FROM TESTTABLE WHERE ID IS NULL';
IBCQuery.ReadOnly := false;
repeat
inc(id);
IBCQuery.Open;
IBCQuery.Insert;
IBCQuery.FieldByName('ID').AsInteger := id;
IBCQuery.Post;
IBCQuery.Close;
Label1.Caption := IntToStr(id);
Application.ProcessMessages;
until (Cancel);
Re: Firebird transaction handling and performance
Posted: Fri 07 Dec 2012 09:53
by AndreyZ
This is a correct Firebird behaviour. Firebird returns old values of "Oldest transaction", "Oldest active", and "Oldest snapshot" until a new transaction is started. You can check this using the following code:
Code: Select all
IBCQuery.Close;
IBCQuery.SQL.Text := 'SELECT * FROM TESTTABLE WHERE ID IS NULL';
IBCQuery.ReadOnly := false;
repeat
inc(id);
IBCQuery.Open;
IBCQuery.Insert;
IBCQuery.FieldByName('ID').AsInteger := id;
IBCQuery.Post;
IBCQuery.Close;
Label1.Caption := IntToStr(id);
Application.ProcessMessages;
until (Cancel);
DefaultTransaction.Rollback;
// shows old values
Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoOldestTransaction));
Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoOldestActive));
Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoOldestSnapshot));
Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoNextTransaction));
Memo1.Lines.Add('');
DefaultTransaction.StartTransaction;
// shows new values
Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoOldestTransaction));
Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoOldestActive));
Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoOldestSnapshot));
Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoNextTransaction));
Memo1.Lines.Add('');
We already had similar requests from our users and we discussed it with Firebird developers. They confirmed that it is a correct behaviour.
Re: Firebird transaction handling and performance
Posted: Fri 07 Dec 2012 11:09
by ralle1
OK, thank you for these informations! Can you tell me one more thing: Which transaction will be used by ExecSQL respectivly Execute in this example:
Code: Select all
IBCQuery.ReadOnly := true;
IBCQuery.Connection := IBCCon;
IBCQuery.Transaction := DefaultTransaction;
IBCQuery.UpdateTransaction := UpdateTransaction;
IBCQuery.SQL.Text := 'UPDATE TESTTABLE SET VAL=123 WHERE ID=2';
IBCQuery.ExecSQL;
Re: Firebird transaction handling and performance
Posted: Fri 07 Dec 2012 12:53
by AndreyZ
In this case, DefaultTransaction is used.
Re: Firebird transaction handling and performance
Posted: Fri 07 Dec 2012 13:18
by ralle1
OK. What's your opinion, is it possible to do something like this or is there any reason to use the DefaultTransaction:
Code: Select all
IBCQuery.Transaction := UpdateTransaction;
UpdateTransaction.StartTransaction;
IBCQuery.ExecSQL;
UpdateTransaction.Commit;
IBCQuery.Transaction := DefaultTransaction;
Re: Firebird transaction handling and performance
Posted: Fri 07 Dec 2012 16:48
by AndreyZ
Yes, it is possible to use such code. It is up to you to decide what transaction to use in each particular situation.
Re: Firebird transaction handling and performance
Posted: Sun 09 Dec 2012 19:07
by ralle1
OK, thank you very much. While testing I noticed the following behavior:
I have exended the loop in the above example:
I use in each continuous a Stored Procedure, which uses a generator, something like this: „ID = GEN_ID(GENERATORNAME,1)“. Now, the values "Oldest transaction" etc. is increasing too. I don't understand
Can you explain this?
Re: Firebird transaction handling and performance
Posted: Mon 10 Dec 2012 11:26
by AndreyZ
InterBase and Firebird require an active transaction for any operation under data. So, when you execute your stored procedure, a new transaction is started and committed, which leads to increasing of the "Oldest transaction", etc. values.
Please note that IBDAC does not control the "Oldest transaction", "Oldest active", "Oldest snapshot", and "Next transaction" values, they are controlled by Firebird. They can change when any transaction-related operations are performed. If you want to know more about these values and situations when they change their values, please consult with Firebird developers.
Re: Firebird transaction handling and performance
Posted: Mon 10 Dec 2012 15:53
by ralle1
I understand that it’s a firebird issue. But with all of your information you have helped me a lot. Thank you for the very good support. I will try to rebuild everything and to use "Update Transaction" respectively explicitly transactions, so that the DefaultTransaction can be set to IsolationLevel ReadOnlyReadCommitted. So I hope the performance will be consistently good.
Thank you!
Re: Firebird transaction handling and performance
Posted: Mon 10 Dec 2012 17:41
by AndreyZ
I am glad I could help. Feel free to contact us if you have any further questions about IBDAC.