Bid208 tools for monitoring the iq server
Download
1 / 88

BID208 Tools for Monitoring the IQ Server - PowerPoint PPT Presentation


  • 228 Views
  • Uploaded on

BID208 Tools for Monitoring the IQ Server. Dan Kernaghan Principal Systems Consultant danielk@sybase.com August 7, 2003. Tools for Monitoring IQ. Administrative Monitoring System Events Basic Auditing SQL statements Space Utilization Versions Performance Monitoring IQ Monitor OS Tools.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'BID208 Tools for Monitoring the IQ Server' - jam


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Bid208 tools for monitoring the iq server l.jpg

BID208 Tools for Monitoring the IQ Server

Dan KernaghanPrincipal Systems Consultantdanielk@sybase.comAugust 7, 2003


Tools for monitoring iq l.jpg
Tools for Monitoring IQ

  • Administrative Monitoring

    • System Events

    • Basic Auditing

    • SQL statements

    • Space Utilization

    • Versions

  • Performance Monitoring

    • IQ Monitor

    • OS Tools


Administrative monitoring l.jpg
Administrative Monitoring

  • Understanding the status of the server

    • What users are coming and going?

    • What statements are being run?

    • What is the Main or Temp space utilization?

    • Why are the versions out of control?


Iq system events l.jpg
IQ System Events

  • Events can be added to automate system functions

    CREATE EVENT event-name

    ... [ TYPE event-type

    ... [ WHERE trigger-condition [ AND trigger-condition ], ... ]

    | SCHEDULE schedule-spec, ... ]

    ... [ ENABLE | DISABLE ]

    ... [ AT { CONSOLIDATED | REMOTE | ALL } ]

    ... [ HANDLER

    BEGIN

    ...

    END ]


Example event l.jpg
Example Event

  • Example

    CREATE EVENT IncrementalBackup

    SCHEDULE

    START TIME ’1:00AM’ EVERY 24 HOURS

    HANDLER

    BEGIN

    BACKUP DATABASE INCREMENTAL

    TO ’/backups/daily.incr’

    END


Basic auditing l.jpg
Basic Auditing

  • A rudimentary auditing function can be added through events


Starting the iq monitor l.jpg
Starting the IQ Monitor

  • Started using IQ UTILITIES command

  • iq utilities [main|private] into <dummy_tblname>

  • start monitor “options”

  • The table name is never used, output is to an ASCII file.

  • The table name is only present for syntactical reasons


Counter output l.jpg
Counter Output

  • The counters are output to an ASCII file, in the directory in which IQ is running

  • <dbname>.<conn#>-[main|temp]-iqmon

  • In ASIQ 12.4.2 the suffix “-iqmon” can be changed to reflect user requirements

Example : asiqdemo.2-main-iqmon


Stopping the iq monitor l.jpg
Stopping the IQ Monitor

  • Stopped using the same basic command

  • iq utilities [main|private] in <dummy_tblname>

  • stop monitor

  • Again as with the start command the “dummy_tblname“ is only present for the syntactical analyser in ASA


Issues in monitor operation l.jpg
Issues in Monitor Operation

  • You can have 2 monitors running

    • One monitoring IQ_MAIN

    • One monitoring IQ_TEMP

  • They will write to different files

    • asiqdemo.2-main-iqmon

    • asiqdemo.2-temp-iqmon

  • Also they must be explicitly started and stopped with the correct syntax


File options l.jpg
File options

  • The –file_suffix option allows you to change the –iqmon suffix to the output file name

  • This is so you can keep multiple copies of the output showing what, when and why you ran it

  • The –truncate and –append options allow you to specify whether the output file is truncated (default) or appended to when the monitor starts up


Graphic of operation l.jpg
Graphic of Operation

Monitor

Temp

Monitor

Main

Language Processor

Catalog

Catalog Store

IQ

Store

IQ Temp

Store

Catalog

Store

IQ Store

Temp Counters O/P

Main Counters O/P

IQ Temp Store


Primary options l.jpg
Primary Options

  • There are two “sets” of options

    • interval

    • Collected counters to display

  • Interval is the time interval that IQ Monitor collects the information

  • The first iteration of Monitor displays counters from the start of the server

  • The subsequent displays are deltas of the information from the last display


Interval l.jpg
Interval

  • Interval can be from 2 seconds to 4,294,967,295 seconds (2^32-1) – as this is around 136 years I do not suggest we use this

  • The default interval is 60 seconds - 1 minute

  • Beware of setting the interval too small the data may not be too meaningful


Basic options l.jpg
Basic Options

  • -summaryReports the key statisticsfor both the main andtemporary cache

  • -cacheMore detailed reports onspecified cache

  • -cacheByTypeReports the cachestatistics by

  • -cache_by_typetype of buffer

  • -ioReports IO subsystemcounters


12 4 2 options l.jpg
12.4.2 Options

  • In AS IQ-M 12.4.2 there are a series of new monitor options

    • -contention

    • -bufalloc

    • -threads

    • -debug

  • These are described after the basic options


Summary reports 1 l.jpg
-summary Reports - 1

  • Find RateNo. of times a buffer was requested

  • Hit RatePercentage of cache hits

  • Read RateNo. of read operations

  • Write RateNo. of write operations

  • Pin Percentage No. of pinned buffers – generally buffers that need to be modified

  • Note – these are for both caches – for the specified interval


Summary reports 2 l.jpg
-summary Reports - 2

Sybase Adaptive Server IQ Performance Monitor

---------------------------------------------

Version 3.1

Options string for Main cache: "-summary -file_suffix summary-iqmon -append -interval 10"

Summary

2000-01-24 10:51:17

Active| Main Cache | Temp Cache

Users| Finds HR% Reads/Writes GDirty Pin% Dirty% InUse%| Finds HR% Reads/Writes GDirty Pin% Dirty% InUse%

0 15 46.7 8/1 0 0.1 0.1 0.5 20 100.0 0/0 0 0.0 0.3 0.3

0 627 64.1 237/0 0 5.8 0.1 15.4 230 100.0 0/0 0 0.1 0.6 0.6

1 957 60.7 376/0 0 29.5 0.1 39.0 200 100.0 0/0 0 1.4 2.1 2.1

1 3 66.7 1/0 0 17.8 0.1 39.1 0 0.0 0/0 0 1.4 2.1 2.1

1 0 0.0 0/0 0 6.8 0.1 39.1 0 0.0 0/0 0 1.4 2.1 2.1


Time out lru mru chain l.jpg
Time Out! – LRU/MRU Chain

  • The layout of the caches is similar to ASE in that they are one long MRU/LRU chain

  • There is a wash marker

  • One major difference is there are multiple sweeper threads to write dirty pages to disk

Buffer Movement

Wash Marker

LRU

MRU

Sweeper Write Activity


Cache reports 1 l.jpg
-cache Reports - 1

  • Find Rateas summary

  • CreatesThis is the number of times the create buffer operation was called

  • DestroyThis is the number of times the destroy operation was called

  • Dirty This is the number of times the dirty operation was called

  • Hit Rateas summary


Cache reports bwait l.jpg
-cache Reports BWAIT

  • This is the count of the number of times a thread tries to get a buffer

  • but the thread had to hold before the buffer could be made available

  • This may be because the buffer was locked or whatever

  • Note-Bwait should be small


Cache reports rereads l.jpg
-cache Reports – REREADS

  • This is a count of number of times a buffer was requested for read, after the same thread had been written

  • If this is the case it means that

    • 1) the cache activity is so high that the write transaction cannot hold a written buffer in memory

    • 2) Or, the transaction is very long and hits the same page multiple times, but with a large time interval between hits

  • This should be very low – except, possibly in case 2 above.


Cache reports f miss l.jpg
-cache Reports – F.MISS

  • This is an internal counter that counts the number of times that a hash operation had to probe a hash table twice

  • Generally this indicates that there has been a rollback shortly before

  • If there is no rollback activity – watch this counter – it should be zero


Cache reports cloned l.jpg
-cache Reports - CLONED

  • This is a count of the number of times a buffer was cloned

  • This is an example of versioning in operation

  • For each page that must be modified (not new pages) a cloned operation takes place

  • A page will only CLONE if there are other users looking at that page – if not then an Opportunistic clone takes place – we update the page in place


Time out cloning l.jpg
Time Out! - Cloning

Page 001

If page 004 need to be “modified”

Then the system will check to see

If other users are using the page

Page 002

Page 003

If no user is using the page – it is modified, marked as dirty – and written to a new location (maybe as page 005)

Page 004

If a user is using the page – then the

Page has to be cloned – and the CLONE

Count is incremented

If a user now needs the (old) page 004 thenthis is recorded as a cache miss the the pageis read from disk


Cache report prefetch l.jpg
-cache Report - Prefetch

  • prefetch Count of how manyprefetchoperations are requested

  • Prefetch Read count of how may prefetch operations result indisk reads

  • Generally we should hope that a good part of the prefetch operations can be satisfied in cache – too many reads indicate that we might be able make use of more memory


Cache report gdirty l.jpg
-cache Report - GDIRTY

  • We really don’t want to see this count above zero

  • This counts the number of times that a buffer get operation has had to stall whilst a dirty page is flushed to disk, effectively this means cache is full of dirty pages

  • If this is the case then we need to move the wash marker - or increase the number of sweeper threads

  • Note this is GrabbedDirty not Got Dirty


Cache report 8 l.jpg
-cache Report - 8

  • Pin Percent Percentage of buffers that are pinned (lockedinto memory)

  • Dirty Percent Percentage of pages that are dirty

  • Try not to let the Dirty Percent get above 85-90% otherwise the GDirty will start to go above zero – not good.


Cache report 9 l.jpg
-cache Report - 9

Sybase Adaptive Server IQ Performance Monitor

---------------------------------------------

Version 3.1

Options string for Main cache: "-cache -file_suffix cache-iqmon -append -interval 10"

Main Shared Buffer Cache

2000-01-24 10:52:26

Finds Creats Dests Dirty HR% BWaits ReReads FMiss Cloned Reads/Writes PF/PFRead GDirty Pin% Dirty%

Mn: 15 0 0 2 46.7 0 8 0 0 512/64 8/1 0/0 0 0.1 0.1

Mn: 1000 0 0 0 71.0 1 284 0 0 7600/0 289/0 0/0 0 1.4 0.1

Mn: 227 0 0 0 74.0 0 74 0 0 3420/ 72/0 15/13 0 1.9 0.1


Cache reports by type l.jpg
Cache Reports By Type

  • This reports the same basic counters as the –cache option, but in this case the counts are broken out by block type in memory

  • These may be of slightly less interest than the –cache reports – but over a longer interval they can provide some interesting statistics on the performance of the buffer manager


Object types in cache l.jpg

4 Variable Length B- Tree

5 Fixed Length B- Tree

7 VDO – used bythe catalogue to find indices

8 Dbspace header

9 Database header

10 Sort buffer (temp)

12 G-Array (HG)

13 B-Array (FP)

14 Block Map

15 Hash Bucket

16 Checkpoint Block

17 Bitmap (of anysort!)

18 Reserved

Object Types in Cache


Cache by type l.jpg
-cache_by_type

Sybase Adaptive Server IQ Performance Monitor

---------------------------------------------

Version 3.1

Options string for Main cache: "-cachebytype -file_suffix cachebytype-iqmon -append -interval 10"

Main Shared Buffer Cache

2000-01-24 10:53:26

Btype Finds Creats Dests Dirty HR% BWaits ReReads FMiss Cloned ReadKB/WriteKB Reads/Writes PF/PFRead GDirty Pin% Dirtyy%

Mn: 0 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 1 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 2 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 3 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 4 34 0 0 0 91.2 ------- 0 0 0 36/0 3/0 0/0 0 -- --

Mn: 5 7 0 0 0 71.4 ------- 2 0 0 24/0 2/0 0/0 0 -- --

Mn: 6 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 7 153 0 0 0 98.0 ------- 3 0 0 40/0 3/0 0/0 0 -- --

Mn: 8 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 9 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 10 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 11 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 12 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 13 17 0 0 0 0.0 ------- 17 0 0 972/0 17/0 0/0 0 -- --

Mn: 14 187 0 0 0 93.6 ------- 12 0 0 768/0 12/0 0/0 0 -- --

Mn: 15 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 16 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 17 131 0 0 0 15.3 ------- 124 12 0 5196/0 124/0 39/13 0 -- --

Mn: 18 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: 19 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: other 0 0 0 0 0.0 ------- 0 0 0 0/0 0/0 0/0 0 -- --

Mn: total 529 0 0 0 72.0 13 158 12 0 7036/0 161/0 39/13 0 0.3 0.1


Io reports l.jpg
-IO Reports

  • IO simply reports the input and output operations the server conducted during the specified interval

  • This is not broken out by device, the counters relate to entire server activity


I o report input l.jpg
-IO Report - Input

  • Reads The number of read requests

  • LRd(KB)The page size multiplied by the number of requests

  • PRd(KB)The actual number of bytes read

  • Rratio The ratio of logical to physical

  • The last figure relates the efficiency of the compression to disk - for reads


I o report output l.jpg
-IO Report - Output

  • Writes The number of write requests

  • LWrt(KB)The page size multiplied by the number of requests

  • PWrt(KB)The actual number of bytes written

  • Wratio The ratio of logical tophysical

  • The last figure relates the efficiency of the compression to disk - for writes


Io report l.jpg
-IO Report

Sybase Adaptive Server IQ Performance Monitor

---------------------------------------------

Version 3.1

Options string for Main cache: "-io -file_suffix io-iqmon -append -interval 10"

Main Shared Buffer Cache

2000-01-24 10:54:21

Input Output

Reads LRd(KB) PRd(KB) Rratio Writes LWrt(KB) PWrt(KB) Wratio

Mn: 8 512 512 1.00 1 64 64 1.00

Mn: 102 6528 4804 1.36 0 0 0 0.00

Mn: 102 6528 4976 1.31 0 0 0 0.00

Mn: 114 7296 5392 1.35 0 0 0 0.00

Mn: 270 17280 8220 2.10 0 0 0 0.00

Mn: 161 10304 3660 2.82 0 0 0 0.00

Mn: 0 0 0 0.00 0 0 0 0.00

Mn: 0 0 0 0.00 0 0 0 0.00

Mn: 0 0 0 0.00 0 0 0 0.00

Mn: 0 0 0 0.00 0 0 0 0.00


12 4 2 and beyond options l.jpg
12.4.2 (and beyond) Options

  • The following slides detail the output from the following new (in 12.4.2) options

    • -contention

    • -threads

    • -bufalloc

    • -debug


Contention l.jpg
-contention

  • The –contention option basically displays all of the lock and mutex counters

  • These are counters that show the activity within the buffer cache and how quickly these locks were resolved

  • Contention shows statistics for both the main and temp buffer caches and the “heap” memory

    • Heap is the load and user memory


Contention described 1 l.jpg
-contention described – 1

  • AU Active Users

  • LRULks# of times a lock was requested

  • woTO# of times a lock did not have to time out (withoutTimeOut)

  • LoopsHow many lock request had to spin waiting for the lock to be granted

  • TO# of Timeouts (opposite of woTO)


Contention described 2 l.jpg
-contention described – 2

  • BWaits (as described above)

  • IOLock # of locks taken while looking for place to write the data

  • IOWait how many had to wait

  • HTLock # of locks on the internal Hash Table

  • HTWait how many had to wait

  • FLLock # of locks on the free list

  • FLWait how many had to wait


Contention heap memory l.jpg
-contention – Heap Memory

  • The heap is any memory that IQ-M has to use that is not within either of the 2 caches

  • This memory is composed of Load Memory, User Memory, thread stacks etc.

  • MemLks # of locks taken on the heap

  • MemWts how many had to wait


Contention42 l.jpg
-contention

Sybase Adaptive Server IQ Performance Monitor

---------------------------------------------

Version 3.1

Options string for Main cache: "-contention -file_suffix contention-iqmon -append -interval 10"

Contention

2000-01-24 10:57:03

AU| Main Cache |

LRULks woTO Loops TOs BWaits IOLock IOWait HTLock HTWait FLLock FLWait

0 66 0 0 0 0 1 0 5 0 4

1 2958 0 0 0 0 160 0 1117 0 6

1 1513 0 0 0 1 378 0 2 0 8

1 370 0 0 0 0 94 0 2 0 10

1 156 0 0 0 0 46 0 2 0 12

1 885 0 0 0 0 248 0 2 0 14

1 1223 0 0 0 0 332 1 2 0 16

1 346 0 0 0 0 66 0 2 0 18

| Temp Cache | Memory Mgr

|LRULks woTO Loops TOs BWaits IOLock IOWait HTLock HTWait FLLock FLWait |MemLks MemWts

0 70 0 0 0 0 1 0 4 0 5 0 55483 13

0 466 0 0 0 0 2 0 15 0 12 0 5705 0

0 963 0 0 0 0 2 0 8 0 20 1 2048 0

0 1186 0 0 0 0 2 0 2 0 23 1 186 4

0 357 0 0 0 0 2 0 2 0 25 1 2 0

0 444 0 0 0 0 2 0 3 0 29 1 137 0

0 884 0 0 0 0 2 0 2 0 31 1 22 0

0 1573 0 0 0 0 2 0 5 0 37 1 203 3


Threads option l.jpg
-threads Option

  • The –threads option details the counters held by the thread manager

  • This tells you actually what is going executing (sort of) inside the server

  • This option can be selected for main or private, however it is irrelevant what you select, the thread manager is server wide!


Threads described l.jpg
-threads described

  • CPU# of CPUs IQ-M is using (maybe not the total number in the box)

  • Limitmax # of threads IQ-M can run

  • NTeams # of teams currently running

  • MaxTeamsHigh Water Mark for teams

  • NThreadsCurrent # of threads in existence

  • Resrvd # of threads reserved for IQ-M

  • Free# of free threads inc. Resrvd

  • Locks# of locks on the thread manager

  • Waitshow many had to wait


Threads l.jpg
-threads

Sybase Adaptive Server IQ Performance Monitor

---------------------------------------------

Version 3.1

Options string for Main cache: "-threads -file_suffix threads-iqmon -append -interval 10"

Threads

2000-01-24 10:59:24

CPU Limit Nteams MaxTeams Nthreads Resrvd Free Locks Wait

10 100 4 12 100 13 68 106 590

10 100 6 12 100 12 63 4 6

10 100 6 12 100 12 63 0 0

10 100 7 12 100 12 62 1 1

10 100 7 12 100 12 62 0 0

10 100 7 12 100 12 58 1 5

10 100 7 12 100 12 58 0 0


Bufalloc l.jpg
-bufalloc

  • -bufalloc prints the statistics from the buffer allocation routine

  • This is the control of how many new pages / buffers were requested and by what

  • This can be executed for both main and temp cache – and is useful for both


Bufalloc described 1 l.jpg
-bufalloc described - 1

  • OU User Resource Allocation number

  • AU # of Active Users

  • MaxBuf # of buffers on the cache

  • Avail# of buffers available in the cache

  • AvPF# of buffers available to the pre-fetchmechanism

  • Slots# of buffers active for pre-fetch

  • PinUsr # of users using the buffer manager


Bufalloc described 2 l.jpg
-bufalloc described - 2

  • PFUsr# of pre-fetch users

  • Posted # of posted users. Users who think they know how many buffers they willuse

  • UnPost # of unposted users. See Above

  • Quota# buffers reserved for use

  • Locks# of locks in the buffer manager

  • WaitsHow many had to wait


Bufalloc49 l.jpg
-bufalloc

Sybase Adaptive Server IQ Performance Monitor

---------------------------------------------

Version 3.1

Options string for Main cache: "-bufalloc -file_suffix bufalloc-iqmon -append -interval 10"

Buffer Allocation

2000-01-24 10:58:39

OU/AU MaxBuf Avail AvPF Slots PinUsr PFUsr Posted UnPost Quota Locks Waits

1/0 1592 1592 20 0 0 0 0 0 0 1 0

1/1 1592 1592 20 0 0 0 0 0 0 1 0

1/1 1592 1592 20 0 0 0 0 0 0 1 0


Debug 1 l.jpg
-debug - 1

  • This is an undocumented (sort of) option for the IQ Monitor.

  • Basically it displays everything we could think of

  • Beware – this option generates a serious amount of paper, but can be very useful

  • It can be run for either the main or temp caches – although some of the information is the same for both

  • Some of the same counters are displayed as described above, but they are called by their internal counter names – do not be confused


Debug 2 l.jpg
-debug – 2

  • There are 10 subsections to the -debug report

    • Buffer Manager (main or temp)

    • Contention Counters

    • Dirty Page and Sweeper Thread Counters

    • Heap Memory Manager

    • Thread Manager

    • Free List

    • Buffer Allocation Manager

    • Buffer Allocation Histogram

    • Prefetch Information


Debug buffer manager 1 l.jpg
-debug – Buffer Manager - 1

  • This section of the –debug report is a sort of re-hash of the –cache-by-type report.

  • The fields are the same, but named subtly differently, and the buffer types are named rather than shown by number

  • The buffer types correspond exactly to the numbers in the -cache_by_type report

  • I have put a “decode” matrix together on the next few slides


Debug buffer manager 2 l.jpg
-debug – Buffer Manager – 2

  • Finds Find Rate (as cache_by_type)

  • Hits # of cache hits

  • Hit% Hit Rate (as cache_by_type)

  • FalseMiss After rechecking to confirm that the buffer we want is not in memory - we find it is!

  • Cloned Cloned (as cache_by_type)

  • Creates Creates (as cache_by_type)


Debug buffer manager 3 l.jpg
-debug – Buffer Manager – 3

  • Destroys Destroys (as cache_by_type)

  • Dirties Dirty (as cache_by_type)

  • RealDirties This is where the dirty flag actually was set

  • Prefetchesprefetch (as cache_by_type)

  • Prefetchmiss When we did the prefetch wefound that the bufferwas actually inmemory


Debug buffer manager 4 l.jpg
-debug – Buffer Manager – 4

  • ReadsReads(as io)

  • PReadBLks# of physical blocks read

  • PReadKB PRd(KB)(as io)

  • ReReads ReReads (as cache_by_type)

  • Writes Writes(as io)

  • PWriteBlks # of physical blocks written

  • PWriteKBPWrt(KB) (as io)


Debug buffer manager 5 l.jpg
-debug – Buffer Manager – 5

  • GrabbedDirtyGDirty(as cache_by_type)

  • MovedtoMRU # of buffers that after use were put back at the MRU

  • MovedtoWash # of buffers that after use were put straight into the wash

  • RemovedfromLRU # of buffers that were deleted in the MRU-LRU chain

  • Removedfromwash # of buffers that were deleted from the wash area

  • RemovedinScanMode # of buffers that were “used” when we thought that they were just being scanned


Debug buffer manager 6 l.jpg
-debug – Buffer Manager – 6

BType Stats Name Total none rfu rfu rfu btreev btreef rfu vdo dbext dbid sort store garray barray blkmap hash ckpt bm test cmid

Finds 2237 0 0 0 0 60 15 0 297 0 0 0 0 6 1256 420 0 0 183 0 0

Hits 1795 0 0 0 0 53 10 0 290 0 0 0 0 4 889 384 0 0 165 0 0

Hit% 80.2 0 0 0 0 9e+01 7e+01 0 1e+02 0 0 0 0 7e+01 7e+01 9e+01 0 0 9e+01 0 0

FalseMiss 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Cloned 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Creates 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Destroys 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Dirties 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

RealDirties 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Prefetchs 9 0 0 0 0 0 4 0 0 0 0 0 0 5 0 0 0 0 0 0 0

PrefetchMiss 8 0 0 0 0 0 3 0 0 0 0 0 0 5 0 0 0 0 0 0 0

Reads 450 0 0 0 0 7 8 0 7 0 0 0 0 7 367 36 0 0 18 0 0

PReadBlks 3908 0 0 0 0 21 36 0 22 0 0 0 0 91 3144 576 0 0 18 0 0

PReadKB 15632 0 0 0 0 84 144 0 88 0 0 0 0 364 12576 2304 0 0 72 0 0

ReReads 443 0 0 0 0 0 8 0 7 0 0 0 0 7 367 36 0 0 18 0 0

Writes 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

PWriteBlks 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

PWriteKB 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

GrabbedDirty 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

MovedToMRU 2121 0 0 0 0 60 16 0 297 0 0 0 0 10 1245 420 0 0 73 0 0

MovedToWash 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

RemovedFromLRU 1692 0 0 0 0 53 10 0 290 0 0 0 0 4 886 384 0 0 65 0 0

RemovedFromWash 1383 0 0 0 0 53 10 0 290 0 0 0 0 4 700 302 0 0 24 0 0

RemovedInScanMode 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0


Debug contention 1 l.jpg
-debug – Contention – 1

  • This section of the report is a rehash of the-contention report

  • This section is cache dependant, so if you need information from both main and temp you will have to run monitors in both temp and main


Debug contention 2 l.jpg
-debug – contention – 2

  • BusywaitsBWaits(as cache_by_type) and (as contention)

  • LRUNumLocks LRULks (as contention)

  • LRUNumSpinsWoTO WoTO (as contention)

  • LRUNumSpinLoops Loops (as contention)

  • LRUNumTimeOuts TOs (as contention)

  • HTNumLocks HTLock (as contention)

  • HTNumWaits HTWait (as contention)

  • IONumLocks IOLock (as contention)

  • IONumWaits IOWait (as contention)


Debug contention 3 l.jpg
-debug – contention – 3

BusyWaits 0

LRUNumLocks 7650

LRUNumSpinsWoTO 0 ( 0.0 %)

LRUNumSpinLoops 0

LRUNumTimeOuts 0 ( 0.0 %)

HTNumLocks 1231

HTNumWaits 0 ( 0.0 %)

IONumLocks 794

IONumWaits 4 ( 0.5 %)


Debug flush operators 1 l.jpg
-debug – flush operators – 1

  • This section of the report concentrates of the handling of dirty pages and how the wash area and sweeper threads are working

  • If you have a write intensive server or a Multiplex Writer Node then this section is useful

  • As with contention this part of the report is cache dependant


Debug flush operators 2 l.jpg
-debug – flush operators – 2

  • Pages# of buffers in the cache

  • InUse # of buffers in the cachemarked asbeing used

  • Dirty Dirty (as cache_by_type)

  • Pinned # of buffers in the cache pinned

  • Flushes # of times the flush operator was called

  • FlushedBufferCount # of buffers flushed to disk


Debug flush operators 3 l.jpg
-debug – flush operators – 3

  • GetPageFrame # of buffers requested from the buffer manager

  • GetPageFrameFailure # of buffers requested but request failed (maybe due toGrabbed Dirty)

  • GotEmptyFrame # of buffers that we got that were empty, that we did nothave to clean or “make”


Debug flush operators 4 l.jpg
-debug – flush operators – 4

  • Washed # of buffers passing the wash mark

  • TimesSweepersWoken # of times that the sweeper threadswent to sleep (with nothing to do) andwere wokenup

  • WashTeamSize # of threads in the sweeper team (option set)

  • WashMaxSize # of buffers in the wash area (option set)


Debug flush operators 5 l.jpg
-debug – flush operators – 5

  • washNBuffers # of clean buffers passing thewash marker

  • washNDirtyBuffers # of dirty buffers passing thewash marker

  • washSignalThreshold # of dirty buffers that have to pass the wash marker before the sweepers are woken up

  • washNActiveSweepers # of sweepers actually working

  • washIntensity Internal flag


Debug flush operators 6 l.jpg
-debug – flush operators – 6

Pages= 1592 ( 100.0 %)

InUse= 459 ( 28.8 %)

Dirty= 1 ( 0.1 %)

Pinned= 23 ( 1.4 %)

Flushes 0

FlushedBufferCount 0

GetPageFrame 451

GetPageFrameFailure 0

GotEmptyFrame 451

Washed 0

TimesSweepersWoken 0

washTeamSize= 10

washMaxSize= 319 ( 20.0 %)

washNBuffers= 319 ( 20.0 %)

washNDirtyBuffers= 1 ( 0.1 %)

washSignalThreshold= 32 ( 2.0 %)

washNActiveSweepers= 0

washIntensity= 1


Debug heap manager 1 l.jpg
-debug – Heap Manager – 1

  • This section of the report concerns the ASIQ-M “overhead memory”

  • This is the Load Memory, the User Memory and any other bits of RAM that IQ-M wants during execution

  • For obvious reasons this section is not cache dependant


Debug heap manager 2 l.jpg
-debug – Heap Manager – 2

  • Memallocated Amount of memory currently in the heap

  • MemAllocatedMax High Water Mark for this run

  • MemAllocatedEver High Water Mark since theserver started

  • MemNAllocated # of calls to allocate memory


Debug heap manager 3 l.jpg
-debug – Heap Manager – 3

  • MemNAllocatedEver # of calls to allocatememory since the server started

  • MemNTimesLocked # of calls to lock (part of) the heap

  • MemNTimesWaited # of times we had to wait for a lock


Debug heap manager 4 l.jpg
-debug – Heap Manager – 4

MemAllocated= 215413736 ( 210364 KB)

MemAllocatedMax= 320573744 ( 313060 KB)

MemAllocatedEver 2456040 ( 2398 KB)

MemNAllocated= 4115

MemNAllocatedEver 6520

MemNTimesLocked 10022

MemNTimesWaited 0 ( 0.0 %)


Debug thread manager 1 l.jpg
-debug – Thread Manager – 1

  • This section (almost) duplicates the –thread report

  • Again this report tells you how active the server processing is, rather than how active memory is

  • This is not cache dependent


Debug thread manager 2 l.jpg
-debug – Thread Manager – 2

  • ThrNumOfCpusCPU(as threads)

  • ThreadLimit Limit (as threads)

  • ThrNumThreads # of threads actually useable

  • ThrReserved Resrvd (as threads)

  • ThrNumFree Free (as threads)

  • ThrNumUsedNThreads (as threads)

  • UsedPerActiveCmd# of threads per command


Debug thread manager 3 l.jpg
-debug – Thread Manager – 3

  • ThrNTeamsInUseNTeams(as threads)

  • ThrMaxTeams MaxTeams (as threads)

  • CumNumTeams Total # of Teams during the run duration

  • CumTeamThreads Total # of threads used in total # of teams

  • CumSingleThr # of teams that had only 1thread

  • ThrMutexLocks Locks (as threads)

  • ThrMutexWaits Waits (as threads)


Debug thread manager 4 l.jpg
-debug – Thread Manager – 4

ThrNumOfCpus= 10

ThreadLimit= 100 ( 100.0 %)

ThrNumThreads= 100 ( 100.0 %)

ThrReserved= 12 ( 12.0 %)

ThrNumFree= 56 ( 56.0 %)

NumThrUsed 44 ( 44.0 %)

UsedPerActiveCmd 44

ThrNTeamsInUse= 8

ThrMaxTeams= 12

CumNumTeams 9

CumTeamThr 15

CumSingleThr 5

ThrMutexLocks 24

ThrMutexWaits 0 ( 0.0 %)


Debug free list 1 l.jpg
-debug – Free List – 1

  • This section describes the activity in the free list

  • This gives an indication on the allocation and de-allocation activity in the server

  • This is cache dependent, as there is one free list per cache

  • An important point, the free list is a misnomer, it should be called the allocated page list


Debug free list 2 l.jpg
-debug – Free List – 2

  • FreeBitCount# of allocated blocks in the store (IQ Main or IQ Temp)

  • FLIsOutOfSpace Flag to indicate when the store is fully allocated (full)

  • FLMutexLocks # of times the free list lock was taken out

  • FLMutexWaits # of times an operator had towait on the free list lock


Debug free list 3 l.jpg
-debug – Free List – 3

FLBitCount= 320116

FLIsOutOfSpace= 0

FLMutexLocks 1

FLMutexWaits 0 ( 0.0 %)


Debug buffer allocation 1 l.jpg
-debug – Buffer Allocation – 1

  • This section of the report details the counters showing the performance of the buffer allocation manager

  • This is almost the same as –bufalloc

  • This is a cache dependent report


Debug buffer allocation 2 l.jpg
-debug – Buffer Allocation – 2

  • Elapsed Seconds # of CPU seconds used during the runinterval

  • CPU User Seconds # of USER Mode CPUSeconds

  • CPU Sys Seconds # of System ModeCPU Seconds

  • CPU Total Seconds

  • Total of System and User Seconds


Debug buffer allocation 3 l.jpg
-debug – Buffer Allocation – 3

  • NactiveCommandsAU (as bufalloc)

  • BufAllocMaxBufs MaxBuf (as bufalloc)

  • BufAllocAvailBufs Avail (as bufalloc)

  • BufAllocReserved Quota (as bufalloc)

  • BufAllocAvailPF AvPF (as bufalloc)

  • BufAllocSlots Slots (as bufalloc)

  • BufAllocNPinUsers PinUsr (as bufalloc)


Debug buffer allocation 4 l.jpg
-debug – Buffer Allocation – 4

  • BufAllocNPFUsersAU (as bufalloc)

  • BufAllocNPostedUsers Posted (as bufalloc)

  • BufAllocNUnpostUsrs Unpost (as bufalloc)

  • BufAllocPinQuota Quota (as bufalloc)

  • BufAllocMutexLocks Locks (as bufalloc)

  • BufAllocMutexWaits Waits (as bufalloc)


Debug buffer allocation 5 l.jpg
-debug – Buffer Allocation – 5

Elapsed Seconds 10.13 ( 10.0 %)

CPU User Seconds 10.58 ( 10.4 %)

CPU Sys Seconds 0.60 ( 0.6 %)

CPU Total Seconds 11.18 ( 11.0 %)

NActiveCommands= 1

BufAllocMaxBufs= 1592 ( 100.0 %)

BufAllocAvailBufs= 1589 ( 99.8 %)

BufAllocReserved= 3 ( 0.2 %)

BufAllocAvailPF= 17 ( 1.1 %)

BufAllocSlots= 100

BufAllocNPinUsers= 0

BufAllocNPFUsers= 2

BufAllocNPostedUsrs= 0

BufAllocNUnpostUsrs= 0

BufAllocPinQuota= 0

BufAllocMutexLocks 5

BufAllocMutexWaits 0 ( 0.0 %)


Debug allocation hist 1 l.jpg
-debug – Allocation Hist. – 1

  • This report splits out the Buffer Allocation Statistics by buffer type

  • Then it takes a count of the users pinning buffers and produces a size histogram showing buffer type against a histogram count

  • This is cache dependent


Debug allocation hist 2 l.jpg
-debug – Allocation Hist. – 2

BufferAlloc StatName Total unknwn hash csort fp test garray btree bm

NumClients= 0 29972724 0 0 1057300152 0 0 0 1127219200

PinUserQuota= 0 29972724 0 0 1057300152 0 0 0 1127219200

PrefetchUserQuota= 0 29972724 0 0 1057300152 0 0 0 1127219200

ClientCountOfPinners 0 1 3 6 10 33 66 100 333 666 1000 3333 6666 10000

Unknown= 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Hash= 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Sort= 0 0 0 0 0 0 0 0 0 0 0 0 0 0

FP= 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Test= 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Garray= 0 0 0 0 0 0 0 0 0 0 0 0 0 0

BTree= 0 0 0 0 0 0 0 0 0 0 0 0 0 0

BM= 0 0 0 0 0 0 0 0 0 0 0 0 0 0

BV= 0 0 0 0 0 0 0 0 0 0 0 0 0 0


Debug prefetch 1 l.jpg
-debug – Prefetch – 1

  • This section of the report details what has happened in the prefetch mechanism

  • This is a sort of amalgamation of a series of reports, but I will describe each counter

  • This is cache dependent


Debug prefetch 2 l.jpg
-debug – Prefetch – 2

  • PFMgrNThreads# of Prefetch Threads

  • PFMgrNSubmitted # of Prefetchsubmissions

  • PFMgrNDropped # of droppedrequests(buffer already in memory)

  • PFMgrNValid # of valid Requests

  • PFMgrNRead # of PF Reads

  • PFMgrReading # of PF Reads completed


Debug prefetch 3 l.jpg
-debug – Prefetch – 3

PFMgrNThreads= 5

PFMggrNSubmitted= 0

PFMgrNDropped= 0

PFMgrNValid= 0

PFMgrNRead= 0

PFMgrNReading= 0


Slide88 l.jpg

EA 514Introduction to the Adaptive Server IQ Monitor