mORMot 2 ORM Performance

ab · 2022-02-15 11:23:00

The official release of mORMot 2 is around the edge. It may be the occasion to show some data persistence performance numbers, in respect to mORMot 1.

For the version 2 of our framework, its ORM feature has been enhanced and tuned in several aspects: REST routing optimization, ORM/JSON serialization, and in-memory and SQL engines tuning. Numbers are talking. You could compare with any other solution, and compile and run the tests by yourself for both framework, and see how it goes on your own computer or server.

In a nutshell, we almost reach 1 million inserts per second on SQLite3, and are above the million inserts in our in-memory engine. Reading speed is 1.2 million and 1.7 million respectively. From the object to the storage, and back. And forcing AES-CTR encryption on disk almost don't change anything. Now we are talking.

This is the forum discussion thread from https://blog.synopse.info/?post/2022/02 … erformance

Márcio Baroni · 2022-02-15 12:14:36

Great news, I was waiting for the version to be stable to migrate my applications...
Congratulations

tbo · 2022-02-15 13:16:20

Impressive values.

I have a small comment on the blog article. There are no explanations what for example "Sqlite file full", "Batch Trans" and "All Direct" means. If someone without mORMot knowledge reads the article and does not know the benchmark source code, they will not understand what the numbers mean. I think as help to argument for mORMot, an short explanation might be useful.

With best regards
Thomas

ab · 2022-02-15 13:27:10

You are right.
Those details are already explained in the mORMot 1 documentation.

Check https://synopse.info/files/html/Synopse … ml#TITL_59 as reference.

edwinsn · 2022-02-16 09:28:37

Great news!

@ab, one suggestion - while new projects should use mORMot2, there might be old projects that'll keep using mORMot1.
So the question is - will it possible to use both mORMot v1 and v2 in the same time? Even in the same project?

ab · 2022-02-16 09:47:33

No you can't use mORMot 1 and mORMot 2 in the same project.

First of all, there would be type names collisions.
Then some low-level process won't work (e.g. the vmtAuto field used for RTTI and ORM).

But of course you can use either mORMot 1 and mORMot 2 in several projects.
Just don't mix the frameworks.

edwinsn · 2022-02-16 09:56:32

Great! Thanks for clarifying things.

mdoyle · 2022-02-19 08:25:05

Great news. And yes it works on aarch64 as well giving similar good numbers.

nomorelogic · 2022-02-21 15:23:27

Hi all
I read about this benchmark in lazarus forum and I'd like to try out using FirebirdsSql.
So I installed mORMot 2 (without NOSYNDBZEOS) and ZeosDbo (7.2.14) packages.

Firebird connection is configured as follow (I just want avoid embedded, so I used 127.0.0.1 to force firebird via network):

{ TTestFirebird }

const
  /// Please configure parameters for firebird connection
  {$ifdef CPU64}
  cFIREBIRDEMBEDDEDDLL = '/usr/lib/x86_64-linux-gnu/libfbclient.so';
  {$else}
  cFIREBIRDEMBEDDEDDLL = '/usr/lib/x86_64-linux-gnu/libfbclient.so';
  {$endif CPU64}

  {$ifdef USEZEOS}
  cFirebirdServerZeos = '127.0.0.1';  // 'localhost:3033'
  cFirebirdDBFileZeos = '/datifb/maiz.fdb';
  {$endif USEZEOS}
  {$ifdef USEIBX}
  {$endif USEIBX}
  cUserName = 'SYSDBA';
  cPassword = 'masterkey';

Running performance test I get this error:

2.3. Firebird: 
! Firebird - Zeos firebird
! Exception ESqlite3Exception raised with messsage:
!  Error SQLITE_ERROR (1) [Step] using 3.37.2 - TRestStorageExternal.Create: TOrmSample: unable to create external missing field SampleRecord.FirstName - SQL=[ALTER TABLE SampleRecord ADD FirstName VARCHAR(40) CHARACTER SET UTF8], extended_errcode=1
  Total failed: 0 / 2  - Firebird PASSED  92.27ms

while in log file

/home/marcello/dev/llibs_external/mORMot2/ex/extdb-bench/exe/PerfTestConsole 0.0.0.0 (2022-02-20 11:45:25)
Host=devoid User=marcello CPU=1xIntel(R)Core(TM)i7-10510UCPU@1.80GHz8MBcache(x64):FFFB8B170B22FADE212484000000000000040030 OS=Linux=Linux-5.10.0-9-amd64#1-SMP-Debian-5.10.70-1-(2021-09-30) Wow64=0 Freq=1000000
TSynLog 2.0.1 2022-02-21T14:25:48

0000000000000001  ! EXC   EZSQLException {Message:"SQL Error: lock conflict on no wait transactionunsuccessful metadata updateobject TABLE \"SAMPLERECORD\" is in use. Error Code: -901. Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements The SQL: drop table SAMPLERECORD; "} [Main] at 8b8a0d ../../src/dbc/ZDbcInterbase6Utils.pas checkinterbase6error (907) ../../src/dbc/ZDbcInterbase6Statement.pas tzinterbase6preparedstatement.executeinternal (206) ../../src/dbc/ZDbcInterbase6Statement.pas tzinterbase6preparedstatement.executeupdateprepared (485) ../../src/db/mormot.db.sql.zeos.pas  (1169) ../../src/db/mormot.db.sql.pas  (3376) PerfTestCases.pas ttestdatabaseexternalabstract.clientcreate (780) PerfTestCases.pas ttestdatabaseabstract.runwrites (612) PerfTestCases.pas ttestdatabaseabstract.runtests (477) PerfTestCases.pas ttestdatabaseexternalabstract.runexternal (802) PerfTestCases.pas ttestfirebird.zeosfirebird (396) ../../src/core/mormot.core.test.pas  (1198) PerfTestConsole.dpr main (24) 
000000000000623F  ! EXC   EZSQLException {Message:"SQL Error: unsuccessful metadata updateALTER TABLE SAMPLERECORD failedviolation of PRIMARY or UNIQUE KEY constraint \"RDB$INDEX_15\" on table \"RDB$RELATION_FIELDS\"Problematic key value is (\"RDB$FIELD_NAME\" = 'FIRSTNAME', \"RDB$RELATION_NAME\" = 'SAMPLERECORD'). Error Code: -607. This operation is not defined for system tables. The SQL: ALTER TABLE SampleRecord ADD FirstName VARCHAR(40) CHARACTER SET UTF8; "} [Main] at 8b8a0d ../../src/dbc/ZDbcInterbase6Utils.pas checkinterbase6error (907) ../../src/dbc/ZDbcInterbase6Statement.pas tzinterbase6preparedstatement.executeinternal (206) ../../src/dbc/ZDbcInterbase6Statement.pas tzinterbase6preparedstatement.executeupdateprepared (485) ../../src/db/mormot.db.sql.zeos.pas  (1169) ../../src/orm/mormot.orm.sql.pas  (1748) ../../src/orm/mormot.orm.sql.pas  (705) ../../src/orm/mormot.orm.sql.pas  (746) ../../src/orm/mormot.orm.storage.pas  (1480) ../../src/orm/mormot.orm.sqlite3.pas  (512) 
000000000000628C  ! EXC   ERestStorage {Message:"TRestStorageExternal.Create: TOrmSample: unable to create external missing field SampleRecord.FirstName - SQL=[ALTER TABLE SampleRecord ADD FirstName VARCHAR(40) CHARACTER SET UTF8]"} [Main] at 591777 ../../src/orm/mormot.orm.sql.pas  (710) ../../src/orm/mormot.orm.sql.pas  (746) ../../src/orm/mormot.orm.storage.pas  (1480) ../../src/orm/mormot.orm.sqlite3.pas  (512) 
00000000000062C8  ! EXC   ESqlite3Exception {Message:"Error SQLITE_ERROR (1) [Step] using 3.37.2 - TRestStorageExternal.Create: TOrmSample: unable to create external missing field SampleRecord.FirstName - SQL=[ALTER TABLE SampleRecord ADD FirstName VARCHAR(40) CHARACTER SET UTF8], extended_errcode=1",ErrorCode:1,SQLite3ErrorCode:"secERROR"} [Main] at 5f0729 ../../src/db/mormot.db.raw.sqlite3.pas  (8507) ../../src/db/mormot.db.raw.sqlite3.pas  (7884) ../../src/db/mormot.db.raw.sqlite3.pas  (7904) PerfTestCases.pas ttestdatabaseabstract.runwrites (615) PerfTestCases.pas ttestdatabaseabstract.runtests (477) PerfTestCases.pas ttestdatabaseexternalabstract.runexternal (802) PerfTestCases.pas ttestfirebird.zeosfirebird (396) ../../src/core/mormot.core.test.pas  (1198) PerfTestConsole.dpr main (24) 
00000000000072D7  ! fail  PerfTestCases.TTestFirebird(7f5bb6739900) Firebird - Zeos firebird [ESqlite3Exception: Error SQLITE_ERROR (1) [Step] using 3.37.2 - TRestStorageExternal.Create: TOrmSample: unable to create external missing field SampleRecord.FirstName - SQL=[ALTER TABLE SampleRecord ADD FirstName VARCHAR(40) CHARACTER SET UTF8], extended_errcode=1] ../../src/core/mormot.core.test.pas  (1469) PerfTestConsole.dpr main (24)

Do is correct an exception called "ESqlite3Exception"?

Anyway, what I'm doing wrong?

Thanks in advance.
nomorelogic

nomorelogic · 2022-02-21 15:40:43

Hi again
I think there's something wrong in dropping/create table SAMPLERECORD.
I'll investigate more in deep, I need some time.

ttomas · 2022-02-21 21:32:33

You don't need to set cFIREBIRDEMBEDDEDDLL = '' if you have only one instance/version of firebirdsql
You run test in non embedded mode, cFirebirdServerZeos = '127.0.0.1';
In this case multithreaded connection pool is created and drop table will fail in firebird if you have active connection to db.
RunWrites(trans, batch) is called 4 times in test. First run will pass, but second can fail if you have active connection to db.
You can see code in procedure TTestDatabaseExternalAbstract.ClientCreate; line 765
Try to change line 385 in procedure TTestFirebird.ZeosFirebird; to Flags := [dbPropUntouched];
Test 4 times drop table and insert 10000 rows. With this changes your test will only insert rows.
You can also uncomment lines 386,387 to delete db file on start. You will need firebird user permission to delete file.

You can also try to change threadingMode of ConnectionProperties to tmMainConnection

nomorelogic · 2022-02-21 23:59:15

Thank for your suggestions.

I went one step further, I didn’t know that the "drop table" could fail with an open connection.
I had flamerobin connected...
Running the test with flamerobin closed, that error does not occur.

But now I've another error:

0000000000000000  ! EXC   ERestStorage {Message:"TRestStorageExternal.JsonDecodedPrepareToSql(TOrmSample): No column for [ID] field in table SampleRecord"} [Main] at 598052 ../../src/orm/mormot.orm.sql.pas  (2093) ../../src/orm/mormot.orm.sql.pas  (2032) ../../src/orm/mormot.orm.sql.pas  (1229) ../../src/rest/mormot.rest.server.pas  (6882) ../../src/orm/mormot.orm.client.pas  (751) PerfTestCases.pas ttestdatabaseabstract.runtests (477) PerfTestCases.pas ttestdatabaseexternalabstract.runexternal (802) PerfTestCases.pas ttestfirebird.zeosfirebird (396) ../../src/core/mormot.core.test.pas  (1198) PerfTestConsole.dpr main (24) 
000000000000106F  ! ERROR mormot.rest.sqlite3.TRestClientDB(7ff309957400) POST root/Sample returned 500 (Internal Server Error) with message {  "errorCode":500,  "error":  {"ERestStorage":  {   "ClassName": "ERestStorage",   "Address": "598052 ../../src/orm/mormot.orm.sql.pas  (2093)",   "Message": "TRestStorageExternal.JsonDecodedPrepareToSql(TOrmSample): No column for [ID] field in table SampleRecord"  }}  } ../../src/rest/mormot.rest.core.pas  (2075) ../../src/rest/mormot.rest.client.pas  (2477) ../../src/orm/mormot.orm.client.pas  (751) PerfTestCases.pas ttestdatabaseabstract.runtests (477) PerfTestCases.pas ttestdatabaseexternalabstract.runexternal (802) PerfTestCases.pas ttestfirebird.zeosfirebird (396) ../../src/core/mormot.core.test.pas  (1198) PerfTestConsole.dpr main (24)
0000000000001078  ! fail  #3 Add ../../src/core/mormot.core.test.pas  (955) PerfTestCases.pas ttestdatabaseabstract.runtests (477) PerfTestCases.pas ttestdatabaseexternalabstract.runexternal (802) PerfTestCases.pas ttestfirebird.zeosfirebird (396) ../../src/core/mormot.core.test.pas  (1198) PerfTestConsole.dpr main (24)
0000000000002C11  ! fail  PerfTestCases.TTestFirebird(7ff30995f900) Firebird - Zeos firebird [Add] ../../src/core/mormot.core.test.pas  (1469) PerfTestCases.pas ttestdatabaseabstract.runtests (477) PerfTestCases.pas ttestdatabaseexternalabstract.runexternal (802) PerfTestCases.pas ttestfirebird.zeosfirebird (396) ../../src/core/mormot.core.test.pas  (1198) PerfTestConsole.dpr main (24)

Seems that in json, field ID is not present.
In db table the column is present.
Any idea?

Thanks

Last edited by nomorelogic (2022-02-21 23:59:31)

ttomas · 2022-02-22 01:05:48

Firebird and FPC/Lazarus version?
Can't confirm on Win32/64, Fb3, Fb4, Lazarus 2.0.12. FPC 3.2.2, same Zoes 7.2.14
All tests pass (Zeos and Ibx).
You can also try IBX driver, I just push new commit with IBatch interface use for Fb4
https://github.com/TTomas/mORMot2/blob/ … ql.ibx.pas

nomorelogic · 2022-02-22 08:06:15

Lazarus 2.2
FPC 3.2.3
Firebird 3.0.7.33374

As soon as possible I'll try same database using IBX.

Edit:
do I need any compiler directive to compile Zeos?

Edit2:
did you try firebird embedded or via network (like my test)?

Last edited by nomorelogic (2022-02-22 08:11:00)

ttomas · 2022-02-22 10:38:44

nomorelogic wrote:

Lazarus 2.2
FPC 3.2.3
Firebird 3.0.7.33374

Can you try with Laz 2.0.12 and Fpc 3.2 as recommended version and trunk version of mormot2

nomorelogic wrote:

do I need any compiler directive to compile Zeos?

No, I just install Zeos from Online package manager, open mormot2 package in custom options uncheck NOSYNDBZEOS, add zdbc as required package, add include path for zeos.inc and recompile mormot2 package.

nomorelogic wrote:

did you try firebird embedded or via network (like my test)?

I try with network localhost:3040, Fb4 version is set to 3040 port.
Do you have test database created before test. I think Zeos only create db file in embedded mode. In network mode you must create database before test.
Also check db file owner, must be firebird:firebird. If you create db in embedded mode owner will be current user.

Last edited by ttomas (2022-02-22 11:01:39)

nomorelogic · 2022-02-22 14:48:56

Hi, I've some good news

Reinstalled mORMot2 (pulled latest sources) in Lazarus 2.0.12 + Zeos and Ibx packages

Using Zeos I still get same error...
Using IBX all test have success!

...
2.2. Sqlite remote: 
  - Remote sqlite socket: 160,030 assertions passed  1.41s
     10000 insert in 459.77ms i.e. 21.2K/s, aver. 45us
     10000 insert batch in 42.68ms i.e. 228.8K/s, aver. 4.26us
     10000 insert trans in 415.02ms i.e. 23.5K/s, aver. 41us
     10000 insert batch trans in 43.19ms i.e. 226K/s, aver. 4.31us
     10000 read one in 410.41ms i.e. 23.7K/s, aver. 41us
     10000 read virtual in 20.02ms i.e. 487.5K/s, aver. 2us
     10000 read direct in 10.23ms i.e. 0.9M/s, aver. 1.02us
  Total failed: 0 / 160,030  - Sqlite remote PASSED  1.42s

 2.3. Firebird: 
  - Ibx firebird: 160,031 assertions passed  11.97s
     10000 insert in 8.56s i.e. 1.1K/s, aver. 856us
     10000 insert batch in 335.61ms i.e. 29K/s, aver. 33us
     10000 insert trans in 792.20ms i.e. 12.3K/s, aver. 79us
     10000 insert batch trans in 346.01ms i.e. 28.2K/s, aver. 34us
     10000 read one in 1.52s i.e. 6.4K/s, aver. 152us
     10000 read virtual in 78.77ms i.e. 123.9K/s, aver. 7.87us
     10000 read direct in 61.43ms i.e. 158.9K/s, aver. 6.14us
  Total failed: 0 / 160,031  - Firebird PASSED  11.97s


Generated with: Free Pascal 3.2.2 64 bit Linux compiler

Time elapsed for all tests: 18s
Performed 2022-02-22 15:21:15 by userxyz on devoid

Total assertions failed for all test suits:  0 / 2,041,191
! All tests passed successfully.

Firebird benchmark vs SqLite are not so good.
I don't know if FB3 is slower than FB4, but I think this is due to network vs local.
However I'll try with Fb embedded in future.

Anyway I can't figure out why Zeos fails.
I'll try to understand.
If you have any ideas you're welcome

Thanks for your support.
nomorelogic

ab · 2022-02-22 15:11:59

Perhaps ask on the Zeos forums.

They are usually very responsive.

nomorelogic · 2022-02-23 18:42:35

I'll make some other test prior to ask about performance: I'm not very skilled in mORMot + Zeos + Firebird.

I must say that here you are doing a good job.
And I’m a beginner with these tools used together.
Thank you for your patience

mORMot Open Source

#1 2022-02-15 11:23:00

mORMot 2 ORM Performance

#2 2022-02-15 12:14:36

Re: mORMot 2 ORM Performance

#3 2022-02-15 13:16:20

Re: mORMot 2 ORM Performance

#4 2022-02-15 13:27:10

Re: mORMot 2 ORM Performance

#5 2022-02-16 09:28:37

Re: mORMot 2 ORM Performance

#6 2022-02-16 09:47:33

Re: mORMot 2 ORM Performance

#7 2022-02-16 09:56:32

Re: mORMot 2 ORM Performance

#8 2022-02-19 08:25:05

Re: mORMot 2 ORM Performance

#9 2022-02-21 15:23:27

Re: mORMot 2 ORM Performance

#10 2022-02-21 15:40:43

Re: mORMot 2 ORM Performance

#11 2022-02-21 21:32:33

Re: mORMot 2 ORM Performance

#12 2022-02-21 23:59:15

Re: mORMot 2 ORM Performance

#13 2022-02-22 01:05:48

Re: mORMot 2 ORM Performance

#14 2022-02-22 08:06:15

Re: mORMot 2 ORM Performance

#15 2022-02-22 10:38:44

Re: mORMot 2 ORM Performance

#16 2022-02-22 14:48:56

Re: mORMot 2 ORM Performance

#17 2022-02-22 15:11:59

Re: mORMot 2 ORM Performance

#18 2022-02-23 18:42:35

Re: mORMot 2 ORM Performance

Board footer