Starting the buffer cache monitor

You run the Sybase IQ buffer cache monitor from Interactive SQL. Each time you start the monitor it runs as a separate kernel thread within Sybase IQ.

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 all tables in the IQ Store of the database you are connected to.

PRIVATE starts monitoring of the temp buffer cache, for all tables in the Temporary Store of the database you are connected to.

You need to issue a separate command to monitor each buffer cache. You must keep each of these sessions open while the monitor collects results; a monitor run stops when you close its connection. A connection can run up to a maximum of two monitor runs, one for the main and one for the temp buffer cache.

dummy_table_name can be any Sybase IQ base or temporary table. The table name is required for syntactic compatibility with other IQ UTILITIES commands. It is best to have a table that you use only for monitoring.

To control the directory placement of monitor output files, set the MONITOR_OUTPUT_DIRECTORY option. If this option is not set, the monitor sends output to the same directory as the database. All monitor output files are used for the duration of the monitor runs. They remain after a monitor run has stopped.

Either declare a temporary table for use in monitoring, or create a permanent dummy table when you create a new database, before creating any multiplex query servers. These solutions avoid DDL changes, so that data stays up on query servers during production runs.

'monitor_options' can include one or more of the following values:

• -summary displays summary information for both the main and temp buffer caches. If you do not specify any monitor options, you receive a summary report. The fields displayed are as described for the other options, plus the following:

  • Users: Number of users connected to the buffer cache

  • IO: Combined physical reads and writes by the buffer cache

• -cache displays activity in detail for the main or temp buffer cache. Critical fields are Finds, HR%, and BWaits. The fields displayed are:

  • Finds: Find requests to the buffer cache. If the Finds value suddenly drops to zero and remains zero, the server is deadlocked. When the server has any activity, the Finds value is expected to be non-zero.

  • Creats: Requests to create a page within the database

  • Dests: Requests to destroy a page within the database

  • Dirty: Number of times the buffer was dirtied (modified)

  • HR%: Hit rate, the percentage of above satisfied by the buffer cache without requesting any I/O. The higher the Hit Rate the better, usually 90% - 100% if you set the cache large enough. For a large query, Hit Rate may be low at first, but increase as prefetching starts to work.

  • BWaits: Find requests forced to wait for a busy page (page frame contention). Usually it is low, but is some special cases it may be high. For example, if identical queries are started at the same time, both need the same page, so the second request must wait for the first to get that page from disk.

  • ReReads: Approximate number of times the same portion of the store needed to be reread into the cache within the same transaction. Should always be low, but a high number is not important for Sybase IQ 12.4.2 and above.

  • FMiss: False misses, number of times the buffer cache needed multiple lookups to find a page in memory. This number should be 0 or very small. If the value is high, it is likely that a rollback occurred, and certain operations needed to be repeated

  • Cloned: Number of buffers that Sybase IQ needed to make a new version for a writer, while it had to retain the previous version for concurrent readers. A page only clones if other users are looking at that page.

  • Reads/Writes: Physical reads and writes performed by the buffer cache

  • PF/PFRead: Prefetch requests and reads done for prefetch.

  • GDirty: Number of times the LRU buffer was grabbed dirty and Sybase IQ had to write it out before using it. This value should not be greater than 0 for a long period. If it is, you may need to increase the number of sweeper threads or move the wash marker.

  • Pin%: Percentage of pages in the buffer cache in use and locked.

  • Dirty%: Percentage of buffer blocks that were modified. Try not to let this value exceed 85-90%; otherwise, GDirty will become greater than 0.

• -cache_by_type produces the same results as -cache, but broken down by IQ page type. (An exception is the Bwaits column, which shows a total only.) This format is most useful when you need to supply information to Sybase Technical Support.

• -file_suffix suffix creates a monitor output file named <dbname>.<connid>-<main_or_temp>-<suffix>. If you do not specify a suffix, it defaults to iqmon.

• -io displays main or temp (private) buffer cache I/O rates and compression ratios during the specified interval. These counters represent all activity for the server; the information is not broken out by device. The fields displayed are:

  • Reads: Physical reads performed by the buffer cache

  • Lrd(KB): Logical kilobytes read in (page size multiplied by the number of requests)

  • Prd(KB): Physical kilobytes read in

  • Rratio: Compression ratio of logical to physical data read in, a measure of the efficiency of the compression to disk for reads

  • Writes: Physical writes performed by the buffer cache

  • Lwrt(KB): Logical kilobytes written

  • Pwrt(KB): Physical kilobytes written

  • Wratio: Compression ratio of logical to physical data written

• -bufalloc displays information on the main or temp buffer allocator, which reserves space in the buffer cache for objects like sorts, hashes, and bitmaps.

  • OU: User_Resource_Reservation option setting (formerly Optimize_For_This_Many_Users)

  • AU: Current number of active users

  • MaxBuf: Number buffers under control of the buffer allocator

  • Avail: Number of currently available buffers for pin quota allocation

  • AvPF: Number of currently available buffers for prefetch quota allocation

  • Slots: Number of currently registered objects using buffer cache quota

  • PinUser: Number of objects (for example, hash, sort, and B-tree objects) using pin quota

  • PFUsr: Number of objects using prefetch quota

  • Posted: Number of objects that are pre-planned users of quota

  • UnPost: Number of objects that are ad hoc quota users

  • Locks: Number of mutex locks taken on the buffer allocator

  • Waits: Number of times a thread had to wait for the lock

• -contention displays many key buffer cache and memory manager locks. These lock and mutex counters show the activity within the buffer cache and heap memory and how quickly these locks were resolved. Watch the timeout numbers. If system time exceeds 20%, it indicates a problem.

  Due to operating system improvements, Sybase IQ no longer uses spin locks. As a result, the woTO, Loops, and TOs statistics are rarely used.

  • AU: Current number of active users

  • LRULks: Number times the LRU was locked (repeated for the temp cache)

  • woTO: Number times lock was granted without timeout (repeated for the temp cache)

  • Loops: Number times Sybase IQ retried before lock was granted (repeated for the temp cache)

  • TOs: Number of times Sybase IQ timed out and had to wait for the lock (repeated for the temp cache)

  • BWaits: Number of “Busy Waits” for a buffer in the cache (repeated for the temp cache)

  • IOLock: Number of times Sybase IQ locked the compressed I/O pool (repeated for the temp cache); can be ignored

  • IOWait: Number of times Sybase IQ had to wait for the lock on the compressed I/O pool (repeated for the temp cache); can be ignored

  • HTLock: Number of times Sybase IQ locked the block maps hash table (repeated for the temp cache)

  • HTWait: Number of times Sybase IQ had to wait for the block maps hash table (repeated for the temp cache); HTLock and HTWait indicate how many block maps you are using

  • FLLock: Number of times Sybase IQ had to lock the free list (repeated for the temp cache)

  • FLWait: Number of times Sybase IQ had to wait for the lock on the free list (repeated for the temp cache)

  • MemLks: Number of times Sybase IQ took the memory manager (heap) lock

  • MemWts: Number of times Sybase IQ had to wait for the memory manager lock

• -threads displays counter used by the processing thread manager. Values are server-wide (i.e., it does not matter whether you select this option for main or private). They represent new events after the last page of the report.

  • cpus: Number of CPUs Sybase IQ is using; this may be less than the number on the system

  • Limit: Maximum number of threads Sybase IQ can use

  • NTeams: Number of thread teams currently in use

  • MaxTms: Largest number of teams that has ever been in use

  • NThrds: Current number of existing threads

  • Resrvd: Number of threads reserved for system (connection) use

  • Free: Number of threads available for assignment. Monitor this value— if it is very low, it indicates thread starvation

  • Locks: Number of locks taken on the thread manager

  • Waits: Number of times Sybase IQ had to wait for the lock on the thread manager

  When an object or query needs work, Sybase IQ allocates a group of processing threads called a thread team. Useful options in adjusting thread use include database options MAX_IQ_THREADS_PER_CONNECTION and MAX_IQ_THREADS_PER_TEAM, and the server option -iqmt which specifies the number of threads Sybase IQ can use.

• -interval specifies the reporting interval in seconds. The default is every 60 seconds. The minimum is every 2 seconds. You can usually get useful results by running the monitor at the default interval during a query or time of day with performance problems. A very short interval may not give meaningful results. The interval should be proportional to the job time; one minute is generally more than enough.

  The first display shows counters from the start of the server. Subsequent displays show the difference from the previous display.

• -append | - truncate Append to existing output file or truncate existing output file, respectively. Truncate is the default.

• -debug is used mainly to supply information to Sybase Technical Support. It displays all the information available to the performance monitor, whether or not there is a standard display mode that covers the same information. The top of the page is an array of statistics broken down by disk block type. This is followed by other buffer cache statistics, memory manager statistics, thread manager statistics, free list statistics, CPU utilization, and finally buffer allocator statistics. The buffer allocator statistics are then broken down by client type (hash, sort, and so on) and a histogram of the most recent buffer allocations is displayed. Note that memory allocations indicate how much is allocated after the last page of the report.