Sybase 12.4.2 manual Monitoring the buffer caches, Starting the buffer cache monitor, 467

CHAPTER 13 Monitoring and Tuning Performance

Block#: 95065709
Temporary IQ Blocks Used:		610 of 17646576, 0%=4Mb, Max Block#: 0
Main Reserved Blocks Available:		1280 of 1280, 100%=10Mb
Temporary Reserved Blocks Available:		1280 of 1280, 100%=10Mb
Memory:		Current: 3351mb, Max: 3384mb
Main IQ Buffers:		Used: 11172, Locked: 0
Temporary IQ Buffers:		Used: 38, Locked: 0
Main IQ I/O:		I: L88043944/P495510 O:
C760342/D761393/P736587 D:24753 C:65.9
Temporary IQ I/O:		I: L16515025/P1222153 O:
C2609951/D3838862/P1228941
D:2609913	C:46.3
Old Versions:		1 = 59Gb
Active Txn Versions:		0 = C:0Mb/D:0Mb

Monitoring the buffer caches

Adaptive Server IQ provides a tool to monitor the performance of the buffer caches. This monitor collects statistics on the buffer cache, memory, and I/O functions taking place within Adaptive Server IQ, and stores them in a log file.

Buffer cache performance is a key factor in overall performance of Adaptive Server IQ. Using the information the monitor provides, you can fine tune the amount of memory you allocate to the main and temp buffer caches. If one cache is performing significantly more I/O than the other, reallocate some of the memory appropriately. Reallocate in small amounts such as 10 to 50MB and on an iterative basis. After reallocating, rerun the workload and monitor the changes in performance.

Starting the buffer cache monitor

You run the Adaptive Server IQ buffer cache monitor from DBISQL. Each time you start the monitor it runs as a separate kernel thread within DBISQL. Use this syntax to start the monitor:

IQ UTILITIES { MAIN PRIVATE }

INTO dummy_table_name

START MONITOR ’monitor_options [ ... ]’

MAIN starts monitoring of the main buffer cache, for the IQ Store of the database you are connected to.

467