1.安装mysqlreport:详见MySQL效能监控工具mysqlreport安装和中文说明
2.安装后使用以下命令查看report
./mysqlreport --user root --password 123456 --host 127.0.0.1 --port 3306 --socket /tmp/mysqld.sock
报告结果如下:
Use of uninitialized value in formline at ./mysqlreport line 1099.
Use of uninitialized value $is in multiplication (*) at ./mysqlreport line 829.
Use of uninitialized value in formline at ./mysqlreport line 1227.
Use of uninitialized value in formline at ./mysqlreport line 1235.
MySQL 5.7.34 uptime 11 11:17:10 Sat May 8 22:23:05 2021
__ Key _________________________________________________________________
Buffer used 7.00k of 8.00M %Used: 0.09
Current 1.47M %Usage: 18.32
Write hit 33.33%
Read hit 100.00%
__ Questions ___________________________________________________________
Total 25.22M 25.4/s
DMS 12.38M 12.5/s %Total: 49.10
COM_QUIT 12.25M 12.4/s 48.59
Com_ 323.14k 0.3/s 1.28
+Unknown 258.30k 0.3/s 1.02
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log:
DMS 12.38M 12.5/s 49.10
SELECT 12.38M 12.5/s 49.10 100.00
DELETE 1 0.0/s 0.00 0.00
REPLACE 0 0/s 0.00 0.00
INSERT 0 0/s 0.00 0.00
UPDATE 0 0/s 0.00 0.00
Com_ 323.14k 0.3/s 1.28
set_option 64.62k 0.1/s 0.26
show_variab 64.62k 0.1/s 0.26
show_status 64.61k 0.1/s 0.26
__ SELECT and Sort _____________________________________________________
Scan 387.71k 0.4/s %SELECT: 3.13
Range 0 0/s 0.00
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 4 0.0/s
Sort range 0 0/s
Sort mrg pass 0 0/s
__ Query Cache _________________________________________________________
Memory usage 16.35k of 1.00M %Used: 1.60
Block Fragmnt 100.00%
Hits 0 0/s
Inserts 1 0.0/s
Insrt:Prune 1:1 0/s
Hit:Insert 0.00:1
__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 12.32M 12.4/s
__ Tables ______________________________________________________________
Open 258 of 2000 %Cache: 12.90
Opened 265 0.0/s
__ Connections _________________________________________________________
Max used 127 of 151 %Max: 84.11
Total 12.25M 12.4/s
__ Created Temp ________________________________________________________
Disk table 9 0.0/s
Table 258.47k 0.3/s Size: 16.0M
File 6 0.0/s
__ Threads _____________________________________________________________
Running 1 of 3
Cached 6 of 9 %Hit: 70.10
Created 3.66M 3.7/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 33 0.0/s
Connects 78 0.0/s
__ Bytes _______________________________________________________________
Sent 43.33G 43.7k/s
Received 1.41G 1.4k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 4.50M of 128.00M %Used: 3.52
Read hit 88.96%
Pages
Free 7.90k %Total: 96.48
Data 288 3.52 %Drty: 0.00
Misc 0 0.00
Latched 0.00
Reads 2.17k 0.0/s
From file 240 0.0/s 11.04
Ahead Rnd 0 0/s
Ahead Sql 0/s
Writes 981 0.0/s
Flushes 85 0.0/s
Wait Free 0 0/s
__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 272 0.0/s
Writes 102 0.0/s
fsync 7 0.0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 49 0.0/s
Read 239 0.0/s
Written 85 0.0/s
Rows
Deleted 0 0/s
Inserted 247 0.0/s
Read 305 0.0/s
Updated 0 0/s
详细解读:
(1)索引报表
__ Key _________________________________________________________________
Buffer used 7.00k of 8.00M %Used: 0.09
Current 1.47M %Usage: 18.32
Write hit 33.33%
Read hit 100.00%
Key Buffer 是指 MyISAM 引擎使用的Shared Key Buffer,InnoDB 所使用的Key Buffer不在这里统计。
从上面的数据来看,MySQL 每次分配的Key Buffer最大是 7K,占 8M 的 0.09%。
current中的数据可以看到的是当前只用了 1.47M,占 8M 的 18.32%。说明 Key Buffer 是够用的,如果这个使用率高,就得增加key_buffer_size的值了。
(2)操作报表
__ Questions ___________________________________________________________
Total 25.22M 25.4/s
DMS 12.38M 12.5/s %Total: 49.10
COM_QUIT 12.25M 12.4/s 48.59
Com_ 323.14k 0.3/s 1.28
+Unknown 258.30k 0.3/s 1.02
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log:
DMS 12.38M 12.5/s 49.10
SELECT 12.38M 12.5/s 49.10 100.00
DELETE 1 0.0/s 0.00 0.00
REPLACE 0 0/s 0.00 0.00
INSERT 0 0/s 0.00 0.00
UPDATE 0 0/s 0.00 0.00
Com_ 323.14k 0.3/s 1.28
set_option 64.62k 0.1/s 0.26
show_variab 64.62k 0.1/s 0.26
show_status 64.61k 0.1/s 0.26
操作报表数据可以反应出来数据库现在忙不忙。从 25.4 每秒的操作量上来说,还是有点忙的。
重点看Slow 这一行,从这行可以看出slow log的时间是设置为 10 秒的,目前每秒是0个慢日志,如果存在慢日志,就重点看这里了。
DMS部分可以告诉我们这个数据库中各种 SQL 所占的比例。其实它是具有指向性的,像当前报表中,显然是SELECT多,那如果要做 SQL 优化的话,肯定优先考虑SELECT的语句,才会起到立竿见影的效果。
(3)查询和排序报表
__ SELECT and Sort _____________________________________________________
Scan 387.71k 0.4/s %SELECT: 3.13
Range 0 0/s 0.00
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 4 0.0/s
Sort range 0 0/s
Sort mrg pass 0 0/s
该报表具有着绝对的问题指向性。
重点关注报表中的Scan(全表扫描)和Full join(联合全表扫描),从上面报表中可以看到主要有scan(全表扫描),要涉及优化也是重点查看的对象。
(4)查询缓存报表
__ Query Cache _________________________________________________________
Memory usage 16.35k of 1.00M %Used: 1.60
Block Fragmnt 100.00%
Hits 0 0/s
Inserts 1 0.0/s
Insrt:Prune 1:1 0/s
Hit:Insert 0.00:1
该报表中,我们看的关键点是,Query Cache没用(Hits是0),因为各种query都没有缓存下来!Hits 这一行指的是每秒有多少个 SELECT 语句从Query Cache中取到了数据,这个值是越大越好。
同时这里我们还要看一个关键值,那就是Block Fragment,它是表明Query Cache碎片的,值越高,则说明问题越大。
如果你看到下面这样的数据,就明显没有任何问题。
__ Query Cache ______________________________________________________
Memory usage 38.05M of 256.00M %Used: 14.86
Block Fragmnt 4.29%
Hits 12.74k 33.3/s
Inserts 58.21k 152.4/s
Insrt:Prune 58.21k:1 152.4/s
Hit:Insert 0.22:1
而通过Insrt:Prune的比值数据,我们可以看到 Insert 远远大于 Prune(每秒删除的Query Cache碎片),这个比值越大就说明Query Cache越稳定。
如果这个值接近 1:1 那才有问题,这个时候就要加大Query Cache或修改你的 SQL 了。
而通过下面的Hit:Insert的值,我们可以看出命中要少于插入数,说明插入的比查询的还要多,这时就要去看这个性能场景中是不是全是插入了。
如果我们查看了,发现 SELECT 语句还是很多的,而这个比值又是 Hit 少,那么我们的场景中使用的数据应该并不是插入的数据。
其实在性能场景的执行过程中经常这样。所以在性能分析的过程中,我们只要知道这个值就可以了,并不能说明Query Cache就是无效的了。
(5)表信息报表
__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 12.32M 12.4/s
__ Tables ______________________________________________________________
Open 258 of 2000 %Cache: 12.90
Opened 265 0.0/s
这个很明显了,表锁倒是不存在。
但是你看现在table_open_cache已打开258,设置值为 2000,如果打开达到上限值就需要注意了,就需要考虑调整table_open_cache参数。
但是建议调之前先分析下其他的部分,如果在这个性能场景中,MySQL 的整体负载就会比较高,同时也并没有报错,那么不建议调这个值。如果负载不高,那再去调它。需要根据实际情况调整。
(6)连接报表和临时表
__ Connections _________________________________________________________
Max used 127 of 151 %Max: 84.11
Total 12.25M 12.4/s
__ Created Temp ________________________________________________________
Disk table 9 0.0/s
Table 258.47k 0.3/s Size: 16.0M
File
这个数据连接已经使用快接近最大值了,需要注意了。
从临时表创建在磁盘(Disk table)和临时文件(File) 上的量级来说,如果使用较大了,可以考虑增大tmp_table_size。
(7)线程报表
__ Threads _____________________________________________________________
Running 1 of 3
Cached 6 of 9 %Hit: 70.10
Created 3.66M 3.7/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 33 0.0/s
Connects 78 0.0/s
__ Bytes _______________________________________________________________
Sent 43.33G 43.7k/s
Received 1.41G 1.4k/s
当 Running 的线程数超过配置值时,就需要增加thread_cache_size。
是从该报表看,当前仅配置了 3,只用到了1。
Cached 的命中%Hit是越大越好,通常都希望在 99% 以上。
(8)InnoDB 缓存池报表
__ InnoDB Buffer Pool __________________________________________________
Usage 4.50M of 128.00M %Used: 3.52
Read hit 88.96%
Pages
Free 7.90k %Total: 96.48
Data 288 3.52 %Drty: 0.00
Misc 0 0.00
Latched 0.00
Reads 2.17k 0.0/s
From file 240 0.0/s 11.04
Ahead Rnd 0 0/s
Ahead Sql 0/s
Writes 981 0.0/s
Flushes 85 0.0/s
Wait Free 0
该报表对 MySQL 来说是很重要的,innodb_buffer_pool_size为 128M(这里是默认值未调整),它会存储表数据、索引数据等。
通常在网上或书籍里,你能看到有人建议将这个值设置为物理内存的 50%,当然这个值没有绝对的,还要在具体的应用场景中测试才能知道。
这里的Read hit达到 88.96%,该值越高越好。
下面还有些其他的读写数据,这部分的数据将和我们在操作系统上看到的 I/O 有很大关系。有些时候,由于写入的过多,导致操作系统的I/O wait很高的时候,我们不得不设置innodb_flush_log_at_trx_commit参数(0:
延迟写,实时刷;1:实时写,实时刷;2:实时写,延迟刷)和sync_binlog 参数(0:写入系统缓存,而不刷到磁盘;1:同步写入磁盘;N:写 N 次系统缓存后执行一次刷新操作)来降低写入磁盘的频率,但是这样
做的风险就是当系统崩溃时会有数据的丢失。
这其实是我们做测试时,存储性能不高的时候常用的一种手段,为了让 TPS 更高一些。但是,你一定要知道生产环境中的存储是什么样的能力,以确定在生产环境中应该如何配置这个参数。
(9)InnoDB 锁报表
__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
该报表信息需要重点关注,以上报表中不存在锁。
但是下面这个就存在,而且锁的次数太多了,并且锁的时间都还不短,平均时间都能达到 754ms,这显然是不能接受的。那就会有人问了,锁次数和锁的平均时间多少才是正常呢?
一般锁平均时间最好接近零。锁次数可以有,这个值是累加的,所以数据库启动时间长,用得多,锁次数就会增加。
__ InnoDB Lock _________________________________________________________
Waits 227829 0.1/s
Current 1
Time acquiring
Total 171855224 ms
Average 754 ms
Max 6143 ms
(10)InnoDB 其他信息
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 272 0.0/s
Writes 102 0.0/s
fsync 7 0.0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 49 0.0/s
Read 239 0.0/s
Written 85 0.0/s
Rows
Deleted 0 0/s
Inserted 247 0.0/s
Read 305 0.0/s
Updated 0 0/s
从该报表中可以看出那种操作占比较高,该示例不是很明显。
可以看下下面的报表,插入场景就占比较高。
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 35.74k 0.0/s
Writes 6.35M 1.6/s
fsync 4.05M 1.0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 87.55k 0.0/s
Read 34.61k 0.0/s
Written 3.19M 0.8/s
Rows
Deleted 707.46k 0.2/s
Inserted 257.12M 65.9/s
Read 137.86G 35.3k/s
Updated 1.13M 0.3/
以上报告解读学习参考极客时间高楼《性能测试30讲》中第22讲丨MySQL:数据库级监控及常用计数器解析(上),推荐学习!如有需要购买可联系我推荐!