1 / 33

Understanding New and Sparsely Documented onstats

H13. Understanding New and Sparsely Documented onstats. John F. Miller III IBM. Wednesday, October 4 2006 • 4:15 p.m. – 5:15 p.m. Overview. Recent Changes to onstat View I/O through onstat Disk IO Network IO Memory Usage Cache Sizes View Action by User Table BAR Performance.

makani
Download Presentation

Understanding New and Sparsely Documented onstats

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. H13 Understanding New and Sparsely Documented onstats John F. Miller III IBM Wednesday, October 4 2006 • 4:15 p.m. – 5:15 p.m.

  2. Overview • Recent Changes to onstat • View I/O through onstat • Disk IO • Network IO • Memory Usage • Cache Sizes • View Action by • User • Table • BAR Performance

  3. Recent onstat changes • onstat –m • Displays the message file when the server is offline • onstat –c • Displays the configuration file when the server is offline

  4. New onstat Options • onstat –g env User/System evironment • onstat –g stm Statement memory usage • onstat –g rwm Read/Write Mutex • onstat –C Btree Scanner • onstat –g cac Display various caches

  5. Looking at Environmentonstat –g env [sid] Session Environment Server Environment cmd> onstat -g env Server start-up environment: Variable Value [values-list] DBDELIMITER | DBPATH . DBTEMP /tmp cmd> onstat -g env 147 Environment for session 147: Variable Value [values-list] CLIENT_LOCALE en_US.8859-1 DBDELIMITER | DBPATH //jmiller_94 DBTEMP /usr/tmp

  6. Memory by SQL Statementonstat –g stm • Shows all currently active SQL statements • Shows the amount of memory each SQL statement currently using in bytes command> onstat -g stm session 16 -------------------------------------- sdblock heapsz statement ('*' = Open cursor) bca2018 11936 *select * from customer, orders session 14 --------------------------------------- sdblock heapsz statement ('*' = Open cursor) bc4d018 2048 Database 'insdel' bc4d140 8496 select count(*) from informix.systables; bc4d268 6264 *select * from t2

  7. Watching The Inverted Pyramidonstat –g iov AIO I/O vps: class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors msc 0 i 0.0 26 0 0 0 26 1.0 0 aio 0 s 0.2 1080 883 188 0 849 1.3 0 aio 1 i 0.2 1072 874 189 0 830 1.3 0 aio 2 s 0.2 884 852 28 0 699 1.3 0 aio 3 i 0.2 647 831 13 0 769 1.1 0 aio 4 i 0.2 252 832 16 0 712 1.2 0 aio 5 i 0.2 24 811 12 0 673 1.2 0 pio 0 i 0.0 2 0 2 0 3 0.7 0 lio 0 i 0.0 3 0 3 0 4 0.8 0 S indicates in System call (VP is currently busy) I indicates the VP is currently idle Errors indicate a lack of resources in the I/O subsystem Especially important if using KAIO

  8. Monitoring I/O Resources by Fileonstat -g iof AIO global files: gfd pathname totalops dskread dskwrite io/s 3 /dev/tsinfo1 1506 1393 113 0.0 4 /dev/tsinfo2 776 3 773 0.0 5 /dev/tsinfo0 22205 7994 14211 0.4 6 /dev/tsinfo3 55092 54102 990 1.1 • dskread/dskwrite • Number of read/write system calls • io/s • Average I/O operations per second

  9. Network Resources By Client Typeonstat -g ntd Client Type Calls Accepted Rejected Read Write sqlexec yes 4744 26 3911350 3882491 srvinfx yes 92 0 0 0 oncheck yes 410 0 114668 114851 onmonitor yes 4 0 136 137 ontape yes 5 0 66113 66117 • Client Type • The type of client connecting to the IDS • Accepted/Rejected • Number of times a successful/unsuccessful connection occurred • Read/Write • The number of times the engine received/sent data to the clients

  10. Network Resources By Useronstat -g ntu Individual thread network information (basic): netscb type thread name sid fd poll reads writes q-nrm q-exp 3fc97bc soctcp sqlexec 5269 66 5 38 38 0/ 1 0/ 0 3f857bc soctcp sqlexec 5268 64 5 1 1 0/ 1 0/ 0 3fa97bc soctcp sqlexec 5267 62 5 41 41 0/ 1 0/ 0 3e737bc soctcp sqlexec 5264 52 5 1586 1585 0/ 1 0/ 0 3c7f7bc soctcp sqlexec 5262 29 5 357 357 0/ 1 0/ 0 • sid • User session id • type • Indicates the communication protocol used for this thread • reads/writes • The number of OnLine packets received/sent by the OnLine

  11. Show System Memory Usageonstat –g seg • Shows the amount of memory current allocate, used and free • Block of memory are in 4KB units Segment Summary: id key addr size ovhd class blkused blkfree 93001 1386432513 10a000000 13631488 424320 R 3160 168 93002 1386432514 10ad00000 31457280 1696 V 4685 2995 9509 1386432519 10eb00000 8388608 992 V 123 1925 Total: - - 53477376 - - 7968 5088

  12. Useful onstat Options • onstat –P Buffers by Table • onstat –g tpf Actions by Thread • onstat –g ppf Actions by Table

  13. Monitoring Different Caches • Buffer Cache • Stores commonly accessed data/index pages from disk • Data Dictionary • Cache used to store commonly accessed system catalog information • Data Distributions • Cache to store update statistics medium/high data used by the optimizer to build query plans • Stored Procedure Cache • Caches stored procedure code • Extensible Caches

  14. Buffer Cache Sizing (onstat –p) Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 78201 141506 1538858 94.92 14480 23668 298884 95.16 • System Read Cache • Increasing buffers improves read cache rate • 90% or better is a good read cache • System Write Cache • Increasing buffers may not help the write cache • LRU parameters and CKPTINTVL greatly affect the write cache

  15. Buffer Pool Stats by Tableonstat –P Buffer Pool System Statistics • Data • Percentage of the buffer pool holding data pages • Btree • Percentage of the buffer pool holding index pages • Other • Control pages, such as, reserve pages, bitmap • Partnum • Numeric link to objects name table/index/fragment • Total • Number of pages currently buffered for this table/fragment • Data • Number of data page currently buffered for this table/fragment partnum total btree data other dirty 1048707 10 6 2 2 0 1048710 4 1 1 2 0 1048714 3 1 1 1 0 1049081 257 0 255 2 0 1049472 5462 0 5459 3 4635 Totals: 9000 52 5747 3201 4636 Percentages: Data 72.86 Btree 25.58 Other 01.57 Ensure the ratio of System Data/Btree pages looks reasonable for your workload

  16. Data Dictionary Cache onstat -g dic Dictionary Cache: Number of lists: 31, Maximum list size: 10 list# size refcnt dirty? heapptr table name -------------------------------------------------------- 11 1 1 no a2ecf88 d1@olympia:jmiller.t2 25 1 0 no a2ec330 d1@olympia:informix.systables Total number of dictionary entries: 2 • Shows the tables in the data dictionary • Total number of current entries • Currently 2 • Total number of available cache locations • Currently 31 * 10 = 310

  17. Data Distributions Cacheonstat –g dsc Cache used to store update statistics medium or high information Distribution Cache: Number of lists : 31 PC_POOLSIZE : 50 Number of entries : 2 Number of entries in use : 0 Distribution Cache Entries: list# id ref_cnt dropped? heap_ptr distribution name ----------------------------------------------------------------- 15 0 0 0 abe2c20 stores@oly:customer.lname 7 0 0 0 abe3820 stores@oly:customer.company Total number of distribution entries: 2.

  18. Stored Procedure Cacheonstat –g prc Stored Procedure Cache: Number of lists : 31 PC_POOLSIZE : 50 Number of entries : 3 Number of inuse entries : 0 Stored Procedure Cache Entries: list# id ref_cnt dropped? heap_ptr procedure name -------------------------------------------------------------- 4 1 0 0 abe3c20 stores7@olympia:informix.systdist 22 2 0 0 a2f0c58 sysmaster@olympia:informix.bitval 14 3 0 0 a2f0458 sysmaster@olympia:informix.l2date Total number of procedure entries: 3.

  19. Extensible Caches • User Defined Routines • Routine Cache • Type Cache • Cast Cache • OpClass • Access Method Cache • Secondary Access Method Cache • User Defined Aggregate Cache

  20. Actions by Table onstat -g ppf Partition profiles partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc 9437341 11 0 0 0 0 4 0 0 26 14 0 9437394 205 0 0 0 63 11 0 8 318 129 6 10485999 15 0 0 0 0 6 0 0 29 18 0 11534359 5 0 0 0 0 1 0 0 18 8 0 6291561 0 0 0 0 1854 0 0 0 3605 0 103 • Table • Locks • SQL like Actions • Buffers

  21. Actions by Table onstat -g ppf Partition profiles partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc 9437341 11 0 0 0 0 4 0 0 26 14 0 9437394 205 0 0 0 63 11 0 8 318 129 6 10485999 15 0 0 0 0 6 0 0 29 18 0 11534359 5 0 0 0 0 1 0 0 18 8 0 6291561 0 0 0 0 1854 0 0 0 3605 0 103 • partnum Decimal number linked to systables:partnum • lkrqs LocK ReQuestS Number of times a lock has been acquired on the table • lkwts LocK WaiTS Number of times a user had to wait when acquiring a lock on this table • Dlks DeadLocKS

  22. Actions by Table onstat -g ppf Partition profiles partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc 9437341 11 0 0 0 0 4 0 0 26 14 0 9437394 205 0 0 0 63 11 0 8 318 129 6 10485999 15 0 0 0 0 6 0 0 29 18 0 11534359 5 0 0 0 0 1 0 0 18 8 0 6291561 0 0 0 0 1854 0 0 0 3605 0 103 • isrd ISam Reads Number of times a rows has been read from this table • iswrt ISam WriTes Number of calls to insert a row into this table • isrwt ISam ReWriTes Number of calls to update a row in this table • Isdel ISam DELetes Number of calls to delete a row from this table

  23. Actions by Table onstat -g ppf Partition profiles partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc 9437341 11 0 0 0 0 4 0 0 26 14 0 9437394 205 0 0 0 63 11 0 8 318 129 6 10485999 15 0 0 0 0 6 0 0 29 18 0 11534359 5 0 0 0 0 1 0 0 18 8 0 6291561 0 0 0 0 1854 0 0 0 3605 0 103 • bfrd BuFfer ReaDs Number of individual page reads from this table • bfwrt BuFfer WRites Number of times a page in this table has been modified • seqsc SeQuential SCans Number of sequential table scans Watch the ratio of bfrd/seqsc as an indicator of a poorly indexed table

  24. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • User Thread • Locks • Log Records • SQL Like Actions • Transactions • Buffers

  25. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • tid Thread ID • Each user can have one or more threads, the TID has a many-to-one relationship to the user's Session ID

  26. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • lgrs LoG Records The number of log records created by this thread • lsus Log Space Used The number of bytes of logical log space used by this thread • lsmx Log Space Maximum • The space used by the thread's largest transaction • Finding users who are on the verge of creating a long transaction

  27. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • isct ISam CommiT The number of commits • isrb ISam RollBack The number of times this thread has rolled back a transaction • lx Long Transactions The number of long transactions perpetrated by this thread

  28. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • isrd ISam Reads Number of times a row has been read by this thread • Iswr ISam WRites Number of calls to insert a row by this thread • Isrw ISam ReWrites Number of calls to update a row by this thread • Isdl ISam DeLetes Number of calls to delete a row by this thread

  29. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • lkreqs LocK REQuestS Number of times a lock has been acquired by this thread • lkwts LocK WaiTS Number of times a user had to wait when acquiring a lock • dlks DeadLocKS Number of Deadlock errors encountered by this user • to lock TimeOuts Number of times a user waited on a lock, but failed to acquire the lock in the time allotted

  30. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • Monitor the ratio of all SQL like actions over buffer actions. This gives an indication of how much work is done for each row processed • For OLTP users this ratio will be low. For DW users this ratio should be higher. • A high ratio can mean indexing or optimizer issues (isrd + iswr + isrw + isdl) / (bfr + bfw)

  31. Monitoring the Movement of Dataonstat –g stq • Always taken from the perspective of the server • Shows if the archive client or archive server is running faster. Stream Queue: (session 11 cnt 10) 0:ad91400 1:ada1400 2:adb1400 3:adc1400 4:add1400 5:ade1400 6:adf1400 7:ae01400 8:ae11400 9:ae21400 Full Queue: (cnt 9 waiters 0) 0:0 1:ada1400 2:adb1400 3:adc1400 4:add1400 5:ade1400 6:adf1400 7:ae01400 8:ae11400 Empty Queue: (cnt 0 waiters 1)

  32. Questions

  33. Session H13 TitleUnderstanding New and Sparsely Documented onstats John F. Miller III IBM miller3@us.ibm.com

More Related