MySQL监控工具之innotop

INNOTOP 简介

INNOTOP是一个通过文本模式显示MySQL和 InnoDB的监测工具。它有很多特点：快速的配置、易于使用等。INNOTOP是用PERL语言写成 的，这是它能更加灵活的使用在各种操作平台之上，它能详细的的监控出当前MYSQL和INNODB运行的状态，以便维护人员根据结果合理的优化 MYSQL，让MYSQL更稳定更高效的运行。

是一个系统活动报告,类似于Linux性能工具,它与Linux的top命令相仿,并参考mytop工具而设计.

它专门用后监控InnoDB性能和MySQL服务器.主要用于监控事务,死锁,外键,查询活动,复制活动,系统变量的主要统计信息
及主机的其他详情。InnoTop被广泛使用，并被当做常用性能监控工具。

由于InnoTop使用Perl语言写成,所以需要安装perl环境和相关的工具

安装方式

shell> tar zxvf innotop-1.9.1.tar.gz
shell> cd innotop-1.9.1
shell> perl Makefile.PL    #需要DBD::mysql、DBI、Term::ReadKey、Time::HiRes模块
shell> make && make install

使用选项

innotop --help
Usage: innotop <options> <innodb-status-file>

  --[no]color   -C   Use terminal coloring (default)
  --config      -c   Config file to read
  --count            Number of updates before exiting
  --delay       -d   Delay between updates in seconds
  --help             Show this help message
  --host        -h   Connect to host
  --[no]inc     -i   Measure incremental differences
  --mode        -m   Operating mode to start in
  --nonint      -n   Non-interactive, output tab-separated fields
  --password    -p   Password to use for connection
  --port        -P   Port number to use for connection
  --skipcentral -s   Skip reading the central configuration file
  --socket      -S   MySQL socket to use for connection
  --spark            Length of status sparkline (default 10)
  --timestamp   -t   Print timestamp in -n mode (1: per iter; 2: per line)
  --user        -u   User for login if not current user
  --version          Output version information and exit
  --write       -w   Write running configuration into home directory if no config files were loaded

innotop is a MySQL and InnoDB transaction/status monitor, like 'top' for
MySQL.  It displays queries, InnoDB transactions, lock waits, deadlocks,
foreign key errors, open tables, replication status, buffer information,
row operations, logs, I/O operations, load graph, and more.  You can
monitor many servers at once with innotop.

可获取的内容:

1. 显示当前innodb的全部事务列表；
2. 显示当前正运行着的查询；
3. 显示当前锁和锁等待的列表；
4. 服务器状态和变量的摘要信息 显示了数值的相对变化幅度；
5. 有多种模式可用来显示Innodb 内部信息，如缓冲区、死锁、外键错误、I/O情况、行操作、信号量等。
6. 复制信息，将主机和从机的状态显示一起；
7. 有一个显示任意服务器变量的模式；
8. 服务器组可以帮你更方便的组织多台服务器；
9. 在命令行脚本下可以使用非交互模式。

shell> innotop -uroot -pmypass -h 127.0.0.1 -P 3306
[RO] Dashboard (? for help)                     127.0.0.1, 1h34m, 0.50 QPS, 2/1/0 con/run/cac thds, 5.6.19

Uptime  MaxSQL  ReplLag  QPS   Cxns  Run  Miss  Lock  Tbls  Repl  SQL
 1h34m                   0.50     2       0.00     0    60  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        q  Quit innotop
   k  Kill a query's connection      r  Reverse sort order
   n  Switch to the next connection  s  Choose sort column
   p  Pause innotop                  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


I  InnoDB I/O Info ：

_________________________ I/O Threads __________________________
Thread  Purpose               Thread Status                     
     0  insert buffer thread  waiting for completed aio requests
     1  log thread            waiting for completed aio requests
     2  read thread           waiting for completed aio requests
     3  read thread           waiting for completed aio requests
     4  read thread           waiting for completed aio requests
     5  read thread           waiting for completed aio requests
     6  write thread          waiting for completed aio requests
     7  write thread          waiting for completed aio requests
     8  write thread          waiting for completed aio requests
     9  write thread          waiting for completed aio requests

____________________________ Pending I/O _____________________________
Async Rds  Async Wrt  IBuf Async Rds  Sync I/Os  Log Flushes  Log I/Os
                                   0          0            0         0

________________________ File I/O Misc _________________________
OS Reads  OS Writes  OS fsyncs  Reads/Sec  Writes/Sec  Bytes/Sec
     650          5          5       0.00        0.00          0

_____________________ Log Statistics _____________________
Sequence No.  Flushed To  Last Checkpoint  IO Done  IO/Sec
1729531646    1729531646  1729531646             8    0.00


Q  Query List：

When   Load   Cxns  QPS      Slow  Se/In/Up/De%  QCacheHit  KCacheHit  BpsIn   BpsOut 
Now    -0.00   151  -254.40     0   0/ 0/ 0/ 0       0.00%    100.00%   7.88k  606.32k
Total   0.00   151     0.30     0   1/ 0/ 0/ 0       0.00%    100.00%  14.39     1.08k

Cmd    ID      State  User   Host           DB      Time   Query

B  InnoDB Buffers ：
___________________________ Buffer Pool ___________________________
Size   Free Bufs  Pages  Dirty Pages  Hit Rate  Memory   Add'l Pool
8.00k       7553    638            0  --        129.72M           0

____________________ Page Statistics _____________________
Reads  Writes  Created  Reads/Sec  Writes/Sec  Creates/Sec
  638       1        0       0.00        0.00         0.00

______________________ Insert Buffers ______________________
Inserts  Merged Recs  Merges  Size  Free List Len  Seg. Size
                   0       0     1            146        148

___________________ Adaptive Hash Index ___________________
Size     Cells Used  Node Heap Bufs  Hash/Sec  Non-Hash/Sec
540.23k                           0      0.00          0.00

整理自网络

Svoid

2014-12-31