MySQLを開発環境等で実行していると、容量が足りなくなってしまうことはありませんか? そんな時にDELETE文を実行して容量を空けようとしても、削除したはずのデータのディスク領域が何故か解放されなくて困ってしまうことがあるかと思います。
そこで今回はOPTIMEZE TABLE
構文を使って、ディスク領域を最適化する方法を紹介したいと思います。
検証環境
今回は第23回 mysqlslapを使って負荷テストをしてみようで使用したCentOS7にSysBenchをインストールして実行しています。MySQLのバージョンは5.7.17を使用しています。
また、今回使用するデータは「第2回 MySQLにはじめてのデータを入れてみる」で紹介をしている郵便番号のテーブルを用いて紹介を行います。
削除した時のテーブルのサイズを確認する
CentOSを使ってyumでMySQLをインストールした場合に、テーブルのファイルは以下のように/var/lib/mysql/データベース名
配下に設置されます。今回はzipcodeデータベースの中身を確認したいので、/var/lib/mysql/zipcode
に移動してls -lh
コマンドでファイルを確認します。
実行した結果、3個のファイルがあることがわかります。
それぞれについて簡単に説明をすると、db.opt
には名前が表す通りデータベースのオプションが入っています。今回は第2回で設定した文字コードの設定が記述されています。
zipcode.frm
ファイルには、zipcodeテーブル構造のメタ定義を管理するためのデータが入っています。zipcode.ibd
ファイルに実際のデータやindexが含まれています。今回住所データを読み込んだ時に、zipcode.ibd
ファイルに27Mのデータが書き込まれていることがわかります。
次項からは、DELETE構文で削除した場合と、TRUNCATE構文で削除した場合について見ていきたいと思います。
DELETE構文で削除した場合
この場合にどのような結果になるのか、実際に以下のSQLを実行してファイルサイズがどうなるか確認してみます。
DELETE構文を使って全件削除を行いました。この時のファイルサイズは以下のようになっています。
以上のようにファイルサイズが変化していないことがわかります。今回は全件削除したのですが、WHERE句に条件をつけて付けて削除をした場合は27MBの領域の中にデータが点在してしまうので、効率的にデータが保存できていません。
TRUNCATE 構文で削除した場合
データ削除にはDELETE構文の他にもTRUNCATE構文があります。こちらを使ってテーブルの一括削除を行った場合は話が変わって、自動的にディスク領域が解放されます。以下のようにSQLを実行し、確認してみます。
この時のファイルサイズは以下のようになってます。
全件削除を単純に行いたい場合は、TRUNCATE構文を使った方がディスク効率が良くなるとわかります。
OPTIMIZE TABLEを実行してみる
さて前項のDELETE構文を使ってデータを削除した場合、データベースから削除しているにもかかわらず、ディスク領域が解放されていないことがわかります。実際には確保している領域をInnoDBが使い回して再利用してくれるのですが、このように一度に大量の削除が走った後はどうしてもフラグメンテーションが発生してしまいます。そんな時にデータ領域を最適化したい場合には、OPTIMIZE TABLE
を使用します。前項のDELETE構文を使った削除が終わった状態から実行してみます。
この時にファイルサイズを確認してみると、以下のようにTRUNCATEした時とおなじ結果が得られました。
このように大量のデータを削除した場合にOPTIMIZE TABLE
を実行するとディスク領域が解放されました。しかし、OPTIMIZE TABLE
にはMySQLのバージョンによって注意することがいくつかありますので、次項でそれらに付いて説明を行っていきます。
MySQLのバージョンによる動作の違い
5.6.17以前を使っている場合
OPTIMIZE TABLE
ですが、5.6.17以下を使っている場合は注意が必要です。5.6.17以下ではOPTIMIZE TABLE
が更新ステートメントをブロックしてしまうため、本番環境で使っているMySQLにOPTIMIZE TABLE
を行う場合には時間的な余裕を持って行うか、mysqldumpを使ってdumpしたSQLを使用して別テーブルとして用意したのちにRENAME TABLE
をするなど、別の方法を検討をする必要があります。
5.6.5以前を使っている場合
MySQLの5.6.6からデフォルトで有効になった設定の1つに、InnoDB File-Per-Tableモードというものがあります。この設定は、InnoDBのテーブルのデータとインデックスの保存領域をファイルに分けて保存する設定です。設定を確認する方法は以下のようになります。
この設定がOFFになっている場合は全てのテーブルが一つにまとまったファイルに保存されてしまうため、OPTIMIZE TABLE
を行っても領域の解放がされません。ただし、空いている領域はInnoDBで必要に応じて再利用されます。
まとめ
今回はOPTIMIZE TABLE
を使ってディスク領域を解放する方法を紹介しました。DELETEが多く発生する環境で作業を行っている場合はフラグメンテーションが発生している可能性が高いので、年末の大掃除としてぜひ一度試してみてはいかがでしょうか?