Spinlock ratio parameters specify the number of internal system resources such as rows in an internal table or cache that are protected by one spinlock. A spinlock is a simple locking mechanism that prevents a process from accessing the system resource currently used by another process. All processes trying to access the resource must wait (or “spin”) until the lock is released.
Spinlock ratio configuration parameters are meaningful only in multiprocessing systems. An Adaptive Server configured with only one engine has only one spinlock, regardless of the value specified for a spinlock ratio configuration parameter.
Table 5-1 lists system resources protected by spinlocks and the configuration parameters you can use to change the default spinlock ratio.
Configuration parameter |
System resource protected |
|---|---|
lock spinlock ratio |
Number of lock hash buckets |
open index hash spinlock ratio |
Index metadata descriptor hash tables |
open index spinlock ratio |
Index metadata descriptors |
open object spinlock ratio |
Object metadata descriptors |
partition spinlock ratio |
Rows in the internal partition caches |
user log cache spinlock ratio |
User log caches |
The value specified for a spinlock ratio parameter defines the ratio of the particular resource to spinlocks, not the number of spinlocks. For example, if 100 is specified for the spinlock ratio, Adaptive Server allocates one spinlock for each 100 resources. The number of spinlocks allocated by Adaptive Server depends on the total number of resources as well as on the ratio specified. The lower the value specified for the spinlock ratio, the higher the number of spinlocks.
Spinlocks are assigned to system resources in one of two ways:
Round-robin assignment
Sequential assignment
Metadata cache spinlocks (configured by the open index hash spinlock ratio, open index spinlock ratio, and open object spinlock ratio parameters) use the round-robin assignment method.
Figure 5-2 illustrates one example of the round-robin assignment method and shows the relationship between spinlocks and index metadata descriptors.
Figure 5-2: Relationship between spinlocks and index descriptors
Suppose there are 400 index metadata descriptors, or 400 rows in the index descriptors internal table. You have set the ratio to 100. This means that there will be 4 spinlocks in all: Spinlock 1 protects row 1; Spinlock 2 protects row 2, Spinlock 3 protects row 3, and Spinlock 4 protects row 4. After that, Spinlock 1 protects the next available index descriptor, Index Descriptor 5, until every index descriptor is protected by a spinlock. This round-robin method of descriptor assignment reduces the chances of spinlock contention.
Table lock spinlocks, configured by the table lock spinlock ratio parameter, use the sequential assignment method. The default configuration for table lock spinlock ratio is 20, which assigns 20 rows in an internal hash table to each spinlock. The rows are divided up sequentially: the first spinlock protects the first 20 rows, the second spinlock protects the second 20 rows, and so on.
In theory, protecting one resource with one spinlock would provide the least contention for a spinlock and would result in the highest concurrency. In most cases, the default value for these spinlock ratios is probably best for your system. Change the ratio only if there is spinlock contention.
Use sp_sysmon to get a report on spinlock contention. See Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon for information on spinlock contention.
