MySQL 慢日志线上问题分析及功能优化
MySQL 慢日志(slow log)是 MySQL DBA 及其他开发、运维人员需经常关注的一类信息。使用慢日志可找出执行时间较长或未走索引等 SQL 语句,为进行系统调优提供依据。
本文将结合一个线上案例,分析如何正确设置 MySQL 慢日志参数和使用慢日志功能,并介绍下网易云 RDS 对 MySQL 慢日志功能的增强。
MySQL 参数组功能
网易云 RDS 实例提供了参数组管理功能,可通过参数管理界面查看绝大部分常用的 MySQL 系统参数,用户可了解当前运行值和建议值:
用户还可通过参数管理页面对所列的参数进行修改,点击 “修改参数” 按钮进行在线设置,点击 “保存修改” 即可一键完成 MySQL 主从节点的参数修改:
查看参数管理界面不难发现,与慢查询相关的参数比较多,那么,这些参数都是如何起作用的呢,相互关系又是如何,满足什么条件的 SQL 语句才会记录到慢日志中?只有了解这些才能更好地利用慢日志进行系统调优和问题定位。
下面,我们以这个线上案例为依托,介绍下该如何正确配置慢日志参数:
有用户报告,他们使用的多个 RDS 5.7 版本实例慢日志异常,明明执行了一分多钟的 SQL 语句,却没有记录到慢日志中。还提供了用于复现的 SQL 语句。
慢日志参数正确配置姿势
首先,我们需要确认该实例是否开启了慢日志功能,默认情况下,MySQL 慢日志功能是关闭的。慢日志开关参数为 slow_query_log,可在 mysqld 启动命令行或配置文件中显式指定,若指定 slow_query_log=1 或不指定值,则表示开启慢日志,赋值为 0 表示关闭。用户可以在运行时动态开启和关闭。
网易云 RDS 实例默认开启慢日志功能,我们确认了该用户未关闭实例的慢日志开关。
接下来,需确认慢日志记录位置,MySQL 使用 log_output 参数指定以文件(FILE)还是以表 (TABLE) 的方式来保存慢日志。需要强调的是,仅指定 log_output 而将 slow_query_log 置为 0 并不会记录慢日志,也就是说 slow_query_log 才是慢日志的开关。若使用文件形式记录慢日志,则可通过 slow_query_log_file 指定文件名,如果用户没有显式指定 slow_query_log_file,则 MySQL 将其初始化为 host_name-slow.log,host_name 即为运行 mysqld 的主机名,慢日志文件默认位于 MySQL 数据目录。
网易云 RDS 实例不允许用户修改日志文件路径,但可以配置 log_output 参数,通过查询,确认该实例以文件方式记录慢日志,查看日志文件确认没有用户所述的 SQL 语句。
由于用户提供了复现语句,我们执行了其 SQL 语句,确实 1 分多钟才返回,通过 explain 命令发现其未走索引,扫描了较多的记录数,再次查看慢日志仍没有记录该 SQL 语句。
MySQL 会记录满足执行时间超过 long_query_time 秒,扫描记录数超过 min_examined_row_limit 行的 SQL 语句。long_query_time 参数最小值和默认值分别为 1 和 10s,该参数可以精确到微秒(ms)。如果选择将慢日志记录到文件中,那么所记录的时间精确到微秒,如果记录到慢日志表(mysql.slow_log)中,那么仅精确到秒,微秒部分被忽略。
网易云 RDS 实例允许用户设置这两个参数值,那么是不是用户调整了上述两个阈值,导致无法满足记录条件呢,进一步查询发现也不是问题原因所在。
我们注意到 MySQL 还有个名为 log_queries_not_using_indexes 的参数用于控制是否记录未走索引的 SQL 查询,代码如下:
重点关注箭头所指内容,如果查询未走索引或者索引无效,且相关参数开启,那么 warn_no_index 设置为 true,若同时满足扫描记录数超过阈值,也会像慢查询一样被记录,那么是不是该参数未开呢?结果仍是否定的。
问题原因之所在
由于数据库实例中可能有较多不走索引的 SQL 语句,若开启 log_queries_not_using_indexes,则存在日志文件或表容量增长过快的风险,此时可通过设置 log_throttle_queries_not_using_indexes 来限制每分钟写入慢日志中的不走索引的 SQL 语句个数,该参数默认为 0,表示不开启,也就是说不对写入 SQL 语句条数进行控制。
启用后,系统会在第一条不走索引的查询执行后开启一个 60s 的窗口,在该窗口内,仅记录最多 log_throttle_queries_not_using_indexes 条 SQL 语句。超出部分将被抑制,在时间窗结束时,会打印该窗口内被抑制的慢查询条数以及这些慢查询一共花费的时间。下一个统计时间窗并不是马上创建,而是在下一条不走索引的查询执行后开启。
对应到该线上问题,log_throttle_queries_not_using_indexes 被设置为 10,在日志文件中看到周期性打印了如下内容:
确实符合上面描述的现象,用户的慢日志应该是被抑制了,汇总到了 359 里面去。我们尝试将 log_throttle_queries_not_using_indexes 设置为 0,再执行对应的 SQL 语句,果然在日志文件中记录了相应的 SQL 语句。
这个线上问题似乎已经定位到了,就是系统产生的不走索引的慢日志太多,而设置的 log_throttle_queries_not_using_indexes 太小,导致无法正常记录用户未走索引的慢日志。但还有一个疑惑点没有解决,那就是 log_throttle_queries_not_using_indexes 为 0 时,每分钟并没有打印超过10条慢日志,更没有 throttle 提示的 359 条这么多,那么设置为 10 的时候用户提供的那条 SQL 语句应该被记录到慢日志中才对啊,为何没有记录,原因何在?其实,仔细看下 MySQL 记录不走索引的日志的代码逻辑可以找到答案:
上图是记录慢日志的主逻辑,是否记录日志由函数 log_slow_applicable 控制,该函数先前已分析了一部分,我们进一步看该函数的其他相关内容,见下图红框:
Suppress_logging 是个决定性的变量,只有它为 false,该 SQL 语句才可能被记录。其结果就跟 log_throttle_queries_not_using_indexes 相关,我们进一步看下 log_throttle_qni.log 相关实现,如下图:
Eligible 即为 warn_no_index,inc_log_count() 函数在 1 分钟内不走索引的语句总数超过 log_throttle_queries_not_using_indexes 时返回值为 true,只有 warn_no_index 和 inc_log_count() 返回值都为 true,suppress_current 才为 true,而 suppress_current 即为 suppress_logging。
通过对上述 2 个截图内容进行分析,可以解答之前的疑惑点:
log_throttle_queries_not_using_indexes 统计的是所有不走索引的语句,其中有些语句因为不满足扫描记录数的约束而不会记录到慢日志中,这就是为什么该值为 10 的时候,慢日志文件中并没有 10 条记录。因为这 10 条中有 8 条 SQL 语句由于扫描记录数太少并没有被记录。
这也解惑了上图中 359 这个数字,它是这个时间窗内不走索引的 SQL 语句总数。所以,log_throttle_queries_not_using_indexes 是个很关键的参数,设置不当会无法正常记录不走索引的慢查询,导致慢日志功能部分失效。所以,用户首先需尽可能避免出现大量不走索引的 SQL 语句,可以通过 RDS 健康检查功能进行优化,再次,若出现慢日志中存在上述提示,应该调大 log_throttle_queries_not_using_indexes 的值,以便以进一步分析问题。
InnoSQL 慢日志功能增强
还有部分 RDS 实例用户问我们,为什么我的 SQL 语句执行时间没有超过所设置的 long_query_time,而且走了索引,但还是被记录到慢日志中,是不是出 Bug 了?其实这不是 Bug,而是因为网易云 RDS 使用的 InnoSQL(网易维护的 MySQL 开源分支)版本对慢日志做了优化,除了考察 SQL 语句的执行时间外,还关注该查询所需的磁盘页面(Disk Page)数,因为所需的页面数目过多,也可能会对系统负载造成较大影响。为了能够量化统计,我们收集了 SQL 查询所需读取的总页面数和这些页面中实际进行 IO 的次数,分别记录为 logical_reads和physical_reads,前者包括命中 InnoDB Buffer Pool 和未命中需要进行 IO 的页面请求。
通过引入 slow_query_type 和 long_query_io 两个参数为用户提供该功能。前者可设置为 0/1/2/3。“1” 表示启用基于执行时间来记录慢日志,“2” 表示基于搜索总页面数来记录慢日志,“3” 是 “1” 和 “2” 的合集。所以在 InnoSQL 中,SQL 查询只需满足执行时间够长或所需总页面数够多即可记录到慢日志中。代码实现片段如下:
页面数阈值通过 long_query_io 参数来衡量,用户可动态设置,如果总页面数 m_logical_reads 超过了该值,即使执行时间未超标,也会被记录。相应的,RDS 实例慢日志表结构和慢日志文件输出内容也增加了新的字段。
上图即为 InnoSQL 版的 slow_log 表结构,其中,logical_reads 和 physical_reads 为 InnoSQL 增加字段。同样的,慢日志文件的输出内容也增加了两个字段,如下所示:
除了以上详细描述的内容外,MySQL 慢日志模块还有如下几个特性值得关注:
○ 进行慢日志统计及慢日志中所记录的时间并不包括该 SQL 语句开始执行前获取锁所需等待的时间;
○ MySQL 在 SQL 语句执行完且所持有的锁均已释放后才将其写入慢日志中,所以慢日志中的 SQL 语句记录顺序并不能准确反映这些 SQL 语句的实际执行顺序;
○ 每条慢日志都包含一个时间戳,若写入文件中,log_timestamps 参数用于将慢日志时间戳转化为指定时区的时间。但该参数对于 mysql.slow_log 表中的慢日志不起作用;
○ 可通过设置 log_slow_slave_statements 来开启 MySQL 从库的慢日志功能;
○ ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE 等表管理操作也能够被记录到慢日志中,可通过 log_slow_admin_statements 选项开启。
作者:温正湖
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
看阿里大神如何扛住1.8亿/秒的双11数据洪峰?[转]
今年的双11再次刷新了记录——支付成功峰值达25.6万笔/秒、实时数据处理峰值4.72亿条/秒。 面对较去年增幅100%的数据洪峰,流计算技术可谓功不可没。今天,我们将揭开阿里流计算技术的神秘面纱。 双11刚刚拉下帷幕,激动的心还停留在那一刻: 当秒针刚跨过11号零点的一瞬间,来自线上线下的千万剁手党在第一时间涌入了这场年度大趴——从进入会场到点击详情页,再到下单付款一气呵成。 前台在大家狂欢的同时,后台数据流量也正以突破历史新高的洪峰形式急剧涌入: 支付成功峰值达 25.6 万笔/秒实时数据处理峰值 4.72亿条/秒 而作为实时数据处理任务中最为重要的集团数据公共层(保障着业务的实时数据、媒体大屏等核心任务),在当天的总数据处理峰值更是创历史新高达1.8亿/秒! 想象下,1秒钟时间内千万人涌入双11会场的同时,依然应对自如。 流计算的产生即来源于数据加工时效性的严苛需求: 由于数据的业务价值会随着时间的流失而迅速降低,因此在数据发生后必须尽快对其进行计算和处理,从而能够通过数据第一时间掌握业务情况。今年双11的流计算也面临着一场实时数据洪峰的考验。 首先来展示今年(2017年)较去年...
- 下一篇
Linux进程关闭和后台运行解析
摘要: 在Linux中,通过信号,关闭进程,通过忽略信号,并把进程挂载到init进程下,保证进程可以后台运行 1.问题背景 Java是跨平台的,大部分程序也都是在Linux服务器上运行的。但是很多朋友其实对服务器了解并不多,对相关知识也是一知半解。很多概念可能知道,但是并不十分清楚,仅仅是基本运用。可能很多新手朋友脱离了IDE或者web容器,都不知道怎么在服务器上运行程序。 举个简单的例子,相信很多朋友刚接触Linux的时候都遇到过这个问题。我们通过终端连接上服务器,通过命令行或者脚本运行了一个程序,然后关闭了终端连接,可能程序进程就消失了。我们也可能知道,在启动命令后面加&符号,可以进程在后台运行,关闭终端后,进程依然会消失,但如果通过shell脚本去运行,关闭终端连接后,程序可能不会消失。或者我们又知道可以通过nohup命令让进程在后台运行,并且关闭连接后,程序不会消失,到底为什么呢? 下面我会用比较通俗的语言进行讲解,如果有错误,欢迎指正。 2.进程关闭分析 当我们连接到服务器以后,运行的命令,启动的程序,设置的环境变量,都会挂在当前的连接中(非特殊设置或者特殊命令),当...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- CentOS8安装Docker,最新的服务器搭配容器使用
- CentOS7安装Docker,走上虚拟化容器引擎之路
- Linux系统CentOS6、CentOS7手动修改IP地址
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- Eclipse初始化配置,告别卡顿、闪退、编译时间过长
- Red5直播服务器,属于Java语言的直播服务器
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题
- Windows10,CentOS7,CentOS8安装MongoDB4.0.16
- Windows10,CentOS7,CentOS8安装Nodejs环境
- SpringBoot2整合Thymeleaf,官方推荐html解决方案