MySQL monitoring management tool-innotop

Author: skate
Time: 2013/02/25

MySQL monitoring management tool-innotop

 

Innotop is a monitoring tool that displays MySQL and InnoDB in text mode. Innotop is written in Perl, which enables it to be used more flexibly on various operating platforms. It can monitor the running status of MySQL and InnoDB in detail, based on DBA results, you can optimize MySQL reasonably to make MySQL run more stably and efficiently.

1. innotop Installation
Reference Official Website: http://innotop.googlecode.com/svn/html/installing.html

# Wget http://innotop.googlecode.com/files/innotop-1.9.0.tar.gz
# Tar-zxvf innotop-1.9.0.tar.gz
# Cd innotop-1.9.0

Installing the innotop tool is very simple. It is written by Perl. Of course, the Perl environment and related toolkit are required. Before installation, make sure that your system has installed four packages: Time: hires, term: readkey, DBI, DBD: MySQL.

# Perl-mcpan-eshell
CPAN> install time: hires
CPAN> install term: readkey
CPAN> install DBI
CPAN> install DBD: MySQL

 

Install the Help file in the decompressed source code path as follows:

# Perl makefile. pl

If no error is reported, use
# Make install

In this way, innotop is installed. After installation, the system will have an additional command, innotop

Get help
# Innptop -- Help

2. Usage
Reference Official Website: http://innotop.googlecode.com/svn/html/manual.html

Usage:
# 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, 3 h, 0.06 QPS, 2/1/0 con/run/CAC thds, 5.1.67-Log
Uptime maxsql repllag cxns lock QPS run t1_repl SQL
3 H 2 0 0.06 13 off

Enter "?" Get help

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

You can use innotop to monitor mysql. For example, you can view the currently running SQL statements and view the execution plan as follows:

When you enter innotop, press SHIFT + Q to enter the SQL query list,

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 K
Total 0.00 151 0.08 5 2/0/0/0 4.17% 100.00% 3.29

Cmd id state user host dB Time query

Query 1 sending data root localhost test 0: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

Then press E and enter the thread ID to display the execution plan, press F to display the complete SQL statement, or press o to display the system-optimized statements (MySQL version must support 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 obtains the complete SQL statement through information_schema.processlist and filters out Idle threads according to the command.

 

--- End ---