Spinlocks and CPU usage in SAP ASE

Purpose

The purpose of this page is to clarify the understanding of how SAP ASE uses spinlocks and what the effects on overall CPU usage may be.

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?

• 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

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

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

• • 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.

__________________________________________________________________________________________________________