Psap at 07:46, 12 March 2013

2013-03-12T07:46:18Z

Psap: Created page with '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 pas…'

2013-02-10T16:50:04Z

Created page with '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 pas…'

New page

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.

← Older revision		Revision as of 07:46, 12 March 2013
Line 59:		Line 59:

	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.		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.
		+
		+	[[category:ASE]]

ASE Cache stalls - Revision history

Psap at 07:46, 12 March 2013

Psap: Created page with '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 pas…'