Difference between revisions of "ASE Cache stalls"

Revision as of 17:50, 10 February 2013

A cache stall in Sybase ASE may occur when a read from disk has to wait for a write to complete so that it can reuse the buffer. The page that needs to be written to disk has passed the wash marker in the cache and is scheduled to be written asynchronously to disk. The tasks in ASE dealing with that are the housekeeper and the checkpoint process.

When a stall occurs the process that requires the data to be read, just has to wait until the preceding writes are completed. Obviously, this is not good for performance.

Here is a simple query to see if your system is suffering from stalls:

select CacheName,
       convert(char(3),IOBufferSize / 1024) + "Kb" as "IO Size",
       Stalls
       from    master..monCachePool
       where   Stalls > 0
       order   by CacheName, IOBufferSize

Sample output

CacheName                      IO Size Stalls     
------------------------------ ------- -----------
tempdb cache                   8  Kb            92
tempdb cache                   64 Kb            15
 
(2 rows affected)

Potential solutions

Increase the wash size

By increasing the wash size of a pool within a cache, the modified page will be triggered at an earlier stage to be written to disk. To see the definition of a cache run sp_cacheconfig followed by the name of the cache.

sp_cacheconfig "tempdb cache"
go

Sample output

Cache Name   Status   Type             Config Value   Run Value   
------------ -------- ---------------- -------------- ------------
tempdb cache Active   Mixed, HK Ignore  51200.00 Mb    51200.00 Mb
                                      ------------ ------------
                      Total    51200.0 Mb   51200.0 Mb  
==========================================================================
Cache: tempdb cache,   Status: Active,   Type: Mixed, HK Ignore
      Config Size: 51200.00 Mb,   Run Size: 51200.00 Mb
      Config Replacement: relaxed LRU,   Run Replacement: relaxed LRU
      Config Partition:           64,   Run Partition:           64
 IO Size  Wash Size     Config Size  Run Size     APF Percent
 -------- ------------- ------------ ------------ -----------
     8 Kb    3932160 Kb      0.00 Mb  25300.00 Mb     15     
    16 Kb      61440 Kb    300.00 Mb    300.00 Mb     15     
    64 Kb    3932160 Kb  25600.00 Mb  25600.00 Mb     15     
(return status = 0)

The washsize can be increased by running sp_poolconfig as in this example:

sp_poolconfig "tempdb cache", "8K", "wash_size=5G"
go

The configuration change is immediate and no reboot is required. Do not make the wash area to big as it may be counterproductive.

Tuning the housekeeper or checkpoint process

Alternatively, you may need to tune the housekeeper or the checkpoint process. There is a good document for this here: http://www.sybase.com/files/White_Papers/Managing-DBMS-Workloads-v1.0-WP.pdf

But beware, a cache can be marked to be ignored by the housekeeper, as is the case in the example above. To configure such a state, add a line "cache status = HK ignore cache" to the ASE configuration file.