Last week, I posted a story about my interaction with a software developer who uses FireBird (FB) as a back-end for their software. The short version is that they left FB’s settings at default and were using a really, REALLY old version of FB. This caused the application to have massive (18 minute) load times and hang the entire server, with just one client connected. Below are the changes I made to increase overall performance of the application and server. These changes worked well for the software DOX by KSB Dental, but could potentially be used elsewhere. I will attempt to explain each option the best I can, but please keep in mind, I am not a FB database expert. I also edited the PATH variable to point to the FB install\bin folder (C:\Program Files\FireBird\bin, in my case) to make running the commands easier.
32bit VS 64bit
Initially, they were running FB (x86) 2.1.3 on Server 2003 (x86). We upgraded the server’s OS to Server 2008 R2 (x64) but left FB at 2.1.3 (x86). This made no difference in speed after the first week or so. Initially it did, but my guess is that the process of backing up and restoring the database re-indexed the data.
Another issue with 32bit is that there are limits within the OS and FB. Specifically, the fbserver process cannot use more than 2GB of memory and the maximum page buffers that can be used by a database is roughly 130k.
We then upgraded FB to 2.5.2 (x64). Just that switch alone doubled the speed of the application and we could have all 35+ clients connected at the same time.
The switch is pretty straight forward. Just use gback to backup the database, uninstall the old version, install the new version, and then restore using gback again.
One final thing to note is that until version 2.1.5, FB (x86) had a known bug with running on 64bit systems. Essentially, it would keep its own cache and use file system cache. Which means that everything cached was being cached twice. It would also continue using memory until the sever started swapping. It would do this without a single process on the server using more than 100MB of memory in the task manager.
Database Page Size
The default page size is 4KB. You can check to see what your database is using by running the gstat command.
gstat -h <path to DB>
We found that increasing our page size to 16KB increased performance a lot. (Almost double, again) In order to do this, the database needs to have a backup/restore done on it. When restoring, specify the -p switch and set it to 16384.
Classic Server VS SuperServer
There are two main differences between these options. The first difference is that in SuperServer mode, every client that connects to the FB server shares the same cache. For our application, this was a must as almost all of the clients were referencing the same data continuously. The second difference is that in Classic Server mode, every client that connects gets its own fbserver process. Our application sometimes opened 3-4 connections per client so we had roughly 180 fbserver processes running at a time.
Choosing which mode to run in is done during the FB installation.
One last thing to note is that in SuperServer mode, by default, the server will only utilize one processor. This can be changed in the firebird.conf file in the installation directory. The variable to set is CPUAffinity. We use four cores, so we set ours to 15. The following is a small guide on how to set this:
Each core is assigned a bit map value. For example core one is 1, core two is 2, core three is 4, and core four is 8. This keeps doubling for each core you have. (Hint: Binary) If we wanted to use just the last two cores, we would set it to 12. The first two? 3. Cores two and three? 6.
We decided to start by using 600k page buffers. This setting basically tells FB how much cache to use. The size of the cache can be determined by multiplying the page size by the page buffers. For example, our maximum cache size would be 9.2GB. We have since increased it to 800k. To set this, just use the gfix command and reboot the FB server:
gfix -buffers <# of page buffer> -user <DB username> -password <DB password> <Path to DB>
ex: gfix -buffers 600000 -user dbadmin -password secretpassword D:\dbname.gdb
cache types and limits
We opted to use file system caching instead of FB’s built-in caching. (Which we may end up changing later, we’re still undecided) To do this, we need to edit two variables within the firbird.conf file. The first is FileSystemCacheSize which is an integer defining what percentage of the system memory FB can use. We set this at 90% as FB is the only thing running on our server.
Next is FileSystemCacheThreshold. To set FB to always use file system cache, make this value way higher than your page buffers.
Both of these changes require a full server reboot, not just FB.
If you’ve ever done any RAID controller configuring, you may be familiar with Write Through and Write Back (Synchronous and Asynchronous) cache modes. By default, FB uses synchronous caching which means that the data gets written to the DB immediately. This is the best option for data validation in case of catastrophic hardware failure.
If you’re looking to increase speeds and have redundancy at the hardware level (RAID, iSCSI, clustering, battery backup, etc) you can use asynchronous caching which writes the changes to cache to be written to the DB when enough of these changes have been cached. We found this increased our speed quite a bit. To do this, simply use the gfix command and restart the FB server:
gfix -w async -user <DB username> -password <DB password> <Path to DB>
ex: gfix -w async -user dbadmin -password secretpassword D:\dbname.gdb
This is a tricky one. To understand sweeping and the reasons behind it, you have to understand how FB’s transactions work. I’ll try to be brief.
Every time the application interacts with FB (reads or writes) it creates a transaction. These transactions are categorized in two ways (that matter to us): interesting and commited. Once a transaction is started, it becomes interesting to FB. FB then marks it to be committed to the DB. until the application sends the commit command, the DB keeps a copy of the old data and the updated data, in-case it needs to roll back. What this does it keep a lot of extra data in the DB unless its being committed correctly.
FB has a built-in function called sweeping to collect the garbage “left open” changes that never got committed and clean them up. They call this garbage collection. By default, FB does it every 20k transactions. (The difference between the oldest snapshot and the oldest interesting transaction) Sweeping uses a good amount of CPU and memory and can bog the system down. We opted to remove the automatic sweeping and run manual sweeps during specific down times. (After-hours/Lunch) To do this, you’ll need to set the database to stop sweeping automatically with gfix and then reboot the FB server.
gfix -h <interval> -user <DB username> -password <DB password> <Path to DB>
ex: gfix -h 0 -user dbadmin -password secretpassword D:\dbname.gdb
Then you’ll need to setup a scheduled task to run the command to initiate a sweep.
gfix -s -user <DB username> -password <DB password> <Path to DB>
ex: gfix -s -user dbadmin -password secretpassword D:\dbname.gdb
After we made these tweaks to the FB database, we noticed an exponential difference in speed. We went from 18 minutes to open the application and very slow speeds within the application to 40 seconds to open and almost instant moving around within the application. Keep in mind that not ALL of these tweaks may be best for your environment. Try and decide which options would be best for you. If you have any questions, feel free to comment below or shoot me an email and I’ll do my best to point you in the right direction.