记录一次数据库CPU被打满的排查过程

1 前言

近期随着数据量的增长,数据库CPU使用率100%报警频繁起来。第一个想到的就是慢Sql,我们对未合理运用索引的表加入索引后,问题依然没有得到解决,深入排查时,发现在 order by id asc limit n时,即使where条件已经包含了覆盖索引,优化器还是选择了错误的索引导致。通过查询大量资料,问题得到了解决。这里将解决问题的思路以及排查过程分享出来,如果有错误欢迎指正。

2 正文

2.1 环境介绍

记录一次数据库CPU被打满的排查过程

2.2 发现问题

22日开始,收到以下图1报警变得频繁起来,由于数据库中会有大数据推数动作,数据库CPU偶尔报警并没有引起对该问题的重视,直到通过图2对整日监控数据分析时,才发现问题的严重性,从0点开始,数据库CPU频繁被打满。

记录一次数据库CPU被打满的排查过程

图1:报警图

记录一次数据库CPU被打满的排查过程

图2:整日CPU监控图

2.3 排查问题

发现问题后,开始排查慢Sql,发现很多查询未添加合适的索引,经过一轮修复后,问题依然没有得到解决,在深入排查时发现了一个奇怪现象,SQL代码如下(表名已经替换),比较简单的一个单表查询语句。

SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
AND id > 2100
ORDER BY
id
LIMIT 500;

看似比较简单的查询,但执行时长平均在90s以上,并且调用频次较高。如图3所示。

记录一次数据库CPU被打满的排查过程

图3:慢Sql平均执行时长

开始检查表信息,可以看到表数据量在2100w左右。

记录一次数据库CPU被打满的排查过程

图4:数据表情况

排查索引情况,主键为id,并且有business_day与full_ps_code的联合索引。

PRIMARY KEY (id) USING BTREE,
KEY idx_business_day_full_ps_code (business_day,full_ps_code)
==========以下索引可以忽略========
KEY idx_erp_month_businessday (erp,month,business_day),
KEY idx_business_day_erp (business_day,erp),
KEY idx_erp_month_ps_plan_id (erp,month,ps_performance_plan_id),
......

通过Explain查看执行计划时发现,possible_keys中包含上面的联合索引,而Key却选择了Primary主键索引,扫描行数Rows为1700w,几乎等于全表扫描。

记录一次数据库CPU被打满的排查过程

图5:执行计划情况

2.4 解决问题

第一次,我们分析是,由于Where条件中包含了ID,查询分析器认为主键索引扫描行数会少,同时根据主键排序,使用主键索引会更加合理,我们试着添加以下索引,想要让查询分析器命中我们新加的索引。

ADD INDEX idx_test(business_day, full_ps_code, id) USING BTREE;

再次通过Explain语句进行分析,发现执行计划完全没变,还是走的主键索引。

explain
SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
AND id > 2100
ORDER BY
id
LIMIT 500;

记录一次数据库CPU被打满的排查过程

图6:执行计划情况

第二次,我们通过强制指定索引方式 force index (idx_test)方式,再次分析执行情况,得到图7的结果,同样的查询条件同样的结果,查询时长由90s->0.49s左右。问题得到解决

记录一次数据库CPU被打满的排查过程

图7:强制指定索引后执行计划情况

记录一次数据库CPU被打满的排查过程

第三次,我们怀疑是where条件中有ID导致直接走的主键索引,where条件中去掉id,Sql调整如下,然后进行分析。依然没有命中索引,扫描rows变成111342,查询时间96s

SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
ORDER BY
id
LIMIT 500

记录一次数据库CPU被打满的排查过程

记录一次数据库CPU被打满的排查过程

第四次,我们把order by去掉,SQL调整如下,然后进行分析。命中了idx_business_day_full_ps_code之前建立的联合索引。扫描行数变成154900,查询时长变为0.062s,但是发现结果与预想的不一致,发生了乱序

SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
AND id > 2100
LIMIT 500;

记录一次数据库CPU被打满的排查过程

记录一次数据库CPU被打满的排查过程

第五次,经过前几次的分析可以确定,order by 导致查询分析器选择了主键索引,我们在Order by中增加排序字段,将Sql调整如下,同样可以命中我们之前的联合索引,查询时长为0.034s,由于先按照主键排序,结果是一致的。相比第四种方法多了一份filesort,问题得解决。

SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
AND id > 2100
ORDER BY
id,full_ps_code
LIMIT 500;

记录一次数据库CPU被打满的排查过程

记录一次数据库CPU被打满的排查过程

第六次,我们考虑是不是Limit导致的问题,我们将Limit 500 调整到 1000,Sql调整如下,奇迹发生了,命中了联合索引,查询时长为0.316s,结果一致,只不过多返回来500条数据。问题得到了解决。经过多次实验Limit 大于695时就会命中联合索引,查询条件下的数据量是79963,696/79963大概占比是0.0087,猜测当获取数据比超过0.0087时,会选择联合索引,未找到源代码验证此结论。

SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
AND id > 2100
ORDER BY
id
LIMIT 1000;

记录一次数据库CPU被打满的排查过程

记录一次数据库CPU被打满的排查过程

经过我们的验证,其中第2、5、6三种方法都可以解决性能问题。为了不影响线上,我们立即修改代码,并选择了force index 的方式,上线观察一段时间后,数据库CPU恢复正常,问题得到了解决。

记录一次数据库CPU被打满的排查过程

3 事后分析

上线后,问题解决了,同时也给我留下了很多疑问。

[En]

After the launch, the problem was solved, and at the same time, it left me a lot of questions.

  • 为什么明明where条件中包含了联合索引,却未能命中,反而选择了性能较慢的主键索引?
  • 为什么在order by中增加了一个索引其他字段,就可以命中联合索引了呢?
  • 为什么我仅仅是将limit限制条件由原来的500调大后,也能命中联合索引呢?

这一切的答案都来自MySQL的查询优化器。

3.1 查询优化器

查询优化器是专门负责优化查询语句的优化器模块。查询优化器通过计算和分析收集到的各种系统统计信息,给出了查询的最优执行计划–最优数据检索方法。

[En]

The query optimizer is an optimizer module which is specially responsible for optimizing query statements. By calculating and analyzing all kinds of system statistical information collected, the query optimizer gives the optimal execution plan-the optimal data retrieval method for the query.

优化器决定如何执行查询的方式是基于一种称为基于代价的优化的方法。5.7在代价类型上分为IO、CPU、Memory。内存的代价收集了,但是并没有参与最终的代价计算。Mysql中引入了两个系统表,mysql.server_cost和mysql.engine_cost,server_cost对应CPU的代价,engine_cost代表IO的代价。

server_cost(CPU代价)
  • row_evaluate_cost (default 0.2) 计算符合条件的行的代价,行数越多,此项代价越大
  • memory_temptable_create_cost (default 2.0) 内存临时表的创建代价
  • memory_temptable_row_cost (default 0.2) 内存临时表的行代价
  • key_compare_cost (default 0.1) 键比较的代价,例如排序
  • disk_temptable_create_cost (default 40.0) 内部myisam或innodb临时表的创建代价
  • disk_temptable_row_cost (default 1.0) 内部myisam或innodb临时表的行代价

由上可以看出创建临时表的代价是很高的,尤其是内部的myisam或innodb临时表。

engine_cost(IO代价)
  • io_block_read_cost (default 1.0) 从磁盘读数据的代价,对innodb来说,表示从磁盘读一个page的代价
  • memory_block_read_cost (default 1.0) 从内存读数据的代价,对innodb来说,表示从buffer pool读一个page的代价

这些信息都可以在数据库中配置,当数据库中未配置时,从MySql源代码(5.7)中可以看到以上默认值情况

记录一次数据库CPU被打满的排查过程

3.2 代价配置

--修改io_block_read_cost值为2
UPDATE mysql.engine_cost
SET cost_value = 2.0
WHERE cost_name = 'io_block_read_cost';
--FLUSH OPTIMIZER_COSTS 生效,只对新连接有效,老连接无效。
FLUSH OPTIMIZER_COSTS;

3.3 代价计算

代价是如何算出来的呢,通过读MySql的源代码,可以找到最终的答案

3.3.1 全表扫描(table_scan_cost)

以下代码摘自MySql Server(5.7分支),全表扫描时,IO与CPU的代价计算方式。

double scan_time=
cost_model->row_evaluate_cost(static_cast<double>(records)) + 1;
// row_evaluate_cost 核心代码
// rows * m_server_cost_constants->row_evaluate_cost()
// 数据行数 * 0.2 (row_evaluate_cost默认值) + 1 = CPU代价
Cost_estimate cost_est= head->file->table_scan_cost();
//table_scan_cost 核心代码
//const double io_cost
// = scan_time() * table->cost_model()->page_read_cost(1.0)
// 这部分代价为IO部分
//page_read_cost 核心代码
//
//const double in_mem= m_table->file->table_in_memory_estimate();
//
// table_in_memory_estimate 核心逻辑
//如果表的统计信息中提供了信息,使用统计信息,如果没有则使用启发式估值计算
//pages=1.0
//
//const double pages_in_mem= pages * in_mem;
//const double pages_on_disk= pages - pages_in_mem;
//
//
//计算出两部分IO的代价之和
//const double cost= buffer_block_read_cost(pages_in_mem) +
// io_block_read_cost(pages_on_disk);
//
//
//buffer_block_read_cost 核心代码
// pages_in_mem比例 * 1.0 (memory_block_read_cost的默认值)
// blocks * m_se_cost_constants->memory_block_read_cost()
//
//
//io_block_read_cost 核心代码
//pages_on_disk * 1.0 (io_block_read_cost的默认值)
//blocks * m_se_cost_constants->io_block_read_cost();
//返回IO与CPU代价
//这里增加了个系数调整,原因未知
cost_est.add_io(1.1);
cost_est.add_cpu(scan_time);

根据源代码分析,当表包含100行数据时,全表扫描的成本为23.1,计算逻辑如下。

[En]

According to the source code analysis, when the table contains 100 rows of data, the cost of the full table scan is 23.1, and the calculation logic is as follows.

//CPU代价 = 总数据行数 * 0.2 (row_evaluate_cost默认值) + 1
cpu_cost = 100 * 0.2 + 1 等于 21
io_cost = 1.1 + 1.0 等于 2.1
//总成本 = cpu_cost + io_cost = 21 + 2.1 = 23.1

验证结果如下图

记录一次数据库CPU被打满的排查过程
3.3.2 索引扫描(index_scan_cost)

以下代码摘自MySql Server(5.7分支),当出现索引扫描时,是如何进行计算的,核心代码如下

//核心代码解析
*cost= index_scan_cost(keyno, static_cast<double>(n_ranges),
static_cast<double>(total_rows));
cost->add_cpu(cost_model->row_evaluate_cost(
static_cast<double>(total_rows)) + 0.01)

io代价计算核心代码

//核心代码
const double io_cost= index_only_read_time(index, rows) *
table->cost_model()->page_read_cost_index(index, 1.0);
// index_only_read_time(index, rows)
// 估算index占page个数
//page_read_cost_index(index, 1.0)
//根据buffer pool大小和索引大小来估算page in memory和in disk的比例,计算读一个page的代价

cpu代价计算核心代码

add_cpu(cost_model->row_evaluate_cost(
static_cast<double>(total_rows)) + 0.01);
//total_rows 等于索引过滤后的总行数
//row_evaluate_cost 与全表扫描的逻辑类似,
//区别在与一个是table_in_memory_estimate一个是index_in_memory_estimate
3.3.3 其他方式

计算代价的方式有很多,其他方式请参考 MySql原代码。https://github.com/mysql/mysql-server.git

3.4 深度解析

通过查看optimizer_trace,可以了解查询优化器是如何选择的索引。

set optimizer_trace="enabled=on";
--如果不设置大小,可能导致json输出不全
set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
SELECT
*
FROM
test
WHERE
is_delete = 0
AND business_day = '2021-12-20'
AND full_ps_code LIKE 'xxx%'
AND id > 0
ORDER BY
id
LIMIT 500;
select * FROM information_schema.optimizer_trace;
set optimizer_trace="enabled=off";

通过分析rows_estimation节点,可以看到通过全表扫描(table_scan)的话的代价是 8.29e6,同时也可以看到该查询可以选择到主键索引与联合索引,如下图。

记录一次数据库CPU被打满的排查过程

上图中全表扫描的代价是8.29e6,我们转换成普通计数法为 8290000,如果使用主键索引成本是 3530000,联合索引 185881,最小的应该是185881联合索引,也可以看到第一步通过成本分析确实选择了我们的联合索引。

记录一次数据库CPU被打满的排查过程

记录一次数据库CPU被打满的排查过程

记录一次数据库CPU被打满的排查过程

但是为什么选择主键索引呢?

[En]

But why did you choose the primary key index?

通过往下看,在reconsidering_access_paths_for_index_ordering节点下, 发现由于Order by 导致重新选择了索引,在下图中可以看到主键索引可用(usable=true),我们的联合索引为not_applicable (不适用),意味着排序只能使用主键索引。

记录一次数据库CPU被打满的排查过程

接下来通过index_order_summary可以看出,执行计划最终被调整,由原来的联合索引改成了主键索引,就是说这个选择无视了之前的基于索引成本的选择。

记录一次数据库CPU被打满的排查过程

之所以有这样的选择,主要原因如下:

[En]

The main reasons why there is such an option are as follows:

The short explanation is that the optimizer thinks — or should I say hopes — that scanning the whole table (which is already sorted by the id field) will find the limited rows quick enough, and that this will avoid a sort operation. So by trying to avoid a sort, the optimizer ends-up losing time scanning the table.

从这段解释可以看出主要原因是由于我们使用了order by id asc这种基于 id 的排序写法,优化器认为排序是个昂贵的操作,所以为了避免排序,并且它认为 limit n 的 n 如果很小的话即使使用全表扫描也能很快执行完,所以它选择了全表扫描,也就避免了 id 的排序。

5 总结

查询优化器会基于代价来选择最优的执行计划,但由于order by id limit n的存在,MySql可能会重新选择一个错误的索引,忽略原有的基于代价选择出来的索引,转而选择全表扫描的主键索引。这个问题在国内外有大量的用户反馈,BUG地址 https://bugs.mysql.com/bug.php?id=97001 。官方称在5.7.33以后版本可以关闭prefer_ordering_index 来解决。如下图所示。

记录一次数据库CPU被打满的排查过程

另外在我们日常慢Sql调优时,可以通过以下两种方式,了解更多查询优化器选择过程。

`
–第一种
explain format=json
sql语句

Original: https://www.cnblogs.com/Jcloud/p/16642188.html
Author: 京东云开发者
Title: 记录一次数据库CPU被打满的排查过程

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/504910/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • 2022-6-2-mysql常用的命令

    mysql -uroot -proot -h192.168.56.10表示使用mysql的客户端进行连接数据库管理系统 -u后面是连接数据库的用户名,一般默认的情况下用户名都是ro…

    数据库 2023年5月24日
    0151
  • Java redisTemplate 使用 increment序列化问题

    添加key: ValueOperations redisTemplate.setValueSerializer(new StringRedisSerializer()); // 设…

    数据库 2023年6月9日
    0159
  • Linux进程管理

    进程管理 基本概念介绍 进程和线程 进程: 一个在内存中运行的应用程序,每一个进程都有自己独立的一块内存空间,并被分配一个ID号(PID),在Windows下可以打开任务管理器查看…

    数据库 2023年6月16日
    0137
  • 最新Spring Boot 跨域配置,亲测好用

    背景: 网上有很多跨域配置,但都存在各种各样问题;经过改良和测试后,最终形成一个稳定配置版本,我的Spring Boot版本是2.5.1 问题: 前后端分离后,进行联调,发现浏览器…

    数据库 2023年6月6日
    0160
  • Python–线程

    进程与线程的区别: 线程是程序执行的最小单位,而进程是操作系统分配资源的最小单位; 一个进程由一个或多个线程组成,线程是一个进程中代码的不同执行路线; 进程之间相互独立,但同一进程…

    数据库 2023年6月9日
    095
  • NopCommerce学习:MSSQL 2005 排序规则导致中文编码错误

    这两天学习电子商务开源项目NopCommerce,她的确做的很好,在电子商务开源项目中应该算是很棒的. 官方站点: http://www.nopcommerce.com/ 中文社区…

    数据库 2023年6月11日
    0116
  • 【01】Maven依赖插件之maven-dependency-plugin

    1、analyze:分析项目依赖,确定哪些是已使用已声明的,哪些是已使用未声明的,哪些是未使用已声明的 2、analyze-dep-mgt:分析项目依赖,列出已解析的依赖项与dep…

    数据库 2023年6月9日
    0128
  • Kafka集群部署

    404. 抱歉,您访问的资源不存在。 可能是网址有误,或者对应的内容被删除,或者处于私有状态。 代码改变世界,联系邮箱 contact@cnblogs.com 园子的商业化努力-困…

    数据库 2023年6月16日
    0122
  • [LeetCode]27. 移除元素

    给你一个数组 nums 和一个值 val,你需要 原地 移除所有数值等于 val 的元素,并返回移除后数组的新长度。 不要使用额外的数组空间,你必须仅使用 O(1) 额外空间并 原…

    数据库 2023年6月9日
    0121
  • RadonDB MySQL Kubernetes 2.2.1 发布!

    RadonDB MySQL Kubernetes[1] 于近日正式发布新版本 2.2.1[2]。该版本主要在用户管理、高可用组件等进行了优化,并修复一些问题。 感谢 @runkec…

    数据库 2023年5月24日
    0126
  • DRF补充数据库异常和Redis异常

    DRF补充数据库异常和Redis异常 (1)在项目适当位置新建exceptions.py,内容如下: from rest_framework.views import except…

    数据库 2023年6月14日
    0103
  • DNS 查询原理详解

    你可能会问,难道 DNS 服务器(比如 1.1.1.1)保存了世界上所有域名(包括二级域名、三级域名)的 IP 地址? 当然不是。DNS 是一个分布式系统,1.1.1.1 只是用户…

    数据库 2023年6月14日
    0170
  • Ubuntu 服务器安装 MySQL 远程数据库

    在 Web 项目中,我们需要使用到远程数据库,开发阶段也需要连接并查看数据库的状况。腾讯云、阿里云等云平台提供了远程数据库,可直接使用;当然也可以自己在部署 Web 的服务器上安装…

    数据库 2023年5月24日
    0122
  • [springmvc]乱码问题解决以及JSON和java对象转换的几种方法

    在web服务器配置中直接加上下面的过滤处理 encoding org.springframework.web.filter.CharacterEncodingFilter enco…

    数据库 2023年6月16日
    0129
  • java se 高级基础

    接口 继承树 Collection接口 Map接口 Collection 接口 Collection接口:单列集合,用来存储一个一个的对象 List接口:extends Colle…

    数据库 2023年6月16日
    0150
  • Win10系统-接口自动化测试持续集成

    使用工具:jdk+jmeter+Ant+jenkins jdk-1.8.0_241版本【安装参考链接:https://blog.51cto.com/u_15463439/52268…

    数据库 2023年6月14日
    0139
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球