How to use InterBase with Multiple Processors and Multithreaded applications

By: Aaron Ruddick

Abstract: Information about InterBase with Multiple Processors and Multithreaded applications and tips to get good performance.

How to use InterBase with Multiple Processors and Multithreaded applications:

In the InterBase 7.0 (IB7.0) kernel, new support was expanded for multithreaded applications.  In doing this InterBase enabled customers to begin taking advantage of the SMP architecture available in new multiprocessor machines.  This opened a whole new scale of processing power to InterBase customers.

Using this new processing power, and new a feature called the performance monitor introduced in IB7.0, InterBase has been able find and clean up some bottle necks that have been around since the introduction of our super server architecture.  Because of this, InterBase 7.1 (IB7.1) sports a new, finely tuned, garbage collection (GC) system, faster performance on SMP systems and, most importantly, a more stable kernel for multithreaded applications.

*** It is strongly recommended that all InterBase SMP customers migrate to IB7.1. ***

Here are a few tips and tricks that will get you running faster with IB7.1 on your SMP system:

1.    Set MAX_THREADS to at least twice the number of connections you anticipate.  InterBase has found in our on-line transaction processing (OLTP) testing (via TPC benchmarks), that InterBase works best with this configuration.  However, if you anticipate only 1 or 2 connection then you are probably better off with MAX_THREADS set to one (the new default for IB7.1).  I always set mine to 10000 for heavy loads, and set it to 1 for single connections.
a.    To set MAX_THREADS, go to your InterBase directory and edit your ibconfig file.
2.    SWEEP_YIELD_TIME specifies the time, in milliseconds, the sweeper or garbage collector thread sleeps.  You should experiment with this.  InterBase has it set to one, but some tests have found that a more aggressive garbage collector (GC) may work better for heavy OLTP users depending on the type of work.  Try zero for a load over 50 users and see what you think.  You can investigate how the GC is performing in a number of ways.  My favorite is the command line tool called gstat.  Look in your operations guide for more details.
a.     To set SWEEP_YIELD_TIME, go to your InterBase directory and edit your ibconfig file.
3.    If you have a good SMP system usually you have some extra memory just waiting to be used.  InterBase, by default, will not utilize this memory because the server will always try to maintain a small footprint.  To utilize your system memory you can configure IB7.1 in two ways:
a.    Set DATABASE_CACHE_PAGES in your InterBase ibconfig file.  This will set a Server-wide default for the number of database pages IB7.1 will allocate in memory per database.  For example, if you have one database and its page size is 4k and you set DATABASE_CACHE_PAGES to 40000, InterBase will be able to grow to 160MB+/- in memory for that database alone.
b.    Set the database BUFFERS individually.  You can do this with your existing database when you do a backup and restore (look in operations guide for gbak BUFFERS if you need help).  This will override what ever you set in step a, but only for this one database.
4.    CPU_AFFINITY should be set to tell IB7.1 which processors can be used by the server.  If you have set MAX_THREADS to one you may consider limiting the number of processors to one also.  Additionally, if you have multiple processors and/or have Hyperthreading support you may consider playing with CPU_AFFINITY to get the best performance out of your system.  There is a big difference in SMP performance and stability across Windows platforms.  I recommend Windows 2000 Server (or Advanced) edition, service pack 2+.  Windows 2003 has just come out and has new threading support.  I will wait for the first few service packs before I go to this platform.
a.     To set CPU_AFFINITY, go to your InterBase directory and edit your ibconfig file.
5.    ENABLE_HYPERTHREADING if you have a system with hyperthreading enabled and you want to tell InterBase to use it.
a.     To set ENABLE_HYPERTHREADING, go to your InterBase directory and edit your ibconfig file. Specifies whether Intel Hyper-threading technology enabled logical processors should be enabled. Valid values are: 1 (enable), 0 (disable)

Here are some more papers on InterBase performance:

Server Response from: ETNASC04