Posted at

MySQLのbinlogがディスク容量を圧迫しているときに確認すべきこと

More than 1 year has passed since last update.

MySQLのバイナリログ(binlog)が大量に出力され、毎日夜間に40G近くのファイルが生成されていた。


バイナリログとは


  • 下記のようにMySQLの更新クエリを記録するログのこと
    binlog.jpg

  • 数日でローテイトされる(設定による)

  • バイナリログはレプリケーションおよびデータ復旧に用いる

  • バイナリログの出力方法には以下の3種類がある

種類
設定値
内容

ステートメントベース
STATEMENT
実行した SQL文をバイナリログに記載します

行ベース
ROW
行の変更内容が全てバイナリログに記載されます

ミックス
MIXED
基本的にステートメントベースと同じ動作をしますが、特定の場合に行ベースに切り替わります


今回の事象


  • MySQL5.5の場合、デフォルトは「ミックス」

  • この場合、テンポラリーテーブルの中身が「行ベース」で出力される

  • 夜間処理で巨大なテンポラリーテーブルを作成していたため、40Gものバイナリログが出力されていた!

  • 「ミックス」がこのような仕様になっている理由は、レプリケーションで用いるため

  • 担当システムではレプリケーションしていないので不要と判断(実際にはデータ復旧時に時間がかかるようにはなる)


バイナリログの出力方法を「ステートメントベース」に変更


  • 設定方法

mysql> SET binlog_format = STATEMENT;

再起動しても大丈夫なようにmy.confにも以下のように設定しておく

binlog_format=STATEMENT


結果


  • 夜間のバイナリログが2G以下になり、ログ出力のディスクアクセスが激減したためか、夜間処理自体も速度アップ