Adam wrote:
I am looking into a particular server that has been performing slowly at certain times of the week. The server is running Firebird 2.1 CS on Win 2008 R2 and on any given day is serving between 80 and 250 concurrent connections.
The output of fb_lock_print is too large to post, but the header is here:
LOCK_HEADER BLOCK ... Mutex wait: 64.6% Hash slots: 1009, Hash lengths (min/avg/max): 2/ 10/ 2
Ann W. Harrison answers:
The mutex wait relates to the amount of time transactions spend waiting to make changes to the lock table. 64.6% is huge. 10% is excessive. One reason for long waits is that the transaction that has mutex for the table takes a long time to find the objects it wants to change. It finds them by hashing on their identifier and walking down the hash chain until it finds the right object. If there's only one object in the hash chain, that's quick. If there are 22, that's a problem.
Increase your hash table size.
Adam wrote:
I noticed in a similar thread a few years back where you mention to reduce the size of the individual caches. Is this also something I need to look at doing?
Ann W. Harrison answers:
The major use of the lock table in Firebird Classic is coordinating access to database pages. Each database page held in any cache is a Lock object; each copy in the cache is a lock request. Reducing the number of pages held in various caches reduces the number of locks and lock requests, but it also (potentially) increases the number of I/O requests. Locks are cheap compared with I/O. First, increase the lock hash size (and possibly the lock table size), then look at the cache size.
Firebird Classic was designed when machines had memory measured in kilobytes - the first 1M machine we got was really exciting. We couldn't get a gig of disk, and the thought of a gig of memory ... wow what would you do with that? The first rounds of performance testing used a 10 page cache - with 1024 byte pages. Over time, as resources grow, the recommendations for memory use change. I wouldn't reduce the cache size unless you're running so many clients that you run out of memory.