MySQL之慢查询和慢日志与mysqldumpslow与pt-query-digest慢查询日志分析工具的使用
慢查询与慢日志
慢日志的查询与开启
查询慢日志是否开启
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)
开启慢查询日志
# 方便测试，所有查询记录都进慢日志
mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.04 sec)
慢日志位置的查询及设置
查看所有日志的变量信息
mysql> show variables like '%log%';
+--------------------------------------------+-----------------------------------------------------+
| Variable_name | Value |
+--------------------------------------------+---------------------------------------------------
| log_queries_not_using_indexes | OFF
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/administrator-slow.log |
+--------------------------------------------+-----------------------------------------------------+
74 rows in set (0.00 sec)
设置慢查询日志的位置
mysql> set global slow_query_log_file='/usr/local/mysql/data/administrator-slow.log';
Query OK, 0 rows affected (0.00 sec)
慢日志判断标准及修改判断标准
判断标准：默认查询时间大于10s的SQL语句
mysql> show variables like 'long_query_time';
+----------------+-----------+
| Variable_name | Value |
+----------------+-----------+
| long_query_time | 10.000000 |
+----------------+-----------+
大于1秒钟的数据记录到慢日志中，如果设置为默认0，则会有大量的信息存储在磁盘中
mysql> set global long_query_time=0;
Query OK, 0 rows affected (0.00 sec)
分析慢查询日志（核心)
mysql> show variables like '%log%';
+--------------------------------------------+-----------------------------------------------------+
| Variable_name | Value |
+--------------------------------------------+-----------------------------------------------------+
| log_queries_not_using_indexes | ON
| slow_query_log | ON
| slow_query_log_file | /usr/local/mysql/data/administrator-slow.log
---------------------------------------+-----------------------------------------------------+
74 rows in set (0.01 sec)
mysql> select * from user where name='李白';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 李白 | 22 |
+----+--------+------+
1 row in set (0.00 sec)
[root@administrator ~]# more /usr/local/mysql/data/administrator-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.32 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 2020-11-19T14:14:58.342982Z
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 0.000216 Lock_time: 0.000112 Rows_sent: 1 Rows_examined: 1
use demo;
SET timestamp=1605795298;
select * from user;
# Time: 2020-11-19T14:15:27.538174Z
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 0.000202 Lock_time: 0.000110 Rows_sent: 1 Rows_examined: 1
SET timestamp=1605795327;
select * from user;
# Time: 2020-11-19T14:19:37.986218Z
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 0.000247 Lock_time: 0.000138 Rows_sent: 1 Rows_examined: 1
SET timestamp=1605795577;
select * from user where name='李白';
慢日志格式说明
# Time: 2020-11-19T14:19:37.986218Z --查询的执行时间
# User@Host: root[root] @ localhost [] Id: 3 --执行sql的主机信息
# Query_time: 0.000247 --SQL的查询时间
# Lock_time: 0.000138 --SQL的查询时间
# Rows_sent: 1 --所发送的行数
# Rows_examined: 1 --锁扫描的行数
SET timestamp=1605795577; --SQL执行时间
select * from user where name='李白'; --SQL的执行内容
慢查询日志分析工具
如果开启了慢查询日志，就会生成大量的数据，可以通过对日志的分析，生成分析报表，通过报表进行优化。
mysqldumpslow
这个工具是最常用的工具，通过安装mysql进行附带安装，但是该工具统计的结果比较少
命令注意事项
mysqldumpslow -h (需做一个软连接到/usr/bin目录，如：ln -s /usr/local/mysql/bin、mysqldumpslow /usr/bin)
./mysqldumpslow -h(需进入mysql安装路径下的bin目录)
查看详细用法
查看详细用法：mysqldumpslow –help
[root@administrator bin]# ./mysqldumpslow -help
Option h requires an argument
ERROR: bad option
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
[root@administrator bin]#
用法示例
查看慢查询日志的前20个
[root@administrator bin]# ./mysqldumpslow -t 20 /usr/local/mysql/data/administrator-slow.log
Count: 1 Time=0.02s (0s) Lock=0.03s (0s) Rows=1.0 (1), root[root]@[117.176.186.42]
SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'S' UNION SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'S' UNION SELECT COUNT(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'S'
Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[117.176.186.42]
INSERT INTO `user`.`user`(`name`, `age`) VALUES ('S', N)
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select * from user where name='S'
分析日志格式说明
Count：语句执行次数
Time：总耗时（平均耗时/次）
Lock：锁表时间
Rows：共返回行数（平均行数）
pt-query-digest
pt-query-digest是一个更强大的慢查询日志分析工具，属于第三方工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。
官网：https://www.percona.com/downloads/percona-toolkit/LATEST/
安装pt-query-digest
下载
wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/redhat/7/x86_64/percona-toolkit-3.3.1-1.el7.x86_64.rpm
安装
yum localinstall –y percona-toolkit-3.3.1-1.el7.x86_64.rpm
pt-query-digest本质是perl脚本，在安装pt-query-digest时会提示安装perl相关依赖模块，手动确认即可。
检查是否安装完成
pt-query-digest –help
[root@administrator root]# pt-query-digest --help
pt-query-digest analyzes MySQL queries from slow, general, and binary log files.
It can also analyze queries from C<SHOW PROCESSLIST> and MySQL protocol data
from tcpdump. By default, queries are grouped by fingerprint and reported in
descending order of query time (i.e. the slowest queries first). If no C<FILES>
are given, the tool reads C<STDIN>. The optional C<DSN> is used for certain
options like L<"--since"> and L<"--until">. For more details, please use the
--help option, or try 'perldoc /usr/bin/pt-query-digest' for complete
documentation.
Usage: pt-query-digest [OPTIONS] [FILES] [DSN]
Options:
--ask-pass Prompt for a password when connecting to MySQL
--attribute-aliases=a List of attribute|alias,etc (default db|Schema)
--attribute-value-limit=i A sanity limit for attribute values (default 0)
--charset=s -A Default character set
--config=A Read this comma-separated list of config files;
if specified, this must be the first option on
the command line
--[no]continue-on-error Continue parsing even if there is an error (
分析慢查询日志（核心)
pt-query-digest /www/server/data/mysql-slow.log
包含：总时间，最小时间，最大时间，平均时间，达到95%的时间，标准时间，中位时间等详细信息
关注点：
1.查询次数多且每次查询占用时间长的sql：看前几个查询（Rank Query ID ），关注每个SQL执行的次数及百分比等信息，执行的次数多，占比比较大的SQL
2.IO大的SQL：Rows examine项，扫描的行数越多，IO越大
3.未命中索引的SQL：Rows examine 和Rows Send的对比差
# Profile
# Rank Query ID Response time Calls R/Call
# ==== =================================== =============== ===== ========
# 1 0xAC8DD5BBF3975693C05247449313884D 208.7543 100.0% 1 208.7543 0.00 CALL insert_temp
# Query 1: 0 QPS, 0x concurrency, ID 0xAC8DD5BBF3975693C05247449313884D 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 2021-12-23T03:22:14
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 100 1
# Exec time 100 209s 209s 209s 209s 209s 0 209s
# Lock time 100 139us 139us 139us 139us 139us 0 139us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 100 18 18 18 18 18 0 18
# String:
# Databases demo
# Hosts 125.71.203.164
# Users root
# Query_time distribution
其他常用命令
查看服务器信息：pt-summary
查看磁盘开销信息：pt-diskstats
查看mysql数据库信息：pt-mysql-summary --user=root --password=123456
查看死锁信息：pt-deadlock-logger --run-time=10 --interval=3 --create-dest-table --dest D=test,t=deadlocks u=root,p=123456
从慢查询日志中分析索引使用情况：pt-index-usage --user=root --password=123456 --host=localhost /www/server/data/mysql-slow.log
从慢查询日志中找数据库表中重复的索引：pt-duplicate-key-checker --host=localhost --user=root --password=123456
查看mysql表和文件的当前活动IO开销，执行耗时不要在高峰时用：pt-ioprofile
pt-find查找mysql表和执行命令
1.查找数据库中大于1M的表：pt-find --user=root --password=123456 --tablesize +1M
2.查看表和索引大小并排序：pt-find --user=root --password=123456 --printf "%T\t%D.%N\n" | sort -rn
pt-kill 杀掉符合标准的mysql进程
1.显示查询时间大于3秒的查询：pt-kill --user=root --password=123456 --busy-time 3 --print
2.kill掉大于3秒的查询：pt-kill --user=root --password=123456 --busy-time 3 --kill
集群相关命令
查看从库和同步状态：pt-slave-find --host=localhost --user=root --password=123456
查看不同配置文件的差异：pt-config-diff /etc/my.cnf /root/my_master.cnf
查看授权，授权复制：
pt-show-grants --user=root --password=123456
pt-show-grants --user=root --password=123456 --separate --revoke
验证数据库复制的完整性，主从复制后检验：pt-table-checksum --user=root --password=123456
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/136991.html
