InnoDBの設定変数について

半年間、MySQLを基礎からやり直すことにしたので、その間に勉強したことをQiitaに投稿していきます。

InnoDBの設定変数で気になるものを個人の記録用に、適当にピックアップして書いてます。

innodb_buffer_pool_size

このバッファ設定は最も重要なInnoDBに関する変数の1つであり、InnoDBテーブルデータとインデックスのキャッシュに使用するメモリ量を制御します。
ただ設定する際に注意しておいた方が良いのは、MyISAMはキーのみをキャッシュし、後はOSがデータをキャッシュするが、InnoDBはOSを信頼することなく、InnoDB自体がデータをキャッシュするという違いがあります。

innodb_flush_log_at_trx_commit

InnoDBは設計上、MyISAMよりずっと厳格なデータ安全性の要件を持っており、たとえトランザクション最中に電源が落ちても最小損失によってデータの一貫性を維持しようとしてくれます。
ですが、パフォーマンスとデータ安全性を考えた上での設定が必要になります。
InnoDBでは、サーバ起動時にリカバリに使用されるトランザクションログを管理しており、トランザクションログの整合性を保つ為に、トランザクションがコミットされるたびにディスクにログをフラッシュします。
ただ、この方法は頻繁に短いトランザクションを実行するアプリケーションの場合は、パフォーマンスの低下に繋がるので、ログのフラッシュの間隔（回数／時間）を制御するなどの対応が必要になります。

値	ログファイルへの書き込み間隔	ディスクへのフラッシュ処理	個々のトランザクションコミット
0	1回/毎秒	ファイルディスクリプタで実行される	何も実行しない
1	1回/毎秒	ファイルディスクリプタで実行される	ログファイルに書き込まれる
2	1回/毎秒	ファイルディスクリプタで実行されない	ファイルディスクリプタに書き込まれる

秒あたり1回のフラッシュは、100%保証されるものではないが、極端にCPU負荷がある場合以外は、実施間隔はほぼ1秒に近いということを頭に入れておいて下さい。

innodb_file_per_table

innodb_file_per_table を有効にした場合、新しいテーブルではインデックスとデータが別個のファイルであるtable_name.ibdに格納されることになり、利点としては、同一サーバにある各テーブルのバックアップとリストアに貢献できます。
しかし、バックアップにも制限があり、サーバがダウンしているときか、またはすべてのトランザクションがコミットされてかつ新しいトランザクションが開始していないときに行う必要があります。
また、リストアする時も、まずALTER TABLE tbl_name DISCARD TABLESPACEを実行し、次に.ibdファイルを適切なデータベースディレクトリにコピーし、その後にALTER TABLE tbl_name IMPORT TABLESPACEを実行する必要がある。
NO_ARGタイプなので、オプションが存在している場合はONになり、オプションが存在していない場合はOFFになる。ただし、任意で1または0のBoolean引数を取ることも出来ます。

innodb_lock_wait_timeout

innodb_lock_wait_timeoutは、潜在的なデッドロックを回避する為にあえて意図的にデットロックを発生させ、定期的にロック検出モニタを実行し、ロックの待機時間の制限枠を超えた場合に異常終了したことをクライアントに通知する設定変数です。

innodb_force_recovery

このオプションは、消失データを回復させる際の強さの度合いを0〜6の7段階で設定し、InnoDBに指示します。
3以下のinnodb_force_recovery値を使用してテーブルをダンプできる場合は、破損した個々のページ上の一部のデータしか失われないため、比較的安全に行うことが出来ますが、4以上の値は、データファイルが永続的に破損する場合があるため、危険であるとみなされています。

値	それぞれの機能
0	リカバリが強制的に実行されない通常の起動
1	破損したページを検出した場合でも、サーバーが動作できるようにする
2	マスタースレッドや、すべてのパージスレッドが実行されないようにする（パージ操作中にクラッシュが発生しそうになった時に有効）
3	クラッシュリカバリのあとにトランザクションロールバックを実行しない
4	挿入バッファーのマージ操作を回避する。すべてのセカンダリインデックスを削除して再作成する必要がある
5	データベースを起動するときに、Undoログを参照せずに、InnoDBに、未完了のトランザクションでさえコミット済みとして処理させる。
6	リカバリに関連したRedoログのロールフォワードを実行しないようにする。データベースページを廃止された状態のままにし、それによってBツリーやその他のデータベース構造にさらに多くの破損が発生する可能性がある。

大きい方の値には、小さい方の値の機能が全て含まれています
4〜6の値は、指定すると、データファイルが永続的に破損する場合があります。
また、MySQL 5.6.15の時点では、InnoDBを読み取り専用に設定します。