MySQLのバイナリログ(binlog)が大量に出力され、毎日夜間に40G近くのファイルが生成されていた。
バイナリログとは
- 下記のようにMySQLの更新クエリを記録するログのこと
- 数日でローテイトされる（設定による）
- バイナリログはレプリケーションおよびデータ復旧に用いる
- バイナリログの出力方法には以下の3種類がある
| 種類 | 設定値 | 内容 |
|---|---|---|
| ステートメントベース | STATEMENT | 実行した SQL文をバイナリログに記載します |
| 行ベース | ROW | 行の変更内容が全てバイナリログに記載されます |
| ミックス | MIXED | 基本的にステートメントベースと同じ動作をしますが、特定の場合に行ベースに切り替わります |
今回の事象
- MySQL5.5の場合、デフォルトは「ミックス」
- この場合、テンポラリーテーブルの中身が「行ベース」で出力される
- 夜間処理で巨大なテンポラリーテーブルを作成していたため、40Gものバイナリログが出力されていた！
- 「ミックス」がこのような仕様になっている理由は、レプリケーションで用いるため
- 担当システムではレプリケーションしていないので不要と判断（実際にはデータ復旧時に時間がかかるようにはなる）
バイナリログの出力方法を「ステートメントベース」に変更
- 設定方法
mysql> SET binlog_format = STATEMENT;
再起動しても大丈夫なようにmy.confにも以下のように設定しておく
binlog_format=STATEMENT
結果
- 夜間のバイナリログが2G以下になり、ログ出力のディスクアクセスが激減したためか、夜間処理自体も速度アップ