MySQL Monitoring management tool –innotop

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

1

2

3

4

5

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