MySQL サーバー IO 100% 分析および最適化ソリューション

序文

ストレス テスト中に、リソース使用のボトルネックによって発生する最も直接的なパフォーマンスの問題が、ビジネス トランザクションの応答時間が長すぎることや、TPS が徐々に低下することなどである場合。問題箇所の分析では、通常、TOP や nmon を使用して CPU やメモリの使用状況を確認するなど、サーバーのリソース使用率を監視することが最優先事項であり、その後、ネットワーク IO やディスク IO の問題などの IO の問題をトラブルシューティングします。 ディスク IO の問題の場合、一般的な問題は、SQL 構文の問題、MYSQL パラメータ構成の問題、または IOPS スループット レートの問題につながるサーバー自体のハードウェア ボトルネックです。

この記事では主にMySQLサーバIO 100%の分析と最適化計画を紹介します。詳しい紹介を見てみましょう。

【質問】

書き込みが主となるMySQL 5.6.21データベースインスタンスがあり、IO %utilは100%に近い。

高い書き込みIOPS

【分析プロセス】

1. iotopツールを使用すると、現在のIO消費量が最も高いmysqlスレッドを確認できます。

2. スレッド 49342 のスタックを確認すると、ファイル 9 に対応する REDO ログが更新されていることがわかります。

3. ファイル番号9はREDOログの最初のファイルに対応します。

MySQL プロセスが REDO ログ ファイルを頻繁に更新するのはなぜでしょうか? REDO ログ フラッシュ戦略と組み合わせて分析する必要があります。鍵となるのは innodb_flush_log_at_trx_commit パラメータです。

デフォルト値は 1 で、これが最も安全です。ただし、書き込み負荷が高い場合はパフォーマンスに重大な影響を与える可能性があります。トランザクションがコミットされるたびに、MySQL はログ バッファー内のデータをログ ファイルに書き込み、ディスクにフラッシュします。

このクラスターの書き込みシナリオを考慮すると、書き込みのほとんどは小さなトランザクションであり、各トランザクションのコミットによってディスク フラッシュがトリガーされます。このシナリオでは、innodb_log_buffer_size と innodb_log_file_size を増やすことによる最適化の効果は明ら​​かではありません。

【最適化計画】

1. アプリケーション レベルでは、書き込み負荷が大きいシステムの場合、単一の挿入ステートメントを小さなバッチの挿入ステートメントに最適化できます。これにより、トランザクションのコミット数と REDO ログのフラッシュ数が減り、理論的にはパフォーマンスが向上します。

2. MySQL レベルでは、ログ タイプのシステムの場合、ダウンタイム時に少量のデータ損失が許容される場合は、innodb_flush_log_at_trx_commit パラメータを 2 に調整できます。

2 に設定すると、トランザクションがコミットされたときに書き込み操作のみが実行され、システム ページ キャッシュのみが書き込まれることが保証されます。したがって、インスタンスがクラッシュしてもトランザクションは失われませんが、システムがクラッシュするとトランザクションが失われる可能性があります。

このサーバーでのテストでは、パラメータを 2 に調整すると、IO 要求が 200M/S から 10M/S 程度に低下し、圧力が 10 倍以上軽減されます。

3. システムレベルでは、よりパフォーマンスの高いディスクに交換する

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。