乐趣区

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

一条慢查问会造成什么结果?刚开始应用 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 慢日志查问问题,提供的几种解决办法。在理论利用过程中,咱们还是要多尝试不同维度的解决方案,并联合本身所处行业、业务等特点,筛选适宜本人和团队应用的数据库剖析工具,保障系统和业务的稳固。

退出移动版