Innotop is a monitoring tool that displays MySQL and InnoDB in text mode. Innotop is written in Perl, which makes it more flexible to use on a variety of operating platforms, it can monitor the current status of MySQL and InnoDB operation, the DBA according to the results, can reasonably optimize MySQL, so that MySQL more stable and more efficient operation.
1.innotop Installation
Installing the Innotop tool is very simple, it is written by Perl, and of course requires the Perl environment and the associated toolkit. Before installing, make sure that your system has the Time::hires,term::readkey,dbi,dbd::mysql four packages installed.
1 |
Cpanm--mirror http://mirrors.sohu.com/CPAN/--mirror-only DBI dbd::mysql term::readkey time::hires3 |
Reference official website: http://innotop.googlecode.com/svn/html/installing.html
6 |
#这样就安装完innotop, the system has an extra command innotop |
2. How to use
Reference official website: http://innotop.googlecode.com/svn/html/manual.html
How to use:
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 maxsq L repllag cxns Lock QPS QPS Run Run tbls Repl SQL 3h 2 0 0.06 13 OFF input "? "Get help switch to a different mode:a Dashboard i InnoDB I/o Info Q Query List B InnoDB buffers K Inn ODB Lock Waits R InnoDB Row Ops C Command Summary L Locks S Variables & Status D InnoDB Dea Dlocks 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 N Ext connection x Kill a queryother:tab Switch to the next server group/quickly filter! Show License and warranty = Toggle Aggregation # select/create Server groups @ select/create Server con Nections $ EDIT configuration settings \ Clear quick-filterspress any key to continue
3.MySQL How to get the currently executing SQL
With the Q-mode of innotop , we can solve the problem of getting the current running SQL perfectly. Innotop-m Q or Innotop Enter and press SHIFT+Q to enter the query list mode:
Query List (MYSQL01), 75+03:16:16, 774.20 QPS, 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
then press e and enter the thread ID to display the execution plan or press F to display the full SQL statement, or press O to display the system-optimized statement (requires MySQL version to support explain EXTENDED). Personal feeling, or e is the most useful, the other two options, it is a little chicken.
Query List (MYSQL01), 75+03:16:16, 774.20 QPS, THD, 5.1.24-rc-logexplain partitionsselect a.poster_id, A.pi C_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.pos Ter_pic a INNER join poster.picture b on (a.pic_id = b.id) where a.poster_id = 3390 ORDER by a.indexed______________ Sub-p Art 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 (mysql01), 75+03:16:16, 774.20 QPS, THD, 5.1.24-rc-logselect a.poster_id, a.pic_id, A.gmt_cre Ate, 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 (mysql01), 75+03:16:16, 774.20 QPS, THD, 5.1.24-rc-logselect a.poster_id, a.pic_id, A.gmt_c Reate, 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 inne R join Poster.picture b on (a.pic_id = b.id) where a.poster_id = 3390 order by A.indexednote:select ' poster '. ' A '. ' Poster_i D ' 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 ' joins ' poster '. ' Picture ' ' B ' where (' poster '. ' B '. ' ID ' = ' poster '. ' A '. ' pic_id ') and (' poster '. ' A '. ' poster_id ' = 3390)) Order BY ' poster '. ' A '. ' INDEXED ' press E-explain, F for Full query, O for optimized query
So where does innotop fetch data from? The complete SQL statement should be obtained through Information_schema.processlist , and the idle thread will be filtered out by 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 Monitoring management tool –innotop