Innotop是一款十分强大的MySQL监控工具,用perl所写,通过文本模式展示MysQL服务器和Innodb的运行状况
具体特性 :
事务列表可以显示Inoodb当前的全部事务
查询列表可以显示当前正在运行的查询
可以显示当前锁和锁等待的列表
以相对值显示服务器状态和变量的汇总信息
有多种模式可用来显示Innodb内部信息(缓冲区、死锁、外键错误、I/O活动、行操作、信号量)
复制监控,将主服务器和从服务器的状态显示在一起
显示任意服务器变量的模式
服务器组可以更方便地组织多台服务器
在命令行脚本下可以使用非交互式模式
安装
下载地址:https://github.com/innotop/innotop
下载到本地
unzip innotop-master.zip
cd innotop-master
perl Makefile.PL
make install
使用
[root@m1 ~]# innotop --help
Usage: innotop <options> <innodb-status-file>
--askpass Prompt for a password when connecting to MySQL
--[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.
输入账号密码监控:
innotop -uroot -pchina -hxxx
输入?
Actions:
a Toggle the innotop process n Switch to the next connection
c Choose visible columns p Pause innotop
d Change refresh interval q Quit innotop
e Explain a thread's query r Reverse sort order
f Show a thread's full query s Change the display's sort column
h Toggle the header on and off t Toggle slave processes
i Toggle idle processes x Kill a query
k Kill a query's connection
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
输入shift+q进入sql查询列表
按e并输入thread ID显示执行计划或者按f显示完整sql语句,或者按o显示系统优化过的语句
比如按e
Query List (? for help) localhost, 12m, 0.20 QPS, 2/2/0 con/run/cac thds, 5.7.14-log
EXPLAIN PARTITIONS
select sleep(100) from t.t1
______ Sub-Part 1 ______
Select Type: SIMPLE
Table: t1
Partitions:
Type: index
Poss. Keys:
Index: PRIMARY
Key Length: 4
Index Ref:
Row Count: 1
Special: Using index
Press e to explain, f for full query, o for optimized query
按f
Query List (? for help) localhost, 12m, 0.20 QPS, 2/2/0 con/run/cac thds, 5.7.14-log
select sleep(100) from t.t1
Press e to explain, f for full query, o for optimized query
其他一些:
监控方式
innotop -uroot -proot -h127.0.0.1
Uptime MaxSQL ReplLag QPS Cxns Run Miss Lock Tbls Repl SQL
2d6h 10.23 316 0.00 0 64
输入?,则进入帮助页面
第一部分为可切换的模式-输入相应的大写字母
第二部分可进行的操作-输入相应的小写字母
第三部分 其他-配置类操作
Q Query List
innodb中最常用的模式,输入大写Q,进入该模式的视图页面。
When Load Cxns QPS Slow Se/In/Up/De% QCacheHit KCacheHit BpsIn BpsOut
Now 0.84 186 2.18 0 40/36/ 0/ 0 0.00% 100.00% 13.50k 2.30k
Total 0.00 2.93k 17.28 0 40/ 5/ 1/ 0 0.00% 99.55% 8.23k 4.81k
Cmd ID State User Host DB Time Query
Query 21092 query end root p_db_long2 21:37:57 INSERT ignore INTO T_1(datatime,datavalue,updatetime) SELECT *
Daemon 1 Waiting for next a event_sc localhost 02:40
参数详解
Load 是负载
QPS (Query Per Second) 表示每秒的查询率
Slow 代表慢查询的个数
Se/In/Up/De%: 查询/插入/更新/删除的比例
QCacheHit:查询缓冲的命中率
KCacheHit:索引的命中率
BpsIn (bytes Per Second in) 每秒输入的字节数
BpsOut(bytes Per Second Out)每秒输出的字节数
在这个页面,可输入e、f、o等键
e键 : 按e并输入thread ID,就能显示该SQL的执行计划
f键 : 显示当前正在运行的完整SQL语句
o键 : 显示当前SQL被mysql内部优化后的SQL
B InnoDB Buffers
模式显示有关InnoDB Buffer Pool,页面统计,插入缓冲,自适应哈希索引。
使用的是show innodb status的数据。
_____________________________ Buffer Pool _____________________________
Size Free Bufs Pages Dirty Pages Hit Rate Memory Add'l Pool
384.00k 8159 362884 3191 999 / 1000
_______________________ Page Statistics ________________________
Reads Writes Created Reads/Sec Writes/Sec Creates/Sec
12520029 22675359 10576483 99.52 126.87 0.00
______________________ Insert Buffers ______________________
Inserts Merged Recs Merges Size Free List Len Seg. Size
0 0 1 20663 20665
__________________ Adaptive Hash Index __________________
Size Cells Used Node Heap Bufs Hash/Sec Non-Hash/Sec
1.52M 33 18174.11 5072.58
Buffer Pool
Size : 某sql使用Buffer Pool的大小
Free Bufs : Innodb_buffer_pool_pages_free的值,空页数;
Pages: Innodb_buffer_pool_pages_data的值, 包含数据的页数(脏或干净)
Dirty Pages : Innodb_buffer_pool_pages_dirty的值,当前的脏页数
Hit Rate: 命中率
Memory : Innodb_buffer_pool_size 的值.
Add'l Pool : innodb_additional_mem_pool_size的值
Page Statistics
Reads : Innodb_pages_read 的值,读取的页数
Writes : Innodb_pages_written 的值,写入的页数
Created : Innodb_pages_created 的值,创建的页数
Reads/Sec : 每秒读取的页数
Writes/Sec : 每秒写入的页数
Creates/Sec : 每秒创建的页数
Insert Buffers
Inserts : 表示执行insert 次数
Merged Recs : 表示执行 insert 索引树合并的次数
Merges: insert 语句合并的次数
Size: 写缓冲的大小
Free List Len : 空闲列表长度
Seg. Size: 段块大小
Adaptive Hash Index 自适应哈希索引
Size: 哈希索引占用大小
Cells Used:
Node Heap Bufs:
Hash/Sec: 每秒哈希索引量
Non-Hash/Sec: 每秒非哈希索引的量
D InoDB Deadlocks
显示innodb产生的死锁,和产生死锁的语句。
______________________ Deadlock Transactions ______________________
ID Timestring User Host Victim Time Undo LStrcts Query Text
____________________ Deadlock Locks ____________________
ID Waiting Mode DB Table Index Special Ins Intent
参数详解:
ID:连接线程ID号
Timestring : 死锁发生的时间
User: 用户名
Host:主机
Victim:Yes表示这个死锁SQL被强行终止了.No表示这个SQL正在执行
Time : 这条SQL的运行时间
Undo : 回滚
LStrcts: 不知道是什么的缩写
Query Text : 死锁执行的SQL
I InnoDB I/O Info
显示InnoDB的I / O统计,包括I / O线程,挂起的I / O,文件I / O,日志统计
________________ I/O Threads _________________
Thread Purpose Thread Status
0 insert buffer thread wait Windows aio
1 log thread wait Windows aio
2 read thread wait Windows aio
3 read thread wait Windows aio
4 read thread wait Windows aio
5 read thread wait Windows aio
6 write thread wait Windows aio
7 write thread wait Windows aio
8 write thread wait Windows aio
9 write thread wait Windows aio
____________________________ Pending I/O _____________________________
Async Rds Async Wrt IBuf Async Rds Sync I/Os Log Flushes Log I/Os
0
________________________ File I/O Misc _________________________
OS Reads OS Writes OS fsyncs Reads/Sec Writes/Sec Bytes/Sec
12538485 24869318 5135901 198.90 109.95 16384
_______________________ Log Statistics _______________________
Sequence No. Flushed To Last Checkpoint IO Done IO/Sec
2081491560717 2081490654329 2081445810301 537481 2.50
Pending I/O 挂起的IO
Async Rds : Asynchronous Reads 异步读
Async Wrt: : Asynchronous Write 异步写
IBuf Async Rds: : Innodb Buffer Asynchronous Reads 缓冲池的异步读
Sync I/Os: : Asynchronous Input/Output:的异步
Log Flushes : log Flushes
Log I/Os : Input/Output 异步写入/读出日志
File I/O Misc(文件IO)
OS Reads: 系统I./O的读
OS Writes: 系统.I/O的写
OS fsyncs: 系统文件同步
Reads/Sec: 每秒多少次读
Writes/Sec: 每秒多少次写
Bytes/Sec: 每秒多少字节的输入输出
Log Statistics(日志统计)
Sequence No: Innodb buffer 里面的 LSN 号
Flushed To: 刷新到事务日志的LSN号
Last Checkpoint:: LSN的检查点
IO Done: 多少I/O已经完成
IO/Sec: 每秒I/O次数