1 / 112

IBM Inform ix Dynamic Server 监控

IBM Inform ix Dynamic Server 监控. Software Group, IBM ShangHai 陆川 2009/11/05. 监控的重要性：. 1. 了解系统运行状况 2. 报警 3. 协助诊断问题 处理运行故障 调整性能. 影响系统性能的 5 大要素. 磁盘 CPU 内存 网络 应用. 监控工具介绍. onstat SMI oncheck OpenAdminTool ( 仅限于 11 版本使用 ) 复制的监控. onstat –d 监控 chunk 的剩余空间.

afram
Télécharger la présentation

IBM Inform ix Dynamic Server 监控

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. IBMInformix Dynamic Server 监控 Software Group, IBM ShangHai 陆川 2009/11/05

  2. 监控的重要性： 1.了解系统运行状况 2.报警 3.协助诊断问题 • 处理运行故障 • 调整性能

  3. 影响系统性能的5大要素 • 磁盘 • CPU • 内存 • 网络 • 应用

  4. 监控工具介绍 • onstat • SMI • oncheck • OpenAdminTool (仅限于11版本使用) • 复制的监控

  5. onstat –d 监控chunk的剩余空间 address chunk/dbs offset size free bpages flags pathname c0000001ce473028 1 1 0 200000 168435 PO-B /dev/dbs/rootdbs c0000001ce473df0 2 2 64 200000 99947 PO-B /dev/dbs/phylogdbs c0000001d0028028 3 3 64 1000000 49947 PO-B /dev/dbs/logdbs c0000001d00281c0 4 4 64 500000 494397 PO-B /dev/dbs/tmpdbs1 c0000001d0028358 5 5 64 500000 493597 PO-B /dev/dbs/tmpdbs2 c0000001d00284f0 6 6 64 500000 496397 PO-B /dev/dbs/tmpdbs3 c0000001d0028688 7 7 64 4000000 641 PO-B /dev/dbs/userdbs-1 c0000001d0028820 8 7 64 4000000 380 PO-B /dev/dbs/userdbs-2 第7个dbspace由两个chunk组成

  6. onstat –r 2每隔2描重复执行当前的onstat命令 onstat –d –r 2 address chunk/dbs offset size free bpages flags pathname c0000001ce473028 1 1 0 200000 168435 PO-B /dev/dbs/rootdbs c0000001ce473df0 2 2 64 200000 99947 PO-B /dev/dbs/phylogdbs c0000001d0028028 3 3 64 1000000 49947 PO-B /dev/dbs/logdbs c0000001d00281c0 4 4 64 500000 494397 PO-B /dev/dbs/tmpdbs1 c0000001d0028358 5 5 64 500000 493597 PO-B /dev/dbs/tmpdbs2 c0000001d00284f0 6 6 64 500000 496397 PO-B /dev/dbs/tmpdbs3 c0000001d0028688 7 7 64 4000000 641 PO-B /dev/dbs/userdbs-1 c0000001d0028820 8 7 64 4000000 380 PO-B /dev/dbs/userdbs-2

  7. onstat –D 监控IO读写是否倾斜以及数据库恢复的进度 INFORMIX-OnLine Version 11.50.UC2 -- On-Line -- Up 122 days 20:48:40 -- 72616 s Dbspaces address number flags fchunk nchunks flags owner name c3fa80e8 1 1 1 1 N informix rootdbs c3fa84b0 2 2001 2 1 N T informix tempdbs c3fa8518 3 1 3 1 N informix db1 c3fa8580 4 1 4 1 N informix db2 4 active, 2047 maximum Chunks address chk/dbs offset page Rd page Wr pathname c3fa8150 1 1 0 1259 289 /home/informix/ROOTDBS c3fa8228 2 2 0 11 11 /home/informix/TEMPDBS c3fa8300 3 3 0 6740492 3758910 /home/informix/db1 c3fa83d8 4 4 0 6 0 /home/informix/db2 4 active, 2047 maximum

  8. onstat –F 获得清页线索的信息 INFORMIX-OnLine Version 11.50.UC2 -- On-Line -- Up 122 days 20:51:45 -- 72616 s Fg Writes LRU Writes Chunk Writes 0 103854321 31153092 address flusher state data c0000001cef5d860 0 I 0 = 0X0 c0000001cef5e098 1 I 0 = 0X0 c0000001cef5e8d0 2 I 0 = 0X0 c0000001cef5f108 3 I 0 = 0X0 states: Exit Idle Chunk Lru state: I 空闲 C 正在执行checkpoint L 正在执行 LRU Writes data : 显示正在写的chunk编号 共有4个清页线索

  9. onstat –l 监控物理日志和逻辑日志 INFORMIX-OnLine Version 11.50.UC2 -- On-Line -- Up 122 days 20:56:26 -- 72616 s Physical Logging Buffer bufused bufsize numpages numwrits pages/io P-2 0 16 274 22 12.45 phybegin physize phypos phyused %used 10003f 500 433 0 0.74 Logical Logging Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io L-2 0 16 2437 113 31 21.6 3.6 address number flags uniqid begin size used %used c3ecc55c 1 U-B---- 7 100233 250 250 100.00 c3ecc578 2 U---C-L 8 10032d 250 106 42.40 c3ecc594 3 F------ 0 100427 250 0 0.00 c3ecc5b0 4 F------ 0 100521 250 0 0.00 c3ecc5cc 5 U-B---- 5 10061b 250 250 100.00 c3ecc5e8 6 U-B---- 6 100715 250 250 100.00 物理日志当前使用的百分比 以页面为单位 使用百分比 已备份 当前日志

  10. onstat –m 察看数据库日志的最后20行，checkpoint时间和间隔 Mon Nov 4 11:23:51 1996 11:23:51 Logical Log 7 Complete. 11:27:10 Checkpoint Completed: duration was 0 seconds. Tue Dec 31 11:16:01 1996 11:16:01 Checkpoint Completed: duration was 1 seconds. 11:21:00 Checkpoint Completed: duration was 0 seconds. 11:26:01 Checkpoint Completed: duration was 0 seconds. 11:36:01 Checkpoint Completed: duration was 0 seconds. 两个时间相减，可以 得到checkpoint间隔 从Informix 1版本开始，checkpoint不在阻塞交易。

  11. onstat –p 监控数据库实例的轮廓信息 INFORMIX-OnLine Version 11.50.UC2 -- On-Line -- Up 122 days 20:58:17 -- 72616 s Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1535 1548 13734 88.82 484 818 3280 85.24 isamtot open start read write rewrite delete commit rollbk 10135 1520 1284 3057 875 6 7 20 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 39.04 28.34 10 70772 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 241 1 11196 0 0 0 174 101 ixda-RA idx-RA da-RA RA-pgsused lchwaits 31 0 329 360 40 select insert update delete read write rewrite delete

  12. onstat –R 监控每个lrus队列的写的情况 8 buffer LRU queue pairs priority levels # f/m pair total % of length LOW HIGH 0 f 1250 100.0% 1250 1250 0 1 m 3.0% 0 0 0 2 f 1250 100.0% 1250 1250 0 3 m 3.0% 0 0 0 4 F 1250 99.9% 1249 1249 0 5 m 3.1% 1 1 0 6 f 1250 100.0% 1250 1250 0 7 m 3.0% 0 0 0 8 f 1250 100.0% 1250 1250 0 9 m 3.0% 0 0 0 10 f 1250 100.0% 1250 1250 0 11 m 3.0% 0 0 0 12 f 1250 100.0% 1250 1250 0 13 m 3.0% 0 0 0 14 f 1250 100.0% 1250 1250 0 15 m 3.0% 0 0 0 1 dirty, 10000 queued, 10000 total, 16384 hash buckets, 4096 buffer size start clean at 60.000% (of pair total) dirty, or 750 buffs dirty, stop at 50.000% 在Informix 11版本之前，可以协助你配置lru_max_dirty和lru_min_dirty的值

  13. onstat –g ppf 察看全表扫描的次数较多的表的partnum Partition profiles partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc 12 0 0 0 0 733836 0 0 0 538183 0 0 15 0 0 0 0 253950 0 0 0 605030 0 0 20 0 0 0 0 1039 0 0 0 122354 0 0 22 0 0 0 0 852962 0 0 0 0 0 0 23 0 0 0 0 172 0 0 0 0 0 0 24 0 0 0 0 6 0 0 0 0 0 0 25 0 0 0 0 36 0 0 0 0 0 0 26 0 0 0 0 115627 0 0 0 24 24 0 28 0 0 0 0 18 0 0 0 0 0 0 40 0 0 0 0 136669 0 0 0 40 24 0 700098 0 0 0 0 1035 0 0 0 0 0 348

  14. onstat –g seg监控virtual segment的个数 SHMVIRTSIZE Segment Summary: id key addr size ovhd class blkused blkfree 1382041601 1382041601 c000000 9568256 215632 R 2299 37 1382041602 1382041602 c920000 8388608 896 V 1433 615 1382041603 1382041603 d120000 8388608 896 V 3 2045 1382041604 1382041604 d920000 8192000 896 V 1 1999 1382041605 1382041605 e0f0000 8192000 896 V 1 1999 1382041606 1382041606 e8c0000 8192000 896 V 1 1999 Total: - - 50921472 - - 3738 8694 SHMADD

  15. onstat –g rea 监控哪种类型vp个数不够 IBM Informix Dynamic Server Version 9.40.FC4 -- On-Line -- Up 03:29:23 -- 49728 Kbytes Ready threads: tid tcb rstcb prty status vp-class name 36 c0000001cfe7c1f0 0 4 sleeping forever 1cpu sqlexec 45 c0000001cff39330 0 4 sleeping forever 3cpu sqlexec

  16. onstat –g sql 监控数据库当前执行的SQL Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 91463230 SELECT userdb DR Wait 10 0 0 9.03 Off 91463220 SELECT userdb CR Wait 10 0 0 9.03 Off 91463169 SELECT userdb DR Wait 10 0 0 9.03 Off 91463156 SELECT userdb DR Wait 10 0 0 9.03 Off 91463153 SELECT userdb DR Wait 10 0 0 9.03 Off 91463152 SELECT userdb DR Wait 10 0 0 9.03 Off 91463125 SELECT userdb DR Wait 10 0 0 9.03 Off 91463108 SELECT userdb DR Wait 10 0 0 9.03 Off 91463078 SELECT userdb DR Wait 10 0 0 9.03 Off 91463071 SELECT userdb DR Wait 10 0 0 9.03 Off 91463070 SELECT userdb DR Wait 10 0 0 9.03 Off 91463069 SELECT userdb DR Wait 10 0 0 9.03 Off 91463065 SELECT userdb DR Wait 10 0 0 9.03 Off 91463064 SELECT userdb DR Wait 10 0 0 9.03 Off 91462455 SELECT userdb DR Wait 10 0 0 9.03 Off

  17. onstat –g ses 监控用户连接和每个用户申请的内存 session #RSAM total used dynamic id user tty pid hostname threads memory memory explain 91463257 informix - 0 - 0 12288 11360 off 91463230 vbs4 3 25744 rp7420_3 1 200704 196080 off 91463220 vbs4 tf 2600 rp7420_3 1 126976 118696 off 91463169 vbs4 3 25742 rp7420_3 1 196608 195728 off 91463156 vbs4 3 25738 rp7420_3 1 196608 195728 off 6241034 cics - 27187 rp7420_3 1 8306688 6902968 off 2452249 webuser - -1 38.19.8. 1 102400 73288 off 2451594 webuser - -1 38.19.8. 1 102400 70984 off 175 filesvr - 8236 rp7420_3 1 69632 52688 off

  18. onstat –g sql #session_id# /onstat –g ses #session_id#监控某个用户连接执行的SQL IBM Informix Dynamic Server Version 9.40.FC3 -- On-Line -- Up 20:25:10 -- 65600 Kbytes Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 211 SELECT stores7 CR Not Wait 0 0 9.03 Off Current statement name : slctcur Current SQL statement : select * from orders Last parsed SQL statement : select * from orders

  19. onstat –g ath 监控数据库线索 Threads: tid tcb rstcb prty status vp-class name 3 c0000001cfa83630 0 2 sleeping forever 33pio pio vp 0 4 c0000001cfaa4630 0 2 sleeping forever 34aio aio vp 0 5 c0000001cfac5630 0 2 sleeping forever 35msc msc vp 0 6 c0000001cfaf6630 0 2 sleeping forever 36aio aio vp 1 7 c0000001cfb17630 0 2 sleeping forever 37aio aio vp 2 8 c0000001cfb38630 0 2 sleeping forever 38aio aio vp 3 9 c0000001cfb59630 0 2 sleeping forever 39aio aio vp 4 10 c0000001cfb7a630 0 2 sleeping forever 40aio aio vp 5 11 c0000001cfb9b890 c0000001cef5d028 4 sleeping secs: 1 5cpu main_loop() 12 c0000001cfac5ce0 0 2 sleeping forever 1cpu sm_poll 13 c0000001cf9a0760 0 2 running 3cpu sm_poll 14 c0000001cf9c46c8 0 2 sleeping forever 4cpu sm_poll 15 c0000001cf9e9630 0 2 running 5cpu sm_poll 16 c0000001cfa06dc8 0 2 sleeping forever 6cpu sm_poll 17 c0000001cfa31c98 0 2 IO idle 7cpu sqlexec 18 c0000001cfa55c10 0 2 IO waiting 41soc sqlexec

  20. onstat –g sch 监控informix进程信息 MT global info: sessions threads vps lngspins 64 97 15 6642 sched calls thread switches yield 0 yield n yield forever total: 1142174954 421309247 1112260992 52108 4260081 per sec: 686165 184933 681261 6 2545 Virtual processor summary: class vps usercpu syscpu total cpu 7 8794.93 162.63 8957.56 aio 4 0.01 0.05 0.06 lio 1 0.00 0.01 0.01 pio 1 0.00 0.01 0.01 adm 1 0.08 0.07 0.15 msc 1 0.32 0.36 0.68 total 15 8795.34 163.13 8958.47

  21. onstat –g sch (continue) Individual virtual processors: vp pid class usercpu syscpu total 1 340366 cpu 1781.23 32.50 1813.73 2 188608 adm 0.08 0.07 0.15 3 397352 cpu 1250.20 42.36 1292.56 4 192554 cpu 1357.92 20.49 1378.41 5 413984 cpu 1103.27 16.95 1120.22 6 352738 cpu 1109.89 17.19 1127.08 7 438398 cpu 1083.12 16.49 1099.61 8 434616 cpu 1109.30 16.65 1125.95 9 479720 lio 0.00 0.01 0.01 10 446548 pio 0.00 0.01 0.01 11 315866 aio 0.01 0.02 0.03 12 282730 msc 0.32 0.36 0.68 13 151896 aio 0.00 0.01 0.01 14 192836 aio 0.00 0.01 0.01 15 434408 aio 0.00 0.01 0.01 tot 8795.34 163.13 8958.47

  22. onstat –u 监控用户线索 Userthreads address flags sessid user tty wait tout locks nreads nwrites c0000001cef632c8 Y--P--D 41 informix - c000000000669138 0 0 0 0 c0000001cef63b00 ---P--D 38 informix - 0 0 0 0 0 c0000001cef64338 Y--P--- 171 cics - c0000001d094d7b0 0 1 338361014 16983197 c0000001cef64b70 Y--P--- 80311498 cics - c0000001dd75f688 0 1 34928980 1134299 c0000001cef653a8 Y--P--- 175 filesvr - c0000001d0cffe48 0 1 557 0 c0000001cef65be0 Y--P--- 136 cics - c0000001d09a3f58 0 1 0 0 c0000001cef66c50 Y--P--- 163 cics - c0000001d0af9028 0 1 329562872 16960921 93 active, 256 total, 201 maximum concurrent

  23. onstat –k 监控锁的使用情况 address wtlist owner lklist type tblsnum rowid key#/bsiz c000000001671840 0 c0000001cef75270 0 S 100002 204 0 c000000002d4c7b8 0 c0000001e4c7abf8 c000000009b3f298 IX 700030 0 0 c000000003283470 0 c0000001e4c5f028 0 S 100002 204 0 c0000000037beef8 0 c0000001cef85970 0 S 100002 204 0 c000000004952688 0 c0000001e4c70798 0 S 100002 204 0 c000000004fa9ec8 0 c0000001cef653a8 0 S 100002 204 0 c000000004fa9fb8 0 c0000001cef75aa8 0 S 100002 204 0 c000000005073c00 0 c0000001cef8d4b8 c00000001cb0fb70 IX 700030 0 0 c000000006ac1c88 0 c0000001cef970e0 c0000000300ceb98 IX 700030 0 0 c0000000079dde88 0 c0000001e4c74120 c0000000297e0190 IX 700030 0 0 c000000008151c78 0 c0000001e4c759c8 0 S 100002 204 0 c0000000088e25c8 0 c0000001cef65be0 0 S 100002 204 0 c000000009b3f298 0 c0000001e4c7abf8 0 S 100002 204 0 c000000009bb9368 0 c0000001e4c75190 0 S 100002 204 0 96 active, 8000000 total, 524288 hash buckets, 0 lock table overflows 锁类型 非零，说明有锁等待 表的partition number

  24. onstat –b 监控修改的内存页信息 address userthread flgs pagenum memaddr nslots pgflgs xflgs owner waitlist c000000045521d58 0 80 31:3909144 c0000000695bf800 3 2801 80 ffffffffffffffff 0 c000000045973bb0 0 80 31:3909112 c00000006cff4000 3 2801 80 ffffffffffffffff 0 c000000045a57068 0 80 31:3909162 c00000006dbea800 3 2801 80 ffffffffffffffff 0 c000000045c9d458 0 80 17:2025672 c00000006fa8f800 3 2801 80 ffffffffffffffff 0 c0000000466ac838 0 80 17:2025666 c000000078219800 3 2801 80 ffffffffffffffff 0 c000000046bef2d0 0 80 25:1878844 c00000007c8fa000 3 801 80 ffffffffffffffff 0 c000000047e21418 0 82 23:597119 c00000008be23800 4 2801 80 ffffffffffffffff c0000001cef98988 c000000047f66390 0 80 25:1874864 c00000008cf3e000 3 801 80 ffffffffffffffff 0 c0000000480dd790 0 80 25:1878795 c00000008e2fe000 3 801 80 ffffffffffffffff 0 c0000000484977b0 0 80 31:3909173 c000000091534000 3 2801 80 ffffffffffffffff 0 222 modified, 3000000 total, 4194304 hash buckets, 2048 buffer size

  25. onstat –t 监控tblspace的信息 n address flgs ucnt tblnum physaddr npages nused npdata nrows nextns 20 c0000001d002daa8 0 1 100001 1:14 400 364 0 0 4 181 c0000001d0119028 0 1 200001 2:4 50 2 0 0 1 182 c0000001d01195a0 0 1 300001 3:4 50 2 0 0 1 183 c0000001d0119b58 0 1 400001 4:4 6800 6800 0 0 51 184 c0000001d011a028 0 1 500001 5:4 6800 6800 0 0 51 185 c0000001d011a5e0 0 1 600001 6:4 6800 6800 0 0 51 186 c0000001d01207d0 0 1 700001 7:4 2800 2800 0 0 34 187 c0000001d007b028 0 80 700002 7:5 112 107 56 855 14 234 c0000001d176c7d8 0 8 700031 7:52 16 16 15 143 2 239 c0000001d1770c20 0 8 700036 7:761 8 8 0 0 2

  26. onstat –x 监控事务，可以用于监控事务回滚 address flags userthread locks beginlg curlog logposit isol retrys coord c0000001e808b4a0 A---- c0000001e4c81f08 1 0 0 0x0 COMMIT 0 c0000001e808b970 A-B-- c0000001e4c738e8 2 47212 47212 0x14ee8048 DIRTY 0 c0000001e808bbd8 A---- c0000001e4c7c4a0 1 0 0 0x0 COMMIT 0 c0000001e808c578 A--c0000001cef73190 1 0 0 0x0 COMMIT 0 c0000001e808c7e0 A---- c0000001cef68d30 2 0 0 0x0 DIRTY 0 c0000001e808ca48 A-B-- c0000001e4c62178 2 47212 47212 0x14e8c048 DIRTY 0 129 active, 384 total, 369 maximum concurrent

  27. onstat –C 监控Btree scan线索的信息 Btree Cleaner Info BT scanner profile Information ============================== Active Threads 1 Global Commands 20000 Building hot list Number of partition scans 153184 Main Block 0xc0000001cfdfd128 BTC Admin 0xc0000001cef62258 BTS info id Prio Partnum Key Cmd 0xc0000001cfdffed8 0 Low 0x00000000 0 40 Yield N Number of leaves pages scanned -209876806 Number of leaves with deleted items 82481260 Time spent cleaning (sec) 2837591 Number of index compresses 5376280 Number of deleted items 718862270 Number of index range scans 0 Number of index leaf scans 150021

  28. onstat –g dic 监控数据字典的信息 Dictionary Cache: Number of lists: 31, Maximum list size: 10 list# size refcnt dirty? heapptr table name ----------------------------------------------------------------------------------------------------------------------------------- 0 10 0 no c0000001e5765038 sysmaster@hcbserver:informix.sysptprof 0 no c0000001e26f9838 sysmaster@hcbserver:informix.sysdbstab 22 no c0000001d25ce838 userdb@hcbserver:vbs4.glscycltotal 31 no c0000001da538838 userdb@hcbserver:vbs4.putout_message 1 yes c0000001ddbaf038 userdb@hcbserver:vbs4.putout_message 1 yes c0000001d2306838 userdb@hcbserver:vbs4.cmsholdm 1 yes c0000001eae68838 userdb@hcbserver:vbs4.putout_message 2 yes c0000001e8f00838 userdb@hcbserver:vbs4.cmsholdm 1 yes c0000001ea1ec838 userdb@hcbserver:vbs4.cmsholdm 1 yes c0000001dbe79038 userdb@hcbserver:vbs4.putout_message

  29. onstat –g dsc 监控表中列的数据分布的信息 Distribution Cache: Number of lists : 31 DS_POOLSIZE : 127 Distribution Cache Entries: list# id ref_cnt dropped? heap_ptr distribution name ---------------------------------------------------------------------------------------------------------------------------------- 3 0 0 0 c0000001d5a28038 userdb@hcbserver:vbs4.pubcycdealcall.calldate 4 0 0 0 c0000001d3250838 userdb@hcbserver:vbs4.pubcycbrccallreg.brc 6 0 0 0 c0000001d37f8c38 userdb@hcbserver:vbs4.pubcycbrccallreg.callstat 7 0 0 0 c0000001d3250438 userdb@hcbserver:vbs4.pubcycbrccallreg.batchno

  30. onstat –g prc 监控用户定义routine的信息 UDR Cache: Number of lists : 31 PC_POOLSIZE : 127 UDR Cache Entries: list# id ref_cnt dropped? heap_ptr udr name -------------------------------------------------------------- 0 1467 0 0 c0000001d2ecd438 userdb@hcbserver:.inssubtranslog 0 1257 0 0 c0000001d2241838 userdb@hcbserver:.dpsbigamttrans_insert 0 1519 0 0 c0000001d343e438 userdb@hcbserver:.lnsacctdyninfo_update 1 1252 0 0 c0000001d4706838 userdb@hcbserver:.dpsacclnkreg_delete

  31. onstat –g pos 监控informix相关的环境信息 1 7 0 infos ver/size 2 272 2 1 0 snum 0 52564801 0 hcbserver 3 4 0 onconfig path /informix/etc/onconfig.hcb 4 5 0 host sdome-1 5 6 0 oninit ver IBM Informix Dynamic Server Version 11.50.FC5 6 6 8 0 sqlhosts path /informix/etc/sqlhosts

  32. onstat –g ioa 监控IO对列和chunk的读写情况 AIO global info: 6 aio classes 55 open files 64 max global files AIO I/O queues: q name/id len maxlen totalops dskread dskwrite dskcopy kio 0 0 236 1293043824 1214749492 78294332 0 kio 1 0 481 2380980578 2276414912 104565666 0 kio 2 0 609 2045476140 1965648808 79827332 0 kio 3 0 481 1991822503 1895287615 96534888 0 kio 4 0 480 1679929856 1588936761 90993095 0 kio 5 0 147 1452406514 1365478601 86927913 0 kio 6 0 474 906868572 863069638 43798934 0

  33. onstat –g ioa (continue) AIO I/O vps: class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors kio 0 s 81.4 470968790 398738691 72230099 0 716722633 0.7 0 kio 1 i 267.1 1544737196 1449517115 95220081 0 2784672407 0.6 0 kio 2 s 183.1 1059085669 983459368 75626301 0 1783627036 0.6 0 kio 3 s 198.1 1145478835 1056564463 88914372 0 1998799317 0.6 0 kio 4 i 142.3 823275247 739055143 84220104 0 1373892196 0.6 0 kio 5 i 105.8 611934071 531708739 80225332 0 975037600 0.6 0 kio 6 s 51.5 297584863 257965729 39619134 0 438395613 0.7 0

  34. onstat –g ioa (continue) AIO global files: gfd pathname totalops dskread dskwrite io/s 3 /dev/dbs/rootdbs 1334981 814062 520919 0.2 4 phylogdbs 9387514 1500 9386014 1.6 5 /dev/dbs/logdbs 13167118 3431695 9735423 2.3 6 /dev/dbs/tmpdbs1 36653365 11795428 24857937 6.3 7 /dev/dbs/tmpdbs2 37799245 12638544 25160701 6.5 8 /dev/dbs/tmpdbs3 34376857 9445985 24930872 5.9 9 userdbs-1 284466220 256566488 27899732 49.2 10 userdbs-2 363701648 353593295 10108353 62.9

  35. onstat –g nta 监控数据库的连接信息 Individual thread network information (basic): netscb type thread name sid c0000001dff148a8 soctcp sqlexec 91463230 c0000001d0473c88 soctcp sqlexec 91463220 c0000001d6816cb8 soctcp sqlexec 91463169 c0000001d6910a70 soctcp sqlexec 91463156 c0000001cfdc74f0 soctcp sqlexec 91463153 c0000001dff10428 soctcp sqlexec 91463152 c0000001d83eccb0 soctcp sqlexec 91463125 c0000001d0b6f028 soctcp sqlexec 91463108 c0000001cfc2ab50 ipcshm sm_poll 7 c0000001cfc13b50 ipcshm sm_poll 6 c0000001cfbfcae8 ipcshm sm_poll 5

  36. onstat –g nta监控数据库的连接时间 Individual thread network information (times): netscb thread name sid open read write address c0000001dff148a8 sqlexec 91463230 02:47:35 02:47:35 02:47:35 c0000001d0473c88 sqlexec 91463220 02:47:34 02:47:38 02:47:38 c0000001d6816cb8 sqlexec 91463169 02:47:28 02:47:28 02:47:28 c0000001d6910a70 sqlexec 91463156 02:47:27 02:47:27 02:47:27 c0000001cfdc74f0 sqlexec 91463153 02:47:27 02:47:27 02:47:27 c0000001dff10428 sqlexec 91463152 02:47:26 02:47:26 02:47:26 c0000001d83eccb0 sqlexec 91463125 02:47:24 02:47:24 02:47:24 c0000001d0b6f028 sqlexec 91463108 02:47:22 02:47:22 02:47:22

  37. onstat –g nta监控连接成功和失败的信息 Client Type Calls Accepted Rejected Read Write sqlexec yes 91459710 15 -721091574 -607189481 srvinfx yes 0 0 0 0 onspace yes 0 0 0 0 onlog yes 0 0 0 0 onparam yes 0 0 0 0 oncheck yes 10 0 71744 264616 onload yes 0 0 0 0 onunload yes 0 0 0 0 onmonitor yes 1 0 60 61 dr_accept yes 0 0 0 0 cdraccept no 0 0 0 0 ontape yes 3444 0 282315963 282319410 srvstat yes 0 0 0 0 asfecho es 0 0 0 0 listener yes 0 0 91463180 15 crsamexec yes 0 0 0 0 safe yes 0 0 0 0 onutil yes 0 0 0 0 客户端类型 接受和拒绝的次数

  38. onstat –g opn 监控被打开的partition的信息 tid rstcb isfd op_mode op_flags partnum ucount ocount lockmode 218 0xcef64338 0 0x00000400 0x00000397 0x00700002 2 2 0 218 0xcef64338 1 0x00000002 0x00000003 0x00700002 2 2 0 218 0xcef64338 4 0x00000400 0x00000407 0x007002ca 1 0 0 218 0xcef64338 5 0x00000440 0x00000713 0x00700138 1 0 0 218 0xcef64338 6 0x00000400 0x00000407 0x00700135 1 0 0 218 0xcef64338 7 0x00000408 0x00000413 0x008018c2 1 0 0

  39. onstat –g dri 监控HDR的状态 Data Replication: Type State Paired server Last DR CKPT (id/pg) primary off amit_secondary -1 / -1 DRINTERVAL 2 DRTIMEOUT 30 DRAUTO 0 DRLOSTFOUND /vobs/tristarm/sqldist/etc/dr.lostfound DRIDXAUTO 0

  40. onstat –g tpf 监控thread的轮廓信息 tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 0 10 0 0 0 95564 3 0 0 0 0 0 0 3124077 2592376 1353340088 0 0

  41. onstat –g arc 最近的备份信息 Dbspaces - Archive Status name number level date log log-position rootdbs 1 0 02/17/2009.03:46 47179 0x87dc018 phylogdbs 2 0 02/17/2009.03:48 47179 0x8824018 logdbs 3 0 02/17/2009.03:48 47179 0x8825018 userdbs 7 0 02/17/2009.03:48 47179 0x8825018

  42. onstat –g dis 监控机器上有几个实例以及启动状态 There are 2 servers found Server : hcbserver Server Number : 0 Server Type : IDS Server Status : Up Server Version : IBM Informix Dynamic Server Version 9.40.FC5 Shared Memory : 0 INFORMIXDIR : /informix ONCONFIG : /informix/etc/onconfig.hcb SQLHOSTS : /informix/etc/sqlhosts Host : sdome-1Server Server : frntserver Server Number : 1

  43. SQL Query Drill Down – onstat –g his 需要配置：SQLTRACE level=LOW,ntraces=2000,size=2,mode=global Database: db3 Statement text: insert into t1 select 0, tabname from systables,sysindexes where systables.tabid < 100 INSERT using table [ t1 ] Iterator/Explain ================ ID Left Right Est Cost Est Rows Num Rows Type 3 0 0 5 15 55 Disk Scan 4 0 0 18 92 92 Disk Scan 2 3 4 287 1380 5060 Nested Join 1 2 0 1 1 5060 Insert Statement information: Sess_id User_id Stmt Type Completion Time Run Time 16 200 INSERT 09:15:23 13.0619 Statement Statistics: Page Buffer Read Buffer Page Buffer Write Read Read % Cache IDX Read Write Write % Cache 1212 21340 94.32 0 1312 16448 92.02 Lock Lock Lock Log Num Disk Sort Requests Waits Time (S) Records Sort Sort Time (S) 5061 0 0 1265 0 0 0 Total Total Avg Max LK Wait I/O Wait Avg Rows Executions Time (S) Time (S) Time (S) Time (S) Time (S) Per Sec 1 13.0619 13.0619 13.0619 0.0000 3.8790 387.3854 Estimated Estimated Actual SQL ISAM Isolation SQL Cost Rows Rows Error Error Level Memory 288 1381 5060 0 0 NL 127696

  44. onstat –g ckp Auto Checkpoins=On RTO_SERVER_RESTART=60 seconds Estimated recovery time 7 seconds Critical Sections Physical Log Logical Log Clock Total Flush Block # Ckpt Wait Long # Dirty Dskflu Total Avg Total Avg Interval Time Trigger LSN Time Time Time Waits Time Time Time Buffers /Sec pages /Sec Pages /Sec 1 18:41:36 Startup 1:f8 0.0 0.0 0.0 0 0.0 0.0 0.0 4 4 3 0 1 0 2 18:41:49 Admin 1:11c12cc 0.3 0.2 0.0 1 0,0 0.0 0.0 2884 2884 1966 162 4549 379 3 18:42:21 Llog 8:188 2.3 2.0 2.0 1 0.0 2.0 2.0 14438 7388 318 10 65442 2181 4 18:42:44 *User 10:19c018 0.0 0.0 0.0 1 0.0 0.0 0.0 39 39 536 21 20412 816 5 18:46:21 RTO 12:188 54.8 54.2 0.0 30 0.6 0.4 0.6 68232 1259 210757 1033 150118 735 Max Plog Max Llog Max Dskflush Avg Dskflush Avg Dirty Blocked pages/sec pages/sec Time pages/sec pages/sec Time 8796 6581 54 43975 2314 0

  45. onstat –g ckp

  46. onstat –g alice Partnum Mode BM_Sz Used_Pg Examined Dirty_Pg # I/O Found Eff 0x00900003 10 17536 4486540 266997444 71606 2086125 17200 0.8 %

  47. View what has been Cleaned onstat –C clean Btree Cleaner Info Index Cleaned Statistics ========================= Partnum Key Dirty Hits Clean Time Pg Examined Items Del Pages/Sec 0x00100002 1 0 0 2 2 2.00 0x00100003 1 0 0 6 0 6.00 0x00100003 2 0 0 2 0 2.00 0x00100004 1 0 0 22 955 22.00 0x00100004 2 0 0 9 439 9.00 0x00100005 1 0 0 1 0 1.00 0x00200003 1 6 91 8096 14501 88.97 0x00300003 1 C 61 130 12108 11829 93.14 0x00300004 1 28787 0 0 0 0.00 Total time spent cleaning this index Index currently being cleaned Index items removed from this index

  48. Operation by Index onstat –C part Btree Cleaner Info Index Statistics ================ Partnum Key Positions Compress Split 0x00100002 1 2429 0 0 0x00100003 1 4 0 0 0x00100114 1 36 0 0 0x00100114 2 22 0 0 0x00100114 3 22 0 0 0x00100118 1 14353 1 922 0x00100119 1 C 0 2198 17750 0x0010011a 1 310 2 1367 0x0010011b 1 0 1 1224 0x0010011b 1 0 1 1224 # of splits on the index # of times an index has been used for reading

  49. onstat –g cpu IBM Informix Dynamic Server Version 11.10.F -- On-Line -- Up 7 days 20:09:14 -- 54744 Kbytes Thread CPU Info: tid name vp Last Run CPU Time #scheds status *2 lio vp 0 3lio 06/27 13:26:39 28.6397 3749 IO Idle *3 pio vp 0 4pio 06/27 13:25:09 5.0609 517 IO Idle *4 aio vp 0 5aio 06/27 13:29:23 31.1610 112645 IO Idle *5 msc vp 0 6msc 06/27 13:27:57 0.1137 50 IO Idle *6 aio vp 1 7aio 06/27 13:29:23 19.1152 5524 IO Idle 7 main_loop() 1cpu 06/27 13:31:55 7.1407 678090 sleeping secs: 1 *8 sm_poll 1cpu 06/27 13:31:55 677245.0333 940398 running 9 sm_listen 1cpu 06/27 13:27:57 0.0057 32 sleeping forever 10 sm_discon 1cpu 06/27 13:31:55 2.5516 676641 sleeping secs: 1 11 flush_sub(0) 1cpu 06/27 13:31:55 1.7716 677707 sleeping secs: 1 *12 aio vp 2 8aio 06/27 13:29:23 21.7697 727 IO Idle *13 aio vp 3 9aio 06/27 13:25:09 23.7650 677 IO Idle *14 aio vp 4 10aio 06/27 13:25:09 18.0777 1118 IO Idle *15 aio vp 5 11aio 06/27 13:25:09 17.0063 350 IO Idle 16 aslogflush 1cpu 06/27 13:31:55 2.0833 676638 sleeping secs: 1 17 btscanner_0 1cpu 06/27 13:31:35 1.7299 22352 sleeping secs: 31 *18 onmode_mon 1cpu 06/27 13:31:55 2.9390 676641 sleeping secs: 1 *40 dbScheduler 1cpu 06/27 13:29:23 1.5202 3444 sleeping secs: 148 *41 dbWorker1 1cpu 06/27 13:24:22 0.9907 2655 sleeping forever *42 dbWorker2 1cpu 06/27 13:24:22 1.0513 2908 sleeping forever 43 bf_priosweep() 1cpu 06/27 13:30:10 0.4217 2255 cond wait bp_cond 45 sqlexec 1cpu 06/20 14:46:53 0.0561 322 cond wait sm_read 46 sqlexec 1cpu 06/20 01:45:59 6.9784 32301 cond wait sm_read

More Related