At the heart of Case Manager's performance lies the amazing Firebird Database system. This blog will help you understand what you could do to measure and troubleshoot the performance of your firebird database. Small environments with dozens of users and thousands or even tens of thousands of cases might never encounter major performance issues ,however, if you are running hundreds or thousands of users and hundreds of thousands of cases then you definitely need to keep a close and constant eye on Firebird Database performance. Even if you are not running high data volumes it is always a good idea to optimize your Firebird Database for the simple reason that with an optimized database everything will be faster and a faster system will provide a better experience to your users and customers. Reach out to your Case Manager dealership if you need assistance!
There are many aspects of your database server and network which can affect Case Manager's Firebird database. The main ones are:
We'll briefly touch on every one of these areas and how you can monitor and fine tune performance.
The easiest way to increase performance of your Firebird Database is to optimize your Firebird configuration which is set in the firebird.conf file.
The first thing you need to know is what server mode you are using (Super, SuperClassic, Classic). If you are using the default which is Super Server then then there are two additional settings you need to check namely DefaultDbCachePages and TempCacheLimit. On servers with 16 Gb of memory or more running Super Server the optimal configuration for these settings are DefaultDbCachePages = 50000 and TempCacheLimit = 1024M . Weirdly the defaults for these two settings are DefaultDbCachePages = 2048 and TempCacheLimit = 8M which is no surprise that the default Firebird settings are never correct for your server.
Any database can only perform as fast as the disk it is reading and writing to. Firebird is a high I/O activity process and in most cases the database is larger than the amount of memory available on your server and therefore has to pull data from your disk to satisfy Case Manager reads. In addition, since the data in the Case Manager database is constantly changing these changes need to be written to disk. An easy way to troubleshoot your disk performance is to use Windows Performance monitor.
The above measurements show two main categories of results: latency (sec/Read, sec/Transfer, sec/Write) and Queue length. Latency refers to the time in fractions of a second to perform a read or write and Queue length indicates the number of operations waiting in queue to be processed.
Disk latency should be below 15 ms (< 0.015 seconds). Disk latency above 25 ms can be indicative of noticeable performance issues. Latency above 50 ms is indicative of extremely underperforming storage.
The Average Disk Queue Lengths (both read and write) should be no greater than twice the number of physical disks serving the drive. For example, if the underlying storage is a 6 disk RAID 5 array, the total disk queue should be 12 or less. I generally like to see Avg. Disk Queue length under 1 at all times except for short periods of extremely high reads / writes.
If Firebird starts using a lot of CPU then it might be related to your database's garbage collection process (referred to as sweep). Review this short article for more information.
A quick way to troubleshoot whether Firebird's performance issues is caused by your network's performance is to test the difference in speed on the server compared to over the network - if this performance is noticeably different then more investigation is warranted.
Our dealerships have assisted hundreds of organizations to implement Case Manager and we have extensive experience with the technical aspects of the Firebird Database server. If you need help troubleshooting Firebird Performance issues please reach out to your Case Manager dealership who will be able to help you troubleshoot and hopefully resolve these issues.
