高い iowait で MYSQL データベース サーバーを最適化する方法

データベース サーバーの高 iowait 最適化のケース

1. 特定のテスト環境では SQL の実行が遅いのに、他のテスト環境では SQL が非常に高速に実行されるという開発フィードバック。 2 つの環境は同じ構成を持ち、mysql サーバーのみがデプロイされます。

2. top コマンドを実行すると、SQL が遅いマシンのディスク iowait が SQL が速いマシンよりもはるかに高いことがわかります。 SQL はテーブルを読み取る必要があり、テーブルが大きく、スキャンされる行の数が多いため、これが SQL の実行速度が遅い主な理由であると推測されます。

3. マシンの iowait が高くなる原因は何ですか? iotop を実行すると、io を消費するプロセスは主に mysql であり、主に mysql での読み取り操作であることがわかります。

4. 大きなテーブルからデータを継続的にクエリする高頻度のクエリ ステートメントが他にも存在する必要があり、クエリ中にインデックスが使用されない可能性があります。スキャンされるテーブルの行数が多いため、高頻度の IO が発生します。その結果、IO 操作を必要とする他の SQL の実行が遅くなります。

5. どの SQL が原因ですか?一般ログをオンにすると、収集されたステートメントが多すぎて、オーバーヘッドの高い SQL を簡単に見つけることができないことがわかりました。

6. 低速ログをオンにし、long_query_time を 1 に設定して低速クエリをキャプチャし、pt-ioprofile を使用して、mysql データ ファイル内のどのファイルがより多くの IO を消費するかを追跡します。

7. スロー ログ (pt-query-digest を使用して集計できる) と pt-ioprofile の結果に基づいて、テーブル全体をスキャンする必要がある典型的な SQL が実際に 2 つあり、対応するテーブルが次のとおりであることがわかります。非常に大きいため、ディスク障害が発生します。

8.残りの問題は、テーブルまたはクエリを最適化することです。最も簡単で直接的な方法は、適切なインデックスを構築してクエリのパフォーマンスを向上させ、テーブル内でスキャンされる行数を減らすことです。パフォーマンスを引き続き向上させる必要がある場合は、SQL の記述方法を最適化し、調整することで問題を解決できます。テーブル構造、パラメータ設定の調整。

9. 利益が最も大きい方法から開始し、最初に SQL ステートメントを評価し、ステートメント内の条件に従って各フィールドのデータ分布を確認し、インデックスまたは複数列結合インデックスを作成する合理性を評価します。 Explain などを通じてフィールドを検索し、適切なインデックスを作成します。

10. 最後に、インデックスの構築後、テーブル全体をスキャンする必要がある元のステートメントにより、インデックスを介してスキャンされる行の数が効果的に削減され、サーバーの IOwait トピックが削減されることがわかりました。 SQL の実行速度が遅いという元のフィードバックは改善できますが、それでも理想的ではありません。

11. 最後に、遅いステートメントに対応するテーブルにインデックスを構築し、where 条件で使用されている間違った値の型を修正することで、SQL ステートメントの実行速度が大幅に向上し、サーバー全体の IO 消費量が減少しました。大幅に減少しました。

12. pt-query-digest を使用して、最適化された mysql サーバーによって生成された低速ログを集約し、pt-ioprofile を使用して最適化された mysql データ ファイルの IO 占有率を分析し、最適化の前後の違いを理解できます。

以上が高い iowait で MYSQL データベース サーバーを最適化する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。