在MySQL中,如何正确有效的删除一张大表?

0    113    3

Tags:

👉 本文共约2036个字,系统预计阅读时间或需8分钟。

在MySQL中如何有效的删除一个大表?

在Oracle中,删除大表可以先通过TRUNCATE + REUSE STORAGE参数清空表,再使用DEALLOCATE逐步缩小占用的存储,最后再DROP掉表。在MySQL中,对于大表的删除,可以通过建立硬链接(Hard Link)的方式来删除。

建立硬链接的方式如下所示:

建立硬链接之后就可以使用DROP TABLE删除表了,最后在OS级别删除硬链接的文件即可。

在OS级别删除文件也需要通过truncate分段删除文件,避免I/O夯住。在生产环境,直接用rm命令来删大文件,会造成磁盘I/O开销飙升,CPU负载过高,这会直接影响其它程序运行。

truncate分段删除文件的脚本如下所示,从2194G开始,每次缩减10G,停2秒,继续,直到文件只剩10G,最后使用rm命令删除剩余的部分:

truncate来源于coreutils工具集,安装过程如下所示:

为什么通过这种方式可以快速删除呢?当多个文件名同时指向同一个INODE时,此时这个INODE的引用数N>1,删除其中任何一个文件都会很快。因为其直接的物理文件块没有被删除,只是删除了一个指针而已。当INODE的引用数N=1时,删除文件时需要把与这个文件相关的所有数据块清除,所以会比较耗时。

一个案例

现象

客户数据库出现假死,导致探测语句下发不下去,出现切换。后来经过排查发现是一个大表 drop 导致的数据库产生假死,也参考过类似的数据库假死的案例,这里将测试一下不同版本drop table的影响

关于drop 大表的历史bug描述

根据 https://bugs.mysql.com/bug.php?id=91977 中的描述,对于大的 buffer pool 中的大表 drop 会占用 mutex 锁,导致其它查询无法进行。提供的临时解决方案为关闭AHI(自适应哈希),预期解决版本是8.0.23。暂未从5.7的后期版本中找到解决方式

使用不同版本测试影响效果

准备流程

测试配置

buffer pool表空间占用
5.7.29128G24G
8.0.28128G24G

关闭 binlog 、调整双一,使用 benchmark 导入300个库的数据

导入数据后使用表空间迁移的方式保留较大表的备份,方便后续连续测试

改回binlog及双一参数。调整bufferpool 到128G

数据库预热

测试结果

5.7.29

开启AHI,drop执行了15s ,TPS/QPS 过程中降为0

关闭AHI,时间缩短至2s,TPS/QPS 过程中降为0

8.0.28

开启AHI,drop执行了2min34 ,未影响TPS/QPS

关闭AHI,执行时间0.58s完成,不影响业务

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

打印堆栈

打印5.7.29的堆栈信息,drop table 过程中持续进行 btr_search_drop_page_hash_index ,在AHI的删除时占用了大量的时间

修复说明

图片

超过32g buffer pool中 drop 大表、drop AHI 中占用大量页面的表、drop 临时表空间,

之前版本会立即的释放脏页和 AHI,这样会对性能产生很大的问题。如今的修复方式采用惰性删除的方式,对业务影响比较小

结论

drop table 过程大概分为三部分:

1、遍历lru,驱逐属于该表的脏页

2、清理AHI中的内容

3、文件系统的删除

其中前两部分属于最耗时,也是最影响业务的。大的 buffer pool 会导致遍历时间过长,通过hash运算找到AHI对应的位置并删除, 这个时间也是比较长的,此阶段持有内部latche不释放,影响其它查询

8.0.23的修复版本主要是对应第一部分,对于脏页采用惰性删除方式,在关闭AHI的时候,是瞬间完成。当开启AHI的时候,时间比历史版本的还要长,区别是不影响业务,猜测是降低锁的持有时间和粒度,使其它事务能够同时执行

案例参考

https://mp.weixin.qq.com/s/YFCHqYRIOuMOxiuyV5i05Q

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

20 − 12 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部