LOGO OA教程 ERP教程 模切知识交流 PMS教程 CRM教程 开发文档 其他文档  
 
网站管理员

[点晴永久免费OA]如何优雅的删除正式环境中的大表

freeflydom
2024年6月17日 16:39 本文热度 369

引起 MySQL 数据库性能抖动的原因有很多,比如大事务、定时批量查询等,而这些原因我们一般都会注意到。但是,有一个引起性能抖动的原因却经常被我们忽视,那就是在生产环境删除无用的大表,即 DROP TABLE。

一、为什么要 DROP TABLE?

生产环境中,为什么要 DROP TABLE?相信绝大部分原因是为了释放空间

生产环境大多数是已经确定的库表,一般不会进行 DROP TABLE 这么重的操作,甚至大部分DBA 都不应该拥有 DROP 权限。

但是随着数据无限增长,在某些系统中,需要将数据归档到历史数据库,删除生产数据库中的表,从而释放存储空间,典型的就是偏日志、流水类的数据。

不过,当我们谈一个数据库多大的时候,单纯讨论存储容量意义并不大,我们更希望得到的是活跃记录有多少。

银行系统的 Oracle 数据库,虽然单库都可以数 T,但大部分都是日志数据或流水数据。

生产环境的流水表,单表容量可能几百 G,上 T,怎样在不影响业务的情况下,优雅地 DROP TABLE 呢 (这里优雅的定义为业务耗时上升不超过20%)

DROP TABLE 本来是一个非常简单的操作,但是要做到在生产环境中,对业务耗时影响很小,基本无感知,简直就是一件艺术。

或许大部分数据库系统都没有这么高的要求,但是海量并发的互联网业务有!

二、关于MySQL版本

在 MySQL 5.5.23 版本前,当删除一张表时,会去遍历 BP(Buffer Pool 缓冲池)中所有该表对应的页,这时需要持有 BP 的互斥锁,而这是一把巨大的锁,对于 BP 的访问都需要持有该锁才能继续

随着硬件的发展,128G、256G 的内存都已是常态,若 BP 非常大,如 100G,则遍历 BP 所需要的时间就会非常长,可能是 5 秒,甚至更长。这时,业务对于数据库的请求会直接掉底,直接变为 0。显然,这不符合我们对于优雅的定义。

MySQL 5.5.23 版本开始,做了两点优化:

  • 遍历脏页列表(flush list)时,只持有一小段时间,之后就释放 BP 的大锁,并且不需要真正刷新脏页,这样的处理能让其他业务请求有机会获得大锁的可能,从而业务的请求不会掉底;

  • 不处理 LRU 列表中干净的页。因为 LRU 中不使用的页会慢慢被淘汰,因此删除表,不用同步地去处理对应的页,可以通过 LRU 机制,异步最终淘汰机制。

MySQL 5.6 版本支持了多个 BP 拆分,5.7 版本支持多个脏页列表,因此对于上述 DROP TABLE 的处理,又得到进一步优化,即持有大锁的时间越来越少,对业务的影响也就越来越少。

(一)IO优化

上面的这些优化是针对内存中的锁进行优化,在 DROP TABLE 的过程中,让持有的大锁尽可能的时间缩短,从而避免 QPS 掉底的问题。

然而,想要对业务操作的耗时响应时间控制在 20%,还需要考虑物理 I/O 的影响。

例如,删除一张 100G 的表,意味着要删除 100G 的物理文件,在删除过程中,I/O 的开销也将会是非常巨大,从而影响业务的耗时。

常见优化的思想是在 DROP TABLE 前,对 ibd 文件创建硬链接,这样在 DROP TABLE 时,只会删除 ibd 文件,不会真正删除 ibd 文件,也就不会释放空间。

这样 DROP TABLE 的操作速度就能得到进一步提升。不过,这时存储空间并没有第一时间释放,还是需要选择低峰期删除文件,方能最终释放存储空间。

但同样的问题,虽然异步删除物理文件,但又如何做到最业务的耗时影响最小呢?这时,可以考虑使用 ionice 这个工具,选择在业务低峰期进行 ibd 文件的删除,如:bash复制代码ionice -c 2 -n 6 rm xxx.ibd

三、自适应哈希功能

自适应哈希(Adaptive Hash Index,下简称AHI)特性作为一个几乎透明的功能,其实一般用户无需关心,基本可以认为 AHI 是即开即用的功能。

默认 AHI 参数的设置也是比较合理的,例如参数 innodb_adaptive_hash_index_parts 设置为 8 。然而,AHI 存在一个副作用:当删除大表,且缓冲池(Buffer Pool,下简称 BP)比较大,如超过 32G,则 MySQL 数据库可能会有短暂被 hang 住的情况发生。

这时会对业务线程造成一定影响,从而导致业务系统的抖动。

产生这个问题的原因是在删除表的时候,InnoDB 存储引擎会将该表在 BP 中的内存都淘汰掉,释放可用空间。这其中包括数据页、索引页、自适应哈希页等。

当 BP 比较大时,扫描 BP 中 flush_list 链表需要比较长的时间,因此会产生系统的抖动。

所以在海量的互联网并发业务中,删除表操作需要做精细的逻辑控制,如:

  • 业务低峰期删除大表;

  • 删除表前禁用 AHI 功能;

  • 控制脏页链表长度,只有长度小于一定阈值,才发起删除操作;

  • 删除表后启用 AHI 功能。

不过,这么麻烦的处理在 MySQL 8.0.23 版本之后,就都不再需要了。因为官方已经彻底修复了这个问题。

四、终极删除优化

除了前面的这些对于版本的选择、I/O 的优化、自适应哈希算法的调整,还可以有进一步减小删除大表时发生抖动的可能性。

当前 MySQL 数据库对于删除时内存的控制已经做的很好了,接下去的问题在于还需要进行 I/O操作,从磁盘上删除操作。ionice 等命令依然有 I/O 开销,性能抖动依然不可避免。

如果根据第一性原理,那是不是可以实现不删除表文件,也能释放存储空间呢?为什么要这么辛苦地去释放空间?只需要把原来的物理存储空间给新表使用不就好了吗?

更推荐在初始化时,就完成 ibdata1 的空间分配,即固定单个 MySQL 实例能使用到存储空间上限。这样可以省去后续磁盘空间的申请释放,进一步提升在业务高峰期,数据库耗时的平稳性。

innodb_file_per_table = 0

innodb_data_file_path=ibdata1:500G

大家可能觉得这样不够灵活,因为用户可能只想日志表、流水表等数据放在一个表空间中,这样 DROP TABLE 时不需要删除物理空间。

这样的话,可以使用 MySQL 中的通用表空间(General Tablespace):

CREATE TABLESPACE `ts_log` ADD DATAFILE 'ts_log.ibd'


CREATE TABLE log_2020_01 ( ... ) tablespace = ts_log;


CREATE TABLE log_2020_02 ( ... ) tablespace = ts_log;


作者:我爱娃哈哈
链接:https://juejin.cn/post/7380179109290688550
来源:稀土掘金
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。



该文章在 2024/6/17 16:39:47 编辑过
关键字查询
相关文章
正在查询...
点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。
点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理,结合码头的业务特点,围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体,是物流码头及其他港口类企业的高效ERP管理信息系统。
点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。
点晴免费OA是一款软件和通用服务都免费,不限功能、不限时间、不限用户的免费OA协同办公管理系统。
Copyright 2010-2024 ClickSun All Rights Reserved