乐趣区

关于云计算:被MySQL慢日志查询搞废了3分钟教你快速定位慢查询问题

jiezi

一条慢查问会造成什么结果？刚开始应用 MySQL 的开发、高级 DBA 认为就是简略的查问变慢些，体验略微有一丢丢影响，殊不知，慢查问的破坏力远不止如此。业务高峰期，这头 SQL 还没解决完，大量新的查问申请沉积，CPU 使用率长时间居高不下，甚至高达 100%，零碎间接解体……慢查问这样的黑天鹅事件，可能间接影响业务稳定性，造成微小经济损失。

慢查问，字面了解就是查问慢的意思，比方某类查问，失常状况下耗费 100ms 左右，异样时可能飙升到 15s。为定位慢查问问题，咱们能够按如下几个步骤进行：
一、开启慢日志；
二、应用慢日志查问剖析管理工具；
三、基于已有的慢日志剖析，对系统自身做优化（如查问语句或表结构设计方面）。
 

启用慢日志，定位异样

慢日志默认状况下时不开启的，如果须要对 SQL 做优化，能够开启这个性能。登陆 MySQL 后，执行如下 SQL 语句即可开启慢日志（这里以 MySQL 5.7.33 为例，其它版本根本通用）：

SET GLOBAL slow_query_log = 'ON';
-- 未应用索引的查问也认为是一个可能的慢查问
set global log_queries_not_using_indexes = 'ON';

个别状况下，MySQL 慢日志位于 /var/lib/mysql/-slow.log，咱们能够模仿一个慢查问，而后即可看到慢日志记录产生：

-- 手动触发一个慢查问：MySQL 默认认为，一个大于 10s 的查问就是慢查问
SELECT sleep(11);

看下慢查问日志：

$ sudo cat /var/lib/mysql/ubt-server-slow.log

/usr/sbin/mysqld, Version: 5.7.33-0ubuntu0.18.04.1 ((Ubuntu)). started with:

Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock

Time: 2021-03-12T08:52:54.227174Z

User@Host: df-test[df-test] @  [10.100.64.118]  Id:     2

Query_time: 11.000551  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0

use number1;

SET timestamp=1615539174;

select sleep(11);

从该日志中，咱们能看到如下几个信息（依据不同的 MySQL 版本或者配置，这些信息可能有增减）：

• 产生工夫：2021-03-12T08:52:54.227174Z

• 起源：df-test[df-test] @  [10.100.64.118]，即用户 df-test 在

10.100.64.118 这个机器上执行了这个查问

• 查问统计：如耗费的工夫，发送 / 接管的行数

• 具体的 SQL 语句

从这些信息中，咱们能够比拟清晰地晓得这个慢查问的前因后果，比拟准确地定位具体的业务代码。但这里有个问题，为保障 MySQL 数据库安全，MySQL 要求只有登录到具体服务器能力看到慢查问日志详情，这间接影响到对异样呈现时的解决效率，连累异样位置、剖析和解决的进度。

除开启零碎自带的慢日志之外，还有什么无效的形式能让开发人员疾速、间接且精确地解决这个问题？

应用 MySQL 慢日志剖析工具

罕用的对慢 SQL 做优化的剖析工具有：mysqldumpslow、mysqlsla、mysql-explain-slow-log、mysql-log-filter、myprofi。

这里以 mysqldumpslow 和 mysql-log-filter 为例。

01 mysqldumpslow

mysqldumpslow 是官网提供的慢查问日志剖析工具。次要性能包含统计不同慢 sql 的

  • 呈现次数 (Count)
  • 执行消耗的均匀工夫和累计总消耗工夫 (Time)
  • 期待锁消耗的工夫 (Lock)
  • 发送给客户端的行总数 (Rows)
  • 扫描的行总数 (Rows)
  • 用户以及 SQL 语句自身 (形象了一下格局，比方 limit 1, 20 用 limit N,N 示意)

可参考：《4.6.9 mysqldumpslow- 汇总慢查问日志文件》
https://dev.mysql.com/doc/refman/8.0/en/mysqldumpslow.html

02 mysql-log-filter

google code 上找到的一个剖析工具，提供了 python 和 php 两种可执行的脚本。基本功能比官网的 mysqldumpslow 多了查问工夫的统计信息 (均匀、最大、累计)，其余相似。特色性能除了统计信息外，针对输入内容做了排版和格式化，保障整体输入的简洁。举荐给喜爱简洁报表的敌人。


可参考：
http://code.google.com/p/mysql-log-filter/

其余几款工具，这里就不再赘述，有趣味的敌人能够间接从网上搜一下。下面介绍的这些工具，多多少少会有一些应用上的小问题，要么数据缺失，要么配置麻烦，诸如此类。上面介绍一下一站式数据监测云平台（DataFlux）的解决方案。

03 DataFlux 计划

如前所述，慢日志具备很大的破坏力，为进一步实现 MySQL 数据库性能优化，咱们须要解决如下几个问题：
• 数据采集
• 数据解析
• 数据存储
• 数据展现以及查问

在 DataFlux 中有专用于各种数据采集的工具——DataKit。针对 MySQL，它提供了各种 MySQL 日志的采集能力。这里咱们介绍下 DataKit 采集器在 Linux 平台的根本应用。

首先，咱们登录 DataFlux 官网注册并登录会员账号，接着可依照下图（控制台 - 治理 - 数据网关），或参考链接 2 找到并装置 DataKit。

参考链接：

  1. DataFlux 官网：https://www.dataflux.cn/
  2. 《DataKit 装置》：https://help.dataflux.cn/doc/0c6ebce225784bd2ad994d5f89c5dbc89e025792#toc34

装置好 DataKit 后，在 /usr/local/cloudcare/dataflux/datakit/conf.d/log/ 目录下，复制一份 MySQL 日志采集配置 

$ sudo cp mysqlog.conf.sample mysqlog.conf

编辑 mysqlog.conf：[[inputs.tailf]]
填写各种 MySQL 日志的文件门路，不同版本可能不同
留神，这里只反对文本文件。咱们这里应用的版本是 MySQL 5.7.33

logfiles = [
"/var/lib/mysql/*.log",
"/var/log/mysql/mysql.log",
"/var/log/mysql/error.log",
]

source = "mysqlog"

指定服务名
service = "mysqlog"

专用的日志解析脚本（DataKit 曾经内置了）pipeline = "mysql.p"

[inputs.tailf.tags]
这里能够增加一些标签，比方：biz = "订单零碎"

省略其它默认配置...

至此，MySQL 的日志采集就配置好了，重新启动一下 DataKit 的即可（数据须要稍等一会能力在 Dataflux 平台看到）

参考链接：《DataKit 不同零碎的重启形式》
https://help.dataflux.cn/doc/0c6ebce225784bd2ad994d5f89c5dbc89e025792#toc27

接下来，咱们就能在 DataFlux 平台看到对应的日志了：

从图中咱们能够看到，SQL 的执行工夫（query\_time）曾经提取进去了，就是上文慢日志中看到的工夫。沿着这条日志，点进去，即可看到日志详情：

从日志详情图中，咱们能够看到红框标记的慢查问 SQL 语句，另外还有其它提取进去的日志信息，比方查问工夫、起源、服务器主机名、申请发送的数据行数等信息。

除此之外，咱们在拉进去的日志详情中，还可看到以后这台主机在慢日志产生的那个工夫点左近（红色虚竖线）的资源占用状况（诸如 CPU、内存、磁盘、网络等信息），在肯定水平上能帮忙开发人员更好的解决问题。

至此，咱们解决了 MySQL 慢日志的采集、解析以及展现问题。当初数据曾经有了，开发人员就能不便的在网页上找到对应的慢查问日志，并且综合 MySQL 服务器的整体资源占用状况，给出更加正当的解决方案。

以上便是明天咱们针对 MySQL 慢日志查问问题，提供的几种解决办法。在理论利用过程中，咱们还是要多尝试不同维度的解决方案，并联合本身所处行业、业务等特点，筛选适宜本人和团队应用的数据库剖析工具，保障系统和业务的稳固。

退出移动版