Increasing performance and reliability with InterBase 2009

By: John Kaster

Abstract: A quick how-to guide for using InterBase 2009 to increase your RAM cache and database performance

Our heaviest hit database on the Developer Network is the CodeCentral database, which is currently about 37GB in size. (We have larger databases, but few that are hit as actively.) We have over 3.5 million registered CodeCentral users, and have served more than 13 million downloads (we only count unique downloads by user, not repeats) since we started. In the last 365 days alone, we have served over 1.5 million unique downloads.

When Sriram Balasubramanian told me that InterBase 2009 Update 3 could greatly increase my RAM cache for the database on my existing 64-bit server, I couldn't wait to try it. After reading about my experience, I'm sure you'll feel the same way!

    Replacing InterBase 2007 with InterBase 2009

A few weeks ago, I replaced the InterBase 2007 instance running on our primary InterBase server machine (which has 4 InterBase instances on it) with InterBase 2009 Update 3, after successfully testing the new server with a CodeCentral backup running on a test Windows Server 2008 machine.

We are now using 2.8GB of RAM for the CodeCentral database cache, and it's working very well. We definitely saw a performance improvement when the additional RAM cache came into play (sorry, I don't have specific numbers) and have had no problems with InterBase running in 64-bit mode.

Here are the steps I used on our production server:

  1. Removed InterBase 2007 from one of our 4 instances (the CodeCentral instance, or gds_cc) on Windows Server 2003 64-bit
  2. Installed InterBase 2009 and registered it
  3. Modified the database page buffers size to maximize RAM

These three steps should take you a total of 10 minutes or less.

    Modifying page buffers

You can modify the page buffers using either gfix or IBConsole.

For gfix, it would be something very similar to this sample command line call:

gfix localhost/gds_cc:d:\ibdata\gds_cc\CODECENTRAL.IB -buffers 350000 -user sysdba -password masterkey 

I used "localhost/gds_cc" to ensure I was talking to the correct instance of InterBase, because a buffers value of 350,000 is too high for InterBase 2007.

The following screen shot shows how you can use IBConsole:

Hide image

Click to see full-sized image

Changing page buffers with IBConsole

The next time the database is freshly opened (first user accesses the database) the page buffer change will go into effect, and you'll have more RAM.

    Quick and Easy

Replacing InterBase 2007 with InterBase 2009 was both quick and easy, and provided immediate performance benefits. (And I'm not even talking about all the other improvements you get!)

The bottom line is:

  • InterBase 2009 Update 3 works great!
  • It was quick to replace InterBase 2007 with InterBase 2009, and modify the specific database to use the additional RAM for the cache with almost no down time
  • It makes large databases run faster (smaller database could be extremely fast, since some could even fit in the whole RAM cache)
  • It coexists with instances of InterBase 2007 on the same server
  • It works with existing InterBase clients without requiring them to be updated
  • It is very little effort for significant gains

Spread the word. :)

Server Response from: ETNASC04