Skip to Content
Author's profile photo Former Member

Sybase 15.0 – Spinlock Contention in Procedue Cache.

I want to share my experiences dealing with this topic.

According to my research, the problem occurs due to several factors, namely:


– Having 15 Optimizer enabled (not in compatibility mode on in the SP). The Sybase opimizador 15 is much more efficient than that of Sybase 12; solving
all algorithms “hash” of execution plans in the procedure cache (temporary tables that weapon to solve these plans “supports” in the procedure cache).
 
Whereupon every execution thereof contributes even more to “break up” the procedure cache.
– Have the option compiliacion defered procedure enabled the engine.
– Have a high turnout of Stored Procedures, which have a huge number of rows with a lot of input parameters, which varies according to their
functional performance, causing them to solve different situations according to the values ​​of these parameters. These together with the compilation defered makes SP of the characteristics cited work more efficiently, arming them plans to “measure” of each of the options you have, arming them at runtime. As
counterpart, this practice means that in times of great amount of mass executions and concurrency of these SP, the “procedure cache” is this continuous loading  plans, producing a high fragmentation of the cache, leading to the processors wander by the “spin” or “turns”, instead of finding and implementing
 
plans.
– A lot of processors (engines), this problem increases.

All these factors together can lead to the processors spend more time doing the spin that solving the SP plans as a result is
a system processor is almost 100% and slowly solving tasks.

The visualization of the percentage of spinlock can be achieved through the commands:
dbcc monitor (“clear”, “spinlock_s”, “on”)
go
waitfor delay “00:02:00” / * The time you want to check * /
go
dbcc monitor (“sample”, “spinlock_s”, “off”)
go
dbcc monitor (“select”, “spinlock_s”, “on”)
go
dbcc traceon (8399)
go
dbcc traceoff (-1)
go
dbcc traceon (8399)
go
And then at the table sysmonitors

If I get problem in procedure cache (more than 10% in wait_percent), and also the total is significant vs sping. the total_spins of different partitions
default data cache, and shows processors sysmon sustained use over 90%, it should clear the cache with the following procedure:
dbcc traceon (3604)
go
dbcc proc_cache (free_unused)
go
dbcc traceoff (3604)
go
-> This procedure cleans the cache of all plans except those that are in use when ejecuctarlo.

To verify that spin and the problem was solved with the above:
– Comparing sysmon the problem and normal sysmon about Disk I / O Checks – Task Context Switches Due To –

Committed Xacts – Total Rows Affected – Total Lock Requests and Total Cache Searches; and, if these values ​​at times that are 100% engines are not higher than lulls is very likely that the engines spinlock are doing.
– Should the engines down immediately to 10% or 20% occupancy, and stabilize after five / ten minutes (to continue the transactional load)
values ​​in normal operation.

Another solution that can be applied to reduce the fragmentation, is to lift the motor with Trace Flag 753, which tells the engine to find BD
the procedure cache fills not take long to re-record storage blocks in it, but to take “a product”, thus decreasing the generation of fragmentation,
 
(This was the behavior as I have understood that I had in the ASE version 12).

I hope it will be useful.

Regards,

Flavio.

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Kimon Moschandreou
      Kimon Moschandreou

      Excellent post, but is dbcc monitor documented? I suppose you used the sp_sysmon SQL to find your commands

      Author's profile photo Former Member
      Former Member

      TF753 turns off large chunk (4K, 8K, 16K) allocations of free space from the so-called Global Cache of Procedure Cache in ASE 15.x.  This avoids fragmentation in the Global Cache when the Engine Local Caches are expanded to meet workload demands searching for available Procedure Cache 2K pages.

      The key in avoiding high CPU usage & server becoming unresponsive at peak loads is:-

      1. Configuring Procedure Cache to an outrageous huge size initially
      2. Monitoring the Procedure Cache utilisation High Water Mark over an extended period
      3. Reducing the Procedure Cache size to accomodate the utilisation HWM plus a wide safety margin - this effectively avoids Procedure Cache space allocation/deallocation

      One should use the dbcc command to reclaim free space in Procedure Cache as a last resort and when there is insufficient shared memory to configure the optimal "procedure cache size" as described above.  If you must run the ASE 15.x server on a shoe-stirng of resources and relying on the dbcc command to avoid high CPU usage & server stall, take great caution to not run the dbcc command while one of the engine is allocating Procedure Cache pages en-mass for a very demanding query otherwise the contention between the dbcc command to reclaim space and the engine allocating space could bring your ASE 15.x server to its knee!

      If you are in UK, please come to the SAP/Sybase UK User Group's Masterclass sessions in May to discover more on the best practices in ASE 15.7 Procedure Cache/Statement Cache configurations.

      Author's profile photo Former Member
      Former Member

      Just FYI, the dbcc monitoring method is crumbersome.  From ASE 15.7 ESD#2, the new MDA table monSpinlockActvity is a easier and more effective way in identifying Spinlock contention (see example below).

      1> select * from monSpinlockActivity where Contention > 0 order by Contention desc
      2> go
      Grabs                Spins                Waits                OwnerPID    LastOwnerPID Contention                  InstanceID SpinlockName
      -------------------- -------------------- -------------------- ----------- ------------ --------------------------- ---------- ---------------------------
                       4407                   40                   10           0            0                    0.002269          0 Sched Q
                       1460                 2200                    3           0            0                    0.002055          0 Deferred Queue
                       3560                 2254                    2           0        65537                    0.000562          0 Kernel->kpsleepqspinlock[i]
                       9060                  141                    2           0      3997727                    0.000221          0 Kernel->kpprocspin[i]

      (4 rows affected)

      The dbcc monitoring using sysmonitors method does not highlight Spinlock contention i.e. the "value" column reflects the number of Spins only so you cannot pick out contentions easily.

      Author's profile photo Former Member
      Former Member

      Thank you very much for your comment and the information of the mda table. The knew but now I have a more clear example from your data.
      Unfortunately our problem was with version 15.0.3 and there is no monSpinlockActivity table.
      I'm doing the plan to migrate to 15.7 anyway.

      regards,

      Author's profile photo Former Member
      Former Member

      Hello kimon . Thanks for your comment . If this documented since 12.5 , I attached :

      Adaptive Server Enterprise 12.5.3 > 12.5.3 ASE New Features > Monitor Counters and sp_sysmon

      Monitor counter concurrency Chapter 15 : Changes to Stored Procedures , Functions, and Commands

      Chapter 14 : Monitor Counters and sp_sysmon

      New dbcc commands

      The System Administrator can now manually modify monitor counter usage count . When an application Enables monitor counters , then fails to disable them before logging off Adaptive Server, the System Administrator can use commands to terminate These monitor counter data collection:

      dbcc monitor ( increment, <group name>)

      dbcc monitor ( decrement , <group name>)

      dbcc monitor ( reset, <group name>)

      Where <group name> can be one of the Following:

          'All'

          spinlock_s

          appl

      increment and decrement Increase and decrease usage counts for the monitor counters in the specified group by 1. reset sets the usage count for the monitor counters in the specified group to zero . This turns off collection of monitoring data for this group .

      You can determine the usage count for all , que Comprises Most of the counters monitor, by Selecting the global variable @ @ monitors_active .

      The usage counts for the groups are spinlock_s and appl Reported by the dbcc command resource .

      regards

      Author's profile photo Former Member
      Former Member

      Does anyone know where to get some more information on the entries in monSpinlockActivity?  Specifically seeing Resource->rpssmgr_spin and fglockspins at the moment for a system but struggling to find any info on them (or ways to remedy the contention).

      Cheers

      Author's profile photo Former Member
      Former Member

      Hi Hayden,

      You did not specify the ASE version nor the platform for me to be more specific in my answers to your question on monSpinLockActivity (but I can guess that it must be ASE 15.7 ESD#2, in which this MDA table was introduced, or later version.

      Resource->rpssmgr_spin

      This is the single spinlick used by ASE to gain exclusive access to a specific PSS data structure corresponding to a SPID; the contents of PSS are extremely volative and there are huge number of crucial data elements within a PSS and any attempt to change, for instance, any of the serveral status flags or list of open object descriptors of system/user categories, will require acquiring the PSS Mananger spinlock.

      You can visiualise the above spinlock sitting on top of a HASH table overseeing all the in-memory PSS data structures keyed by SPID.  Contention of this spinlock will be expected to increase when there are very high number of user sessions and majority of them are ACTIVE (arther than passive) in running queries on an ASE dataserver.

      As a side note,

      excessive polling on master..sysprocesses table adds to the PSS spinlock issue since rows in this fake table must be created on the flight on when each SELECT from master..sysprocesses query is being processed - each row in this table holds info about each SPID in the dataserver and the column values are retrieved from the PSS of a given SPID.  Although his is a read-only access and does not require acquiring the PSS Manager spinlock but while this SELECT query is running, it delays active SPIDs running on other engines to acquire the spinlock to gain exclusive access to modify the affected PSS data structures.

      In essense,

      1. you have very little control over reducing high PSS Manager spinlock due to having thousands of user sessions on the dataserver and the majority SPIDs are active at peak load time - reviewing the number of online engines best suited to sustain the transaction throughputs at peak load time may be the only option (rather than following the common tendency to increase number of engines just because there are CPU cores avaliable fuelled by the misinformed view of improving transaction  throughput via higher concurrency without careful consideration & benchmark testing of potential overall spinlock contention jumps);
      2. if the DBAs are fond of running scripts and/or stored procedures, which query the master..sysprocesses table, you should, as a minimum, reduce the concurrent queries against sysprocesses table but, ideally, review all such scripts / stored procedures and change them to poll from the MDA table monProcess instead.
      • NOTE - Although this MDA table also accesses the PSS data structures, however, adding in the WHERE clause to filter by monProcess.SPID & other columns will impact fewer SPIDs waiting for PSS Manager spinlock (unlike sysprocesses table, which is populated for each SELECT by access ALL the SPIDs in the dataserver even if the SELECT from sysprocesses is expected to return one row! - the SARG predicates are applied only AFTER the entire table is populated with rows).

      fglockspins

      This is one of the spinlocks used by the ASE Lock Manager in mainaging table/page/row locks. David Putz has an excellent ASE Tech Note SCN page on Spinlocks in general but refers to Lock Manager spinlocks with advice on what ASE server configurations should be tuned to reduce Lock Manager spinlock contentions.

      http://wiki.scn.sap.com/wiki/display/SYBASE/Spinlocks+and+CPU+usage+in+SAP+ASE