Spinlocks and CPU Usage

Overview

Often high CPU  in SAP ASE can be traced to spinlock usage. This page will show how to identify that condition and suggest ways to tune ASE.

What is a Spinlock?

When modifications are made to a cache in Sybase, a spinlock (or mutex) denies all other tasks access to the cache while the changes are being made. Although spinlocks are held for extremely brief durations, they can slow performance in systems with high transaction rates. If spinlock contention is more than 10%, consider using named caches or adding cache partitions. This alert will return a list of all caches along with their spinlock contention which Ignite will then compare to the thresholds defined in the alert. In the example below, a warning email will be sent when the spinlock contention is between 5-10% and a critical email will be sent when it is above 10%.

In a multi-engine server synchronization mechanisms are needed to protect shared resources

ASE uses spinlocks as one of its synchronization mechanisms

A spinlock is a data structure with a field that can only be updated atomically (that is, only one engine at a time can make changes to it).

When a task modifies a data item which is shared it must first hold a spinlock

Shared items are such things as run queues, data cache page lists, lock structures, etc.

The spinlock prevents another task from modifying the value at the same time

This of course assumes that the other task also performs its access under the protection of a spinlock

A task needing a spinlock will “spin” (block) until the lock is granted

When multiple engines are spinning at the same time CPU usage can rise substantially.

Spinlocks must be as fast and efficient as possible

·          

In order to reduce contention a process which loops typically acquires and releases its spinlock each time through the loop.

·          

Therefore the spinlock code is written in platform-specific assembly language.

Comparison of Spinlocks to Other Synchronization Mechanisms

Type                Complexity             CPU overhead                 Wait time

Spinlock                  Low                                            High                                       Very low

Latch                        Moderate                                  Low                                       Should be small

Table/page/row/

address Lock              High                                       Low                                          Can vary considerably

Spinlocks and CPU Usage

Spids trying to get a spinlock will never yield the engine until they have it.

So one spid, waiting on a spinlock, will cause 100% user busy on one engine until it gets the spinlock.

Spinlock contention percentage is measured as waits/grabs

        Example: 10,000 grabs with 3,000 waits = 30% contention

For looking at performance issues, use total spins, not contention

        Example: Assume two spinlocks

        One had 100 grabs with 40 waits and 200 spins = 40% contention

       Second had 100,000 grabs with 400 waits and 20,000 spins = 4% contention

       The second used up more many cpu cycles spinning, even though contention was lower.

        We should then look at tuning for the second example, not the first.

 As more engines spin on the same spinlock, the wait time and number of spins increases; sometimes geometrically

Troubleshooting Spinlocks

Spinlock contention/spinning is one of the major causes of high CPU

Step 1 is determining if, in fact, the high cpu is being caused by spinlock usage.

Step 2 is determining which spinlock or spinlocks are causing the condition.

Step 3 is determining what tuning to use to help reduce the problem.

*Note* You will never get to 0% spinlock contention unless you only run with one engine. That is, do not think that spinlock contention can be eliminated. It can only possibly be reduced.

Step 1 – Checking for spinlock contention/spinning

Using sp_sysmon to determine if high cpu is due to spinlocks

Check “CPU Busy” (or “User Busy” in 15.7 Threaded Mode).

If engines  are not showing high busy% then spinlocks are not a big issue.

Check “Total Cache Hits” in the “Data Cache Management” section.

If the cache hits per second is high, and goes up with cpu busy %, then you likely are looking at table scanning/query plans and not spinlocks.

In general, if cpu usage increases but measurements of throughput such as committed xacts, cache hits, lock requests, scans, etc. go down then it is very possible that spinlock usage is an issue

Step 2 – which spinlock or spinlocks are causing the contention?

Using sp_sysmon

There are several spinlocks listed, but only contention % is shown

ŸObject Manager Spinlock Contention

 

ŸObject Spinlock Contention

ŸIndex Spinlock Contention

ŸIndex Hash Spinlock Contention

ŸPartition Spinlock Contention

ŸPartition Hash Spinlock Contention

ŸLock Hashtables Spinlock Contention

ŸData Caches Spinlock Contention

High contention on any of these may indicate a problem

But, you may have contention on other spinlocks not reported in sp_sysmon

Using MDA table monSpinockActivity

This table was added in 15.7  ESD#2

Query using standard SQL.

One possible query showing the top 10 spinlocks by number of spins over a one-minute interval

select * into #t1 from monSpinlockActivity

waitfor delay "00:01:00"

select * into #t2 from monSpinlockActivity

select top 10 convert(char(30),a.SpinlockName) as SpinlockName,

(b.Grabs - a.Grabs) as Grabs, (b.Spins - a.Spins) as Spins,

(b.Waits – a.Waits) as Waits,

case when a.Grabs = b.Grabs then 0.00 else convert (numeric(5,2),(100.0 * (b.Waits - a.Waits))/(b.Grabs - a.Grabs))

end as Contention

from #t1 a, #t2 b where a.SpinlockName = b.SpinlockName

order by 3 desc

Possible Issues with monSpinlockActivity

Spinlocks with multiple instances will get aggregated

For example, all default data cache partition spinlocks will show up as one line

This can make it impossible to see if just one cache partition is causing the problem

You must set the ‘enable spinlock monitoring’ configuration variable

Tests show that this adds about a 1 percent overhead to a busy server.

 

monSpinlockActivity  does show the current and last owner KPIDs. This can be useful to check if certain processes are the ones heavily hitting certain spinlocks.

 

Step 3 – what tuning to can be done to help reduce the problem

This is going to depend a great deal on which spinlock(s) the high spins are on.

Note as well that it is quite possible to reduce contention on one spinlock only to have it increase on another

Some of the more common spinlocks and possible remedies

Object Manager Spinlock (Resource->rdesmgr_spin)

Make sure that sufficient ‘number of open objects’ have been configured.

Identify ‘hot’ objects by using monOpenObjectActivity.

Use dbcc tune (des_bind) to bind the hot objects to the DES cache.

The reason this works is that the spinlock is used to protect the DES keep count in order to make sure an in-use DES does not get scavenged. When the DES is bound that whole process gets skipped.

Data Cache spinlocks

The best single method to reduce data cache spinlock usage is to increae the number of partitions in the data cache.

Note that if a cache can be set to ‘relaxed LRU’ the spinlock usage may be decreased dramatically. This is because the relaxed LRU cache does not maintain the LRU->MRU chain, and so does not need to grab the spinlock to move pages to the MRU side.

There are definite requirements for this to help (a cache that has high turnover is a very poor candidate for relaxed LRU).

Procedure Cache Spinlock (Resource->rproccache_spin)

This spinlock is used when allocating or freeing pages from the global procedure cache memory pool (this includes statement cache).

 

Some possible causes include

Proc cache too small – procs and statements being frequently removed/replaced.

Procedure recompilations

Large scale allocations

To reduce pressure on the spinlock

Eliminate the cause(s) for procedure recompilations (maybe TF 299)

If you are running a version prior to ASE 15.7 ESD#4 upgrade. ASE 15.7 ESD#4 and 4.2 have some fixes to hold the spinlock for less time

Trace flags 753 and 757 can help reduce large-scale allocations

In ASE versions past 15.7 SP100, use the configuration option “enable large chunk elc“.

Use dbcc proc_cache(free_unused) as temporary help to reduce spinlock/cpu usage.

Procedure Cache  Manager Spinlock (Resource->rprocmgr_spin)

This spinlock is used whenever moving procedures and dynamic SQL into or out of procedure cache.

 

This spinlock was also used prior to ASE 15.7 ESD#1 when updating the memory accounting structures (pmctrl).

Due to contention a separate spinlock was created.

Causes of high contention include:

Heavy use of dynamic SQL

Procedure cache sized too small

Possible remedies are the same as for rproccache_spin

Lock Manager spinlocks (fglockspins , addrlockspins, tablockspins)

These spinlocks are used to protect the lock manager hashtables.

If the lock HWMs are set too high, that means more locks and more contention

Configuration tunables are the primary way to address this

lock spinlock ratio

lock address spinlock ratio

lock table spinlock ratio

lock hashtable size

What not to do

Resist the urge to add more engines because cpu is high

Adding additional engines when the high cpu busy is caused by spinlock contention will only make matter worse

Adding more “spinners” will simply increase the amount of time it takes each spid to obtain the spinlock, slowing things doen even more.