Firebird 3:Firebird 4 comparison - discussion
Following the publication of our White Paper: Firebird Performance comparison of Windows and Linux Operating Systems on November 2, 2021, the following discussion took place on November 3, 2021 with FirebirdSQL QA, regarding the correct and most realistic way to test the performance of different Firebird versions. The discussion raised a few interesting and valid points, even though we did not agree with everything claimed.
Alexey Kovyazin:
Without sources/details of test it does not look credible. Firebird OLTP-EMUL test does not confirm slowness of Firebird 4.
FirebirdSQL:
And, just noticed - Firebird 2.5 SuperServer is faster than 2.5 SuperClassic in simultaneous operations? Fikret, please double-check the results 🙂 Either an interpretation mistake or the test itself is very artificial.
Holger Klemt:
we can very simply explain what the benchmark does
db it is the same that is distributed with ibexpert as demo db. if you start the benchmark tool and close bench.exe in taskmanager while it shows "running threads", you will have the database still on the disk and can do with it whatever you want, also in your own tests. if you have the script already in your ibexpert version installed, you can also use that.
Running create database is first step and done twice, but not measured to avoid slow results on slow networks. Since free IBExpert benchmark tool always run on localhost, it will not have such a problem, but the one built in full version allows remote testing also.
So called Drive test is done with an empty database. Only tables with tmp* prefix are already filled.
For drive test, cache buffers are set to 50 (minimum possible value, uses 16k page size on fb<=3 and 32k on fb4). CPU Test is the same with a new empty db but 5000 cache buffers.
First measured operation is running procedure initall(10000) in db (named in GUI "create db").
This will run single threaded and creates a lot of test data inside the database (10000 customers, 10000 products etc) In drive test we also test temp file handling single threaded doing an ordered select on a table with 10000 records and a varchar(32000) column (this always results in a temp file creation, except you use a lot of memory for this by conf, but since we use our own fb instance in the test, it will result in tmp file creation by default).
Last (but most important) part of the test is 10 threads are created and each running create_more_orders procedure inside the database. when all threads are finished, time is taken.
So called Thread test result is calculated on based on cpu test threads test result, create db is not measured. We know that our benchmark does around 13GB filesystem page Read Write operations by the fb server process in superserver architecture and around 43 GB in *classic architecture.
Important: We start all threads with empty cache, drive i/o is needed for each connection individually. Filling the cache in advance makes no advantage when on using 50 cache buffers, but as the name says: we want to test drive i/o speed for firebird and nothing else.
This explains also why superserver even in older version is faster than classic/superclassic.
Our test represents perhaps a different read/write rate compared to other test, but in fb4 for example running initall procedure with param 10000 result in this:
- Memory buffers = 50
- Reads from disk to cache = 299.512
- Writes from cache to disk = 115.530
- Fetches from cache = 2.889.622
in fact it is running around 120000 individual inserts or updates and around 370000 individual selects (all indexed reads)
so with around 25% write/75% read operations, we think the result is a good value to compare inidivdual hardware and OS settings.
Interestingly fb4 was single threaded with initall procedure fast than all older version, but multithreaded we have always seen lower performance compared to fb3.
And we definitly have no intention to give anyone the advice not to use fb4, but from our results, real world multithreaded performance fb3 is better than fb4.
Fikret Hasovic:
@Holger Klemt Thanks for detailed explanation.
Any person can do the same on their hardware and post results
Holger Klemt:
I am very happy with your clarification and recommended additional settings or changes in benchmark test database, but you wrote and have to admit, that we use firebird.conf in default setting for all versions!
That is a fact and that results are reproducable was also confirmed from your side!
After you changes conf params from default to whatever, your results on fb4 were getting better!
That´s nice, but why is that setting not default setting?
99% of all Firebird installations worldwide are definitly done based on default conf setting and the main reason why our benchmark is still valid, it is to compare the speed of different hardware and OS settings. Is the server with architecture/cpu/mainboard/driver/OS/... faster than another with the same Firebird.
That was why we invented the test. All explained changed firebird conf params could make the fb4 installation faster, but in fact, why are they not default?
And whatever you think is done wrong in the database operations itsself, we use the same operations with the almost similar database since around 20 years and have no reason to change it because a specific firebird Version wants to do things better in different ways.
Anyone will have no problem to change the default conf and as you have seen, we simply deliver a preconfigured firebird for each version in the free benchmark versions subdirectory, together with the default conf file.
If you or any other user is interested in change it to have a better compare between fb3 and fb4, feel free to recommend it as you did here, but in fact the much better way would be that new fb version will support the params also by default, if they have no disadvantages.
that is a very simple fact and now the ball is back to the fb dev team. Why are not optimal params used for default? Or do they have disadvantages! I remember talks in berlin that not anyone was happy with the changes .... #IBExpertBenchmark
FirebirdSQL:
@Holger Klemt Did you read the explanation in the first part in details? The reason why Firebird 4 is slower is that your test does the artificial trick with order lines IDs, it is very far from any real-world application. However, as it follows from the 2nd part of the investigation, the test in the current form is not credible for any Firebird version.
Holger Klemt:
it might be your point of view, as i said, we mainly use it for hardware comparisons:
but you also recommend to use more than 5000 cache buffers, which is in fact of no advantage when full database used for testing has much less than 5000 pages at all after full test was done, what should it do with more cache pages?
there are also some issues in your explanation which are not 100% helpful and we should not discuss what real world application do.
I expect i saw more of them in my job as database software consultant since more than 25 years in interbase and firebird area.
so we should stay where we are:
i am open for discussions regarding a better fb4 setup, which i would expect to be the default conf setup, but as you showed it is not the default conf setup. Why? please anyone explain this to me!
please explain only that part to me and the readers here, not if some of my database operations are not done in the way you like it.
I also do not like a lot of the metadata in that database and nowadays i would do it better. Just have a look at firebird example database employee.fdb , it is still on the level of interbase4 25 years ago. we should also not talk about this.
My main goal with the benchmark was to have high reproducable firebird load (in less than 3.5h setup and runtime). Any generic benchmark does not what firebird does, so why compare that. i worked with firebird at customer sites on nas systems worth more millions of euro, 3PB space, capable of copying data between vm to other parts with more than 5gb per second, handling in benchmarks around 1.5 million iops as seen on benchmark, but will their vm firebird setup was much slower than my laptop!
as we wrote in our document, we were astonished by slower fb4 results also, and if we need to add more infos, we will do, but we will not use an non standard conf file and an optimized benchmark database, based on remarks, that some operations may be done better. A benchmark never does things in the best way, otherwise if anyone needs to compare the speed of several setups for firebird he can use the free version and the choose then one with better results! thats the idea. And that works perfect.
if anyone wants to optimize his local already installed firebird version by changing cache/hard/os and whatever setting, they can also use the much more flexible version in IBExpertIDE.
so from my side, i now just wait for explanation why you recommend settings are not used by default and focus my future posts here only on that!
FirebirdSQL:
Holger Klemt The document on the official FirebirdSQL site has the full and complete explanation of slow results of your test: in short, it is a test problem, not Firebird. Yes, we have demonstrated that your test, even with wrong Firebird configuration (because neither 50 nor 5000 page buffers are default, and both are bad), can be improved with configuration change, but the correct solution will be to fix the business logic to avoid/reduce random updates (and there are 2 options in the article how to do it). The correct solution will be to fix test, not default configuration, because a) your test uses artificial constructions in load simulation, b) your test runs on non-default configuration (Page Buffers in header overrides values from firebird.conf). Also, the good idea will be to move the discussion to the Firebird-devel list, since Facebook is not suitable.
Holger Klemt:
FirebirdSQL perhaps you should first write your posts signed with your own name and not as "FirebirdSQL"
Trying to speak in the name of an organisation with your universal knowledge about what is bad or not is not the way it works.
