InnoDB ストレージエンジンのパフォーマンス調整

パラメータ名	主な効果	パフォーマンスへの影響
`innodb_buffer_pool_size`	データ/インデックスのキャッシュサイズを決定	読み込み性能向上。大きくするとI/O削減。
`innodb_flush_method`	ディスクへの書き込み方法の制御	適切な方法によりI/O効率化・書き込み速度向上。
`innodb_flush_log_at_trx_commit`	トランザクションログの書き込みタイミング制御	耐障害性と性能のバランスに影響。
`sync_binlog`	バイナリログの同期頻度を制御	値によって性能とクラッシュ時のデータ保全性が変化。

tamaco489

innodb_buffer_pool_size

show variables like 'innodb_buffer_pool_size';

概要：
- InnoDBが読み込んだデータ、及びインデックスを保持するためのメモリ領域を指定する。
- デフォルトは128MB。
推奨設定：
- 専用サーバー：物理メモリの 70〜80%
効果：
- キャッシュヒット率が上がり、ディスクI/Oが減少。
- 特に読み取りが多いワークロードで大きな効果。
注意点：
- 大きすぎるとOSがスワップを起こすため、物理メモリに余裕があることが前提。
- OSによるディスクキャッシュと二重でメモリを確保しないように、DBの読み書きを行う際には innodb_flush_method=O_DIRECT にしておく。
二重書き込みバッファーがアトミック書き込みをサポートする Fusion-io デバイス上にある場合、二重書き込みバッファーは自動的に無効になり、代わりに Fusion-io アトミック書き込みを使用してデータファイル書き込みが実行されます。ただし、innodb_doublewrite 設定はグローバルであることに注意してください。二重書き込みバッファーが無効になっている場合、Fusion-io ハードウェア上に存在しないデータファイルを含むすべてのデータファイルに対して無効になります。この機能は Fusion-io ハードウェアでのみサポートされ、Linux の Fusion-io NVMFS でのみ有効になります。この機能を最大限に活用するには、O_DIRECT の innodb_flush_method 設定をお薦めします。

tamaco489

innodb_flush_method

SHOW VARIABLES LIKE 'innodb_flush_method';

概要：
- InnoDBがデータファイルやログをディスクに書き込む方法を制御する。
主なオプション：
- O_DIRECT：OSキャッシュを経由せず直接ディスクに書き込み（推奨）
- fsync：標準的な同期書き込み
効果：
- O_DIRECTを使用するとダブルキャッシュを回避し、パフォーマンスが安定。
推奨設定：
- SSD使用時など、高速ストレージ環境ではO_DIRECTを選ぶとよい。

tamaco489

innodb_flush_log_at_trx_commit

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

概要：
- トランザクションコミット時にログをどのタイミングでフラッシュするかを指定。
設定値と効果：

値性能耐障害性

0 高速低い（障害時に1秒分のデータ消失）

1 遅い高（最も安全、デフォルト値）

2 中間中程度
推奨設定：
- 安全性重視：1
  - 本番環境ではこの設定が良い
- 性能重視：2や0（アプリ側での再送信や冗長化がある場合）
  - クラッシュ時に最大1秒間のデータを消失する可能性はあるものの、負荷試験環境や、データの一貫性がそこまで重要でない場合はこの設定でも良い。
  - 0にした場合は更新データを1秒毎にログに書くが、フラッシュはしない。
  - 2にした場合はコミット毎にログに書き出し、1秒毎にログをフラッシュする。

値	性能	耐障害性
0	高速	低い（障害時に1秒分のデータ消失）
1	遅い	高（最も安全、デフォルト値）
2	中間	中程度

tamaco489

sync_binlog

SHOW VARIABLES LIKE 'sync_binlog';

概要：
- バイナリログ（binlog）をどのタイミングでディスクに同期（fsync）するかを制御する。
- バイナリログはレプリケーションや障害復旧に不可欠なログ。
主な設定値：
- 0：OSに任せる（同期しない）
- 1：各トランザクションごとにバイナリログを同期（最も安全、デフォルト値）
- N（2以上）：N回に1回バイナリログを同期（性能と安全性のバランス）
効果：
- sync_binlog=1 に設定すると、クラッシュ発生時でもバイナリログの整合性が保証される。
- 0 や 1以外の数値にすると、書き込み性能は向上しますが、障害時に数トランザクション分のバイナリログが失われるリスクがある。
推奨設定：
- レプリケーションを使っている場合や、データ損失を最小限にしたい場合は 1。
- 高速なストレージがあり、性能を優先する場合は 100 などの大きめの値を設定してもよい。