Firebird database optimization

Firebird Performance Optimization is a service provided by IBSurgeon to improve the speed and efficiency of Firebird database and its applications. IBSurgeon has two options for optimizing Firebird (and InterBase) databases:

1. Optimization/Configuration/Audit Incident

We offer a one-time service that can make your Firebird database faster and better. We can tune your firebird.conf file, find out why your database is slow, or fix any other Firebird issue you have.

We can do this service remotely, using Zoom, Anydesk, RDP, or any other tool you prefer. We can do it as soon as tomorrow, depending on your time zone.

The service costs USD 299 and it includes 1-2 sessions with us (each session is 1-1.5 hours long). If you want to book this service, just email us at [email protected] and we will send you a link to pay. We look forward to hearing from you.

 

2. Full scale optimization

Do you want to make your Firebird database run as fast as possible? Then you need our "Full scale optimization" service. This service can fix any issue that makes your Firebird database slow. It can improve every part of your Firebird database's performance.
 
# Service Description
1 Initial analysis of database performance Database performance degradation can result from various factors and interactions among them. To achieve effective database optimization, the first step is to diagnose the main sources of performance issues.
2 Tuning of Firebird configuration file firebird.conf We create a firebird.conf file, tailored for the needs of your database and application.
3 Hardware analysis and configuration We check an existing hardware/VM and OS configuration, fix problems if possible, or provide detailed guidance on how to fix them. Also, we provide a draft of hardware specifications/VM changes if an upgrade needed.
4 SQL tuning with indices We find out which queries are the most troublesome (the ones that take the longest, run the most often, etc) and which ones affect your performance the most. We accelerate the 7 slowest or top frequent queries identified in your application or provide tuning recommendations.
5 Transactions analysis and maintenance tuning We check transactions' flow in your database and give a conclusion on how transaction control can be improved, including practical recommendations for maintenance.
Fee (+VAT) USD$ 2990
*Optimization service includes the license for IBSurgeon HQbird for 1 month.

How to start? Please feel free to ask your questions: [email protected].

Firebird database performance optimization:

what is it and how we can help to improve your Firebird database

Do you want to make your Firebird database faster and more efficient? If so, you've come to the right place. We are IBSurgeon, and we are experts in Firebird performance optimization. In this article, we will explain what Firebird performance means, how you can improve it, and how we can help you achieve it.

Please read this article carefully - it will only take you 15 minutes, and you will learn a lot about Firebird optimization. Even if you are not planning to hire us, you will still benefit from this information.

What is optimization?

Some companies claim that they can optimize your Firebird database by 100% if you buy their products or services. But what does that really mean? How do you know if your Firebird database is optimized or not?

The answer is simple: you need to measure the performance of your Firebird database using some criteria. And the most important criteria for Firebird performance are:

  • Average response time
  • Maximum response time

Response time is the time it takes for your Firebird database to execute a SQL query and return the results. It depends on many factors, such as the size and complexity of your database, the hardware and software configuration, the network and system load, and the type and number of concurrent users.

Response time is also what your end users care about the most. They will judge your software based on how fast or slow it responds to their requests. They will not be happy if they have to wait too long for your software to do its job.

How can you measure the response time of your Firebird database in a real-world scenario? You need to use a SQL logging tool that can record every SQL query that your software sends to your Firebird database, along with its execution time and SQL plan. This way, you can analyze the data and find out the average and maximum response times of your Firebird database, as well as identify the queries that cause the most delays or problems.

Criteria of Firebird performance optimization

Figure 1. Average and maximum response times

Before you start optimizing your Firebird database, you need to measure its performance. This is very important, because you need to know where you are and where you want to go. You can't optimize something without a clear goal.

How do you measure the performance of your Firebird database? You need to look at two things: the average response time and the maximum response time. These are the times that your Firebird database takes to run a SQL query and give you the results.

The average response time is the average of all the response times of your Firebird database. The maximum response time is the longest response time of your Firebird database. You want both of these times to be as low as possible, but especially the maximum response time. Why? Because the maximum response time is what your end users will notice the most. They will get frustrated if they have to wait too long for your software to respond. For example, look at figure 1. You can see that the average response time is about 250ms, which is pretty fast. But there are also two spikes of 1 second and 1.2 seconds, which are very slow. These spikes are what you want to avoid or reduce.

So, how can you lower the average and maximum response times of your Firebird database? You need to tune four things:

  • Firebird configuration: how your Firebird database is set up and what settings you use
  • Hardware configuration: how your computer or server is set up and what hardware you use
  • SQL queries tuning: how you write and improve your SQL queries and your database structure
  • Transactions tuning and workaround techniques: how you manage and optimize your database transactions and use some tricks to speed them up

Let's explore these four things and see how they can help you make your Firebird database faster and better.

Firebird configuration: architecture and configuration parameters

Firebird (versions 2.5 - 5.0) has 4 architectures: SuperServer, Classic, SuperClassic, and Embedded.

They affect how your Firebird database works and performs. In version 2.5, only Classic and SuperClassic can use more than one core or CPU in your computer or server. In versions 3.0-5.0, SuperServer can also do that.

Choosing the right architecture for your Firebird database is very important, and it will be one of the first things we will do when we consult you.

But that's not all. There are also many settings that you can change in your Firebird configuration file (firebird.conf/database.conf) and in your Firebird database itself, that can make your Firebird database faster and better. We have created a tool called Configuration Calculator for Firebird, that can help you find the best settings for your Firebird database. You can check it out here: https://cc.ib-aid.com/democalc.html.

The Calculator is not perfect, though. It can give you a good starting point, but you may need to tweak some settings further, especially if your Firebird database is very busy or has limited resources (like in a virtual machine).

That's where we come in. We can help you fine-tune your Firebird database settings to match your hardware and your needs. When we optimize your Firebird database performance, we keep an eye on how much memory your Firebird database uses and adjust all the database and server settings to fit in the available memory. But don't worry, we don't try to cram your whole database into the memory. That's a bad idea that some beginners have, and it can actually make your Firebird database slower. For example, setting the parameter DefaultDBCachePages too high can hurt your Firebird database performance.

What can you expect from Firebird configuration tuning?

The biggest improvement you can see is from changing the architecture of your Firebird database. Choosing the right architecture can make a huge difference, sometimes up to 500% or more.

But that's not the only thing you can do. You can also improve your Firebird database performance by changing other settings, usually by 20-50%.

You may think that 20% is not a big deal, but you would be wrong. Because 20% here means 20% faster average response time. And that's what matters the most. The average response time is how long your Firebird database takes to run a SQL query and give you the results. The lower the average response time, the faster your software will be.

What IBSurgeon offers for Firebird configuration tuning?

We ensure that Firebird is configured in the best way for your specific application, database, and hardware.

Hardware/VM configuration tuning

Do you want to make sure that your Firebird database runs smoothly and efficiently on your hardware or virtual machine (VM)? If so, you need to pay attention to how you set up and configure your hardware or VM.

The first thing you need to check is how fast your disk is. Your disk is where your Firebird database stores and accesses your data, so it is very important for your performance. There are many things that can affect your disk performance, such as the type of file system, the disk cache settings, and the RAID configuration.

The second thing you need to check is how powerful and suitable your CPU and RAM are. Your CPU and RAM are what your Firebird database uses to process and handle your data, so they are also very important for your performance. There are many questions that you may have, such as:

  • How many cores do you need for your Firebird database?
  • Why does your Firebird database not use all the available memory?
  • What is the difference between different cloud instances with similar specifications?

When you need to optimize your Firebird database for a VM, the task becomes more challenging. Some people may think that Firebird does not work well on VMs. But that is not true. Firebird can work very well on VMs, if you know how to configure it properly. Firebird configuration for VMs is a bit different from hardware configuration, but it is possible.

When it comes to cloud services, we know the ins and outs of different VMs and plans, and we can help you choose the best one for your Firebird database. Whether you use AWS, Azure, Digital Ocean, SaveInCloud, or any other cloud provider, we can help you find the best option.

What can you expect from hardware or VM tuning?

The right hardware or VM choice, along with other optimizations, can make a big difference in your Firebird database performance and cost. You can reduce your cloud costs by up to 50%, and save thousands of dollars with the hardware that is really fast and reliable.

What IBSurgeon offers for hardware or VM configuration tuning and upgrade?

We have a lot of experience and expertise in hardware and VM tuning for Firebird database performance. We can also help you with hardware or VM upgrade, and give you the best advice on what you need and what you don't.

In short, we make sure that your hardware or VM is the best fit for your Firebird database, and that it works at its full potential.

SQL queries tuning

One of the biggest reasons why your Firebird database may be slow is because your SQL queries are not optimized. This means that your SQL queries are not designed to handle a lot of data. When your SQL queries are not optimized, they can run 10 times, or even 100 times slower than they should. And no matter how good your hardware or configuration is, you can't make a query run 100 times faster.

But don't worry, there are some ways to make your SQL queries faster: you can create or change indices, rewrite SQL, or redesign your database structure (which also involves rewriting SQL for the new structure):

Indices

Indices are one of the best and most common ways to speed up your SQL queries. They help your Firebird database find and access your data faster. But how do you know which indices to create or change? First, you need to log all your SQL queries and find out which ones are causing the most problems. You can't just look at the execution time, because sometimes the longest query is not the worst one. You also need to look at how often a query runs, how much time it takes in total, how many reads and fetches it does, and what plan it uses. We use a tool called HQbird advanced monitoring to do this.

Then, you need to look at the SQL execution plans, the database schema, and the database performance information, to see what index could help or hurt your SQL query.

After that, you need to create the index and test the query (on a copy of your production database, not the real one) and see if it makes a difference. If the query runs faster, you can apply the index to the production database.

As you can imagine, this is not an easy task. Most of the slow SQL queries are very complex (they can be 2-3 pages long or even longer), and they require a lot of careful analysis.
SQL refactoring

Sometimes, indices are not enough, and you need to change the SQL itself. For example, Firebird does not handle the IN clause very well, and replacing it with an INNER JOIN can make a query run much faster (10 times or more).

SQL refactoring means that you need to have access to the source code of your software, so you can change the SQL there. You also need to make sure that the new SQL gives you the same results as the old one.

Schema refactoring

Sometimes, even changing the SQL is not enough, and you need to change the structure of your tables. This is a big deal, and you can only do it with the help of the developers who made your software.

What if you can't change your software code?

Maybe you don't have the source code of your software, or maybe your software is too old to change. But you still want to use the optimized SQL queries that we suggest. Is there a way to do that? Yes, there is. We have a solution that can replace the SQL queries on the fly. It's called HQbird, and it can change the SQL queries inside the Firebird engine. This way, when your software runs a slow query, Firebird will run a faster one instead.

What can you expect from SQL tuning
With the right tuning, your SQL queries can run much faster, from 20% to 10000%. (yes, it is correct, some queries can run 100 times faster)

What IBSurgeon offers for SQL tuning?

We make sure that your SQL queries run as fast as possible. We offer a service that includes optimizing 7 of your most problematic queries with indices. If you have more slow queries, or you need to change your SQL schema, we can give you a custom quote.

Transactions handling and Maintenance tuning

Often we get asked to help with Firebird performance that gets worse as the day goes on, like in Figure 2:

Performance degradation

Figure 2. Performance degradation - average response time gets higher during the day.

This usually means that there are too many record versions that slow down the reading and writing of data. To fix this, we need to change how the transactions are controlled in the software: transactions should be as short as possible, or at least shorter than they are now.

But what if you don't have the source code of your software, or you can't change it because it's too old? Don't worry, we have some ways to deal with this or make it less of a problem.

Another performance issue, also related to transactions, is when there are times of slowness (see Figure 3):

peaks and periods of slowness

Figure 3. Periods of slowness

During these times, the response time for normal operations gets much higher, and users complain that it's «very slow».

These times are probably caused by sweep operations in the database, which need to be controlled more carefully.

What can you expect from transactions tuning?

Transactions tuning is the key to stop the performance from getting worse and to avoid the times of slowness. After we tune the transactions, the average response time will be low and steady.

What IBSurgeon offers for transaction handling and maintenance tuning?

We can't tune the transactions without looking at how they work, how the SQL queries work, and how the database is structured. Luckily, we have a tool for that: HQbird. It has a transaction monitor that shows us how the transactions change, and tools to analyze the SQL queries, the transactions, and the database structure.

Transaction tuning is not possible without thorough analysis of transactions dynamics, SQL logs and database structure: fortunately, we have tool for it: HQBird includes transaction monitor to watch for dynamics of transactions, tools to analyze SQLs, transactions, and database structure.

Please feel free to ask your questions: [email protected].

Contact us now

Send inquiry to [email protected] and we will contact you shortly.

More information

Our tests

We like to test our tuning techniques a lot, to make sure they work well in practice.

We've done some tests based on TPC standards (TPC-R and TPC-C) that gave us some interesting results and some useful tips and tricks about how to set up and use your hardware better. One of the tests was to make and test a 1.7 Terabyte Firebird database.

We've also done some tests with Firebird databases from 9Gb to 1.7 Terabyte, to see how Firebird performance gets worse and how to fix it.

We are maintaining our own Insert Update Delete Test, and support development of Firebird OLTP-EMUL test, which is used in the everyday performance tests.

Our customers

Here's what one of our customers said about us:

"They used their own tools and methods to look at our database structure and our dialer software and came up with a plan to make our database services better. In no time, our dialer system was super fast. Users said it was 300 per cent faster and the database was stable and reliable. We couldn't believe how IBSurgeon made our system run so fast."

Our customers have real-world databases up to 4 Terabytes that we helped to tune. You can be one of them.