利用pt工具对慢日志slow_query_2023_12_11*进行分析
pt-query-digest slow_query_2023_12_11* --since '2023-12-11 09:35:00' --until '2023-12-11 20:21:14' >./1212.txt
--javascripttypescriptbashsqljsonhtmlcssccppjavarubypythongorustmarkdown
第一部分:总体统计结果
# 该工具执行日志分析的用户时间1.1s,系统时间60ms,物理内存占用大小30.32M,虚拟内存占用大小221.02M
# 1.1s user time, 60ms system time, 30.32M rss, 221.02M vsz
# 工具执行时间 2023/12/12 08:57:06
# Current date: Tue Dec 12 08:57:06 2023
运行分析工具的主机名k8s-master01
# Hostname: k8s-master01
被分析的文件名slow_query_2023_12_11*
# Files: slow_query_2023_12_11_08_00_04.log, slow_query_2023_12_11_09_00_05.log, slow_query_2023_12_11_10_00_05.log, slow_query_2023_12_11_11_00_04.log
# 语句总数量1.77k,唯一的语句数量11,0.25 QPS,0.35x并发数
# Overall: 1.77k total, 11 unique, 0.25 QPS, 0.53x concurrency ___________
# 日志记录的时间范围 2023-12-11T09:35:05 to 2023-12-11T11:31:45
# Time range: 2023-12-11T09:35:05 to 2023-12-11T11:31:45
# 属性 总计 最小 最大 平均 95% 标准 中等
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# 语句执行时间
# Exec time 3714s 1s 326s 2s 2s 11s 2s
# 锁占用时间
# Lock time 24s 44us 6s 14ms 224us 273ms 167us
# 发送到客户端的行数
# Rows sent 2.60k 0 31 1.51 23.65 5.70 0
# select语句扫描行数
# Rows examine 3.64G 194.02k 13.80M 2.11M 2.05M 499.47k 2.05M
# 查询的字符数
# Query size 547.22k 49 322 316.76 313.99 28.23 313.99
第二部分:查询分组统计结果
# Profile
# Rank Query ID Response time Calls R/Call
# ==== =================================== =============== ===== ========
# 1 0x94115CD367C000523EE3F4DE243F60BF 2715.4743 73.1% 1694 1.6030 0.00 SELECT csb_soo_user_owe
# 2 0x7ABA8DFFB6618F3F9D6E5BEF0846877C 710.9618 19.1% 4 177.7405 11... DELETE inf_finish_flow
# 3 0xEEA72FE573EB08CE91FBD3D00AAB7912 157.9055 4.3% 48 3.2897 0.00 UPDATE csb_soo_user_owe
# 4 0x7DD99AC32A503DFAE1CA70A3A1085E92 54.5792 1.5% 12 4.5483 3.88 SELECT inf_finish_flow
# MISC 0xMISC 75.1361 2.0% 11 6.8306 0.0 <7 ITEMS>
Rank:所有语句的排名,默认按查询时间降序排列,通过–orderby指定
Query ID:语句的ID,(去掉多余空格和文本字符,计算hash值)
Response:总的响应 时间
time:该查询在本次分析中总的时间占比
calls:执行次数,即本次分析总共有多少条这种类 型的查询语句 R/Call:平均每次执行的响应时间
V/M:响应时间Variance-to-mean的比率 Item: 查询对象
第三部分:每一种查询的详细统计结果
查询语句占用第一的语句;
# Query 1: 0.24 QPS, 0.39x concurrency, ID 0x94115CD367C000523EE3F4DE243F60BF at byte 485044
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-12-11T09:35:05 to 2023-12-11T11:31:45
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 95 1694
# Exec time 73 2715s 2s 2s 2s 2s 36ms 2s
# Lock time 1 287ms 84us 416us 169us 224us 45us 167us
# Rows sent 96 2.52k 0 31 1.52 23.65 5.74 0
# Rows examine 93 3.42G 2.06M 2.07M 2.07M 2.05M 0 2.05M
# Query size 97 532.68k 322 322 322 322 0 322
# String:
# Databases jkdb_1 (847/50%), jkdb_2 (847/50%)
# Hosts 135.152.10.60
# Users dbproxy_b8cfa
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `jkdb_1` LIKE 'csb_soo_user_owe'\G
# SHOW CREATE TABLE `jkdb_1`.`csb_soo_user_owe`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT *
FROM csb_soo_user_owe a
WHERE a.state = 0
AND NOT EXISTS (SELECT 1
FROM csb_soo_user_owe t
WHERE t.acc_nbr = a.acc_nbr
AND t.order_item_id = a.order_item_id
AND t.state = 2)
AND owe_time > '2022-08-01 00:00:00'
AND owe_time < date_sub(now(), INTERVAL 59 SECOND)
AND productid = '900000001'
LIMIT 100\G