Firebird transactions

ab · 2024-01-19 14:23:32

Thanks for the numbers!

So Zeos seams WAY faster than IBX.
Nice to know it - but not unexpected.

About the Windows version detected, on you need a proper manifest in the executable to have Windows 10/11 be detected.
Add e.g.

{$R ..\src\mormot.win.default.manifest.res}

ttomas · 2024-01-19 16:52:28

This results is expected with default config. 3 reasons:
1. Zeos have better parameters and results binding, low level access to Fb Api records, without copy strings/varchars.
2. Zeos only use CommitRetaining after any statement and never close/Commit transaction. Ibx use StartTransaction, execute statement and CommitTransaction. This is by design. You can simulate zeos transaction handling if you enable {$define ZEOSTRANS} at line 48 in mormot.db.sql.ibx.
3. fbintf for Fb3 and above use new Firebird3 interface, by default, which is slower then old legacy ICS/Interbase Api. zeos use only legacy Api. Look in fbintf, IB.pas lines 76-81, you can force to use legacy Api interface only for Fb3/4/5 by defining LEGACYFIREBIRDAPIONLY define to your project. You will see Direct/Trans insertion speed increase for Fb4/5 in range of Fb25 (Fb2.5 use only legacy interface)

Last edited by ttomas (2024-01-19 16:56:27)

vs · 2024-01-19 21:43:32

ab, thanks for the tip.
Still, there may be a small typo, line 10 in ..\ex\extdb-bench\PerfTestConsole.dpr

  {../../src/$R mormot.win.default.manifest.res}

ttomas, thank you for the clarification!

1 and 3 - clear.

2. A few words about this. As I wrote earlier in this thread, this long transaction leads to an increase records versioning for frequently updated tables, which for systems running 24/7 after some time, leads to a dramatically decrease performance for both reading and writing. Firebird 4 solved this problem at some point, but still, a long transaction that opens when the server starts, in my opinion, is not a very correct configuration. Therefore fbintf seems more suitable.

ttomas · 2024-01-19 23:45:18

@ab, I create push request.
2. Correct, this is the reason I create ibx mormot driver. Main goal is to have 0 active transaction if nobody is connected to app/server.
{$define ZEOSTRANS} is only for testing, not recomended for production.

Last edited by ttomas (2024-01-19 23:48:10)

ttomas · 2024-01-20 00:28:11

@vs, do you success build with delphi, if yes delphi version please. @ab alearedy commit changes in trunk.

vs · 2024-01-20 01:43:00

ttomas wrote:

2. Correct, this is the reason I create ibx mormot driver. Main goal is to have 0 active transaction if nobody is connected to app/server.

Great, thanks for sharing your experience!

ttomas wrote:

{$define ZEOSTRANS} is only for testing, not recomended for production.

Ок, thanks. Yes, it is clear!

tomas wrote:

@vs, do you success build with delphi, if yes delphi version please. @ab alearedy commit changes in trunk.

Yes, with my local edits MVCServerFirebirdIbx compiling successful for both (Lazarus and Delphi 12) and working as expected, except FTS.
Tomorrow I'll try to play with ..\ex\extdb-bench\PerfTestConsole.dpr for both (Lazarus and Delphi 12) with the latest ab's commit.

I suspect that the performance figures that I gave a little earlier are not entirely correct.
Most likely the embedded version of fbclient.dll server from firebird 5 installation was used instead of connecting to a particular version of firebird server.
But this is just a guess, I'll try to figure it out tomorrow.

Add.
By the way. Thanks madorin, there is a pretty good library with precise transaction controls for Firebird. Separate for reading and writing transactions, at least.
Maybe this could be a starting point for the next Firebird driver for mORMot 2
https://github.com/madorin/fibplus/
https://github.com/madorin/fibplus/commits/master

Last edited by vs (2024-01-20 02:28:31)

vs · 2024-01-20 18:41:03

More accurate test results.

Running tests using Synopse mORMot framework 2.2.6792, compiled with Free Pascal 3.2.2 64 bit, against SQLite 3.44.2, on Windows 11 64bit (10.0.22631), at 2024-01-20 20:02:16.

Insertion speed (rows/second)                                           Read speed (rows/second)
                  Direct      Batch       Trans       Batch Trans   |                     By one      All Virtual All Direct
Zeos firebird 25  2739        76921       5911        58403         |   Zeos firebird 25  7811        407398      427313
Zeos firebird 4   2471        57124       7942        41158         |   Zeos firebird 4   7868        391282      412609
Zeos firebird 5   1960        70443       7295        49310         |   Zeos firebird 5   8212        413479      498529
Ibx firebird 25   756         43030       6628        40509         |   Ibx firebird 25   1356        100883      117096
Ibx firebird 4    1824        33112       7650        42821         |   Ibx firebird 4    1617        138475      104979
Ibx firebird 5    1367        60364       6479        40248         |   Ibx firebird 5    1791        235510      211555

A separate test was run for each driver and each firebird server version.

Ibx firebird 25 did not pass the test due to an error: Solved by changing line 616 cMaxStm = 50 in mormot.db.sql.ibx at cMaxStm = 45

Project PerfTestConsole raised exception class 'EIBInterBaseError' with message:
Engine Code: 335544569
Dynamic SQL Error
-SQL error code = -204
-Implementation limit exceeded
-block size exceeds implementation restriction

 In file 'FBClientAPI.pas' at line 412:

Last edited by vs (2024-01-21 17:10:44)

ttomas · 2024-01-20 20:01:35

Do you change line 616 cMaxStm = 50 in mormot.db.sql.ibx. Previous test on 2.5 pass without problem. Fb3 have limit of 255 statements, Fb2.5???
Edited: Or execute statement size exceeded 64K limit on Fb2.5, Fb3 and above have limit of 10MB.
Batch insert create execute block statement with 50 insert statements inside.
Do you try to add define LEGACYFIREBIRDAPIONLY to force using of legacy api with Fb4/5?

Last edited by ttomas (2024-01-20 20:23:03)

vs · 2024-01-21 17:10:37

Do you change line 616 cMaxStm = 50 in mormot.db.sql.ibx.

Yes, 48 and below working fine ))). I added the numbers for Ibx firebird 25 to my previous post.

Previous test on 2.5 pass without problem.

Because, as I wrote earlier, embedded version of fbclient.dll from firebird 5 installation was used instead of connecting to 2.5 firebird server

Edited: Or execute statement size exceeded 64K limit on Fb2.5, Fb3 and above have limit of 10MB.

Yes, that's right, https://www.firebirdfaq.org/faq197/

Do you try to add define LEGACYFIREBIRDAPIONLY to force using of legacy api with Fb4/5?

Yes, but without success.

! EXC   EIBInterBaseError {Message:"Engine Code: 335544351\r\nunsuccessful metadata update"} [Main] at 0100375a46 client/include/wloadlibrary.inc tfbclientapi.ibdatabaseerror (209)

ttomas · 2024-02-02 10:30:40

Thanks @vs for report of exception with LEGACYFIREBIRDAPIONLY. I found problem with definition of isc_start_transaction dll function in fbintf.
More info in this topic: https://forum.lazarus.freepascal.org/in … opic=66087

mORMot Open Source

#101 2024-01-19 14:23:32

Re: Firebird transactions

#102 2024-01-19 16:52:28

Re: Firebird transactions

#103 2024-01-19 21:43:32

Re: Firebird transactions

#104 2024-01-19 23:45:18

Re: Firebird transactions

#105 2024-01-20 00:28:11

Re: Firebird transactions

#106 2024-01-20 01:43:00

Re: Firebird transactions

#107 2024-01-20 18:41:03

Re: Firebird transactions

#108 2024-01-20 20:01:35

Re: Firebird transactions

#109 2024-01-21 17:10:37

Re: Firebird transactions

#110 2024-02-02 10:30:40

Re: Firebird transactions

Board footer