en
brDatabase performance optimization
Firebird Performance Optimization Service
IBSurgeon offers Firebird Performance Optimization service to increase the performance of your Firebird database and application. We will optimize the configuration of Firebird server and database, tune settings of hardware, virtual environments and operation systems, speed up your SQL queries with indices and tweaks, and improve database structure and transactions management.
IBSurgeon offers optimization services to improve Firebird (and InterBase) databases performance. It includes the following options:
Optimization/Configuration Incident, USD$299
If you need the expertise to tune firebird.conf, investigate the reason for slowness, or get a quick diagnosis of any Firebird-related problem, we offer affordable "Optimization/Configuration Incident". It is done through a remote desktop (Zoom/Anydesk/RDP, etc), and usually can be scheduled for the next day (depends on the time zone).It cost is USD 299. The configuraton incident includes 1-2 remote desktop sessions.
Please contact us at [email protected] to receive the payment link for it.
Optimization service, USD$2200
| # | Service | Description |
|---|---|---|
| 1 | Initial analysis of database performance | We identify the top (the longest, the most frequent, etc) queries in the Firebird database and determine which queries have the most negative impact on the performance. |
| 2 | Tuning of Firebird configuration file firebird.conf | We create a special firebird.conf file, tailored for the needs of your database and application. |
| 3 | Hardware analysis and configuration | We check an existing hardware/OS configuration, fix problems if possible, or provide detailed guidance on how to fix them. Also, we provide a draft of hardware specifications if an upgrade needed. |
| 4 | SQL tuning with indices | We create indices to accelerate the 7 slowest or top frequent queries identified in your application or provide tuning recommendations. |
| 5 | Basic transactions analysis | We check transactions' flow in your database and give a conclusion on how transaction control can be improved. |
| Price | USD$ 2200 | |
How to start?
- Click here to pay for optimization service level 1 (USD$2200) – it is a mandatory fee for Level 1 optimization, it is paid in advance.
- Fill out this form and submit it (or copy-paste and send by email to [email protected]).
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 the intention to order our Firebird optimization service, you will get the 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 the 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 analyze gathered data and find average response time and maximum response time (i.e., peaks):
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 figure 1 you can see that average response time is ~250ms, which is considered by the majority of users as «fast enough», and there are 2 peaks around 1 second and 1.2 seconds, 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:
- Firebird configuration: architecture and configuration parameters
- Hardware configuration: tuning and upgrade
- SQL queries tuning: a) Indices b) Refactoring of SQL queries c) Refactoring of database schema
- 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 the 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 the 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 an 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:
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 in Optimization Service Level 1.
How can hardware 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:
Figure 3. Check that you have ENABLED write caching on your device!
The more sophisticated type of disk cache problem is related to RAID write cache settings and, especially, with Battery Backup Unit (BBU).
It is an often that RAID is being installed into the 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, the effect from SSD is about 30-50%, but this is also really good.
Another popular upgrade is to increase the 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, a number of CPU cores could be important in the 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 a simplified example to demonstrate the 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 ORDERS 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 the 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 HQbird optimization tools (FBScanner, MonLogger and PerfMon) tools for this purpose. Then, we need to analyze 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 the 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, from 20% to 600%.
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:
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 to transactions' control, is periods of slowness (see Figure 5):
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 to sweep operations in the database, which should be put under explicit control.
What can you expect from transactions tuning?
Transactions tuning is a key in eliminating 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 HQBird Standard, which includes IBTM (transaction monitor to watch for dynamics of transactions), MonLogger, FBScanner/FBPerfMon (SQL and transaction logs) and IBAnalyst (database structure analysis).
With HQbird Standard 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: [email protected].
Contact us now
Send inquiry to [email protected] and we will contact you shortly.
More information
Our team
Our optimization expert, Dmitri Kuzmenko, is well-known among Firebird and InterBase users, Embarcadero InterBase MVP.
As a chief developer of IBAnalyst ( a 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 pays 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 to configuration parameters and hardware settings. One of the tests was creating and testing the 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.
Also, the good compilation of relatively easy ways to improve Firebird performance is in our article "45 Ways To Improve 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 customers run real-world databases up to 4 Terabytes which we helped to tune. You can be next in this list.