关于mysql:技术分享-如何优雅的删除-Zabbix-的-history-相关历史大表

作者：徐文梁

爱可生 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 to
history_str;

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

ln history_str_old.ibd history_str_old.ibd.hdlk

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

set sql log bin=0;       // 长期敞开写操作记录 binlog
drop 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=$1
chunk=$2
sleepTime=$3
chunkSize=$((chunk * 1024))
rotateTime=$((fileSize / chunkSize))
declare -a currentSize
echo $rotateTime
  
function truncate_action()
{for (( i=0; i<=${rotateTime}; i++ ))
do
if [$i -eq 0];then
echo "开始进行 truncate 操作，操作文件名为:"$fileName
fi
  
if [$i -eq ${rotateTime} ];then
echo "执行 truncate 操作完结！！！"
fi
  
truncate -s -${chunk}M $fileName
currentSize=`du -sh $fileName|awk -F""'{print $1}'`
echo "以后文件大小为:"$currentSize
sleep $sleepTime
done
}
  
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=51325
https://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 table
https://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

关于mysql:技术分享-如何优雅的删除-Zabbix-的-history-相关历史大表

问题背景:

问题沟通:

最终计划:

小常识: