mysql慢查询日志分析工具推荐:pt-query-digest
pt-query-digest 是用于分析 mysql 慢查询日志的工具,它还可以分析来自 “SHOW PROCESSLIST” 和 MySQL 的查询 tcpdump 中的协议数据。我们可以把分析日志输出到指定的文件中,通过分析日志文件做对应的优化等操作。
1、下载并且安装
根据不同的系统安装,我的系统是 centos,所以直接选择 centos 安装,
点击下载:
https://downloads.percona.com/downloads/percona-toolkit
直接使用 yum 安装
> wget https://downloads.percona.com/downloads/percona-toolkit/3.4.0/binary/redhat/7/x86_64/percona-toolkit-3.4.0-3.el7.x86_64.rpm
> yum install percona-toolkit-3.4.0-3.el7.x86_64.rpm
或者使用源码包编译安装
> wget https://downloads.percona.com/downloads/percona-toolkit/3.4.0/source/debian/percona-toolkit-3.4.0.tar.gz
> tar -zxvf percona-toolkit-3.4.0.tar.gz
> cd percona-toolkit-3.4.0
> perl Makefile.PL PREFIX=/usr/local/percona-toolkit
> make && make install
2、安装完成
3、分析 msql 的慢查询日志
pt-query-digest 参数可以使用 perldoc 命令查看
perldoc /usr/bin/pt-query-digest
分析整个慢日志文件
pt-query-digest mysql-slow.log > slow_report.log
分析指定时间的日志 since~until
pt-query-digest mysql-slow.log --since '2022-10-01 00:00:00' --until '2022-11-05 00:00:00' > slow_report_date_20221021-202221105.log
–since: 表示开始时间
–until: 表示结束时间
分析慢日志,并且保存分析结果到 mysql 数据表
pt-query-digest --user=root --password=123456 --history h=192.168.33.10,D=local_test_db,t=query_review --create-history-table mysql-slow.log --since '2022-11-01 00:00:00' --until '2022-11-05 00:00:00'
–user: 数据库用户名
–password: 数据库密码
h: 数据库 host
D: 数据库名
t: 生成的表名
4、结果分析
第一部分:总的一个分析概况
Overall:总共有多少条查询
Time range:查询执行的时间范围
unique:唯一查询数量,即对查询条件进行参数化以后,总共有多少个不同的查询
total:所有查询总计时长
min:所有查询最小时长
max:所有查询最大时长
avg:所有查询平均时长
95%:把所有时长值从小到大排列,位置位于 95% 的那个时长数,这个数一般最具有参考价值
median:中位数,把所有时长值从小到大排列,位置位于中间那个时长数
# A software update is available:
# 23.7s user time, 15.8s system time, 35.67M rss, 249.01M vsz
说明:
执行过程中,在用户中所花费的所有时间
执行过程中,在内核空间中所花费的所有时间
pt-query-digest 进程所分配的内存大小
pt-query-digest 进程所分配的虚拟内存大小
# Current date: Mon Nov 7 09:01:23 2022
说明:当前时间
# Hostname: localhost.localdomain
说明:执行pt-query-digest的主机名
# Files: mysql-slow.log
说明:被分析的文件名称
# Overall: 44.78k total, 54 unique, 0.01 QPS, 0.07x concurrency __________
说明:
total: 语句总数量
unique: 唯一语句数量
QPS: 每秒查询量
concurrency: 查询的并发
# Time range: 2022-10-01 00:00:03 to 2022-11-04 16:05:24
说明:执行过程中日志记录的时间范围
# Attribute total min max avg 95% stddev median
说明:属性 总计 最小值 最大值 平均值 95% 标准差 中位数
95%: 把所有时长值从小到大排列,位置位于 95% 的那个时长数,这个数一般最具有参考价值
median: 中位数,把所有时长值从小到大排列,位置位于中间那个时长数
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 204553s 3s 1540s 5s 10s 8s 3s
说明:执行时间
# Lock time 8s 0 107ms 186us 80us 2ms 36us
说明:锁占用时间
# Rows sent 238.87M 0 2.88M 5.46k 11.95 68.22k 0.99
说明:发送到客户端的行数
# Rows examine 73.56G 0 5.01M 1.68M 3.86M 724.49k 1.32M
说明:扫描的语句行数
# Query size 8.18M 30 4.36k 191.46 511.45 224.63 72.65
说明:查询的字符数
第二部分:分析
Rank:所有语句的排名,默认按查询时间降序排列,通过 –order-by 指定
–order-by Query_time:sum :按总的查询时间倒序
排序参数介绍:
sum Sum/total attribute value (默认值)
min Minimum attribute value(最小值)
max Maximum attribute value (最小值)
cnt Frequency/count of the query (按出现 sql 次数)
Query ID:语句的 ID(去掉多余空格和文本字符,计算 hash 值)
Response:总的响应时间
time:该查询在本次分析中总的时间占比
Calls:执行次数,即本次分析总共有多少条这种类型的查询语句
R/Call:平均每次执行的响应时间
V/M:响应时间 Variance-to-mean 的比率
Item:查询对象
# Profile
说明:分析
# Rank Query ID Response time Calls R/Call
# ==== =================================== ================ ===== =======
# 1 0xC000AA97F210B2AEAE4933AF9B00296A 104236.2061 5... 30988 3.3638 0.03 SELECT xxx
# 2 0x974C6E6D54DB8B0DF505CA7BDC508686 32167.9607 15.7% 3418 9.4113 1.34 SELECT xxx
# 3 0x6BE180C5804B585F25BB16550447DC6C 18453.0185 9.0% 2499 7.3842 0.92 SELECT xxx
# 4 0xADF16E3E9EB5D6B08245E39FF1428C9F 17873.4338 8.7% 3114 5.7397 0.84 SELECT xxx
# 5 0x2964CD629A24595719659BDAEBCF0E6F 10648.5404 5.2% 1437 7.4103 0.93 SELECT xxx
# 6 0x50566E6DCF8FA562B88AE41AB1E32DC6 7424.3855 3.6% 303 24.5029 15.41 SELECT xxx
# 7 0xDB0A3D60F85C2212C476B144E1678AB8 5327.8370 2.6% 1627 3.2746 0.05 SELECT xxx
# 8 0x04BB0B332CEED517298AB06DE2A30AD6 3190.6822 1.6% 657 4.8564 1.36 SELECT xxx
# 10 0xDAB0AF524151C621DC0E9B92AC002C38 526.6288 0.3% 140 3.7616 0.01 SELECT xxx
# MISC 0xMISC 1807.1067 0.9% 57 31.7036 0.0 <27 ITEMS>
第三部分:具体的 sql 统计和分析
pct:该 sql 语句某执行属性占所有慢查询语句某执行属性的百分比
total:该 sql 语句某执行属性的所有属性时间。
Count:sql 语句执行的次数。对应的 pct 表示此 sql 语句执行次数占所有慢查询语句执行次数的 % 比(下图为 69%),对应的 total 表示总共执行了 30988 次。
Exec time:sql 执行时间
Lock time:sql 执行期间被锁定的时间
Rows sent:传输的有效数据,在 select 查询语句中才有值
Rows examine:总共查询的数据,非目标数据。
Query_time distribution:查询时间分布
SQL 语句:下图中为 select sleep (7)\G
# Query 1: 0.01 QPS, 0.03x concurrency, ID 0xC000AA97F210B2AEAE4933AF9B00296A at byte 221452362
说明:查询队列1:每秒查询量,查询的并发,队列1的ID值,对应第二部分的Query ID, 221452362表示偏移量(查看方法看下面的“查看偏移”)
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.03
# Time range: 2022-10-01 00:00:05 to 2022-11-04 16:05:24
说明:sql语句在慢日志文件mysql_slow.log出现的时间范围
# Attribute pct total min max avg 95% stddev median
说明:属性 占整个 总数 最小值 最大值 平均值 95% 标准差 中间值
分析中
的百分
比
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 69 30988
说明:执行语句总数量
# Exec time 50 104236s 3s 7s 3s 4s 303ms 3s
说明:执行时间
# Lock time 24 2s 22us 93ms 65us 66us 775us 38us
说明:锁占用时间
# Rows sent 0 70.53k 0 799 2.33 3.89 16.60 0.99
说明:发送到客户端的行数
# Rows examine 54 40.28G 1.32M 1.35M 1.33M 1.32M 15.65k 1.32M
说明:扫描语句的行数
# Query size 26 2.16M 73 73 73 73 0 73
说明:查询的字符数
# String:
# Hosts localhost
说明:使用的数据主机IP
# Users xxx
说明:使用的用户
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
说明:查询时间分布
# Tables
# SHOW TABLE STATUS LIKE 'xxx'\G
# SHOW CREATE TABLE `xxx`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from `table_name` where `updated_at` >= '2022-10-15 00:00:40'\G
说明:查询的mysql语句
第三部分是每一种查询比较慢的 sql 的详细统计结果
pct:该 sql 语句某执行属性占所有慢查询语句某执行属性的百分比
total:该 sql 语句某执行属性的所有属性时间。
Count:sql 语句执行的次数。
Exec time:sql 执行时间
Lock time:sql 执行期间被
5. 查看偏移
可以利用偏移量在慢查询日志文件中到查找到具体的 SQL 语句,查找方法如下:
[localhost]# tail -c +221452362 ./mysql-slow.log | head
t: root[root] @ localhost [] Id: 13704150
# Query_time: 7.058835 Lock_time: 0.000040 Rows_sent: 2 Rows_examined: 1392521
SET timestamp=1665763267;
select * from `xxxxxx` where `updated_at` >= '2022-10-15 00:00:40';
# [email protected]: root[localhost] @ localhost [] Id: 13704174
# Query_time: 7.445741 Lock_time: 0.000015 Rows_sent: 3 Rows_examined: 2214002
SET timestamp=1665763267;
select xxx from table where xxx
# Time: 221015 008
# [email protected]: root[localhost] @ localhost [] Id: 13704414
相关文章