community.borland.com

Article #25532: Using lock_hash_slots setting to improve performance

Problem:
I've read that the setting LOCK_HASH_SLOTS could be used to
increase performance, if this is true what does it mean and how 
do I calculate the best setting?


Solution:
"There is no simple  cook-book answer to how to set LOCK_HASH_SLOTS.
If there were a simple formula to set LOCK_HASH_SLOTS,  InterBase
would have coded that  algorithm into the lock table and done the adjustment for you.  
The reason that LOCK_HASH_SLOTS is a settable parameter is that the
'right' value varied depending on the behavior of the application.

LOCK_HASH_SLOTS determines the width of the hash table used by the
lock manager to control physical access to pages, transaction-based
access to tables, and other lockable entities (e.g. transaction id's,
the database itself). If you're using superserver architecture, have lots of 
users, and use a large page cache, then you might be a candidate for 
a wider hash table. 

If you've already determined that lock response time is a problem
in your system (and I don't know how you'd do that other than to
find the perfect cache size and tune the O/S buffering first), and
you're running lots of users on a superserver, then you look
at the lock manager statistics. Get then from the server manager 
tasks menu.

LOCK_HEADER BLOCK
        Version: 114, Active owner:      0, Length:  32768, Used:  13264
        Semmask: 0x0, Flags: 0x0001
        Enqs:    122, Converts:      2, Rejects:      0, Blocks:      0
        Deadlock scans:      0, Deadlocks:      0, Scan interval:  10
        Acquires:    357, Acquire blocks:      0, Spin count:   0
        Mutex wait: 0.0%
        Hash slots:  101, Hash lengths (min/avg/max):    0/   0/   1 

This one isn't doing a thing, but it illustrates the part of the output
you care about.  Look at 'Hash lengths'  ...  if you've got a max of
100 or more, then consider changing the LOCK_HASH_SLOTS.

Choose a prime number. The hash algorithm works better with a prime
number of slots.  Choose a prime number.  Choose a prime number.  Some
hash algorithms don't care.  This one does (or did).  Choose a prime
number."

Source : Ann Harrison on the listserver

Last Modified: 26-OCT-00