作者：徐文梁

爱可生DBA成员，一个执着于技术的数据库工程师，次要负责数据库日常运维工作。善于MySQL,redis，其余常见数据库也有涉猎，喜爱垂钓，看书，看风光，结交新敌人。

本文起源：原创投稿

*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

问题背景:

前段时间，客户反馈 Zabbix 实例的 history_str 表数据量很大，导致磁盘空间使用率较高，想要清理该表，征询是否有好的倡议。想着正好最近学习了相干的知识点，正好能够测验一下学习成绩，通过实际的测验，最终考试合格，客户也比较满意，于是便有了此文。

问题沟通:

通过理论查看环境及与客户沟通，得出以下信息:

1.现场是双向主从复制架构，未设置从库read_only只读。

2.history_str表的ibd数据文件超460G。

3.history_str表的存量数据能够间接清理。

4.现场实例所在的服务器是虚拟机，配置较低。

因而，综合思考后倡议客户新建雷同表构造的表而后对原表进行drop操作，然而表数据量比拟大，须要思考以下危险:

1.drop大表可能会导致实例hang住，影响数据库失常应用。

2.drop大表操作导致主从延时。

3.删除大文件造成磁盘io压力较大。

最终计划:

在思考以上的根底上，最终给出如下计划:

1.在主库执行如下命令建设雷同表构造表并进行rename操作:

create table history_str_new like history_str;rename table history_str to history_str_old, history_str_new tohistory_str;

2.在主库和从库执行以下操作，建设硬链接文件:

ln history_str_old.ibd history_str_old.ibd.hdlk


3.实现第二步后，倡议距离一两天再进行操作，让history_str_old表数据从innodb buffer pool中冷却，而后业务低峰期在主从库别离执行如下操作，倡议先操作从库,从库验证没问题后再在主库操作:

set sql log bin=0;       //长期敞开写操作记录binlogdrop table history_str_old;//执行drop操作set sql log bin=l;       //复原写操作记录binlog


4.删除history_old.ibd.hdlk文件，开释空间，能够通过linux的truncate命令实现，参考脚本如下:

#!/bin/bash################################################################################            第一个参数为须要执行操作的文件的文件名称           ####           第二个参数为每次执行操作的缩减值，单位为MB          ####           第三个参数为每次执行后的睡眠工夫，单位为S           ################################################################################  fileSize=`du $1|awk -F" " '{print $1}'`fileName=$1chunk=$2sleepTime=$3chunkSize=$(( chunk * 1024 ))rotateTime=$(( fileSize / chunkSize ))declare -a currentSizeecho $rotateTime  function truncate_action(){for (( i=0; i<=${rotateTime}; i++ ))doif [ $i -eq 0 ];thenecho "开始进行truncate操作，操作文件名为:"$fileNamefi  if [ $i -eq ${rotateTime} ];thenecho "执行truncate操作完结！！！"fi  truncate -s -${chunk}M $fileNamecurrentSize=`du -sh $fileName|awk -F" " '{print $1}'`echo "以后文件大小为: "$currentSizesleep $sleepTimedone}  truncate_action

示例:sh truncateFile.sh history_str_old.ibd.hdlk 256 1，示意删除history_str_old.ibd.hdlk文件，每次截断大小为256M，而后sleep距离为1s。

5.到此，静静期待就行了。无聊的话也能够思考一下人生。

小常识:

后面解决了如何操作的问题，然而作为一个称职的DBA，不光要晓得如何做，还得晓得为什么这么做，不然的话，敲回车键容易，悔恨却很难，干货来了，一起理解一下吧。下次遇到相似问题就不慌了。

tips1：

MySQL删除表的流程:1.持有buffer pool mutex。2.持有buffer pool中的flush list mutex。3.扫描flush list列表，如果脏页属于drop掉的table，则间接将其从flush list列表中移除。如果开启了AHI,还会遍历LRU,删除innodb表的自适应散列索引项，如果mysql版本在5.5.23之前，则间接删除，对于5.5.23及当前版本，如果占用cpu和mutex工夫过长，则开释cpu资源，flush list mutex和buffer pool mutex一段时间，并进行context switch。一段时间后从新持有buffer pool mutex,flush list mutex。4.开释flush list mutex。5.开释buffer pool mutex。

tips2:

对于linux零碎，一个磁盘上的文件能够由多个文件系统的文件援用，且这多个文件完全相同，并指向同一个磁盘上的文件，当删除其中任一一个文件时，并不会删除实在的文件，而是将其被援用的数目减1，只有当被援用数目为0时，才会真正删除文件。

tips3:

大表drop或者truncate相干的一些bug: 这两个指出drop table 会做两次 LRU 扫描：一次是从 LRU list 中删除表的数据页，一次是删除表的 AHI 条目。https://bugs.mysql.com/bug.php?id=51325https://bugs.mysql.com/bug.php?id=64284  对于分区表，删除多个分区时，删除每个分区都会扫描LRU两次。https://bugs.mysql.com/bug.php?id=61188  truncate table 会扫描 LRU 来删除 AHI，导致性能降落；8.0 已修复，办法是将 truncate 映射成 drop table + create tablehttps://bugs.mysql.com/bug.php?id=68184  drop table 扫描 LRU 删除 AHI 导致信号量期待，造成长时间的阻塞https://bugs.mysql.com/bug.php?id=91977  8.0仍旧修复了 truncate table 的问题，然而对于一些查问产生的磁盘长期表（innodb 表），在长期表被删除时，还是会有同样的问题。这个bug在8.0.23中失去修复。https://bugs.mysql.com/bug.php?id=98869