mysql監控管理工具–innotop

標籤：

INNOTOP是一個通過文字模式顯示MySQL和InnoDB的監測工具。INNOTOP是用PERL語言寫成的，這使它能更加靈活的使用在各種操作平台之上，它能詳細的的監控出當前MYSQL和INNODB啟動並執行狀態，以DBA根據結果，可以合理的最佳化MYSQL，讓MYSQL更穩定更高效的運行。
 
1.innotop安裝
安裝INNOTOP工具非常簡單，其是由PERL寫的，當然需要PERL環境和相關的工具包。在安裝之前先要確定你的系統安裝了Time::HiRes,Term::ReadKey,DBI,DBD::mysql這四個包

1	cpanm --mirror http://mirrors.sohu.com/CPAN/ --mirror-only DBI DBD::mysql Term::ReadKey Time::HiRes3

參考官網：http://innotop.googlecode.com/svn/html/installing.html

1	wget http://innotop.googlecode.com/files/innotop-1.9.0.tar.gz

2	tar -zxf innotop-1.9.0.tar.gz

3	cd innotop-1.9.0

4	perl Makefile.PL

5	make install

6	#這樣就安裝完innotop，系統多出一個命令innotop

 
2.使用方法
參考官網：http://innotop.googlecode.com/svn/html/manual.html
使用方法：
innotop -uroot -p123456

[RO] Dashboard (? for help)                            127.0.0.1, 3h, 0.06 QPS, 2/1/0 con/run/cac thds, 5.5.30-logUptime  MaxSQL  ReplLag  Cxns  Lock  QPS   QPS  Run  Run  Tbls  Repl  SQL    3h                      2     0  0.06                   13  Off    輸入“？”得到協助Switch to a different mode:   A  Dashboard         I  InnoDB I/O Info     Q  Query List   B  InnoDB Buffers    K  InnoDB Lock Waits   R  InnoDB Row Ops   C  Command Summary   L  Locks               S  Variables & Status   D  InnoDB Deadlocks  M  Replication Status  T  InnoDB Txns   F  InnoDB FK Err     O  Open Tables         U  User StatisticsActions:   d  Change refresh interval        p  Pause innotop   k  Kill a query‘s connection      q  Quit innotop   n  Switch to the next connection  x  Kill a queryOther: TAB  Switch to the next server group   /  Quickly filter what you see   !  Show license and warranty         =  Toggle aggregation   #  Select/create server groups       @  Select/create server connections   $  Edit configuration settings       \  Clear quick-filtersPress any key to continue

 
3.MySQL如何擷取當前執行的SQL
用innotop的Q模式則可以完美的解決擷取當前啟動並執行SQL的問題。innotop -m Q 或者innotop進入後再按shift+q進入Query list模式：

Query List (? for help) mysql01, 75+03:16:16, 774.20 QPS, 83 thd, 5.1.24-rc-logCXN    When   Load  QPS     Slow  QCacheHit  KCacheHit  BpsIn    BpsOut my120  Now    0.00  774.20     0     40.22%    100.00%  207.98k    1.46Mmy120  Total  0.00  212.69     2     29.70%    100.00%   56.90k  402.15kCXN     Cmd    ID      User    Host           DB      Time   Query                                                                  mysql01   Query   20936  poster  192.168.1.1    poster  00:00  select a.poster_id, a.pic_id, a.gmt_create, a.gmt_modified, a.url, a.no

然後按e並輸入thread ID顯示執行計畫或者按f顯示完整sql語句，或者按o顯示系統最佳化過的語句（需要MySQL的版本支援EXPLAIN EXTENDED）。個人感覺，還是e最有用，其他兩個選項，則有點雞肋了。

Query List (? for help)  mysql01, 75+03:16:16, 774.20 QPS, 83 thd, 5.1.24-rc-logEXPLAIN PARTITIONSselect a.poster_id, a.pic_id, a.gmt_create, a.gmt_modified, a.url, a.notes, a.type, a.indexed, a.user_id, a.user_nick, b.pic_path from poster.poster_pic a inner join poster.picture b on (a.pic_id = b.id) where a.poster_id = 3390 order by a.indexed______________ Sub-Part 1 ______________  ________ Sub-Part 1 ________Select Type: SIMPLE                       Select Type: SIMPLE               Table: a                                  Table: b               Partitions:                               Partitions:                       Type: ref                                 Type: eq_ref          Poss. Keys: PRIMARY                       Poss. Keys: PRIMARY              Index: PRIMARY                            Index: PRIMARY         Key Length: 4                             Key Length: 8                Index Ref: const                          Index Ref: poster.a.PIC_ID  Row Count: 14                             Row Count: 1                  Special: Using where; Using filesort      Special:                Press e to explain, f for full query, o for optimized query

 

Query List (? for help)  mysql01, 75+03:16:16, 774.20 QPS, 83 thd, 5.1.24-rc-logselect a.poster_id, a.pic_id, a.gmt_create, a.gmt_modified, a.url, a.notes, a.type, a.indexed, a.user_id, a.user_nick, b.pic_path from poster.poster_pic a inner join poster.picture b on (a.pic_id = b.id) where a.poster_id = 3390 order by a.indexedPress e to explain, f for full query, o for optimized query

 

Query List (? for help)  mysql01, 75+03:16:16, 774.20 QPS, 83 thd, 5.1.24-rc-logselect a.poster_id, a.pic_id, a.gmt_create, a.gmt_modified, a.url, a.notes, a.type, a.indexed, a.user_id, a.user_nick, b.pic_path from poster.poster_pic a inner join poster.picture b on (a.pic_id = b.id) where a.poster_id = 3390 order by a.indexedNote:select `poster`.`a`.`POSTER_ID` AS `poster_id`,`poster`.`a`.`PIC_ID` AS `pic_id`,`poster`.`a`.`GMT_CREATE` AS `gmt_create`,`poster`.`a`.`GMT_MODIFIED` AS `gmt_modified`,`poster`.`a`.`URL` AS `url`,`poster`.`a`.`NOTES` AS `notes`,`poster`.`a`.`TYPE` AS `type`,`poster`.`a`.`INDEXED` AS `indexed`,`poster`.`a`.`USER_ID` AS `user_id`,`poster`.`a`.`user_nick` AS `user_nick`,`poster`.`b`.`PIC_PATH` AS `pic_path` from `poster`.`poster_pic` `a`  join `poster`.`picture` `b` where ((`poster`.`b`.`ID` = `poster`.`a`.`PIC_ID`) and (`poster`.`a`.`POSTER_ID` = 3390)) order by `poster`.`a`.`INDEXED`Press e to explain, f for full query, o for optimized query

那麼innotop是從哪裡取的資料呢？應該是通過information_schema.processlist來獲得完整的sql語句，並且根據COMMAND來過濾掉空閑線程的。

mysql> desc information_schema.processlist;+---------+-------------+------+-----+---------+-------+| Field   | Type        | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| ID      | bigint(4)   | NO   |     | 0       |       | | USER    | varchar(16) | NO   |     |         |       | | HOST    | varchar(64) | NO   |     |         |       | | DB      | varchar(64) | YES  |     | NULL    |       | | COMMAND | varchar(16) | NO   |     |         |       | | TIME    | bigint(7)   | NO   |     | 0       |       | | STATE   | varchar(64) | YES  |     | NULL    |       | | INFO    | longtext    | YES  |     | NULL    |       | +---------+-------------+------+-----+---------+-------+8 rows in set (0.00 sec)

mysql監控管理工具–innotop