java监控mysql_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

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-log

Uptime 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 Statistics

Actions:

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 query

Other:

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-filters

Press 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-log

CXN When Load QPS Slow QCacheHit KCacheHit BpsIn BpsOut

my120 Now 0.00 774.20 0 40.22% 100.00% 207.98k 1.46M

my120 Total 0.00 212.69 2 29.70% 100.00% 56.90k 402.15k

CXN 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-log

EXPLAIN PARTITIONS

select 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-log

select 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

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-log

select 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

Note:

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)