Performance Data What is Important and How Do

Performance Data: What is Important and How Do I Make Sense of It Adam Backman A Nice Guy, DBAppraise 1

Agenda • • • 2 Why do you need a monitor? Monitoring Alternatives What Are VSTs? Basic Capabilities Advanced Features

Monitoring Tools/Services • Free – Glance – TOP – PROMON – Pro. Top • Not Free – Open. Edge Management – Pro. Monitor – DBAppraise 3

What Are VSTs? • Virtual System Tables • A 4 GL View of Progress Data Structures (the same as those shown in PROMON. ) • No Performance Impact (mostly) • Primarily Read-Only • Knowing what to use can be a challenge 4

Some VST Quirks • Updateable: – _startup. _spin – Private buffers • Table & Index Ranges: -tablebase, -tablerangesize -indexbase, -indexrangesize – Table & Index Window can be reset! • Quirky Keys: – _myconnection… – _tablestat & _indexstat 5

6

Basic Capabilities • • 7 Summary Data Blocked Clients & Open Transactions Table & Index Activity User Activity Storage Area Capacity Estimating Big B Latches & Resources Clients & Servers

Summary Data 11: 32: 52 Pro. Top xvi -- Progress Database Monitor Sample Hit Ratio: Miss% : sports [/db/sports] 182: 1 195: 1 Commits: 149 0. 549% 0. 512% Latch Waits: 13 Hit% : 99. 45% 8 07/05/05 99. 48% Log Reads: 76342 80927 OS Reads: 419 Rec Reads: 23789 Tot/Mod Bufs: 60002 195 Sessions: 2057 16 Local: 953 3167 Remote: 956 2 1 414 Lock Table: 1516 3 Server: 97 23619 Lk. HWM|Old. Trx: 1392 00: 00 Other: 51 54 TRX: 26 Blocked: 0 Log/Rec: 3. 2091 3. 4264 Area Full: 1 98. 60% Evict Bufs: Rate Old/Curr BI: 54 After Image: Disabled Batch: 1045

Sample, Summary, Rate & Raw Data • Base. Value • Last. Value • This. Value • Sample. Time • Summary. Time Sample. Rate = (This. Value – Last. Value) / Sample. Time. Summary. Rate = (This. Value – Base. Value) / Summary. Time. 9 Sample. Raw = (This. Value – Last. Value). Summary. Raw = (This. Value – Base. Value).

Blocked Sessions Usr Name Note -----------------24 tom 10 REC XQH 102 [Order] julia

Locked Records for each _Lock no-lock while _Lock-usr <> ? : if _Lock-recid = _Connect-wait 1 then do: find _file where _file-num = _Lock-table no-error. bxtbl = _file-name. end. if _Lock-usr = _Connect-usr then bxwait = bxwait + “ “ + _Lock-flags. else bxque = bxque + " " + _Lock-name. end. bxnote = bxtbl + bxwait + bxque. 11

Open Transactions Usr Name TRX Num BI Clstr Start Trx Stat Duration Wait -------- ---------- 12 9 root 2432897 1024 15: 39: 05 ACTIVE 00: 01 -- 29440 20 root 2432896 ALLOCATE 00: 00 -- 20115 5 root 2432898 1024 15: 39: 06 ACTIVE 00: 00 -- 21952 7 root 2432899 1024 15: 39: 06 ACTIVE 00: 00 -- 19040 23 julia 2418661 - ALLOCATE 00: 00 -- 0 22 tom 2417938 - ALLOCATE 00: 00 -- 0 -

Table Activity Table Statistics Tbl# Table Name Create Read Update Delete ---------- -----4 Order. Line 13 0 444 11 0 18 Order 0 98 1 0 24 POLine 0 92 1 0 23 Purchase. Order 0 42 1 0 21 Bin 0 5 0 0 2 Customer 0 4 0 0 1 Invoice 0 1 0 0 10 Employee 0 0 13 Family 0 0

Table Stats /** This does NOT work if –tablebase <> 1!!! find _File no-lock where _File-num = p_tbl no-error. find _Table. Stat no-lock where _Table. Stat-id = p_tbl no-error. display p_tbl _file-num _Table. Stat-id. **/ /*** use the following: ***/ find _Table. Stat no-lock where _Table. Stat-id = p_tbl no-error. find _File no-lock where _File-num = _Table. Stat-id no-error. display p_tbl _file-num _Table. Stat-id. 14

Index Activity Index Statistics Idx# Index Name Create Read Split Delete Blk. Del ---------- -------- 15 22 orderline PU 0 44 0 0 0 12 Cust. Num PU 0 3 0 0 0 2 _File/Field PU 0 0 0 3 _Field-Name U 0 0 0 5 _File/Index PU 0 0 0

Index Type find _Index. Stat no-lock where _Index. Stat-id = p_idx no-error. find _Index no-lock where _Index. _Idx-num = _Index. Stat-id no-error. find _File where recid( _File ) = _Index. _File-recid no-error. tt_index. idxnote = _File-name + “. ” + _Index-name + ( if _file. _prime-index = recid(_index) then “ P" else “ " ) + ( if _index. _unique then "U" else "" ) 16

User IO Activity UIO Usr Name Flags PID DB Access OS Rd OS Wr Hit% ------- ------13 tom SB 13590 2266 200 1 91. 13% 10 jami SB 13584 190 6 1 97. 10% 16 julia SB 13596 185 6 1 97. 03% 17 peter SB 13598 181 5 1 97. 07% 15 emily SB 13594 177 5 1 97. 12% 11 tiger SB* 13586 166 4 0 97. 58% 14 tucker SB 13592 159 5 1 97. 10% 19 granite SB 13602 146 1 0 99. 25% 13578 145 4 1 97. 16% 7 astro 17 SB

Estimating Big B Guess. Timator Pct 18 Big B % db Size Hit: 1 Miss% Hit% OS Rd --------- ----- 10% 6000 0. 124% 30 3. 306% 96. 694% 1343 25% 15001 0. 311% 48 2. 091% 97. 909% 849 50% 30001 0. 622% 68 1. 479% 98. 521% 601 100% 60002 1. 243% 96 1. 046% 98. 954% 425 <= 150% 90003 1. 865% 117 0. 854% 99. 146% 347 200% 120004 2. 486% 135 0. 739% 99. 261% 300 400% 240008 4. 973% 191 0. 523% 99. 477% 213

Big B http: //www. peg. com/lists/dba/history/200301/msg 00509. html Miss. Pct = 100 * ( 1 – ( Log. Rd – OSRd ) / Log. Rd )). Hit. Pct = 100 – Miss. Pct. OSRd = Log. Rd * ( Miss. Pct / 100 ). m 2 = m 1 * exp(( b 1 / b 2 ), 0. 5 ). 19

Latch Waits Id Latch Requests Waits Lock% ------------ ------28 MTL_BF 4 5540 33 99. 40% 17 MTL_BHT 4205 106 97. 49% 21 MTL_LRU 4154 55 98. 68% 10 MTL_LHT 1800 24 98. 65% 15 MTL_LKF 1798 0 100. 00% 26 MTL_BF 2 1218 6 99. 48% 27 MTL_BF 3 1184 10 99. 13% 25 MTL_BF 1 1150 10 99. 16% 913 4 99. 60% 4 MTL_OM 20

Resource Waits Id Resource Locks Waits Lock% ------------ ------10 DB Buf S Lock 2661 0 100. 00% 658 0 100. 00% 7 DB Buf Read 40 0 100. 00% 2 Record Lock 21 0 100. 00% 11 DB Buf X Lock 11 0 100. 00% 19 TXE Share Lock 11 0 100. 00% 3 0 100. 00% 21 TXE Commit Lock 2 0 100. 00% 1 Shared Memory 0 0 0. 00% 3 Schema Lock 0 0 0. 00% 6 Record Get 8 DB Buf Write 21

Servers and Clients Servers Srv Type Port Con Max MRecv MSent RRecv RSent QSent Time. Slice --- ----- ----- ----1 Login 7150 0 1 0 0 0 2 Auto 1026 10 55 0 0 0 3 Auto 1027 10 55 23 13 0 6 10 86 Server IO Srv Type Port Con Max DB Access OS Rd OS Wr Hit% ----- --- --------- ------- 22 19 Auto 1043 10 55 5041 2 0 99. 96% 20 Auto 1044 10 55 1348 1 0 99. 96% 18 Auto 1042 10 55 157 1 0 99. 51% 16 Auto 1040 10 55 42 1 0 98. 70%

Storage Area Capacity Area Statistics Area Name Blocks Hi Water Free %Used RPB Note --------- --------1 Control Area 31 9 22 29. 03% 64 3 Primary Recovery 31998 30720 1278 96. 01% 1 6 Schema Area 11711 10688 1023 91. 26% 64 i(25) ** 7 Cust_Dat 895975 602498 293477 67. 24% 64 t(48) 8 Cust_Idx 191991 115270 76721 60. 04% 64 i(157) 1311962 1103066 208896 84. 08% 64 t(41) 87585 67. 80% 64 i(125) 9 Sales_Dat 10 Sales_Idx 23 271986 184401

Storage Area Capacity for each _Area. Status no-lock, _Area no-lock where _Area-num = _Area. Status-Areanum: bfree = _Area. Status-Totblocks - _Area. Status-Hiwater. if ( _Area. Status-Freenum <> ? ) then bfree = bfree + _Area. Status-Freenum. if bfree = ? then bfree = _Area. Status-totblocks. used = (( _Area. Status-totblocks - bfree) / _Area. Status-totblocks ) * 100. end. 24

Storage Area Contents for each _storageobject no-lock where _storageobject. _area-number = xid and _storageobject. _object-num > 0 and _storageobject. _object-associate > 0: if _storageobject. _object-type = 1 then so_tbl = so_tbl + 1. else if _storageobject. _object-type = 2 then so_idx = so_idx + 1. end. 25

Balancing IO Database File IO Id Extent Name Mode Blksz Reads Writes Extends ------------ -------12 sports 2000_11. d 1 BUFIO 1024 28 3 0 6 sports 2000_8. d 1 BUFIO 1024 9 0 0 BOTHIO 1024 6 0 0 1 sports 2000. db BOTHIO 1024 2 0 0 3 sports 2000. d 1 BOTHIO 1024 1 0 0 4 sports 2000_7. d 1 BUFIO 1024 1 0 0 8 sports 2000_9. d 1 BUFIO 1024 1 0 0 10 sports 2000_10. d 1 BUFIO 1024 1 0 0 2 sports 2000. b 1 UNBUFIO 8192 0 1 0 9 sports 2000_9. d 2 BOTHIO 1024 0 0 0 13 sports 2000_11. d 2 26

A Monitoring Architecture • VST Based • Multi-Platform – UNIX Character – HTML – Windows GUI • Using Publish & Subscribe 27

Events That A Module Handles • Mon-Restart – Empty Temp-Table – Remove self from memory • Mon-Init – Empty Temp-Table – Define Display Data Elements • Mon-Update – Refresh Data – Calculate intervals, rates and so forth – Update UI Temp-Table with results 28

Questions 29