#1 2020-03-24 16:01:28

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

"Samples/15 - External DB performance/PerfTestConsole.dpr" new project

This new sample will be the new cross-platform and cross-compiler way to benchmark and validate our ORM with external data.

Some preliminary tests on Ubuntu, with our brand new SynDBPostgres unit:

Running tests using Synopse mORMot framework 1.18.5887, compiled with Free Pascal 3.1.1 64 bit, against SQLite 3.31.0, on Ubuntu 18.04.4 LTS - Linux 5.3.0-42-generic, at 2020-03-24 16:48:46.

Insertion speed
               Direct	Batch	Trans	Batch Trans
SynDBPostgres	1362	68393	5085	88671

Read speed
              	By one	All Virtual	All Direct
SynDBPostgres	5625	175463	       349577

Nice numbers I guess!

Check https://synopse.info/fossil/info/cf2408ef90

Offline

#2 2020-03-24 16:33:42

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: "Samples/15 - External DB performance/PerfTestConsole.dpr" new project

@ab -this is VM or host? What CPU? Can you run a pgbench just to have a relative numbers

// once for initialization
// pgbench postgresql://postgres:postgres@localhost:5432 -i
// and then test selection speed
// for prepared stmt
pgbench postgresql://postgres:postgres@localhost:5432 -c 4  -M prepared -T 4 -S
// and without prepare
pgbench postgresql://postgres:postgres@localhost:5432 -c 4  -M simple -T 4 -S

I fight 2-n day long with hosting where  I rent a 12-Core-XeonE5 server for test. The shits they give me works 3 times slower when my laptop (I think they have problems with hypervosor)

Offline

#3 2020-03-24 16:57:23

Márcio Baroni
Member
From: Brasil
Registered: 2015-10-07
Posts: 28

Re: "Samples/15 - External DB performance/PerfTestConsole.dpr" new project

@ab, the fossil and github synchronization problem still remains ... not in the commit then github

Offline

#4 2020-03-24 17:27:04

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: "Samples/15 - External DB performance/PerfTestConsole.dpr" new project

@Marcio
I didn't add the files to my github local repo.

Should be fixed now.

Offline

#5 2020-03-24 17:44:44

Márcio Baroni
Member
From: Brasil
Registered: 2015-10-07
Posts: 28

Re: "Samples/15 - External DB performance/PerfTestConsole.dpr" new project

Thanks @ab

 2.1. Postgresql:
  - SynDBPostgres: 80,031 assertions passed  2.12s
     5000 insert in 903.66ms i.e. 5,532/s, aver. 180us
     5000 insert batch in 134.05ms i.e. 37,298/s, aver. 26us
     5000 insert trans in 423.24ms i.e. 11,813/s, aver. 84us
     5000 insert batch trans in 53.26ms i.e. 93,866/s, aver. 10us
     5000 read one in 388.92ms i.e. 12,856/s, aver. 77us
     5000 read virtual in 21.99ms i.e. 227,303/s, aver. 4us
     5000 read direct in 10.02ms i.e. 498,902/s, aver. 2us
  Total failed: 0 / 80,031  - Postgresql PASSED  2.13s

 2.2. Zeos postgresql:
  - ZeosPostgres: 80,031 assertions passed  2.67s
     5000 insert in 836.90ms i.e. 5,974/s, aver. 167us
     5000 insert batch in 35.29ms i.e. 141,667/s, aver. 7us
     5000 insert trans in 388.70ms i.e. 12,863/s, aver. 77us
     5000 insert batch trans in 34.84ms i.e. 143,513/s, aver. 6us
     5000 read one in 462.50ms i.e. 10,810/s, aver. 92us
     5000 read virtual in 22.67ms i.e. 220,526/s, aver. 4us
     5000 read direct in 11.39ms i.e. 438,981/s, aver. 2us
  Total failed: 0 / 80,031  - Zeos postgresql PASSED  2.69s

Generated with: Delphi XE5 32 bit compiler

last zeos with USE_SYNCOMMONS active

Offline

#6 2020-03-24 19:04:02

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: "Samples/15 - External DB performance/PerfTestConsole.dpr" new project

Small fix (@ab - it strange how you can compile w/o this changes on FPC/Linux, at last I got an error) https://github.com/synopse/mORMot/pull/294

Please, add a reference PerfTestConsole.ini file into git just to know what t wrote there smile

There is AV in case ini file if missing (actually when PG password is wrong

20200324 18560400  !  +    PerfTestCases.TTestPostgresql(7ff84798d400).SynDBPostgres
20200324 18560400  !  +         SynDBPostgres.TSQLDBPostgresConnection(7ff847a2aa40).Connect
20200324 18560400  ! EXC                ESQLDBPostgres {"Message":"Connection to database postgres failed [FATAL:  password authentication failed for user \"postgres\"\n]"} [] at   $000000000074902D  CONNECT,  l
20200324 18560403  ! ERROR              SynDBPostgres.TSQLDBPostgresConnection(7ff847a2aa40) Connect: {"ESQLDBPostgres(7ff84798cd00)":{"Message":"Connection to database postgres failed [FATAL:  password authenti
20200324 18560403  !  -         00.043.138
20200324 18560403  ! EXCOS      EAccessViolation (41c451) [] at   $000000000057354A  METHODCLEANUP,  line 282 of PerfTestCases.pas  $000000000064784D  RUN,  line 1035 of ../../../SynTests.pas  $00000000006488E8
20200324 18560404  !  -    00.067.859
20200324 18560404  ! EXCOS EAccessViolation (41c451) [] at   $00000000005757FF  SAVESTATS,  line 803 of PerfTestCases.pas  $00000000005756EE  DESTROY,  line 717 of PerfTestCases.pas 

Last edited by mpv (2020-03-24 19:08:15)

Offline

#7 2020-03-24 19:28:43

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: "Samples/15 - External DB performance/PerfTestConsole.dpr" new project

Running tests using Synopse mORMot framework 1.18.5894, compiled with Free Pascal 3.1.1 64 bit, against SQLite 3.31.0, on Ubuntu 18.04.4 LTS - Linux 5.3.0-42-generic, at 2020-03-24 20:26:53.


Insertion speed (rows/second)
               Direct      Batch       Trans       Batch Trans
SynDBPostgres  1372        80652       5155        92843
Zeos postgres  1484        91195       5460        94435

Read speed (rows/second)
               By one      All Virtual All Direct
SynDBPostgres  5602        174923      350951
Zeos postgres  4197        161885      292500

So I confirm reading is faster with SynDBPostgres and writing faster with SynDBZeos...

Offline

#8 2020-03-24 19:40:30

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: "Samples/15 - External DB performance/PerfTestConsole.dpr" new project

All available tests: https://gist.github.com/synopse/53601e7 … a4e771d270
(please do not put numbers in the forum thread, but use gists as I did)

@mpv
I think I have corrected now what was missing.

Offline

#9 2020-03-24 20:05:54

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: "Samples/15 - External DB performance/PerfTestConsole.dpr" new project

My previous benchmarks were with logs enabled.
A lot of logs in fact - around 100MB.

Here are the new numbers with logs disabled:
https://gist.github.com/synopse/d2be310 … ea34f5bf1e

In practice, numbers are almost the same.
Meaning that a log of log doesn't impact the business... good to know! smile

chart?chtt=Insertion+speed+%28rows%2Fsecond%29&chxl=1:|Batch+Trans|Trans|Batch|Direct&chxt=x,y&chbh=a&chs=600x500&cht=bhg&chco=3D7930,3D8930,309F30,40C355&chxr=0,0,264900&chds=0,264900,0,264900,0,264900,0,264900,0,264900&chd=t:507,18308,50041,132703|7796,112989,55336,146322|20705,134321,55041,143299|13779,120186,55219,134134|45243,147684,55710,145408|90107,237665,122531,264900|115663,255597,119855,260538|448,9981,47545,134564|9674,103150,60436,146985|28599,134978,60428,149494|54080,149907,61707,151952|10204,71554,10700,76261|1386,91849,6379,95307|1524,84598,6367,93445&chdl=Sqlite+file+full|Sqlite+file+off|Sqlite+file+off+exc|Sqlite+file+off+exc+aes|Sqlite+in+memory|In+memory+static|In+memory+virtual|External+sqlite+file+full|External+sqlite+file+off|External+sqlite+file+off+exc|External+sqlite+in+memory|Remote+sqlite+socket|SynDBPostgres|Zeos+postgres>

Worth noting that AES encryption has almost no impact about performance.
Thanks to our AES-NI hardware accelerated SynCrypto library, for sure!

chart?chtt=Read+speed+%28rows%2Fsecond%29&chxl=1:|All+Direct|All+Virtual|By+one&chxt=x,y&chbh=a&chs=600x500&cht=bhg&chco=3D7930,3D8930,309F30,40C355&chxr=0,0,839066&chds=0,839066,0,839066,0,839066&chd=t:39185,418760,418550|38651,426657,421443|61696,421300,426949|61829,422654,424196|61333,426003,433500|130103,788270,839066|127632,324991,334291|72456,215964,396070|72026,218732,408697|70117,217183,419216|72867,220916,404989|11087,196086,342301|6480,182076,344305|5402,162924,326498&chdl=Sqlite+file+full|Sqlite+file+off|Sqlite+file+off+exc|Sqlite+file+off+exc+aes|Sqlite+in+memory|In+memory+static|In+memory+virtual|External+sqlite+file+full|External+sqlite+file+off|External+sqlite+file+off+exc|External+sqlite+in+memory|Remote+sqlite+socket|SynDBPostgres|Zeos+postgres>

Note that I have a very fast SSD (3GB/s burst).

Offline

Board footer

Powered by FluxBB