提供：MySQL Practice Wiki

概要

InnoDBのログファイルは、別名WAL - Write Ahead Logと呼ばれるもので、名前を日本語に直すと「前もって書き込んでおくためのログ」とでも呼べるだろうか。InnoDBのテーブルに対して行われた更新は、全ていったんログに書き込まれるのである。トランザクションがコミットされると、innodb_flush_log_at_trx_commit=1 が設定されていればログファイルに書き込みが行われる。0または2の場合には、ログバッファと呼ばれる領域にデータが保持される。その後、時間をおいてからテーブルスペースへ更新が反映されるのである。

なぜこのような仕組みになっているのだろうか？

コミットと同時にテーブルスペースを反映すればいいじゃないか？と思われるかも知れない。しかし、テーブルスペースへの更新には時間がかかるのである。なぜか？簡単に言うと、更新するべき箇所がたくさんあるからである。テーブルスペースに含まれているのはデータだけではなく、インデックス情報も含まれている。インデックスはBツリー構造になっているため、場合に因ってはインデックスを辿ったり、ページが分かれる場合にはリーフノード以外にも1つ以上のノンリーフノードを更新しなければいけない。そして、大抵の場合一つのテーブルに定義されているインデックスは複数あり、インデックスが増えればそれだけ更新するべきページも増えてしまう。さらに、一つのトランザクションにおいて更新するテーブルが複数ある場合も多い。更新するページはテーブルスペース内に離散して格納されているので、ディスクに対するランダムアクセスが発生してしまう。

つまり、テーブルスペースの更新はとてもコストが高いので、コミット時にこれら全ての更新を行っていたのでは書き込み性能が出ないのである。

そこで、まずはコミット時に全ての更新をWALに書き込み、時間が経ってからテーブルスペースを更新するという方法が採られている。この方法にはさらに次のメリットがある。

WALに対する書き込みはシーケンシャルなので、シーク時間がなくとても速い。
急激にたくさんの書き込み要求が来た場合に耐えることができる。
複数のトランザクションが同じページを更新した場合、書き込み回数を減らすことができる。

即ち、WALにはテーブルスペースにまだ反映されていないデータが含まれていることになる。つまり、InnoDBのデータは、テーブルスペースとログを合わせて初めて、完全な情報を含んでいるのである。なのでみだりにログを削除してはいけない。

MySQL サーバを普通にシャットダウンした場合でも、WALにはテーブルスペースに反映されていないデータが残っている。シャットダウンと同時にテーブルスペースへデータを反映させるには、innodb_fast_shutdown=0を設定すれば良い。このオプションはMySQLサーバ稼働中に変更できるので、以下のようにしてからシャットダウンすると、WALを削除しても安全な状態になる。

mysql> SET GLOBAL innodb_fast_shutdown=0;

MySQL サーバ稼働中、WALにだけ書き込まれたデータはどこにあるのだろうか？答えは、InnoDBバッファプールである。バッファプールはテーブルスペースに対するキャッシュ＋その他のデータを保持する領域であるが、対応するページがバッファプール内でだけ更新され、テーブルスペースは更新されていないという状態になる。このとき、そのページは「ダーティ」な状態であるという。

ダーティなページをテーブルスペースへ書き込む動作は、チェックポイント処理と呼ばれる。チェックポイントが行われると、「このページのチェックポイントしたよ！」という情報が、WALに書き込まれる。

また、InnoDBのログファイルは、クラッシュリカバリ時にRedoログとして使用される。最後のチェックポイント以降におこなわれた更新を、データファイルへ再適用するのである。ログファイルは全てスキャンされるため、ログファイルが大きいとクラッシュリカバリに時間がかかるようになる。

設定

innodb-log-file-size

デフォルトの5MBは小さすぎるので、とりあえず特にサイジングしていなくても100MBぐらいに増やしておくといいだろう。ディスクに余裕がある時、または非常に更新が多いサービスを展開するときは、ログファイルをデータディスクとは別のディスクにするといい。

性能向上のためにInnoDBログファイルのディスク領域としてSSDが有用なのではないかと思うのだが、ハードウェアがないため検証はしていない。

innodb-log-files-in-group

InnoDBログファイルの数を指定する。デフォルトおよび推奨は2である。ログファイルのトータルサイズは、（innodb_log_file_size × innodb_log_files_in_group）となる。ログファイルが複数ある場合、InnoDBはそれらを順番に使用する。

innodb-flush-log-at-trx-commit

InnoDBログファイルに対する書き込み時の挙動を調整するパラメータである。取り得る値は0、1、2の3種類で、それぞれ次のような意味がある。

0: ログファイルへの書き込みとディスクのフラッシュを1秒に1回実行する。コミット時には何もしない。
1: トランザクションがコミットする度に（コミットが完了してクライアントへ応答が返る前に）ディスクへの書き込みとフラッシュを実行する。ACIDのD=Durabilityを保証できるのはこの設定を利用したときだけである。
2: コミット時にログファイルへの書き込みを行うが、ディスクのフラッシュは1秒に1回である。

デフォルトは1で、よほどの事がない限りデフォルトで利用されることをを推奨する。また、0よりは2の方がマシである。

innodb-fast-shutdown

このオプションはMySQLサーバーがシャットダウンする際に、InnoDBのログファイルがどのように処理されるかを決定する。取り得る値は0, 1, 2の3種類。

0: ログの内容を完全にテーブルファイルへフラッシュする。最も安全だがとても時間がかかる。
1: いくつかの操作をスキップする。終了前にチェックポイント処理を行うため再起動時にリカバリが走らない。
2: ログの内容をファイルへ反映することだけを保証する。再起動後にはクラッシュリカバリが行われる。

よほどのことがない限りデフォルトの1でいいだろう。

innodb-log-buffer-size

InnoDBがログファイルへ書き込みを行う時に利用するバッファのサイズ。innodb_flush_log_at_trx_commit=1または2の時はログファイルへの書き込みがコミットする度に行われるので、InnoDBログバッファのサイズは小さくて良い。innodb_flush_log_at_trx_commit=0のときは、1秒間に行われる書き込み量に従ってサイジングするのが良いだろう。innodb_flush_log_at_trx_commit=0の場合でも、書き込み回数は多いが書き込み量は少ないという場合には、それほど大きな値にしなくても良い。一般的には8MB以上に増やしてもあまり意味がないとされている。

sync-binlog

何回のトランザクションごとにバイナリログをディスクへ同期するかを調整するパラメーター。AUTO COMMITモードのときには、いくつのステートメントごとに同期するのかを決定する。ディスクへの同期にはfdatasync()を使用する。ディスクへの同期はバイナリログの安全性を高めるが、同期の回数が多いと性能が落ちてしまう。しかしバイナリログの損傷は、特に多数のスレーブを使って負荷分散を行っているようなサイトでは非常に重要な問題であるので、極力避けたい問題である。そのような場合は小さな値をお勧めする。

sync_binlogが1に設定されるとMySQLは特別な動作をする。トランザクションがInnoDBだけから成る場合、2相コミットを使用してバイナリログがInnoDBのログと同期するようになるのである。(5.0.x以上・・・詳細失念)そのさい、トランザクショナルなファイルシステムと組み合わせると、バイナリログに対する中途半端な書き込みがなくなるのでさらに安全性が高まる。

バイナリログの安全性を高める場合、以下のような構成をとるのが最適だろう。

sync_binlog=1に設定する。
トランザクショナルなファイルシステムを使用する。(ext3やzfsなど。)
バイナリログをデータディレクトリとは別のディスクに保存する。（これはI/Oの負荷分散のためである。さらに性能を考えるなら、キャッシュ付きのRAIDカードまたはアレイ装置を使うといいだろう。）

ステータス確認

InnoDBログの状態は、SHOW INNODB STATUSコマンドで確認することが出来る。「LOG」セクションを見てみよう。

---
LOG
---
Log sequence number 0 531941548
Log flushed up to   0 531941548
Last checkpoint at  0 503180567
0 pending log writes, 0 pending chkp writes
116 log i/o's done, 0.00 log i/o's/second

Log sequence numberは、ログバッファへの更新が行われたトータルのバイト数、Log flushed up toはWALへの書き込みが行われたバイト数、Last checkpoint atは最後にチェックポイントが行われたバイト数である。innodb_flush_log_at_trx_commit=1ならば、Log sequence numberとLog flushed up toは非常に近い値になる。

サイジング

デフォルトの5MBでは大抵の場合不十分であることが多い。InnoDBのログファイルサイズは、innodb_log_file_sizeと innodb_log_files_in_groupの2つのパラメータで調整することができる。ログファイルのサイズは、2つのパラメータの積である。そして、ログファイルにはまだチェックポイントが完了していない全ての更新データを記録しておく必要がある。つまり、以下の式が成り立つのである。

innodb_log_file_size × innodb_log_files_in_group ≧ Log sequence number − Last checkpoint at

実際にシステムが稼働している状態で、最大で(Log sequence number − Last checkpoint at)がどのぐらいになるかを見てみよう。ログファイルサイズはそれより大きくする必要はない。ログファイルサイズが大きいと、クラッシュリカバリの時間が伸びてしまうというデメリットがある。InnoDBはクラッシュ後最初に再起動した際、ログファイルに含まれている全ての変更をテーブルスペースに反映する。なので、ログファイルサイズが1GBを超えるような場合には、クラッシュリカバリに長時間を要してしまう。特にMySQLサーバをHA化している場合には、切り替わりの所要時間に影響してしまうので、大きすぎるログファイルは禁物である。

個人的には、以下のような設定を推奨したい。

innodb_log_files_in_group=2（デフォルト固定）
innodb_log_file_size=Log sequence number − Last checkpoint atの最大値

これは実際に必要な量の倍のログファイルサイズという意味である。ざっくりと2倍のマージンを取っているが、突発的な負荷に備えるという意味でこんなもんではないだろうか。もし今InnoDBのログファイルのサイズを大きくし過ぎている場合には、実際の負荷を見て少し減らしてみるといいだろう。計測するのが面倒だ！という人は、大抵の場合innodb_log_file_size=128M程度で十分であるので、このぐらいのサイズで使い始めよう。

サンプル設定

innodb_log_files_in_group=2
innodb_log_file_size=128M
innodb_flush_log_at_trx_commit=1
innodb_fast_shutdown=1
innodb_log_buffer_size=8M
sync_binlog=1

参考

InnoDBログファイル