MySQL がデータの削除と挿入に非常に時間がかかる問題の解決策

会社の開発者がテスト環境で挿入ステートメントを実行すると、正常に実行されるまでに 10 秒以上かかります。テスト環境のデータベースパフォーマンス、データ量、デッドロックなどの情報を確認しましたが、異常は見つかりませんでした。最終的に、ログの書き込み方法を変更することでこの問題は解決されました。

1. 変更方法

/etc/my.cnf ファイルを変更し、innodb_flush_log_at_trx_commit = 1 を 0 に変更します。ただし、これにより、データベースがクラッシュしてから 1 秒以内にデータベースに保存されていないデータが失われるリスクが発生します。 MySQL のドキュメントでは、このパラメータについて次のように説明されています。

innodb_flush_log_at_trx_commit の値が 0 の場合、ログ バッファーは 1 秒ごとにログ ファイルに書き出され、ログ ファイルに対してディスクへのフラッシュ操作が実行されますが、トランザクションのコミット時には何も行われません。値が 1 (デフォルト) の場合、ログ バッファーはトランザクションのコミットごとにログ ファイルに書き出され、ログ ファイルに対してディスクへのフラッシュ操作が実行されます。値が 2 の場合、ログ バッファーはコミットごとにファイルに書き出されますが、ディスクへのフラッシュ操作は実行されません。ただし、値が 2 の場合でも、ログ ファイルのフラッシュは 1 秒ごとに実行されます。プロセスのスケジュールの問題により、1 秒ごとに 1 回のフラッシュが毎秒実行されるとは限らないことに注意してください。

2. パラメータの説明

• 0: ログ バッファは 1 秒ごとにログ ファイルに書き込まれ、同時にログ ファイルがフラッシュ (ディスクにフラッシュ) されます。このモードでは、トランザクションがコミットされても、ディスクへの書き込み操作はアクティブにトリガーされません。
• 1: トランザクションがコミットされるたびに、MySQL はログ バッファ内のデータをログ ファイルに書き込み、ディスクにフラッシュします。このモードはシステムのデフォルトです。
• 2: トランザクションがコミットされるたびに、MySQL はログ バッファー内のデータをログ ファイルに書き込みますが、同時にフラッシュ操作は実行されません。このモードでは、MySQL は 1 秒ごとにフラッシュ操作を実行します。
  

3. 注意事項

0 に設定すると、このモードは最も高速になりますが、安全性は低くなります。mysqld プロセスがクラッシュすると、最後の 1 秒間のすべてのトランザクション データが失われます。

1 に設定すると、このモードは最も安全ですが、最も遅くなります。 mysqld サービスまたはサーバー ホストがクラッシュした場合、バイナリ ログでは最大 1 つのステートメントまたは 1 つのトランザクションが失われる可能性があります。

2 に設定すると、このモードは 0 よりも高速かつ安全になります。最後の 1 秒間のすべてのトランザクション データは、オペレーティング システムがクラッシュした場合、またはシステムの電源が失われた場合にのみ失われる可能性があります。

2 つのパラメータ innodb_flush_log_at_trx_commit と sync_binlog は、MySQL ディスク書き込み戦略とデータ セキュリティを制御するための重要なパラメータです。両方のパラメータを 1 に設定すると、書き込みパフォーマンスが最悪になります。推奨される方法は、innodb_flush_log_at_trx_commit=2、sync_binlog=500 または 1000 です。

これで、MySQL のデータの削除と挿入が非常に遅い問題を解決する方法についての記事は終わりです。MySQL のデータの削除と挿入が非常に遅いことに関する関連コンテンツの詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。