首页
学习
活动
专区
圈层
工具
发布
社区首页 >专栏 >33 | 我查这么多数据,会不会把数据库内存打爆?

33 | 我查这么多数据,会不会把数据库内存打爆?

作者头像
HaC
发布于 2020-12-30 17:51:12
发布于 2020-12-30 17:51:12
1.2K0
举报
文章被收录于专栏:HaC的技术专栏HaC的技术专栏

本文围绕一个问题展开: 假如主机内存只有 100G,现在要对一个 200G 的大表做全表扫描,会不会把数据库主机的内存用光了?

全表扫描对 server 层的影响

假如有如下全表扫描:

代码语言:javascript
复制
mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

InnoDB 的数据是保存在主键索引上的,所以全表扫描实际上是直接扫描表 t 的主键索引。 这条查询语句由于没有其他的判断条件,所以查到的每一行都可以直接放到结果集里面,然后返回给客户端。

取数据和发数据的流程:

  1. 取一行,写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的,默认是 16k。
  2. 重复获取行,直到 net_buffer 写满,调用网络接口发出去。如果发送成功,就清空 net_buffer,然后继续取下一行,并写入 net_buffer。
  3. 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK,就表示本地网络栈(socket send buffer)写满了,进入等待。直到网络栈重新可写,再继续发送。

流程如下:

这个流程中:

  1. 一个查询在发送过程中,占用的 MySQL 内部的内存最大就是 net_buffer_length ,并不会达到 200G;
  2. socket send buffer 也不可能达到 200G,如果 socket send buffer 被写满,就会暂停读数据的流程。

net_buffer_length 的最大值是 1G,这个值比 socket send buffer大(一般是几M)

也就是说,MySQL是“边读边发”的,如果客户端接收得慢,或者socket_receive_buffer ,堵住了,会导致客户端的结果发不出去,这个事务的执行时间就会变长。

如下是客户端不去读 socket receive buffer 中的内容,然后在服务端 show processlist 看到的结果:

如果你看到 State 的值一直处于“Sending to client”,就表示服务器端的网络栈写满了。

对于正常的线上业务来说,如果一个查询的返回结果不会很多的话,我都建议你使用 mysql_store_result 这个接口,直接把查询结果保存到本地内存。

MySQL 里看到很多个线程都处于“Sending to client”这个状态,可以这样处理:

  1. 评估业务,是否有必要返回这么多结果
  2. 把net_buffer_length参数设置大一点。

与“Sending to client”长相很类似的一个状态是“Sending data”。

一个查询语句的状态变化:

  1. MySQL 查询语句进入执行阶段后,首先把状态设置成“Sending data”;
  2. 然后,发送执行结果的列相关的信息(meta data) 给客户端;
  3. 再继续执行语句的流程;
  4. 执行完成后,把状态设置成空字符串。

也就是说,“Sending data”并不一定是指“正在发送数据”,而可能是处于执行器过程中的任意阶段。比如,一个锁等待的场景,就能看到 Sending data 状态。

比如:

我在mysql5.5版本看到的状态是这样的:

state的值是statistics,不知道是不是一样的。

总结: 就是说,仅当一个线程处于“等待客户端接收结果”的状态,才会显示"Sending to client";而如果显示成“Sending data”,它的意思只是“正在执行”。

全表扫描对 InnoDB 的影响

InnoDB 内存的一个作用,是保存更新的结果,再配合 redo log,就避免了随机写盘。

内存的数据页是在 Buffer Pool (BP) 中管理的,在 WAL 里 Buffer Pool 起到了加速更新的作用。而实际上,Buffer Pool 还有一个更重要的作用,就是加速查询。

如果此时有一个查询,可以直接在内存里拿结果,不用读磁盘,所以buffer Pool 还有加速查询的作用,加速查询的一个重要指标就是:内存命中率。

代码语言:javascript
复制
show engine innodb status

InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的,一般建议设置成可用物理内存的 60%~80%。

如果一个 Buffer Pool 满了,而又要从磁盘读入一个数据页,那肯定是要淘汰一个旧数据页的,有如下两种算法:

最近最少使用算法(Least Recently Used, LRU )

这个算法的核心就是淘汰最久未使用的数据。

InnoDB 管理 Buffer Pool 的 LRU 算法,是用链表来实现的。

  1. 链表头部是 P1,表示 P1 是最近刚刚被访问过的数据页;
  2. 假设内存里只能放下这么多数据页;这时候有一个读请求访问 P3,因此变成状态 2,P3 被移到最前面;
  3. 状态 3 表示,这次访问的数据页是不存在于链表中的,所以需要在 Buffer Pool 中新申请一个数据页 Px,加到链表头部。但是由于内存已经满了,不能申请新的内存。于是,会清空链表末尾 Pm 这个数据页的内存,存入 Px 的内容,然后放到链表头部。
  4. 从效果上看,就是最久没有被访问的数据页 Pm,被淘汰了。

缺点: 假设按照这个算法,我们要扫描一个 200G 的表,而这个表是一个历史数据表,平时没有业务访问它。那么,按照这个算法扫描的话,就会把当前的 Buffer Pool 里的数据全部淘汰掉,存入扫描过程中访问到的数据页的内容。也就是说 Buffer Pool 里面主要放的是这个历史数据表的数据。

此时Buffer Pool 的内存命中率急剧下降,磁盘压力增加,SQL 语句响应变慢。

改进的LRU算法

在 InnoDB 实现上,按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。图中 LRU_old 指向的就是 old 区域的第一个位置,是整个链表的 5/8 处。也就是说,靠近链表头部的 5/8 是 young 区域,靠近链表尾部的 3/8 是 old 区域。

  1. 状态 1,要访问数据页 P3,由于 P3 在 young 区域,因此和优化前的 LRU 算法一样,将其移到链表头部,变成状态 2。
  2. 之后要访问一个新的不存在于当前链表的数据页,这时候依然是淘汰掉数据页 Pm,但是新插入的数据页 Px,是放在 LRU_old 处。
  3. 处于 old 区域的数据页,每次被访问的时候都要做下面这个判断:
  • 若这个数据页在 LRU 链表中存在的时间超过了 1 秒,就把它移动到链表头部;
  • 如果这个数据页在 LRU 链表中存在的时间短于 1 秒,位置保持不变。1 秒这个时间,是由参数 innodb_old_blocks_time 控制的。其默认值是 1000,单位毫秒。

所以整个流程简单为:不在链表数据页,先放在old处,如果在old处呆的超过1秒了,移到young这里的头部,如果数据一直在young处,移到young的前面。

针对全表扫描的200G数据,

  1. 扫描过程中,需要新插入的数据页,都被放到 old 区域 ;
  2. 一个数据页里面有多条记录,这个数据页会被多次访问到,但由于是顺序扫描,这个数据页第一次被访问和最后一次被访问的时间间隔不会超过 1 秒,因此还是会被保留在 old 区域;
  3. 再继续扫描后续的数据,之前的这个数据页之后也不会再被访问到,于是始终没有机会移到链表头部(也就是 young 区域),很快就会被淘汰出去。

小结: 由于 MySQL 采用的是边算边发的逻辑,因此对于数据量很大的查询结果来说,不会在 server 端保存完整的结果集。所以,如果客户端读结果不及时,会堵住 MySQL 的查询过程,但是不会把内存打爆。

疑问:

  1. net_buffer内容分发给socket_send_buffer流程:net_buffer写满,一起发,然后清空net_buffer,组装下一批。
  2. 如果可以一次性放入net_buffer, 对于执行器来说是不是意味着“全都写出去了”,也就不会有 sending to client 状态,体现出 sending to client 的状态就是网络栈 socket receive buffer 写满了。
  3. net_buffer 应该是针对每个请求线程单独分配的。
  4. InnoDB改进的LRU算法,如果遇到连续两次的全表扫描,会把young区的3/5给覆盖掉了,因为两次扫描时间间隔会超过一秒?
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2020/03/03 ,如有侵权请联系 cloudcommunity@tencent.com 删除
目录
  • 全表扫描对 server 层的影响
  • 全表扫描对 InnoDB 的影响
    • 最近最少使用算法(Least Recently Used, LRU )
    • 改进的LRU算法
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档