聊聊数据库~6.SQL运维中篇 (2)

临时开启参考

# 开启 set global general_log = 1; # set [global | persist] general_log_file = '日志路径'; set global log_output = 'TABLE'; 4.slow_query_log(慢查询日志)

这个是最常用的,把符合条件的查询语句记录在日志中,一般都是些需要优化的SQL

PS:出现性能瓶颈的时候,或者为了优化SQL会开启一段时间(小项目推荐直接开启)

先看下默认值:show variables like '%slow%';、show variables like 'long%';

2.慢查询.png

SQL查询

-- 是否开启 select @@slow_query_log; -- 默认是关闭 -- CentOS:/var/lib/mysql/localhost-slow.log -- Ubuntu:/var/lib/mysql/ubuntuserver-slow.log select @@slow_query_log_file; -- 条件:设置超过多少秒为慢查询(一般设置1s) select @@long_query_time; -- 默认是10s(支持小数:0.003) -- PS:设置为0就会记录所有SQL(不推荐这么干) -- 条件:没有使用索引的查询记录到日志中 select @@log_queries_not_using_indexes; -- 默认是0(不开启) -- 记录optimize table、analyze table和alter table的管理语句 select @@log_slow_admin_statements; -- 默认是0(不开启) -- 记录由Slave所产生的慢查询 select @@log_slow_slave_statements;

常用设置

PS:高并发下的互联网项目,对SQL执行时间的容忍度一般都是低于300~500ms的(long_query_time=0.05)

# 常用如下:(需要MySQL的root权限) set global slow_query_log = 1; # 开启慢查询日志 set global long_query_time = 1; # 记录大于1s的SQL set global log_slow_admin_statements = 1; # 记录管理语句 set global log_queries_not_using_indexes = 1; # 记录没有使用索引的SQL # set [global | persist] slow_query_log_file = '路径'; # 设置log路径

设置long_query_time时,需要重新连接才能生效(不需要重启DB)

PS:当前会话不生效,之后的会话就生效了(不想重连可以再设置下当前会话的long_query_time)

知识拓展:(chown mysql:mysql /work/log/xxx.log)

https://shihlei.iteye.com/blog/2311752

https://www.cnblogs.com/1021lynn/p/5328495.html

扩展:慢查询工具

先简单分析下慢查询日志:

# Time: 2019-05-22T21:16:28.759491+08:00 # User@Host: root[root] @ localhost [] Id: 11 # Query_time: 0.000818 Lock_time: 0.000449 Rows_sent: 5 Rows_examined: 5 SET timestamp=1558530988; select * from mysql.user order by host; # SQL语句

Time:查询的执行时间(start_time)

User@Host: root[root] @ localhost [] Id:11:执行 sql 的主机信息

Query_time:SQL查询

Lock_time:锁定时间

Rows_sent:所发送的行数

Rows_examined:锁扫描的行数

SET timestamp=1558530988;:SQL执行时间

现在可以说说工具了,推荐两款:

自带的慢日志分析工具:mysqldumpslow

MySQL工具箱(percona-toolkit)中的pt-query-digest

mysqldumpslow(精简)

查询最慢的10条SQL:mysqldumpslow -s t -t 10 /var/lib/mysql/localhost-slow.log

-s 按照那种方式排序 t: 查询时间 c:访问计数 l:锁定时间 r:返回记录 al:平均锁定时间 ar:平均访问记录数 at:平均查询时间 -t 返回多少条数据(可以理解为top n) -g 可以跟上正则匹配模式,大小写不敏感。

PS:使用mysqldumpslow的分析结果不会显示具体完整的sql语句:

翻页sql不一样,性能也是不一样的,越往后的页数越容易出现慢查询,而mysqldumpslow把所有翻页sql当成一个sql了

eg:select * from tb_table where uid=20 group by createtime limit 10000, 1000; ==> select * from tb_table where uid=N group by createtime limit N, N;

不管你uid和limit怎么变,mysqldumpslow认为是一样的

pt-query-digest(推荐)

官方文档:https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html

分析慢查询日志:pt-query-digest /var/lib/mysql/localhost-slow.log

使用tcppdump捕获MySQL协议数据,然后报告最慢的查询:

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

pt-query-digest --type tcpdump mysql.tcp.txt

查看来自远程进程列表上最慢的查询:

pt-query-digest --processlist h=ip

安装可以参考:

PS:percona-toolkit的常用工具我也在里面简单说了下,对应文档也贴了

other

PS:还有一款mysqlsla我没用过,所以贴个参考文章,感兴趣的同志自己研究下

https://www.cnblogs.com/fengchi/p/6187099.html

知识拓展:https://www.cnblogs.com/fengchi/p/6187099.html

5.binary_log(二进制日志)

上节主要说了通用日志和慢查日志,今天说下二进制日志:

二进制日志算是最常用的了,主要就是记录对数据库的修改,然后就是主从复制用的比较多(比如增量备份)

PS:记录了修改操作,那么衍生出的场景就是:增量备份和恢复(基于时间点的备份和恢复)

PS:MySQL日志主要分为这两类:(互不干扰)

服务层日志(和使用存储引擎无关)

通用日志、慢查询日志、二进制日志

存储引擎层日志

eg:innodb的重做日志(redo log)和回滚日志(undo log)

Q:那什么样的修改会记录下来呢?

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/wsxwsz.html