【MySQL】不要データである『data_free』を削除した話

※この記事は『2017/07/20』に書いた記事をリライトしました。

ある日仕事をしていたら、データの増減が激しいから原因を調べて！！

と丸投げされましたw

「mysql メンテナンス」　とか　「mysql データ量」とかいろいろググってみたのでまとめます。

結論ですが、

data_freeが大量にありましたので削除しました。

ピンとこない人は下へどうぞ！！

MySQLの『data_free』って何？？

data_freeとは、SQLでUPDATEやDELETEをした時に発生するゴミのようなものです。

UPDATEはともかくDELETEでもゴミが発生しちゃうんですね・・・。

それから何か復元とかできるのかもですが、それは違う話なので今後の課題でw

調べ方

SQLコマンドは下記サイトのものをそのまま使わせていただきました。

SELECT table_name "Table", engine,table_rows "Table Rows", ( data_length + index_length ) / 1024 / 1024 / 1024 "Data (GB)", ( data_free )/ 1024 / 1024 / 1024 "Free Space (GB)" FROM information_schema.TABLES WHERE table_schema=database() ORDER BY (data_length+index_length) desc limit 10;

qiita.com

そうすると、Free Space(GB)で10GBもあるデータがあったりしてもうビックリ！

テーブル名から処理を追ってみるとなんと毎日何千件ものデータをDeleteしているバッチが・・・。

絶対こいつのせい・・・・。

最適化コマンド

SQLコマンドとしては、

OPTIMIZE TABLE table_name;

でいけるのですが、ここで豆知識。

設定されてないと、「Table does not support optimize, doing recreate + analyze instead」なんて不安なメッセージが出ることがあります。

ただこれ、調べてみると代わりに

ALTER TABLE table_name ENGINE InnoDB;

というコマンドが実行されて最適化してくれるみたいです。

実際最適化されてたし、大丈夫なんでしょうね。

詳細は下記のマニュアル頑張って読んでみてください！

MySQL :: MySQL 5.7 Reference Manual :: 13.7.2.4 OPTIMIZE TABLE Syntax

本番環境などで実行上の注意

安易に本番環境で最適化コマンド打つと環境が死んでしまうかもしれません。

理由としてはALTER TABLEをする時にロックがかかるから。です。

10GB最適化するのにどれくらいのメモリを食うとかいろいろ検証しないとですが、なんにせよ夜中など、システムが比較的利用されていない時間帯にやった方が安心そうですね。

SHOW PROCESSLIST

とかコマンド打ってプロセスが複数走ってないかとか確認してからやると安心安心ですね。

その他の情報はこちら！

mizominton.hatenablog.jp

MySQL 即効クエリチューニング(Think IT Books)