Subject Cache Performance Options
Author Joshua Hartmann
Post date 2014-05-12T19:32:39Z
I am a MSP for a large dental office that uses an application with a Firebird DB. I've done a lot of database work in my life, but none with firebird so I need a little help with the configurations. The office is complaining of slowness and what we've done so far to help them will be below. Keep in mind, I am not the developer of the application, just a MSP trying to help them out since the developer has no clue what they're doing.

The server is QEMU/KVM virtual server running Microsoft Server 2008 R2. The host machine is running Ubuntu Server 12.04.
Xeon 1275
4 cores allocated to the virtual server
12GB memory allocated to the virtual server (32GB total on the host machine)
750GB Momentus Hybrid SSD/HDD's (RAID 0+1 1.5TB total usable)

The database is ~30GB

At first, they were running in classic server mode (FB version 2.1.3 x86) on a physical server 2003 machine with 4GB of memory. That machine's HDD's started to fail so we just moved it to the new virtual 2008 server.

We let the software company do the install and they set it to classic mode (version 2.1.3 x86) but left all options at default. We noticed the server would start pretty fast but then throughout the day slow down. When checking the system's resources, I noticed it was maxing out on RAM and moving to the page file which explained the slowness. However, task manager did not display that memory was actually being used by the fbserver.exe processes.

I asked the developer to switch it to superserver because I read its better for shared cache and since all the clients are accessing the same data (appointment lists, patient lists, etc) I thought it would speed things up. It didn't change the memory usage and fbserver.exe still only reported using about 60MB.

So I started reading through articles online and found a bunch of optimization and cache settings. I changed the database options for async, set page buffers at 60k, and turned off auto sweeping. I also changed the firebird.conf settings. Specifically, the MaxFileSystemCache. None of it made any difference.

Next, I upgraded to 2.5.2 (still x86) and its made a little difference. I still notice the fbserver using quite a bit of disk IO though and its still not very fast.

I guess my questions are:
In my situation, should I be using all file system cache or firebird cache? Essentially, what should I set my page buffers and FileSystemCacheThreshole to? I've already set my FileSystemCache to 90%.
Should I enable or disable async?
Should I enable auto sweeping or not?
Would performance be faster if we upgraded to the 64bit version of 2.5.2?

Thanks for any advice you can give.

-Josh