mysql监控利器之innotop

安装innotop

下载地址： https://github.com/innotop/innotop/releases

1、安装依赖

yum install -y perl-TermReadKey

2、安装

# tar zxf innotop-1.11.4.tar.gz

# cd innotop

# perl Makefile.PL

#make install

 

perl Makefile.PL 编译错误解决：

Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1. BEGIN failed--compilatio

 

解决：yum install perl-ExtUtils-Embed -y

[root@bj-ksy-p1-kf_master-01 ntupload]# yum install perl-Time-HiRes

 

错误2：

[root@bj-ksy-p1-kf_master-01 ntupload]# innotop -uroot

Can't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/bin/innotop line 1417.

BEGIN failed--compilation aborted at /usr/local/bin/innotop line 1417.

[root@bj-ksy-p1-kf_master-01 ntupload]# innotop --help

Can't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/bin/innotop line 1417.

解决办法：

 

错误3： Ubuntu环境

 

Can't locate DBI.pm in @INC (you may need to install the DBI module) (@INC contains: /etc/perl /usr/local/lib/x86_64-linux-gnu/perl/5.22.1 /usr/local/share/perl/5.22.1 /usr/lib/x86_64-linux-gnu/perl5/5.22 /usr/share/perl5 /usr/lib/x86_64-linux-gnu/perl/5.22 /usr/share/perl/5.22 /usr/local/lib/site_perl /usr/lib/x86_64-linux-gnu/perl-base .) at /usr/local/bin/innotop line 39.
BEGIN failed--compilation aborted at /usr/local/bin/innotop line 39.
 

解决办法：sudo apt-get install libdbi-perl 

Ubuntu 错误1：sudo apt-get install libterm-readkey-perl

 

一、监控

1、连接

innotop -uroot -proot -h127.0.0.1

 

2、输入？，则进入帮助页面

 

 

3、

第一部分为可切换的模式-输入相应的大写字母

第二部分可进行的操作-输入相应的小写字母

第三部分 其他-配置类操作

 

4、Q Query List

innodb中最常用的模式，输入大写Q，进入该模式的视图页面。

 

[RO] Query List (? for help) localhost, 17d, 10.47 QPS, 58/1/59 con/run/cac thds, 5.6.29-logWhen Load Cxns QPS Slow Se/In/Up/De% QCacheHit KCacheHit BpsIn BpsOutNow 0.05 58 10.47 0 32/ 0/ 0/ 0 0.00% 100.00% 5.60k 20.38kTotal 0.00 7.81k 14.99 11.01k 36/ 2/ 2/ 0 0.00% 99.99% 2.55k 20.79kCmd ID State User Host DB Time Query

 

参数详解

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

 

1. B InnoDB Buffers

模式显示有关InnoDB Buffer Pool，页面统计，插入缓冲，自适应哈希索引。

使用的是show innodb status的数据。

 

____________________________ Buffer Pool _____________________________Size Free Bufs Pages Dirty Pages Hit Rate Memory Add'l Pool63.99k 13207 48854 0 1000 / 1000 1.02G 0____________________ Page Statistics _____________________Reads Writes Created Reads/Sec Writes/Sec Creates/Sec21299 240690 29436 0.00 0.61 0.00______________________ Insert Buffers ______________________Inserts Merged Recs Merges Size Free List Len Seg. Size 0 0 1 1316 1318__________________ Adaptive Hash Index __________________Size Cells Used Node Heap Bufs Hash/Sec Non-Hash/Sec2.26M 3467 0.04 139.75

 

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: 每秒非哈希索引的量

 

1. 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

 

1. 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 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 22142 1163454 982096 0.00 0.00 0_____________________ Log Statistics ______________________Sequence No. Flushed To Last Checkpoint IO Done IO/Sec20178545268 20178545268 20178545268 870538 0.00

 

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次数