Database performance optimization

IBSurgeon offers an optimization service to improve Firebird (and InterBase) databases performance. It includes the following:

Optimization service, Level 1

# Service Description
1 Initial analysis of database performance We determine average and maximum response times for your Firebird database, and define bottlenecks.
2 Tuning of Firebird configuration file firebird.conf We create special firebird.conf file, tailored for the needs of your database and application.
3 Hardware analysis and configuration We check an existing hardware configuration, fix problems if possible or provide detailed guidance how to fix them. Also we provide draft of hardware specification if upgrade needed.
4 SQL tuning with indices We create indices to accelerate the 5 slowest queries identified in your application.
5 Basic transactions analysis We check transactions' flow in your database and give a conclusion how transactions control can be improved.
Price EUR 299
*Optimization service Level 1 includes license for IBSurgeon Optimization Pack for 1 month.

Optimization service, Level 2

Optimization service Level 2 includes all positions from Level 1, and the following positions:

# Service Description
1 SQL tuning of all slow queries, either with indices or with SQL refactoring. All slow queries will be optimized. It includes refactoring of stored procedures and triggers, if necessary.
2 Schema refactoring If necessary, we help you with the database schema refactoring.
3 Transactions control redesign We help you to create the best possible transaction control in your application. If it is not possible to fix problems with transactions in the source code of your application, we provide you with workarounds to reduce negative effects of bad transcation control.
Price Individual quote
Optimization service Level 2 is extension of Level 1, it cannot be shipped separately.

How to start?

  1. Click here to pay for optimization service level 1 – it is a mandatory fee to start optimization
  2. Fill out this form and submit it to us.
Please feel free to ask your questions: support@ib-aid.com.

Firebird database performance optimization:

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

Here we describe what Firebird performance is, how Firebird database work can be improved, and how we at IBSurgeon can help you with it.

Please spend 15 minutes to read it carefully – even if you don't have an intention to order our Firebird optimization service, you will get better perception of optimization problems with Firebird.

What is optimization?

Nowadays some companies promise «100% optimization» for your Firebird database, in case if you buy their hardware, or virtualization solution, or software, or some kind of [unclearly described] service.

If you need an actual improvement of Firebird database performance, the first thing you need to define is criteria of performance optimization. And, Firebird database performance has such criteria:

  • Average response time
  • Maximum response time

Response time should be measured with your real-world application and with your production database (or in test environment which simulates production close enough).

There is no reason to measure average response time out of the scope of your real application, until you want some abstract «optimization» - or you are using test environment which is proven to be very close or even identical to your production database.

Response time is what your end users will refer as «slow», «very slow» or «good enough» (end users never say that your software is «fast»).

How to measure the average and maximum response times in real-world production environments running Firebird? There should be [transparent] SQL logging tool, installed on typical workstations in your production environment, which logs every SQL query with its execution time and SQL plan, in order to analyse gathered data and find average response time and maximum response time (i.e., peaks):

Criteria of Firebird performance optimization

Figure 1. Average and maximum response times

The initial measurement of Firebird database performance is a mandatory step for any optimization service – it is not possible to set an optimization goal without such starting point.

Most often Firebird database performance should be optimized with the goal to decrease the maximum response time, while the average time is usually good enough for end-users (it is not a rule, of course). For example, at the figure 1 you can see that average response time is ~250ms, which is considered by majority of users as «fast enough», and there are 2 peaks around 1 second and 1.2 second, which are noticeable delays.

After setting the goal we can start figuring out how average and maximum response times can be decreased. There are 4 main tuning areas:

  1. Firebird configuration: architecture and configuration parameters
  2. Hardware configuration: tuning and upgrade
  3. SQL queries tuning: a) Indices b) Refactoring of SQL queries c) Refactoring of database schema
  4. Transactions tuning and workaround techniques

Let's go through these areas and see what can be done to improve Firebird performance.

Firebird configuration: architecture and configuration parameters

Firebird (version 2.5) has 4 architectures: SuperServer, Classic, SuperClassic and Embedded. Since Classic and SuperClassic can use multi-core/multi-CPU hardware, and SuperServer uses only 1 core/CPU per database, sometimes simple change from SuperServer to Classic/SuperClassic leads to the significant improvement of performance (you can see an example of such situation in our test with 1.7 Terabyte Firebird database - please read it later – there are more interesting things below!).

Also, there are several important parameters in Firebird configuration file (firebird.conf) and in the Firebird database itself, which can be tuned for better performance. We have created set of optimized configurations for Firebird 1.5-2.5, which should be good enough for 90% of databases and applications: you can download them from here (please download them later – there are more interesting and important things below!).

For heavy loaded databases (by heavy loaded we mean 100+ users) firebird.conf should be tuned to fit the specific hardware in the best way. For example, if you have plenty of RAM, the good idea would be to increase RAM limit for in-memory sorting (TempCachLimit).

When we perform Firebird database performance optimization, we monitor actual memory consumption and tune all database and server parameters to fit into RAM. However, it does not mean that the goal of such optimization is to «put whole database into RAM» - it is a popular (and wrong) idea among rookie Firebird administrators. For example, setting parameter DefaultDBCachePages to very high value can ruin Firebird database performance.

What can you expect from Firebird configuration tuning?

The biggest effect can be from architecture changes – since usually Classic/SuperClassic enables multi-CPU processing, users with heavy parallel queries can notice immediate effect, which can be up to 100% or even more.

However, the usual performance improvement of other parameters tuning is between 5-20%.

You can say «well, 20% is damn good», and you will be wrong, since 20% here is for an average response time. Look at figure 2:

20% improvement of an average response time of Firebird

Figure 2. Average time is decreased by 20%, but users are still unhappy with peak slow queries

All queries now are executed 20% faster, but it does not change picture a lot: users will not notice the difference between «fast» and «faster by 20%» queries, but they will still consider 1 second query as «slow»: and to turn slow 1.2 second query into fast 300 ms one, you need 4x improvement, not 20%.

So, the conclusion is the following: Firebird configuration tuning is good for decreasing an average response time, but usually it is pretty useless to accelerate the longest queries (there are some exceptions related to the sorting settings).

However, due to the fact that very often Firebird is configured in a wrong way, this step is mandatory for all Firebird database performance optimizations (to be honest, we very rarely see a perfect Firebird configuration file).

What IBSurgeon offers for Firebird configuration tuning?

We ensure that Firebird is configured in the best way for your specific application, database and hardware.
Firebird configuration tuning is included into Optimization Service Level 1.

How hardware can improve Firebird database performance?

The next tuning area is hardware: configuration and upgrade.

Hardware configuration tuning

The number one problem with hardware configuration is disk cache settings. The easy type of this problem is absence of checkbox in Windows disk hardware configuration dialog:

Check that you have ENABLED write caching on your device.

Figure 3. Check that you have ENABLED write caching on your device!

The more sophisticated type of disk cache problem is related with RAID write cache settings and, especially, with Battery Backup Unit (BBU).

It is an often that RAID is being installed into server without Battery Backup Unit, or with discharged battery. It automatically turns off write cache, and makes IO speed very, very low – even less than usual cheap SATA drive has.

When you bought the new high-end server and encountered that it works slower than an old one, this is it: you have problems with RAID: either write cache is set to «write through» or BBU is absent.

The main advice about hardware configuration: make sure that write cache is ENABLED for all your disks .

Hardware upgrade

During the optimization process our customers ask about the best hardware. It is not possible to give a full universal specification, but there is one thing which is definitely recommended: Solid State Drives (SSD). Pair of enterprise-class SSD in RAID1 (mirror, for reliability) can decrease the average response time significantly: one of our performance tests has shown 8x improvement (!). Usually effect from SSD is about 30-50%, but this is also really good.

Another popular upgrade is to increase amount of RAM, since RAM is often can be a bottleneck (including those cases when Firebird is configured in the wrong way, and cases when Firebird is used on the server with other software which also consumes memory).

Usually CPU speed is not a bottleneck, so having faster CPU is not a priority for Firebird. However, number of CPU cores could be important in case of Classic/SuperClassic architecture.

What can you expect from hardware upgrade?

Usually, a hardware tuning/upgrade decreases an average response time. For example, switching to SSD gives 30-50% decrease of an average response time.

What IBSurgeon offers for hardware configuration tuning and upgrade?

We have a lot of experience with hardware tuning and especially with RAID problems. Also, we can give you a reasonable advice regarding needed hardware upgrade, and help with hardware specification.

In essence, we ensure that your existing and future hardware will work at full throttle. Hardware tuning and upgrade consulting are included into Optimization Service Level 1.

SQL queries tuning

The main source of problems with Firebird performance is [not optimized] SQL queries. The usual situation is that developer of a database application does not design SQL queries to work with real-world amounts of records. Non-optimized SQL queries work 10x, or even 100x times slower than they could run. Obviously, there is no hardware or configuration means to speed up query 100x times.

Let's consider simplified example to demonstrate problem with queries. Imagine a simple database structure with 2 tables: CUSTOMERS and ORDERS, and kind of simple query which fetches orders for the specific customer: SELECT * FROM CUSTOMER c join ORDERS o ON (o.CUSTOMER_ID = c.ID) WHERE c.ID=12345.

If, for some reason, there is no foreign key or index for ORDERS (Customer_ID), this query will scan the whole table ORDERS to find orders for the specified customers. Since table ORERS can have millions of records, this simple select will take very long time. If there is an index, Firebird will use it to find orders for customer very fast (fractions of a second).

So, there are several ways to accelerate SQL queries: create/amend indices, rewrite SQL and redesign database schema (includes SQL rewriting for the new schema):

Indices

Creating new indices is one of the most efficient and prevalent ways to optimize SQL queries. First of all, we need to log all queries and then sort them by execution time (we use our FBScanner, FBMonLogger and FBPerfMon tools for this purpose). Then, we need to analyse SQL execution plans, database schema and database performance information, to figure out what index could be potentially created (or, vice versa, excluded).

After that index is created, and queries are tested (they should be tested on the copy of a production database to see actual changes) and, if there is acceleration, index could be applied to the production.

As you can understand, majority of slow SQL queries are complex (their text can be 2-3 pages and even bigger), and their analysis is a thorough manual work.

SQL refactoring

Sometimes indices cannot help, and some changes should be applied in SQL text. For example, Firebird optimizer does not process clause IN effectively, and replacement IN to INNER JOIN query could improve performance dramatically (10+ times).

SQL refactoring usually implies that customer has an access to the source code of application, in order to apply new SQL text to the application. Also, it is necessary to make sure that new queries return the same results as the original ones.

Schema refactoring

Sometimes it is even not enough to change SQL text, and in this case we need to consider change of tables structure. This is a serious effort, it can be done only with developers of an original application.

What can you expect from SQL tuning

Tuning of SQL queries is the main way to accelerate your database, and to reduce maximum and average response time. With proper tuning of SQL queries their execution time should become much faster.

What IBSurgeon offers for SQL tuning?

IBSurgeon ensures that all queries run at maximum available speed. Optimization service level 1 includes optimization of 5 slowest queries with indices. If there are more than 5 slow queries, or SQL rewriting is needed, we will issue an individual quote for Optimization Service level 2.

Transactions tuning

Sometimes we receive requests to optimize Firebird performance which degrades over the day, like on Figure 4:

Performance degradation

Figure 4. Performance degradation - average response time grows during the day.

Usually it means that there are excessive record versions which make reading and writing slower over time. To fix this transactions' control in the application should be changed: transactions must be as short as possible, or at least shorter than in existing applications.

However, it often happens with in case of the application without sources, what can we do in this case? There are some techniques and tools to work around or reduce the loss of performance.

Another performance problems, also related with transactions' control, is periods of slowness (see Figure 5):

peaks and periods of slowness

Figure 5. Periods of slowness

During such periods the response rate for typical operations grows a lot, and users claim it as «very slow».

Most likely these periods are related with sweep operations in the database, which should be put under explicit control.

What can you expect from transactions tuning?

Transactions tuning is a key to eliminate performance degradation and get rid of periodical slowness. After transactions' tuning the average response time will be stable and low.

What IBSurgeon offers for transaction tuning?

Transaction tuning is not possible without thorough analysis of transactions dynamics, SQL logs and database structure: fortunately, we have tools for it. They are packed in Optimization Pack, which includes IBTM (transaction monitor to watch for dynamics of transactions), FBScanner/FBPerfMon (SQL logs) and IBAnalyst (database structure analysis).

With Optimization Pack and our expertise we can perform very sophisticated transactions tuning tasks.

Basic transactions analysis is included into Optimization service Level 1, and transactions tuning is included into Optimization service level 2 (it requires an individual quote).

Please feel free to ask your questions: support@ib-aid.com.

Contact us now

Send enquiry to support@ib-aid.com and we will contact you shortly.
 

More information

Our team

Our optimization expert, Dmitri Kuzmenko, is well-known among Firebird and InterBase users.

As a chief developer of IBAnalyst (tool to analyze database statistics and find database problems with indices and garbage), Dmitry has a lot of experience in identifying and solving optimization puzzles. Dmitry supervises all optimization services and pay maximum attention to each case.

We also have core Firebird developers as consultants for difficult optimization cases.

Our tests

We follow the practical approach and perform a lot of tests to improve our tuning techniques.

We've performed a series of TPC based tests (TPC-R and TPC-C) which showed interesting results and highlighted some practical tips and tricks related with configuration parameters and hardware settings. One of the test was creating and testing performance of 1 Terabyte Firebird database.

Recently we have run tests with Firebird databases from 9Gb to 1.7 Terabyte, in order to investigate Firebird performance degradation and how to tune Firebird performance.

Our customers 

The quote from one of our customers:

"Using their own tools and approaches, IBSurgeon analyzed our database structure and predictive dialer applications in use and came up with a plan of action on how to optimize our database services. In no time our dialer system was literally "flying". Users reported 300 per cent increase in application response time and the database became stable and reliable. We were amazed how IBSurgeon got our system to work at speeds we never imagined it is capable of."

Our real-world customers run databases up to 700Gb which we helped to tune.