I'm currently using a tool to monitor our sybase servers 24/7.
This tool is reporting about denied I/O in the default data cache.
Running sp_sysmon gives this result:
Data Cache Management
---------------------
Cache Statistics Summary (All Caches)
-------------------------------------
per sec per xact count % of total
------------ ------------ ---------- ----------
Cache Search Summary
Total Cache Hits 8877.0 486.3 1597853 99.4 %
Total Cache Misses 54.8 3.0 9860 0.6 %
------------------------- ------------ ------------ ----------
Total Cache Searches 8931.7 489.3 1607713
Cache Turnover
Buffers Grabbed 207.0 11.3 37258 n/a
Buffers Grabbed Dirty 0.0 0.0 0 0.0 %
Cache Strategy Summary
Cached (LRU) Buffers 8914.5 488.3 1604616 99.8 %
Discarded (MRU) Buffers 21.2 1.2 3812 0.2 %
Large I/O Usage
Large I/Os Performed 22.3 1.2 4021 95.4 %
Large I/Os Denied due to
Pool < Prefetch Size 1.1 0.1 194 4.6 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 23.4 1.3 4215
Large I/O Effectiveness
Pages by Lrg I/O Cached 101.3 5.6 18242 n/a
Pages by Lrg I/O Used 73.2 4.0 13169 72.2 %
Asynchronous Prefetch Activity
APFs Issued 16.4 0.9 2954 2.8 %
APFs Denied Due To
APF I/O Overloads 0.0 0.0 0 0.0 %
APF Limit Overloads 33.2 1.8 5979 5.6 %
APF Reused Overloads 1.5 0.1 264 0.2 %
APF Buffers Found in Cache
With Spinlock Held 0.0 0.0 1 0.0 %
W/o Spinlock Held 538.6 29.5 96952 91.3 %
------------------------- ------------ ------------ ----------
Total APFs Requested 589.7 32.3 106150
Other Asynchronous Prefetch Statistics
APFs Used 13.6 0.7 2443 n/a
APF Waits for I/O 12.9 0.7 2324 n/a
APF Discards 0.0 0.0 0 n/a
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
-------------------------------------------------------------------------------
Cache: default data cache
per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Spinlock Contention n/a n/a n/a 0.0 %
Utilization n/a n/a n/a 100.0 %
Cache Searches
Cache Hits 8877.0 486.3 1597853 99.4 %
Found in Wash 49.0 2.7 8828 0.6 %
Cache Misses 54.8 3.0 9860 0.6 %
------------------------- ------------ ------------ ----------
Total Cache Searches 8931.7 489.3 1607713
Pool Turnover
2 Kb Pool
LRU Buffer Grab 67.8 3.7 12203 32.8 %
Grabbed Dirty 0.0 0.0 0 0.0 %
4 Kb Pool
LRU Buffer Grab 12.9 0.7 2321 6.2 %
Grabbed Dirty 0.0 0.0 0 0.0 %
16 Kb Pool
LRU Buffer Grab 9.4 0.5 1700 4.6 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 207.0 11.3 37258
Buffer Wash Behavior
Statistics Not Available - No Buffers Entered Wash Section Yet
Cache Strategy
Cached (LRU) Buffers 8914.5 488.3 1604616 99.8 %
Discarded (MRU) Buffers 21.2 1.2 3812 0.2 %
Large I/O Usage
Large I/Os Performed 22.3 1.2 4021 95.4 %
Large I/Os Denied due to
Pool < Prefetch Size 1.1 0.1 194 4.6 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 23.4 1.3 4215
Large I/O Detail
4 Kb Pool
Pages Cached 25.8 1.4 4642 n/a
Pages Used 11.6 0.6 2096 45.2 %
16 Kb Pool
Pages Cached 75.6 4.1 13600 n/a
Pages Used 61.5 3.4 11073 81.4 %
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
Tuning Recommendations for Data cache : default data cache
-------------------------------------
- Consider using 'relaxed LRU replacement policy'
for this cache.
I have been in contact with sybase support and they have told me to run sp_sysmon and sp_monitorconfig to find out if I need to create some named caches to improve the performance.
I'm not quite sure what to look for in the output from sp_sysmon and what option to use with sp_monitorconfig.
Any suggestions?
-holdahl-
This tool is reporting about denied I/O in the default data cache.
Running sp_sysmon gives this result:
Data Cache Management
---------------------
Cache Statistics Summary (All Caches)
-------------------------------------
per sec per xact count % of total
------------ ------------ ---------- ----------
Cache Search Summary
Total Cache Hits 8877.0 486.3 1597853 99.4 %
Total Cache Misses 54.8 3.0 9860 0.6 %
------------------------- ------------ ------------ ----------
Total Cache Searches 8931.7 489.3 1607713
Cache Turnover
Buffers Grabbed 207.0 11.3 37258 n/a
Buffers Grabbed Dirty 0.0 0.0 0 0.0 %
Cache Strategy Summary
Cached (LRU) Buffers 8914.5 488.3 1604616 99.8 %
Discarded (MRU) Buffers 21.2 1.2 3812 0.2 %
Large I/O Usage
Large I/Os Performed 22.3 1.2 4021 95.4 %
Large I/Os Denied due to
Pool < Prefetch Size 1.1 0.1 194 4.6 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 23.4 1.3 4215
Large I/O Effectiveness
Pages by Lrg I/O Cached 101.3 5.6 18242 n/a
Pages by Lrg I/O Used 73.2 4.0 13169 72.2 %
Asynchronous Prefetch Activity
APFs Issued 16.4 0.9 2954 2.8 %
APFs Denied Due To
APF I/O Overloads 0.0 0.0 0 0.0 %
APF Limit Overloads 33.2 1.8 5979 5.6 %
APF Reused Overloads 1.5 0.1 264 0.2 %
APF Buffers Found in Cache
With Spinlock Held 0.0 0.0 1 0.0 %
W/o Spinlock Held 538.6 29.5 96952 91.3 %
------------------------- ------------ ------------ ----------
Total APFs Requested 589.7 32.3 106150
Other Asynchronous Prefetch Statistics
APFs Used 13.6 0.7 2443 n/a
APF Waits for I/O 12.9 0.7 2324 n/a
APF Discards 0.0 0.0 0 n/a
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
-------------------------------------------------------------------------------
Cache: default data cache
per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Spinlock Contention n/a n/a n/a 0.0 %
Utilization n/a n/a n/a 100.0 %
Cache Searches
Cache Hits 8877.0 486.3 1597853 99.4 %
Found in Wash 49.0 2.7 8828 0.6 %
Cache Misses 54.8 3.0 9860 0.6 %
------------------------- ------------ ------------ ----------
Total Cache Searches 8931.7 489.3 1607713
Pool Turnover
2 Kb Pool
LRU Buffer Grab 67.8 3.7 12203 32.8 %
Grabbed Dirty 0.0 0.0 0 0.0 %
4 Kb Pool
LRU Buffer Grab 12.9 0.7 2321 6.2 %
Grabbed Dirty 0.0 0.0 0 0.0 %
16 Kb Pool
LRU Buffer Grab 9.4 0.5 1700 4.6 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 207.0 11.3 37258
Buffer Wash Behavior
Statistics Not Available - No Buffers Entered Wash Section Yet
Cache Strategy
Cached (LRU) Buffers 8914.5 488.3 1604616 99.8 %
Discarded (MRU) Buffers 21.2 1.2 3812 0.2 %
Large I/O Usage
Large I/Os Performed 22.3 1.2 4021 95.4 %
Large I/Os Denied due to
Pool < Prefetch Size 1.1 0.1 194 4.6 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 23.4 1.3 4215
Large I/O Detail
4 Kb Pool
Pages Cached 25.8 1.4 4642 n/a
Pages Used 11.6 0.6 2096 45.2 %
16 Kb Pool
Pages Cached 75.6 4.1 13600 n/a
Pages Used 61.5 3.4 11073 81.4 %
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
Tuning Recommendations for Data cache : default data cache
-------------------------------------
- Consider using 'relaxed LRU replacement policy'
for this cache.
I have been in contact with sybase support and they have told me to run sp_sysmon and sp_monitorconfig to find out if I need to create some named caches to improve the performance.
I'm not quite sure what to look for in the output from sp_sysmon and what option to use with sp_monitorconfig.
Any suggestions?
-holdahl-