0%

mysql的缓慢日志

发表于 分类于 阅读次数：

简介

mysql有4种日志，分别为错误日志，一般查询日志，慢查询日志，二进制日志

错误日志

错误日志是最重要的日志之一，它记录了MySQL服务启动和停止正确和错误的信息，还记录了mysqld实例运行过程中发生的错误事件信息，mysql默认开启，在my.cnf中log-error=/var/log/mysqld.log 配置对应错误日志的位置。

一般查询日志

一般查询日志，记录所有操作记录都记录在日志中，默认不开启，一般也不建议开启，这样会造成I/O等资源的浪费，对于查询分析我们开源开启慢查询日志。

二进制日志

二进制日志包含了引起或可能引起数据库改变(如delete语句但没有匹配行)的事件信息，但绝不会包括select和show这样的查询语句。语句以”事件”的形式保存，所以包含了时间、事件开始和结束位置等信息。mysql中主从同步采用二进制日志来进行同步，由my.cnflog-bin来控制,具体配置可查看hive分类中的hive主从配置这篇文章

慢查询日志

查询超出变量 long_query_time 指定时间值的为慢查询。但是查询获取锁(包括锁等待)的时间不计入查询时间内。
mysql记录慢查询日志是在查询执行完毕且已经完全释放锁之后才记录的，因此慢查询日志记录的顺序和执行的SQL查询语句顺序可能会不一致(例如语句1先执行，查询速度慢，语句2后执行，但查询速度快，则语句2先记录)

慢查询日志配置
  • 在my.cnf中 
1
2
3
4
5
6
7
8
#是否启用慢日志
slow_query_log=ON
#慢日志文件的路径
slow_query_log_file=/var/log/mysql-slow.log
#指定慢查询超时时长(默认10秒)，超出此时长的属于慢查询
long_query_time=0.5
#没有使用索引时候记录慢日志
log_queries_not_using_indexes=OFF
慢语句分析

当初的慢日志文件如下,记录着查询时间，锁时间，和执行的记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

/usr/sbin/mysqld, Version: 8.0.17 (MySQL Community Server - GPL). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2019-10-15T03:47:35.922001Z
# User@Host: root[root] @  [192.168.169.58]  Id:     8
# Query_time: 2.061037  Lock_time: 0.000320 Rows_sent: 712182  Rows_examined: 712182
use test;
SET timestamp=1571111253;
SELECT * FROM d_area;
# Time: 2019-10-15T03:48:16.302963Z
# User@Host: root[root] @  [192.168.169.58]  Id:     8
# Query_time: 1.250141  Lock_time: 0.000096 Rows_sent: 712182  Rows_examined: 712182
SET timestamp=1571111295;
SELECT * FROM d_area;

这样查询日志很不直观，mysql提供了mysqldumpslow来查看缓慢日志,用法如下 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysqldumpslow -s [Parameter] -t [Parameter] 缓慢日志路径
参数
-s : 排序方式

c：query执行的次数
t：sql执行的时间
l：lock锁表的时间
r：sql返回的行数
-t： top，返回前n条数据

-g： 正则匹配，大小写不敏感

返回执行时间最长的前两个query
mysqldumpslow -s t -t 2 /var/log/mysql-slow.log

显示执行次数最多的前两个query
mysqldumpslow -s c -t 2 /var/log/mysql-slow.log

返回按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql-slow.log

执行 

1
mysqldumpslow -s t -t 2 /var/log/mysql-slow.log

结果如下

1
2
3
4
5
6
Reading mysql slow query log from /var/log/mysql-slow.log
Count: 2  Time=1.66s (3s)  Lock=0.00s (0s)  Rows=712182.0 (1424364), root[root]@[192.168.169.58]
  SELECT * FROM d_area

Count: 2  Time=0.64s (1s)  Lock=0.00s (0s)  Rows=61757.5 (123515), root[root]@[192.168.169.58]
  SELECT * FROM d_area where province_ like 'S' or town_ like 'S'
注意

配置的slow_query_log_file（慢日志路径）的文件，mysql账号需要有读写权限执行

1
chown mysql:mysql /路径

否则查询 ,语句状态一直未on

1
show variables like '%slow_query_log%';

设置会话基本缓慢日志

以上设置的缓慢日志，或者当缓慢日志过大清理时，需要重启数据库，但是在生产环境中是不允许这样做的，所以我们采用SET变量的方法去设置缓慢日志。

  • 关闭缓慢日志服务
1
set global slow_query_log=OFF;
  • 查看缓慢日志时候关闭
1
show variables like '%slow%';
  • 迁移缓慢日志
1
直接用linux mv命令或者其它方法迁移缓慢日志
  • 重新设置缓慢日志路径
1
set global slow_query_log_file='/路径';
  • 重新开启缓慢日志
1
set global slow_query_log=ON;
实际操作方案

实际生产环境中可以采用上述方案，每天编写脚本完成缓慢日志迁移.