MySQL 性能剖析

发表于   |   分类于   |   阅读次数

分析慢查询日志

　　在分析慢查询日志之前，需要先让 MySQL 开启慢查询日志。首先，创建目录用于存放慢查询日志：

1
2
3
$ mkdir -p /home/ubuntu/log/mysql
$ chmod 755 /home/ubuntu/log/mysql/
$ sudo chown mysql:mysql /home/ubuntu/log/mysql/

　　下面是和慢查询相关的配置：

1
2
3
4
5
6
7
8
9
10
11
$ cat /etc/mysql/conf.d/mysql.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /home/ubuntu/log/mysql/mysql-slow.log
# 若查询的耗时超过 2 秒，并且至少需要分析 100 行记录，才会被判定为慢查询
long_query_time = 2
min_examined_row_limit = 100
# 若查询没有使用到索引，也会被判定为慢查询
log-queries-not-using-indexes

　　在开启慢查询日志之后，可以借助pt-query-digest工具分析慢查询日志，生成剖析报告：

1
$ pt-query-digest mysql-slow.log

　　生成的报告会对慢查询进行排序，比较严重的慢查询会排在前面：

1
2
3
4
5
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ==============
#    1 0x9F602236FBB89FCB  0.0169 98.7%     1 0.0169  0.00 SELECT payment
#    2 0x5665CD6BAE86EAEC  0.0002  1.3%     1 0.0002  0.00 SELECT actor

　　如何解读这份报告呢？

  • Response：查询的响应时间。
  • time：查询占总时间的百分比。
  • Calls：查询的执行次数。
  • R/Call：单次查询的平均响应时间。
  • V/M：方差与平均值的比值。如果这个值较大，表示查询对应的执行时间变化较大。

　　除此之外，报告还包含每种查询的详细报告：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2017-12-16 14:17:37
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         50       1
# Exec time     98    17ms    17ms    17ms    17ms    17ms       0    17ms
# Lock time     54    72us    72us    72us    72us    72us       0    72us
# Rows sent     64     179     179     179     179     179       0     179
# Rows examine  99  15.67k  15.67k  15.67k  15.67k  15.67k       0  15.67k
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent    72  11.25k  11.25k  11.25k  11.25k  11.25k       0  11.25k
# Query size    68      63      63      63      63      63       0      63
# String:
# Databases    sakila
# Hosts        localhost
# Last errno   0
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `sakila` LIKE 'payment'\G
#    SHOW CREATE TABLE `sakila`.`payment`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from payment where last_update = '2006-02-15 22:12:30'\G

　　这份报告还包含了一个直方图，可以看到查询时间的分布情况。

参考资料