SLOWLOG 慢日志
作用
记录mysql运行过程中较慢的语句，通过一个文本的文件记录下来。
帮助我们进行语句优化工具日志。
如何配置：
默认慢日志没有开启
怎么打开？
1，会话
2.配置文件
mysql> select @@slow_query_log;
| @@slow_query_log |
| 0 |
日志位置
mysql> select @@slow_query_log_file;
| @@slow_query_log_file |
| /data/3306/later03-slow.log |
配置参数
select @@slow_query_log; #是否开启
select @@slow_query_log_file; #文件存放位置
select @@long_query_time; 慢语句认定时间阀值
select @@log_queries_not_using_indexes; 不走索引的语句记录
mysqldumpslow 慢日志分析
mysqldumpslow 语句
-s c 抓取规则
-t 2 抓取次数
/data/3306/later03-slow.log 慢日志路径
[root@later03 tmp]# mysqldumpslow -s c -t 2 /data/3306/later03-slow.log
Reading mysql slow query log from /data/3306/later03-slow.log
Count: 6 Time=0.41s (2s) Lock=0.00s (0s) Rows=4.2 (25), root[root]@localhost
select * from test1 limit N,N
Count: 1 Time=0.95s (0s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select count(N)from test1
基于pt-query-digest 工具的慢日志解析
[root@later03 tmp]# pt-query-digest /data/3306/later03-slow.log
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /usr/bin/pt-query-digest line 12012.
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /usr/bin/pt-query-digest line 12012.
# A software update is available:
# 240ms user time, 40ms system time, 29.08M rss, 242.25M vsz
# Current date: Thu Mar 26 20:52:15 2020
# Hostname: later03
# Files: /data/3306/later03-slow.log
# Overall: 9 total, 3 unique, 0.01 QPS, 0.01x concurrency ________________
# Time range: 2020-03-26T12:34:02 to 2020-03-26T12:45:10
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 4s 389us 1s 485ms 1s 414ms 412ms
# Lock time 1ms 101us 178us 129us 176us 24us 125us
# Rows sent 127 0 100 14.11 97.36 29.61 4.96
# Rows examine 8.97M 100 2.00M 1020.14k 1.95M 802.18k 961.27k
# Query size 304 25 39 33.78 38.53 4.72 34.95
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== ================================== ============= ===== ====== =====
# 1 0xBFB2C3593EF66DB212F4A4669C4D79ED 2.4572 56.2% 7 0.3510 0.40 SELECT test?
# 2 0x191EE7FF39E6846EB973D642EE544D3C 0.9589 21.9% 1 0.9589 0.00 SELECT test?
# 3 0x7AB4EB576176BCA59730ECA6D2915E28 0.9531 21.8% 1 0.9531 0.00 SELECT test?
# Query 1: 0.02 QPS, 0.01x concurrency, ID 0xBFB2C3593EF66DB212F4A4669C4D79ED at byte 871
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.40
# Time range: 2020-03-26T12:39:10 to 2020-03-26T12:45:10
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 77 7
# Exec time 56 2s 389us 1s 351ms 1s 374ms 374ms
# Lock time 71 830us 101us 136us 118us 131us 12us 108us
# Rows sent 98 125 0 100 17.86 97.36 32.67 4.96
# Rows examine 55 4.97M 100 2.00M 726.46k 1.95M 678.23k 961.27k
# Query size 82 252 31 39 36 38.53 2.46 36.69
# String:
# Databases world
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us #####################
# 1ms #####################
# 10ms #####################
# 100ms ################################################################
# 1s #####################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `world` LIKE 'test1'\G
# SHOW CREATE TABLE `world`.`test1`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from test1 limit 100000000,5\G
# Query 2: 0 QPS, 0x concurrency, ID 0x191EE7FF39E6846EB973D642EE544D3C at byte 649
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2020-03-26T12:39:28
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 11 1
# Exec time 21 959ms 959ms 959ms 959ms 959ms 0 959ms
# Lock time 15 178us 178us 178us 178us 178us 0 178us
# Rows sent 0 1 1 1 1 1 0 1
# Rows examine 22 2.00M 2.00M 2.00M 2.00M 2.00M 0 2.00M
# Query size 8 27 27 27 27 27 0 27
# String:
# Databases world
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `world` LIKE 'test1'\G
# SHOW CREATE TABLE `world`.`test1`\G
# EXPLAIN /*!50100 PARTITIONS*/
select sum(id) from test1\G
# Query 3: 0 QPS, 0x concurrency, ID 0x7AB4EB576176BCA59730ECA6D2915E28 at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2020-03-26T12:34:02
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 11 1
# Exec time 21 953ms 953ms 953ms 953ms 953ms 0 953ms
# Lock time 13 157us 157us 157us 157us 157us 0 157us
# Rows sent 0 1 1 1 1 1 0 1
# Rows examine 22 2.00M 2.00M 2.00M 2.00M 2.00M 0 2.00M
# Query size 8 25 25 25 25 25 0 25
# String:
# Databases world
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `world` LIKE 'test1'\G
# SHOW CREATE TABLE `world`.`test1`\G
# EXPLAIN /*!50100 PARTITIONS*/
select count(0)from test1\G
网友评论