innodb_flush_log_at_trx_commit パラメーターと sync_binlog パラメーターは、REDO ログの記録方法と ApsaraDB RDS for MySQL インスタンスのバイナリログの更新ポリシーを管理するために使用されます。データベースのパフォーマンスとデータセキュリティのバランスをとるために、パラメーターを変更できます。
使用上の注意
パラメーターを変更する前に、データの損失やパフォーマンスの低下などの予期しない結果を防ぐために、パラメーターの意味と機能をよく理解しておくことをお勧めします。詳細については、「innodb_flush_log_at_trx_commit」および「sync_binlog」をご参照ください。
ワークロード、セキュリティ要件、インスタンスの仕様と構成など、ビジネス要件に基づいてパラメーターを変更できます。
オフピーク時にパラメーターを変更し、クエリのパフォーマンスの変化を継続的に監視することをお勧めします。
パラメーターを変更する前に、セキュリティ要件を決定する必要があります。ワークロードで高いセキュリティが必要な場合は、パラメーターを 1 に設定することをお勧めします。ワークロードで高いパフォーマンスが必要な場合は、
innodb_flush_log_at_trx_commitパラメーターを 2 に、sync_binlogパラメーターを 1000 に設定することをお勧めします。ただし、これらの設定によりデータが失われる可能性があります。パラメーターを適切に構成する必要があります。
次の設定は使用しないことをお勧めします。
sync_binlog=0: この設定により、バイナリログのローテーションでジッターが発生する可能性があります。innodb_flush_log_at_trx_commit=0: RDS インスタンスを再起動すると、データが失われる可能性があります。
パラメーター値と変更ルール
innodb_flush_log_at_trx_commit
このパラメーターは InnoDB ストレージエンジンに固有のもので、InnoDB ストレージエンジンが REDO ログを記録する方法を指定するために使用されます。有効な値: 0、1、および 2。
0: InnoDB ストレージエンジンは、ログバッファー内のデータを 1 秒ごとにディスクに書き込みます。トランザクションがコミットされると、InnoDB はプロアクティブにディスクをフラッシュしません。
利点: 最適なパフォーマンスを実現できます。
リスク: インスタンスのダウンタイムが発生した場合、最大 1 秒間のトランザクションデータが失われる可能性があります。
1 (デフォルト値): InnoDB ストレージエンジンは、トランザクションがコミットされるたびにログバッファー内のデータをディスクに書き込み、ディスクをフラッシュします。
利点: この設定は原子性、一貫性、分離性、耐久性 (ACID) に完全に準拠しており、最高のセキュリティを提供します。
欠点: 特に高並列シナリオでは、パフォーマンスのオーバーヘッドが高くなります。
2: InnoDB ストレージエンジンは、トランザクションがコミットされるたびにログバッファー内のデータをファイルシステムバッファーに書き込みます。次に、オペレーティングシステムは 1 秒に 1 回データをディスクにスケジュールしてフラッシュします。
利点: この設定は、値 1 よりも高いパフォーマンスと、値 0 よりも高いデータセキュリティを提供します。
リスク: インスタンスのダウンタイムが発生した場合、直近 1 秒以内のトランザクションデータが失われる可能性があります。
sync_binlog
このパラメーターは、MySQL バイナリログの重要なパラメーターです。このパラメーターは、バイナリログの更新ポリシーを指定するために使用されます。有効な値: 0、1、および N。値 N は正の整数を指定します。
0: トランザクションがコミットされると、MySQL はバイナリログをファイルシステムバッファーに書き込み、オペレーティングシステムはデータをディスクにスケジュールしてフラッシュします。
利点: 最適なパフォーマンスを実現できます。
リスク: インスタンスのダウンタイムが発生した場合、ディスクにフラッシュされていないバイナリログが失われる可能性があります。
1 (デフォルト値): トランザクションがコミットされると、MySQL はバイナリログをすぐにディスクに書き込みます。
利点: この設定は最高のデータセキュリティを提供します。
欠点: パフォーマンスのオーバーヘッドが高くなります。
N: N 個のトランザクションがコミットされるたびに、MySQL はバイナリログをディスクに書き込みます。
利点: この設定は、パフォーマンスとデータセキュリティの妥協点です。
リスク: インスタンスのダウンタイムが発生した場合、最大 N 個のトランザクションのバイナリログが失われる可能性があります。
最適化ルール
ワークロードで高いセキュリティが必要な場合は、
innodb_flush_log_at_trx_commitパラメーターとsync_binlogパラメーターを 1 に設定することをお勧めします。これにより、データがリアルタイムでディスクに書き込まれることが保証されます。ワークロードで高いパフォーマンスが必要な場合は、
innodb_flush_log_at_trx_commitパラメーターを 2 に、sync_binlogパラメーターを 1000 に設定することをお勧めします。これによりパフォーマンスは向上しますが、データが失われる可能性があります。次の設定は使用しないことをお勧めします。
innodb_flush_log_at_trx_commit=0: RDS インスタンスを再起動すると、大量のデータが失われる可能性があります。sync_binlog=0: バイナリログのローテーションが長時間ブロックされ、パフォーマンスと安定性に影響を与える可能性があります。
パラメーターを構成する手順
詳細については、「ApsaraDB RDS for MySQL インスタンスのパラメーターを変更する」をご参照ください。
パラメーター構成の例
次のセクションでは、次のシナリオで上記のパラメーターを構成する方法について説明します。
シナリオ 1: 読み取り専用 RDS インスタンスでレイテンシが発生する。
ApsaraDB RDS for MySQL では、読み取り専用 RDS インスタンスを作成して、プライマリ RDS インスタンスからの読み取りリクエストをオフロードできます。読み取り専用 RDS インスタンスに
sync_binlog=1およびinnodb_flush_log_at_trx_commit=1の設定を使用するデフォルトのパラメーターテンプレートを選択した場合、プライマリ RDS インスタンスが大量の書き込みリクエストを処理する必要があるときに、バイナリログが読み取り専用 RDS インスタンスにすぐに適用されない可能性があります。最適化の提案:
バイナリログが読み取り専用 RDS インスタンスにすぐに適用されるようにするには、読み取り専用 RDS インスタンスの
sync_binlogパラメーターを 1000 に、innodb_flush_log_at_trx_commitパラメーターを 2 に設定することをお勧めします。注: これらの設定を行っても、レイテンシが完全に解消されるわけではありません。
シナリオ 2: DTS ベースのデータ移行が遅い。
DTS のデータ移行機能を使用してデータを移行する場合、バイナリログと REDO ログが頻繁にディスクに書き込まれ、必要なインスタンスでパフォーマンスのボトルネックが発生する可能性があります。
最適化の提案:
データの書き込みを高速化するには、
sync_binlogパラメーターを 1000 に、innodb_flush_log_at_trx_commitパラメーターを 2 に設定することをお勧めします。データ移行が完了したら、ビジネス要件に基づいて設定をデフォルト設定に戻すことができます。
シナリオ 3: ホットスポットの更新によりインスタンスのパフォーマンスが低下する。
sync_binlogパラメーターとinnodb_flush_log_at_trx_commitパラメーターの両方を 1 に設定し、フラッシュセールや大規模プロモーションなどのシナリオで大量のデータが同時に書き込まれる場合、パフォーマンスが低下する可能性があります。最適化の提案:
並列性を向上させるには、
sync_binlogパラメーターを 1000 に、innodb_flush_log_at_trx_commitパラメーターを 2 に設定することをお勧めします。アクティビティが終了したら、設定をデフォルト設定に戻してデータセキュリティを確保できます。
参考資料
パラメーター診断 機能を使用して、パラメーターの最適化ソリューションを取得できます。