Subject Re: [firebird-support] LockHashSlots
Author Ann W. Harrison
Faisal Abdullah wrote:
> An associate advised to set LockHashSlots = 20011 in firebird.conf,
> and we experience tremendous improvement in performance.

Interesting. I would expect some improvements in some cases, but not
the level of improvement you describe. The parameter affects the lock
table that coordinates access to database resources. The largest use of
the lock table is keeping one thread from changing a page buffer while
another thread is reading the page.

The lock table contains three major types of blocks: user blocks that
represent something that asks for, holds, and releases locks, lock
blocks that represent something a that can be locked, and request blocks
that represent an owner's interest in a lock. Requests can be pending
or granted.

The LockHashTableSlots parameter controls the width of the lock hash
table. Lock blocks are located by hashing the name of the object and
looking for that value in the hash table. The default value of the
parameter is 101, which is OK for a moderately active database by the
standards of 1990. A more active system will have lots of duplicates on
each hash value. That means that in addition to hashing on the name, a
lookup involves walking through a chain of hash duplicates.

The way to determine if your hash table is wide enough is to run the
lock print utility. It prints out the min, max, and average length of
the hash chains. My experience is that when a hash table has an average
larger than 10, increasing the width will improve performance.

There is a related parameter, LockTableSize (from memory, that may not
be its name exactly). Typically, a lock table that needs a wider hash
table also needs more room generally. You'd see that in the error log -
running out of space in the lock table is an error.

The problem with your case is that you've specified a hash table width
that is enormously larger than anything I've ever seen - at least 20
times as large as anything I'd recommend. And you haven't increased the
table size. I'm not at all sure the lock management code is prepared
for overflows in all areas - and worry that you may have overflowed
something without detecting it.

Please run the lock print utility on your system

gds_lock_print -a >foo.lis

and send the result to me.