Brutal slow down performances using INSERT/UPDATE/DELETE test between Firebird 2 and Firebird 3/4/5 on Linux

[medi6]

Hi everyone,

and thanks for this beautifull project.

I'm creating this issue because we have got big differences in results using ib-aid test with firebird Super Server docker images
(jacob alberty for fb 2.5, fb3 and fb 4.0.2-4.05, official firebird image for fb5).
https://ib-aid.com/en/simple-insert-update-delete-test-for-firebird/#how_to_run_firebird_performance_test

Each test is executed individually on the same virtual machine, many times, without any specific configuration, and here are the results approx' :

FB2.5.9 :
INSERTS 12000/s
UPDATES 9500/s
DELETES 16100/s

FB 3.0.10 :
INSERTS 5500/s
UPDATES 1700/s
DELETES 1400/s

FB4.0.5 :
INSERTS 5500/s
UPDATES 1600/s
DELETES 1700/s

FB5.0.2 :
INSERTS 5200/s
UPDATES 1400/s
DELETES 1600/s

What could it be ?

Are there any default configuration differences that could cause this?

Thanks for all,

[sim1984]

Each test is executed individually on the same virtual machine, many times, without any specific configuration, and here are the results approx' :

This is where your mistake lies. Starting with Firebird 3.0, the default architecture is Super Server, while in Firebird 2.5 it is a classic server. But the default page cache for Super Server is a tiny 2048 pages. Try increasing it to at least 50,000 pages and compare the results.

[EPluribusUnum]

@medi6 write speed is affected by many factors, you should include those information also in the xls:

• forced writes (on/off)
• file system and FS parameters (eg barrier)
• hyper-thread (on/off)
• p-core vs. e-core numbers
• RAID (no, HW, SW)
• operating system and its confiugration (power saving mode, domain controller, etc...)
• multithread test (newer FB version focused on mutithreaded performance, not single threaded)

Your test is a very basic test and tells very little about real life performance.

[medi6]

Each test is executed individually on the same virtual machine, many times, without any specific configuration, and here are the results approx' :

This is where your mistake lies. Starting with Firebird 3.0, the default architecture is Super Server, while in Firebird 2.5 it is a classic server. But the default page cache for Super Server is a tiny 2048 pages. Try increasing it to at least 50,000 pages and compare the results.

Hi,

Thanks for your answer.

I'm using Super Server instance for all versions...

[livius2]

Such a slowdown cannot be caused by settings alone, as:

• Inserts are slower by 2.3×
• Updates are slower by 6.7×
• Deletes are slower by 10×

Also, @medi6 mentioned that everything was running in SuperServer mode.

[medi6]

@livius2 thanks.

The problem, is that i've got the same bad results on my own PC, in my WSL environment (Ubuntu 22.04).
Pc config : proc i7 11800h - 8 cores - 32gb ram - 1TO SSD)

### Results of the test with fb 2.5 - super server

INSERTED_ROWS 1000000
ELAP_MS 38564

Current memory = 35297056
Delta memory = 192344
Max memory = 37506208
Elapsed time= 38.57 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 148698
Writes = 291738
Fetches = 26512586
Current memory = 35254968
Delta memory = -42088
Max memory = 37506208
Elapsed time= 0.01 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 1
Writes = 2017
Fetches = 2

UPDATED_ROWS 1000000
ELAP_MS 66234

Current memory = 35334744
Delta memory = 77656
Max memory = 37550008
Elapsed time= 66.24 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 974042
Writes = 1133797
Fetches = 37508122
Current memory = 35289744
Delta memory = -45000
Max memory = 37550008
Elapsed time= 0.01 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 1
Writes = 1922
Fetches = 2

DELETED_ROWS 1000000
ELAP_MS 49780

Current memory = 35321488
Delta memory = 29624
Max memory = 37550008
Elapsed time= 49.79 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 1632831
Writes = 1659543
Fetches = 36795396
Current memory = 35293712
Delta memory = -27776
Max memory = 37550008
Elapsed time= 0.00 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 1
Writes = 170
Fetches = 2

### Results with fb 4.0.5 - super server

INSERTED_ROWS 1000000
ELAP_MS 260416

Current memory = 37578592
Delta memory = 214672
Max memory = 39950448
Elapsed time = 260.422 sec
Cpu = 41.390 sec
Buffers = 2048
Reads = 152828
Writes = 278628
Fetches = 25333512
Current memory = 37543344
Delta memory = -35248
Max memory = 39950448
Elapsed time = 2.205 sec
Cpu = 0.000 sec
Buffers = 2048
Reads = 1
Writes = 2039
Fetches = 2

UPDATED_ROWS 1000000
ELAP_MS 1006820

Current memory = 39723392
Delta memory = 2174736
Max memory = 42096656
Elapsed time = 1006.823 sec
Cpu = 72.460 sec
Buffers = 2048
Reads = 982639
Writes = 1108877
Fetches = 32300335
Current memory = 39683376
Delta memory = -40016
Max memory = 42096656
Elapsed time = 2.207 sec
Cpu = 0.020 sec
Buffers = 2048
Reads = 1
Writes = 2037
Fetches = 2

DELETED_ROWS 1000000
ELAP_MS 2679942

Current memory = 39764016
Delta memory = 1425440
Max memory = 42125600
Elapsed time = 2679.953 sec
Cpu = 132.150 sec
Buffers = 2048
Reads = 1611416
Writes = 1611337
Fetches = 32410167
Current memory = 39721456
Delta memory = -42560
Max memory = 42125600
Elapsed time = 3.534 sec
Cpu = 0.010 sec
Buffers = 2048
Reads = 1
Writes = 2026
Fetches = 2

I don't know what to think about this...

[aafemt]

From your numbers, inserts:

Reads = 148698 vs 152828
Writes = 291738 vs 278628
Fetches = 26512586 vs 25333512

It is definitely not difference in I/O.

[medi6]

From your numbers, inserts:

Reads = 148698 vs 152828 Writes = 291738 vs 278628 Fetches = 26512586 vs 25333512

It is definitely not difference in I/O.

Ok, but it's slower as you can see in elapsed time.

[medi6]

Other tests : I've got the same problem with a real Firebird Linux installation (without Docker)...

[medi6]

UPDATE : FB4 is faster in Windows environment than in Linux Ubuntu WSL or Linux Docker...

### Firebird Super Server 4.0.5 Windows 11

INSERTED_ROWS 1000000
ELAP_MS 53311

Current memory = 37563792
Delta memory = 227808
Max memory = 39935648
Elapsed time = 53.312 sec
Buffers = 2048
Reads = 154766
Writes = 296611
Fetches = 25334089
Current memory = 37528160
Delta memory = -35632
Max memory = 39935648
Elapsed time = 0.190 sec
Buffers = 2048
Reads = 1
Writes = 2030
Fetches = 2

UPDATED_ROWS 1000000
ELAP_MS 108870

Current memory = 39708624
Delta memory = 2175152
Max memory = 42081888
Elapsed time = 108.871 sec
Buffers = 2048
Reads = 985374
Writes = 1116550
Fetches = 32300395
Current memory = 39668192
Delta memory = -40432
Max memory = 42081888
Elapsed time = 0.179 sec
Buffers = 2048
Reads = 1
Writes = 2026
Fetches = 2

DELETED_ROWS 1000000
ELAP_MS 112503

Current memory = 39749184
Delta memory = 237856
Max memory = 42110768
Elapsed time = 112.505 sec
Buffers = 2048
Reads = 1613509
Writes = 1613583
Fetches = 32317238
Current memory = 39706272
Delta memory = -42912
Max memory = 42110768
Elapsed time = 0.149 sec
Buffers = 2048
Reads = 1
Writes = 2012
Fetches = 2

### Firebird Super Server 2.5.9 Windows 11

INSERTED_ROWS 1000000
ELAP_MS 59364

Current memory = 35296336
Delta memory = 191528
Max memory = 37506784
Elapsed time= 59.38 sec
Buffers = 2048
Reads = 148336
Writes 290908
Fetches = 26513485
Current memory = 35254248
Delta memory = -42088
Max memory = 37506784
Elapsed time= 0.53 sec
Buffers = 2048
Reads = 1
Writes 1925
Fetches = 2

UPDATED_ROWS 1000000
ELAP_MS 92741

Current memory = 35334080
Delta memory = 77712
Max memory = 37549344
Elapsed time= 92.75 sec
Buffers = 2048
Reads = 974907
Writes 1130853
Fetches = 37508034
Current memory = 35289080
Delta memory = -45000
Max memory = 37549344
Elapsed time= 0.79 sec
Buffers = 2048
Reads = 1
Writes 1982
Fetches = 2

DELETED_ROWS 1000000
ELAP_MS 73646

Current memory = 35337224
Delta memory = 42896
Max memory = 37549344
Elapsed time= 73.66 sec
Buffers = 2048
Reads = 1614651
Writes 1615150
Fetches = 36776328
Current memory = 35293048
Delta memory = -44176
Max memory = 37549344
Elapsed time= 0.83 sec
Buffers = 2048
Reads = 1
Writes 1424
Fetches = 2

[sim1984]

I don't understand where such a huge number of Reads on the Insert test came from. As I said before, it doesn't make sense to test super server on the default configuration. The value of 2048 pages was invented in the time of the dinosaurs. Superserver likes a large page cache.

[medi6]

@sim1984 Ok, but why FB4 is much faster with the same config on Windows ?
I'll do some test with a native Linux install (not in WSL or VM) to see if there is somes differencies.
The fact is that, with standard config, in WSL or VM (stored on a big Nutanix conf), "dockerised" or not, FB3-4-5 are much slower than FB2 - Linux and Windows version - and FB4 - Windows version.
I'm trying different distros actually to check...

[medi6]

Same result on Fedora, Kali on my WSL : FB3-4-5 are very slow, slower than FB2.

[medi6]

I confirm, with a native Linux Ubuntu 24.04 installation (on Hp Probook G8 SSD 450go 8go RAM) :

Firebird 5.0.2 Super Server

INSERTED_ROWS 1000000
ELAP_MS 260393

Current memory = 37381952
Delta memory = 234112
Max memory = 39753488
Elapsed time = 260.387 sec
Cpu = 64.360 sec
Buffers = 2048
Reads = 156959
Writes = 282712
Fetches = 25326459
Current memory = 37377440
Delta memory = -4512
Max memory = 39753488
Elapsed time = 2.202 sec
Cpu = 0.020 sec
Buffers = 2048
Reads = 1
Writes = 2043
Fetches = 2

UPDATED_ROWS 1000000
ELAP_MS 913430

Current memory = 39558224
Delta memory = 2175344
Max memory = 41931168
Elapsed time = 913.426 sec
Cpu = 123.590 sec
Buffers = 2048
Reads = 984023
Writes = 1110280
Fetches = 32300287
Current memory = 39554304
Delta memory = -3920
Max memory = 41931168
Elapsed time = 2.056 sec
Cpu = 0.030 sec
Buffers = 2048
Reads = 1
Writes = 2042
Fetches = 2

DELETED_ROWS 1000000
ELAP_MS 893889

Current memory = 39638336
Delta memory = 704544
Max memory = 42008624
Elapsed time = 893.877 sec
Cpu = 108.400 sec
Buffers = 2048
Reads = 790562
Writes = 1121937
Fetches = 18153224
Current memory = 39619152
Delta memory = -19184
Max memory = 42008624
Elapsed time = 2.356 sec
Cpu = 0.030 sec
Buffers = 2048
Reads = 824749
Writes = 495453
Fetches = 14133266

Firebird 2.5.9 Super Server (docker)

Use CONNECT or CREATE DATABASE to specify a database

INSERTED_ROWS 1000000
ELAP_MS 34904

Current memory = 35296992
Delta memory = 192280
Max memory = 37506144
Elapsed time= 34.90 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 149097
Writes = 292402
Fetches = 26507626
Current memory = 35254904
Delta memory = -42088
Max memory = 37506144
Elapsed time= 0.01 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 1
Writes = 2013
Fetches = 2

UPDATED_ROWS 1000000
ELAP_MS 52065

Current memory = 35334680
Delta memory = 77656
Max memory = 37549944
Elapsed time= 52.07 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 971476
Writes = 1131478
Fetches = 37508320
Current memory = 35289680
Delta memory = -45000
Max memory = 37549944
Elapsed time= 0.01 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 1
Writes = 1983
Fetches = 2

DELETED_ROWS 1000000
ELAP_MS 35242

Current memory = 35321424
Delta memory = 26496
Max memory = 37549944
Elapsed time= 35.24 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 1651625
Writes = 1685191
Fetches = 36793516
Current memory = 35293648
Delta memory = -27776
Max memory = 37549944
Elapsed time= 0.00 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 1
Writes = 64
Fetches = 2

[medi6]

In resume, Firebird Super Server perfs with basic configuration :

Firebird Super Server Version	OS	VM ?	Docker ?	Speed
2.5.9	Windows 11	No	No	OK
2.5.9	Ubuntu 22.04	Yes (WSL)	Yes	OK
2.5.9	Ubuntu 22.04	Yes (Nutanix)	Yes	OK
2.5.9	Ubuntu 24.04	No	No	OK
3.0.1	Ubuntu 22.04	Yes (WSL)	Yes	Down
3.0.8	Ubuntu 22.04	Yes (WSL)	Yes	Down
4.0.5	Ubuntu 22.04	Yes (WSL)	Yes	Down
5.0.2	Ubuntu 22.04	Yes (WSL)	Yes	Down
5.0.2	Ubuntu 22.04	Yes (WSL)	No	Down
4.0.5	Ubuntu 22.04	Yes (Nutanix)	Yes	Down
4.0.5	Windows 11	No	No	Ok
5.0.2	Windows 11	No	No	Ok
5.0.2	Ubuntu 24.04	No	No	Down
5.0.2	Fedora	Yes (WSL)	No	Down
5.0.2	Kali	Yes (WSL)	No	Down