Hi,

Your database is not big but your settings are low. Change your FB server to 64bit and increase your dbbuffer or in fbconfig defaultdbcasche setting and use superserver. Check your database data page size. I always set it to 16KB. Your database utylize memory in this pattern:

Dbpage size * dbbuffers
Or if dbbuffer is 0
Db page size * defaultdbcachepages

You must add to this sort buffer for any connection.

You should also download shnapshot of firebird 2.5.3.xxx to fix windows 2008 memory problem

Regards,
Karol Bieniasxewski

Wysłane z mojego HTC

----- Reply message -----
Od: "Joshua Hartmann jhartmann@... [firebird-support]" <firebird-support@yahoogroups.com>
Do: <firebird-support@yahoogroups.com>
Temat: [firebird-support] Cache Performance Options
Data: pon., maj 12, 2014 21:32

 

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