Safely Dropping a Large MySQL history_str Table to Reclaim Disk Space

Problem Background

Recently a client reported that the MySQL history_str table had grown so large that its .ibd file exceeded 460 GB, causing high disk usage on a low‑spec virtual machine. The client wanted to clean the table, and the author used this opportunity to test recent learning.

Problem Communication

Through environment inspection and client discussion, the following facts were gathered:

The site uses a bidirectional master‑slave replication architecture without setting the slave to read‑only.

The history_str table's .ibd file is over 460 GB.

The data in the table can be deleted directly.

The server hosting the instance is a low‑performance virtual machine.

Considering these points, the recommendation was to create a new table with the same structure, rename the old table, and then drop it, while being aware of the risks:

Dropping a large table may cause the instance to hang.

It may introduce replication lag.

Deleting large files can create heavy disk I/O pressure.

Final Solution

The final plan consists of the following steps:

On the master, create a new table with the same structure and rename the tables: create table history_str_new like history_str; rename table history_str to history_str_old, history_str_new to history_str;

On both master and slave, create a hard link to the old .ibd file: ln history_str_old.ibd history_str_old.ibd.hdlk

After a day or two, when the old table’s pages have cooled in the InnoDB buffer pool, perform the drop during a low‑traffic window, starting on the slave for verification, then on the master: set sql_log_bin=0; // temporarily disable binlog drop table history_str_old; // drop the table set sql_log_bin=1; // re‑enable binlog

Delete the hard‑link file to free space using a truncate script. Example script: #!/bin/bash ############################################################################## ## Parameters: $1 file name, $2 chunk size in MB, $3 sleep seconds ## ############################################################################## fileSize=`du $1|awk -F" " '{print $1}'` fileName=$1 chunk=$2 sleepTime=$3 chunkSize=$(( chunk * 1024 )) rotateTime=$(( fileSize / chunkSize )) function truncate_action(){ for (( i=0; i<=${rotateTime}; i++ )) do if [ $i -eq 0 ]; then echo "Starting truncate on $fileName"; fi if [ $i -eq ${rotateTime} ]; then echo "Truncate completed!"; fi truncate -s -${chunk}M $fileName currentSize=`du -sh $fileName|awk -F" " '{print $1}'` echo "Current size: $currentSize" sleep $sleepTime done } truncate_action

Run the script, e.g., sh truncateFile.sh history_str_old.ibd.hdlk 256 1 , to shrink the file by 256 MB chunks with a 1‑second pause between operations.

Additional Knowledge

Understanding why these steps work is essential for a competent DBA. The article also provides low‑level details about the MySQL drop‑table process, including buffer‑pool mutex handling, flush‑list operations, and adaptive hash index cleanup.

Tips 1: mysql删除表的流程: 1. 持有 buffer pool mutex。 2. 持有 flush list mutex。 3. 扫描 flush list，若脏页属于被 drop 的表，则移除；若开启 AHI，则遍历 LRU 删除自适应哈希索引项。 4. 释放 flush list mutex。 5. 释放 buffer pool mutex。

Tips 2: 在 Linux 系统中，一个磁盘文件可以被多个硬链接引用，删除任意一个硬链接仅会减少引用计数，只有计数为 0 时文件才真正被删除。

Tips 3: 大表 drop 或 truncate 相关的已知 bug 包括： - 两次 LRU 扫描导致性能问题（Bug 51325、64284） - 分区表删除多个分区时每个分区都会扫描 LRU 两次（Bug 61188） - truncate 在 8.0 之前会扫描 LRU 删除 AHI，已在 8.0 中修复（Bug 68184） - drop table 扫描 LRU 删除 AHI 导致信号量等待（Bug 91977） - 临时表删除时的 LRU 扫描问题已在 8.0.23 中修复（Bug 98869）