Matrix of Buffer Cache Monitor values and options

From SybaseWiki
Jump to: navigation, search

The Sybase IQ performance and Tuning guide has a section called the "Buffer cache monitor checklist". Some 12 different items are listed to watch for performance problems, along with some hints for tuning. The list does not show which option of the IQ monitor statement you should use to capture the value. Below you find this matrix.

How to monitor

Use these statements to capture IQ performance data:

set option Monitor_Output_Directory = "<path where the output should be placed>"
declare local temporary table dummy_monitor (dummy_column integer)
iq utilities main into dummy_monitor start monitor '-summary'

/* Do not disconnect from IQ and wait until the monitoring should stop. Then type this: */
iq utilities main into dummy_monitor stop monitor

Now a file has been created with the captured data. In the example above the "-summary" option was used. You can use these options:

  • -summary
  • -cache
  • -cache_by_type
  • -io
  • -buffalloc
  • -contention
  • -threads
  • -debug

IQ monitor matrix

The following shows you which option you can choose to see a specific value.

IQ monitor matrix
Statistic option Look in column
HR% -cache HR%
-cache_by_type HR%
-summary HR%
GDirty Tuning GDirty -cache GDirty
-cache_by_type Gdirty
-debug GrabbedDirty
-summary GDirty
Bwaits -cache BWaits
-cache_by_type BWaits
-contention BWaits
-debug BusyWaits (?)
LRU Waits -debug LRUNumTimeOuts (percentage value)
IO Wait -contention IOWait
-debug IONumWaits (percentage value)
FL Wait -contention FLWait
-debug FLMutexWaits (percentage value)
HT Wait -contention HTWait
-debug BmapHTNumWaits
Mem Wts -contention MemWts
-debug MemNTimesWaited
PFMgrCondVarWaits -debug PFMgrCondVar (percentage value)
CPU Time (System time) -debug CPU Sys Seconds
CPU Time (Total time) -debug CPU Total Seconds
In use % -summary InUse%
Pin % -cache Pin%
-cache_by_type Pin%
-debug Pinned
-summary Pin%
Free threads -debug ThrNumFree and ThrReserved
-threads Free and Resrvd
FlOutOfSpace -debug FLIsOutOfSpace