[All]
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:
