MySQLのメンテをした話
前回は、PHPのセッション削除についての投稿をしました。 さて、今回はその後の話になります。
1. 初めに
前回お話した溜めに溜めたsessionをあらかた削除したはいいけど、容量がほとんど戻らないという事象が発生しました。 その対処を行ったのでメモを残しておこうと思います。
2. DBに何が起きていたのか
さて、DBに起きたか見ていきましょう。 原因は、テーブルのDELETEによるフラグメンテーションの増加です。
フラグメンテーションと聞いて、エンジニアだとピンとくるかたはいるのではないでしょうか?
HDDなどで良くある虫食いの状態です。
InnoDBはMVCCと呼ばれる機構で、削除が行われてもどうも履歴のようなものを残しており、DELETEをしてもデータとしては残っているようです。
これはセッションを管理しているので、DELETEは頻繁に行なわれていると推測できますが、一度もOPTIMYZEなどで解消をしようとしたことはありませんでした。
そして断片がどんどん溜まり、ストレージの容量を食いつぶしている状態を作り出したということになります。
3. 問題を解決する
さて、そんな断片化ですが、以下の二つの方法で対処することができます。
OPTIMIZE TABLE [table name]
ALTER TABLE [table name] ENGINE=INNODB
4. 大量削除をするときに気をつけること
但し上記のやり方ではできない時があります。
特に「ストレージの残り容量」は気をつけて下さい。
特にALTER TABLEは、一度テーブルのコピーをとってから、テーブルの置き換えを行う関係で、削除対象分の容量がストレージに必要となってきます。
今回、私たちは削除対象がおよそ10GBあり残量が5Gほどだったため、ALTER TABLEを行うことができませんでした。
そのような時の対処方は、
一度テーブルをDROPして削除し同じテーブルを新しく作成する
同じ構造のDBを作成し、その後該当のテーブルを削除したあと、作成しておいたテーブル名を削除したテーブル名に変更する。
といった方法が挙げられます。
何はともあれ、一度テーブルを削除して作成するのが手っ取り早いです。
稼働しているサービスであればダウンタイムは少ないほうがいいと思うので、あらかじめ用意したテーブルに切り替えるのが、一番ダウンタイムが少ない方法かと思います。
今回調べるにあたって参考サイト
漢(オトコ)のコンピュータ道: 大人のためのInnoDBテーブルとの正しい付き合い方。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.5.37 SHOW TABLE STATUS 構文
MySQLでDBとテーブルのサイズを確認するSQL - Qiita
そろそろMySQL5.7もまじめに使っていきたいな・・(´・ω・`)