このページは機械翻訳したものです。

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

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

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

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

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

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

  この問題が発生する可能性を最小限に抑えるには:

  • すべてのデータ変更がディスクに即座に書き込まれるのではなくキャッシュされるように、buffer pool のサイズを増やします。

  • 挿入に加えて更新および削除操作がバッファされるように innodb_change_buffering=all を設定します。

  • ビッグデータ変更操作中に COMMIT ステートメントを定期的に発行することを検討してください。これにより、単一の削除または更新が少数の行で動作する複数のステートメントに分割される可能性があります。

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

  この問題は、デフォルト設定の innodb_change_buffering=all ではあまり発生しないことが予想されます。これにより、更新および削除操作をメモリーにキャッシュできるため、最初の場所での実行が高速になり、必要に応じてロールバックも高速になります。 多くの挿入、更新、または削除を伴う長時間実行トランザクションを処理するサーバーでこのパラメータ設定を使うようにしてください。

• 予期しない終了が発生した場合に最新のコミット済トランザクションの一部が失われる可能性がある場合は、innodb_flush_log_at_trx_commit パラメータを 0 に設定できます。 フラッシュが保証されていなくても、InnoDB はとにかく 1 秒に 1 回ログをフラッシュしようとします。

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

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

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

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