At 07:19 AM 5/10/2007, you wrote:

>Hi,
>
>I am not familiar with Firebird, but I'm trying to solve a performance
>issue a customer is having.
>
>The system slows to a crawl and when I bring up perfmon, it show the
>pages/sec are pegged. Everything was fine up until a month ago when
>for no apparent reason they started having this issue.
>
>The firebird.conf looks defaulted.
>
>Specs: Xeon 2.8ghz / 4gb RAM / SCSI raid1
>
>firebird.conf:

If it was OK before and is not now, this is a likely source of the problem:

# ----------------------------
# Temporary directories
#
# Provide ';'-separated trees list, where temporary files are stored.
# Relative paths are treated relative to RootDirectory entry
# (see above). Default value is determined using FIREBIRD_TMP,
# TEMP or TMP environment options. Every directory item may have optional
# size argument to limit its storage, this argument follows the directory
# name and must be separated by at least one space character. If the size
# argument is omitted or invalid, then all available space in this
# directory will be used.
#
# E.g.:
# TempDirectories = c:\temp
# or
# TempDirectories = c:\temp;d:\temp
# or
# TempDirectories = c:\temp 100000000;d:\temp 500000000;e:\temp
#
# Type: string (special format)
#
#TempDirectories =

Firebird tries to operate in RAM on the intermediate files that it
creates for sorting operations (ORDER BY and GROUP BY
queries). These can be very large, however, and if insufficient RAM
is available, it will write these files to disk instead.

If you don't configure TempDirectories, then on Windows it will just
use whatever temp space is defined by Windows itself. The default
location of this space varies according to the version of
Windows: on older versions it was just c:\TMP but later versions
assign temp space in the user's operating space. In the case of the
localsystem user (which is the user under which Firebird runs if you
did the default installation) there is a lot of competition from
other applications for temp space, including (not insignificantly)
any web browsers that users are accessing via the LAN.

Whether or not this is an issue with your RAID I am unable to
say. However, it would be quite simple to configure some explicit
temp space for Firebird's exclusive use and see whether it mitigates
the problem. Make sure that the space is sufficient to accommodate
whatever your apps are going to need. For many joined sets, this
could be quite huge.

Also, as you seem to have plenty of RAM (but not knowing what else is
competing with Firebird for resources, natch!) it could be that a
recent installation of some other software on the server is eating
RAM that Firebird used to have available for sorting, thus forcing
most if not all of your sorts to disk where, before, there was enough
RAM available most of the time. You can push up the RAM available to
Firebird for sorting by upping the SortMemUpperLimit parameter, which
defaults to 64 Mb. It sets the maximum amount of RAM that Firebird
will request for sorting. (It is configured in bytes so you need to
take care with the numbers!)

Here is one config that is not defaulted on your system:

DefaultDbCachePages = 16384

It's nice to think that you can utilise that extra RAM by pushing the
page cache up as high as it will go. Unfortunately, it doesn't quite
work that way. That mega-sized cache will be great if your server is
running only Firebird and there is only one database. The maximum
total RAM that a 32-bit process can have on Windows is 2 Gb.

So, again, if your users installed something hungry recently, it
could be that Firebird is getting starved of RAM and your enormous
cache is being constantly paged to disk. In that case, it's a double
death. The *purpose* of the page cache is to minimise the amount of
disj i/o that the server has to do. If Firebird's page cache dwells
mostly or entirely on disk, you're not only getting i/o regardless,
you could very well be getting double i/o when the searched data
isn't found in cache and has to be fetched fresh from the database.

These comments assume that it is Superserver you are running. If it
is Classic, the resource situation is much worse with the
configuration you have.

Another thing to check is whether someone has installed an anti-virus
app that hits every file whenever a change is detected...do I need to
describe how this could *inhibit* database performance? ;-) If you
need to have such stuff running on your server, make sure you
configure it to leave your database files and logs alone!

Take note that changes made in firebird.conf don't take effect until
the database file is totally closed. That means getting absolutely
all users off the system after you've saved the changes (or stopping
and restarting Firebird, worst case, if you can't control
users). Tip, you can test whether the database file is actually
closed, by attempting to rename it.

./heLen