Library

Firebird Performance Newsletter: Issue 1

We decided to launch a more or less regular "Firebird performance newsletter", devoted to performance tests, tips, tricks, configuration improvements, etc. 

In the first issue, we have the following:

• Comparing Firebird 4 vs Firebird 3 Write Performance,
• Choosing the best AWS EC2 instance for the maximum Firebird write performance,
• Recent results from INSERT/UPDATE/DELETE test

Firebird 4 vs Firebird 3: Good News, Everyone!

Since 2019, when we have published the first collection of results from the simple INSERT/UPDATE/DELETE test, many people have sent us results from their Firebird servers, and we added them to the spreadsheet and appropriate graph.
The test is a simple, but powerful tool to measure and compare the performance of different hardware+Firebird configurations, to allow quickly confirm or refute the hardware or configuration problem.
Recently we have used this test to compare INSERT/UPDATE/DELETE performance of Firebird 4.0 (version 4.0.0.2394, a few builds from the release) and 3.0 (version 3.0.8.33426, snapshot, pre-release of upcoming 3.0.8, it is stable as a minor release, according to the Firebird auto-tests).

The testing environment was Intel i3-10100F 3.60GHz with SSD Samsung SSD 870QVO and RAM drive (qSoft), with various sizes of RAM (16,32,64) and Page Buffers.

Below is the graph, and here is the XLS spreadsheet with the results.

As you can see, in the same conditions and with the same configuration, Firebird 4 is approximately 10% faster than 3.0.8 on write operations. This is definitely good news and just another sign that it is time to take a closer look at the upcoming release and start preparations for the migration.

Of course, the best approach will be to run the same test on your own server and see the actual improvement by yourself.

See how to do it >>

Choosing the best AWS EC2 instance for the maximum Firebird write performance

However, AWS offers a lot of different instance types, how to choose the best one? Of course, do the tests!

We did the simple INSERT/UPDATE/DELETE tests for the 20 instance types and found several really good options for Firebird.

Please note – all prices in the calculations and graphs below are for the Frankfurt region of AWS EC2, they are taken as-is from aws.amazon.com, without any discounts, they can be subject to additional taxes, and they can change over time - so please don’t consider prices below as finals or as an exact buying guide.

Here is the overall graph and XLS spreadsheet  with results:

For simplicity, we have created the column Operations, which essentially is a sum of Inserts+Updates+Deletes, and used it as the united write performance metric:

​As you can see, the following 3 instance types are leaders in performance (from the point of view of Firebird writes operations):

Interesting that the leaders are not the most expensive instance types! Of course, we need to keep in mind that the test is single-threaded (and does not benefit from the number of cores), and does not require a large amount of RAM (because the database is only 3.6Gb), but for the applications which require quickly process peaked write operations, these instances look really optimal.

Despite the fact that these instances types are not the most expensive (among tested), they are still expensive enough to think twice about the budget, and since one of the advertised advantages of the cloud is flexibility, it makes sense to start with cheaper VM instances types, which could be good enough for serve our Firebird database, right?

To find out the optimal cost/performance instance types we created another column in our spreadsheet: “Operations per 1 USD”.

It means exactly what it means – how many write operations you can buy for 1 USD.

The formula is the following

Operations_Per_Second * 3600 seconds in hour / Price per Hour

​

As you can see, from this point of view, the leaders are different:

The very interesting is #3, m5dn.xlarge with peak performance ~40K/per second – it is pretty close to the performance leader z1d.xlarge with 45834 operations/second, but significantly cheaper.

In general, our experience with AWS EC2 shows that it is a stable mature environment, with many good security/backup/high-availability/etc features, but, like any complex platform, it requires experience (or external expertise) to make the proper choice and do not pay overwhelming bills for applications with the non-fantastic load.

Recent results from INSERT/UPDATE/DELETE test

We have published a graph with recently collected results and an XLS spreadsheet to play with it, so you can check it yourself here.

Below are 3 obvious conclusions:

1. NVME drives really rock, so if you need the powerful Firebird server, purchase NVME for databases.
2. The high CPU frequency is very important for the high performance of Firebird databases. Often vendors push you to buy multi-core processors with lower frequency (<3Ghz), but this can not the best option for Firebird, and fewer cores with higher frequency can give a better result.
3. If your database load profile is heavily write-oriented, try to reduce Page Buffers: make experiments with DefaultDbCachePages like 50K, 100K, 250K, etc. It will be cool if you share the results with us!

In the next issues of "Firebird Performance Newsletter"

If you are interested and would like to receive notifications about new issues, join us on FirebirdSQL Telegram Channel.

Contact us