8.5.2 InnoDB トランザクション管理の最適化

InnoDB トランザクション処理を最適化するには、トランザクション機能のパフォーマンスオーバーヘッドとサーバーのワークロードの理想的なバランスを見つけます。たとえば、アプリケーションで、秒あたり数千回コミットする場合にパフォーマンスの問題が発生し、2、3 時間に 1 回だけコミットする場合に別のパフォーマンスの問題が発生することがあります。

• デフォルトの MySQL 設定 AUTOCOMMIT=1 は、ビジーなデータベースサーバーにパフォーマンスの制限を課すことがあります。現実的であれば、SET AUTOCOMMIT=0 または START TRANSACTION ステートメントを発行し、すべての変更を行なったあとに、COMMIT ステートメントを発行することで、複数の関連 DML 操作を単一のトランザクションにまとめます。

  InnoDB は、トランザクションによってデータベースが変更された場合、そのトランザクションのコミットのたびにディスクにログをフラッシュする必要があります。変更のたびにあとでコミットされる場合 (デフォルトの自動コミット設定のように)、ストレージデバイスの I/O スループットによって、秒あたりに可能な操作数が制限されます。

• または、単一の SELECT ステートメントのみから構成されるトランザクションの場合、AUTOCOMMIT をオンにすると、InnoDB が読み取り専用トランザクションを認識し、それらを最適化するのに役立ちます。要件については、セクション14.13.14「InnoDB の読み取り専用トランザクションの最適化」を参照してください。

• 大量の行の挿入、更新、または削除後のロールバックの実行は避けます。大きなトランザクションによってサーバーのパフォーマンスが低下する場合、それをロールバックすると、問題が悪化し、元の DML 操作の数倍の実行時間がかかる可能性があります。ロールバックはサーバーの起動時に再度開始されるため、データベースプロセスを強制終了しても役立ちません。

  この問題の発生の可能性を最小にするには: すべての DML の変更をただちにディスクに書き込むのではなく、キャッシュできるように、バッファープールのサイズを増やします。挿入に加えて、更新および削除操作がバッファリングされるように、innodb_change_buffering=all を設定します。大きな DML 操作中に、COMMIT ステートメントを定期的に発行し、可能であれば単一の削除または更新を少数の行に対して操作する複数のステートメントに分割することを考慮します。

  ロールバックの暴走が発生した場合にそれを解消するには、ロールバックが CPU に依存して高速に実行するように、バッファープールを増加するか、セクション14.16.1「InnoDB のリカバリプロセス」に説明するように、サーバーを強制終了し、innodb_force_recovery=3 で再起動します。

  この問題は、MySQL 5.5 以上または InnoDB プラグイン付きの MySQL 5.1 では、あまり目立たなくなっていると予想されます。デフォルトの設定 innodb_change_buffering=all により、更新および削除操作がメモリー内にキャッシュされ、それらがそもそも高速に実行されるようになり、必要な場合にロールバックも高速になったためです。多くの挿入、更新、または削除を伴う長時間実行トランザクションを処理するサーバーでこのパラメータ設定を使うようにしてください。

• クラッシュが発生した場合に、最新のコミットされたトランザクションの一部の損失を許容できる場合は、innodb_flush_log_at_trx_commit パラメータを 0 に設定できます。フラッシュが保証されていなくても、InnoDB はとにかく 1 秒に 1 回ログをフラッシュしようとします。さらに、innodb_support_xa の値を 0 に設定し、これにより、ディスク上データとバイナリログの同期によるディスクフラッシュの数を減らします。

• 行が変更されるか削除される場合、行と関連付けられたUndo ログはただちに、またはトランザクションのコミットの直後でも、物理的に削除されません。以前または同時に開始したトランザクションが終了するまで古いデータは保持されるため、それらのトランザクションは変更または削除された行の以前の状態にアクセスできます。そのため、長時間実行トランザクションは、InnoDB が別のトランザクションによって変更されたデータをパージすることを妨げることがあります。

• 長時間実行トランザクション内で行が変更されるか、削除された場合、READ COMMITTED および REPEATABLE READ 分離レベルを使用するほかのトランザクションは、古いデータを再構築するために、それらの同じ行を読み取る場合、多くの作業を実行する必要があります。

• 長時間実行トランザクションでテーブルが変更された場合、ほかのトランザクションからのそのテーブルに対するクエリーは、カバリングインデックス技法を利用しません。通常、セカンダリインデックスからすべての結果カラムを取得できるクエリーは、代わりにテーブルデータから該当する値をルックアップします。

  セカンダリインデックスページに、新しすぎる PAGE_MAX_TRX_ID があることが検出された場合、またはセカンダリインデックス内のレコードに削除がマークされている場合、InnoDB はクラスタ化されたインデックスを使用してレコードをルックアップする必要がある可能性があります。