参考官网:http://innotop.googlecode.com/svn/html/manual.html
使用方法:
# innotop -uroot -proot -h127.0.0.1
[root@racdb2 innotop-1.9.0]# innotop -uroot -proot -h127.0.0.1
[RO] Dashboard (? for help) 127.0.0.1, 3h, 0.06 QPS, 2/1/0 con/run/cac thds, 5.1.67-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
可以用innotop监控mysql,例如可以看当前正在运行的sql,并查看执行计划,如下:
进入innotop时,按shift+q进入sql查询列表,
When Load Cxns QPS Slow Se/In/Up/De% QCacheHit KCacheHit BpsIn BpsOut
Now 0.10 2 0.46 0 0/ 0/ 0/ 0 0.00% 100.00% 19.79 1.11k
Total 0.00 151 0.08 5 2/ 0/ 0/ 0 4.17% 100.00% 3.29 223.67
Cmd ID State User Host DB Time Query
Query 1 Sending data root localhost test 03:20 insert into user select * from user
Query 21 Locked root localhost test 00:04 insert into user select * from user
Select a thread to analyze: 21
然后按e并输入thread ID显示执行计划或者按f显示完整sql语句,或者按o显示系统优化过的语句(需要MySQL的版本支持EXPLAIN EXTENDED)
EXPLAIN PARTITIONS
select * from user
____ Sub-Part 1 _____
Select Type: SIMPLE
Table: user
Partitions:
Type: ALL
Poss. Keys:
Index:
Key Length:
Index Ref:
Row Count: 41943040
Special:
[This query has been re-written to be explainable]
Press e to explain, f for full query, o for optimized query
innotop是通过information_schema.processlist来获得完整的sql语句,并且根据COMMAND来过滤掉空闲线程的
---end---