InterBase 7.5.1 and Firebird 1.5.2 TPC-R based test results
First thing I’d like to apologizes to all readers for delay with publishing TPC-R based test (see “How to run…” here) results which were announced in Issue 3. We’ve encountered some non-expected results and checked them twice before published.
Our first goal was testing of existed current release versions of InterBase and Firebird – InterBase 7.5.1 and Firebird 1.5.2 accordingly. Well, we’ve encountered problems with InterBase 7.5.1 testing – it generated so bad plan for queries 3 and 20 that we were not able to wait its finishing, so these queries were omitted in test results.
The overall conclusion is that InterBase 7.5.1 has problem with optimizer work and as result many queries are executed with bad plans. Also it seems like data access methods is worse than Firebird, because queries with the same plans usually executed slower.
Hardware and software configuration
Test results shown below are gathered from computer Pentium 4, 2.4Ghz, 1Gb RAM DDR3200, HDD 160Gb. Windows XP SP2 is installed, system restore is turned off.
We’ve run test on several configurations (Windows XP/2003 and Linux, database size coefficient = 1 and 10) and encountered similar results.
Thanks
Many thanks to Anton Glazunov and Sergey Mereutsa for assistance!
Results
Full logs are available here (zipped txt format):
Firebird 1.5.2 SuperServer
InterBase 7.5.1
Execution time and queries plans are available here (zipped Excel):
http://www.ibdeveloper.com/test/tpcr_ib751_fb152.zip
Below you can find table with results (execution time only) for InterBase 7.51. and Firebird 1.5.
| # | Firebird 1.5 | InterBase 7.5.1 |
| 1 | 145.87 | 258.94 |
| 2 | 47.2 | 220.53 |
| 3 | 360.83 | skipped* |
| 4 | 125.31 | 170.99 |
| 5 | 217.19 | 254.89 |
| 6 | 56.02 | 309.18 |
| 7 | 390.54 | 627.95 |
| 8 | 354.14 | 376.03 |
| 9 | 4417.47 | 6526 |
| 10 | 357.58 | 365.98 |
| 11 | 131.35 | 132.17 |
| 12 | 145.69 | 366.5 |
| 13 | 339.75 | 497.74 |
| 14 | 237.57 | 393.39 |
| 15 | 25.92 | 72.87 |
| 16 | 10.83 | 30.45 |
| 17 | 27.09 | 45.02 |
| 18 | 66.95 | 126.18 |
| 19 | 190.95 | 12.98 |
| 20 | 1812.91 | skipped* |
| 21 | 433.62 | 1003.56 |
| 22 | 126.05 | 200.13 |
* means that execution time was too long to wait for query finishing (6 hours and more).
Sincerely yours,
Alexey Kovyazin
test@ibdeveloper.com
December 13th, 2005 at 2:32 am
Very interesting… It would be informative to see these tests with MySQL and PostgreSQL also. If you did, this site would be slashdotted and digged so be prepared!
December 13th, 2005 at 5:31 am
Thanks guys for doing the tests, and thanks Arno for being the optimizer man :-))
December 13th, 2005 at 6:59 am
It would be more interest if the no of pages increase from 2048 to 8192 (or higher) to see the the effectiveness of caches
Great work - Firebird team.
December 13th, 2005 at 10:04 am
Informative information. Firebird Team tops.
Where can we get the (FB) Database the tests were performed on, Meta Data as well as test data and queries? Should be interesting to port to other RDBMS’s and perform the same queries on the resultant database.
December 13th, 2005 at 11:29 am
Hello everyone,
Thanks for attention. Next step will be testing beta versions (Vulcan and Firebird 2), simultaneously we’re working at TPC-C, which is closer to real-life. After that we have plans to run such test on other DBMS.
Marius, all sources for tests are available here http://ibdeveloper.com/tests/tpc-r/how-to-run-tpc-r-based-test/
Sincerely, Alex Kovyazin
December 13th, 2005 at 12:35 pm
great work guys! thanks you all.
December 13th, 2005 at 4:58 pm
I am not that familiar with TPC-R benchmarks. Does it factor in scalability? It is my understanding that the major advantage of InterBase over Firebird is the SMP support and the reworked memory manager. We had serious scalability problems with Firebird 1.0.x, and bailed out to persue the InterBase 7.x product. It saved our bacon.
It appears that Firebird is faster than InterBase in a foot race. Can you compare the scalability of the two databases?
December 13th, 2005 at 5:29 pm
Hello, Tom!
TPC-R based test is for optimizer work and data access (fetch records, access to indices, etc). Of course, experienced develeloper can use explicit query plans to replace bad plans.
TPC-C based is the next step of our test program. It simulates OLTP system work and can be used as scalability test.
Sincerely, Alexey Kovyazin
December 14th, 2005 at 4:35 am
Nice work Alex.
If these results are reflective of the normal sorts of queries, then Firebird embarasses its distant cousin. I would definately like to know how FB 2 fairs in relation to this.
The only test where FB clearly fails is 19. Is there any reason why FB is slower on this query?
Perhaps some additional information like a “winner” column which lists FB, IB, or draw. I would ignore small differences such as 11 as possibly caused by environmental factors as “draw”.
December 14th, 2005 at 12:30 pm
I don’t trust your results. Maybe you did a bad job designing the statements. Where is the sample database and where are the complete statements?
Sincerely, Geri
December 14th, 2005 at 12:59 pm
While I do not question the results (they seem impressive indeed for Firebird), you should mention at least what the tuning parameters are of the firebird and interbase databases and servers: page size, various memory sizes etc. As far as I know, these parameters have different default values for IB/FB, and you should take them into account as well, because they affect performance in a major way (I did a lot of tests myself). It’s not correct to compare a firebird installation with 100.000 mem pages with an interbase installation with 2048 mem pages.
For clarity, you also might want to consider putting units in your time table. I assume they are times in seconds, but it could just as well be milliseconds, hours, days.
Nevertheless, it’s good to see some results published.
December 14th, 2005 at 10:35 pm
I use InterBase 7.5.1 and the performance is much better than FireBird 1.5.2, this result don’t test concorrency….
Before migrate to InterBase 7.5.1 I used FireBird 1.5.2 and I had many performance problems, problem like adhoc querys stoped FireBird server, in InterBase I don’t have problem with adhoc querys because server is mult-thread.
Sincerely, Jorge
December 15th, 2005 at 12:42 am
Hello!
Thanks for your comments!
Please take some time to download, investigate and perform tests youself. All you need is here: http://ibdeveloper.com/tests/tpc-r/how-to-run-tpc-r-based-test/
Check design of sqls there (and compare them with sources at tpc.org), then check config settings and run tests.
We will be glad to publish all results you will receive.
With best regards, Alexey Kovyazin
December 15th, 2005 at 10:42 pm
I need to say that there was no tuning for these tests. Why? At first, we used default ibconfig and firebird.conf, just changed (added) temporary file directory, and checked that ALL default parameters for both servers are equal. Second, we checked each server performance report to check where and why results are different (if any). Also we controlled how much memory and processor each server uses. For example, for queries 3 and 20 that I’ve commented here, there were only 3mb RAM usage difference - InterBase used 20Mb RAM, but Firebird - 17Mb RAM.
We provided test scripts, ready to use, so anyone who don’t believe results can check it by himself. BTW, while Alexey did tests on “fresh restored” databases, I’ve made tests with “fresh created” databases. There were some performance difference, but same for both InterBase and Firebird.
Someone can try TPC-R test with Database Cache Pages set to 16K or 32K, or 64k (Firebird 1.5 can’t use more than 64k pages for cache, while IB 7.5 can use 131k pages). You can get a bit different results. If you wish us to redo tests for specific configuration parameters, I can repeat them full or selected queries.
December 15th, 2005 at 10:51 pm
Jorge wrote: “I use InterBase 7.5.1 and the performance is much better than FireBird 1.5.2, this result don’t test concorrency….” Yes, of course, concurrency is checked by TPC-C test. Anyway, we all know that InterBase 7.x have temporary system tables that helps to optimise queries and server performance, and it supports SMP for SuperServer, while Firebird - don’t.
So, look at these TPC-R test as “what will be if I’ll run queries like that”.
December 16th, 2005 at 7:30 am
Guys!!! don’t play the full. Read the original Spec for the test and then the implentation used. As they said, this kind of test is more like and Algorimth test.
December 16th, 2005 at 8:35 am
Can you comment on ANY differences in performance between Windows and Linux?
December 17th, 2005 at 2:51 am
Muy interesante el articulo, sera que para una proxima evaluacion se puede hacer un articulo en mas pero en español
December 20th, 2005 at 11:08 pm
Muito interessante, é otimo ter uma base para comparação … Precisamos ter mais artigos deste genero, como por exemplo comparando o firebird com POSTGRESQL, SQLServer, entre outros.
December 21st, 2005 at 1:01 am
[…] ** Los detalles sobre el hardware y configuración utilizados para realizar las pruebas están disponibles en el artículo original: Interbase 7.5.1 & Firebird 1.5.2 TPC-R based test results […]
December 21st, 2005 at 3:41 pm
I´d like to know what is the size of the database e the total number of records in each table.
December 21st, 2005 at 10:13 pm
I Would like to see comparative tests between Firebird versus MySQL and Firebird versus PostGres
December 26th, 2005 at 2:57 pm
I’d like to know too about comparison test Firebird vs MySQL and PostgreSQL. Very interesting, specially vs MySQL. Where Firebird had more power in perfomance?
December 30th, 2005 at 10:02 pm
Why were the queries not written using the SQL-92 join syntax? The Interbase optimizer does a much better job with that syntax than the old style join syntax.
January 2nd, 2006 at 2:47 pm
Hello alll!!! We ha’ve no doubt that Firebird soon will be the first option in mind when asked about SGDB. Good job guys.
January 11th, 2006 at 8:59 pm
This results are very interessant. Thanks guys, this was a great work! BUT. To make a real comparison between the systems it would be necessery if the same database would be prepared and processed by supplier’s teams. Why? They know really good the possibilities of his own systems, they could fit his system to the database btw. the database to his system. May be by this way the implementation could differ but this could show in whole complexity the possibilities of each system. Again thanks for your great work.