稼働中のサービスで安全にdrop tableする方法

DBサーバーのディスク容量の空きが少なくなり、不要なテーブルを削除することになりました。
対象のテーブルのデータ量が多く、平常時でもそこそこ負荷のあるサーバーなのですが、
テーブル削除だけのためにメンテナンスをするのもアレなので、
深夜帯にしれっとdropしたい気持ちを抑えつつ、負荷のかからないdrop tableの手順を試してみました。

サービス稼働中にテーブルdropする時の問題点

metadata lock

drop対象のテーブルに書き込みやトランザクションが貼ってあるとmeta data lockがかかってしまいます
今回は参照等がないテーブルなので、問題なし

巨大なファイルを削除するとI/O待ちが発生する

巨大なテーブルのデータファイルを削除すると他のプロセスがI/O待ちになる可能性があります

安全にテーブルdropするには

完全にこちらを参考をさせて頂きました。
負荷の高いMySQLでメタデータを極力ロックさせずにDROP TABLEする

dropする前にデータファイルにハードリンクを貼ることで、
drop時はファイル削除を行わず、後に優先度を下げて実ファイルを削除するという流れです。

しかしioniceで優先度を下げても、
巨大なテーブルのデータファイルなどを削除する際はCPU使用率など見る限り負荷がかかっていました。
ioniceは緩やかに削除というより、実行の順番の優先度を下げるという挙動のようです。

なので、次に負荷をかけずに巨大なファイルを削除するということで下記を参考にさせてもらいました。
大量・巨大なファイル操作を低負荷で行う方法

truncateコマンドで徐々にファイルサイズを削り、小さくした後に削除するというやり方です。

安全にテーブルdropする手順

まとめて下記のような流れで処理しました

• 削除前に念のため、dump取る

mysqldump DB_NAME hoge_table > dump_hoge_table.sql

• 対象テーブルのデータファイルにハードリンクを貼る

ln /usr/local/mysql/data/DB_NAME/hoge_table.ibd /root/hoge_table.ibd
ln /usr/local/mysql/data/DB_NAME/hoge_table.frm /root/hoge_table.frm

• drop tableを実行

mysql DB_NAME -e "DROP TABLE hoge_table"

• ハードリンクを貼ったファイルをtruncateコマンドで徐々にサイズを削った後に、rmを実行

php

//ファイルサイズ取得
$file_size = 0;
$cmd    = "du -m {$file_path} | awk '{print $1}'";
exec($cmd, $output, $ret);
if($ret == 0) $file_size = $output[0];

//truncateで徐々に切り詰める
if($file_size){
  $i = 1;
  while ($i <= $file_size) {
    $tmp_size = $file_size - $i;
    $cmd    = "truncate -s {$tmp_size}M {$file_path}";
    exec($cmd, $output, $ret);
    //0.1sec sleep
    usleep(100000);
    $i++;
  }
//ファイル削除
$cmd = "rm {$file_path}";
$res = exec($cmd, $output, $ret);

的な感じで、稼働中の本番DBで百数テーブル、260GB程度削除しましたが、負荷なく実行できました。
そこそこ時間はかかりますが、、、truncateの部分はもっと攻めて良さそうですがそれはまた次の機会に・・・