MySQLのスレッド実行の急増とクエリの遅延の問題を解決する

背景

新年を迎える前は、一年間の仕事を振り返り、総括する時期であるはずですが、春節に向けてさまざまなプロモーションや活動が控えているため、多くの問題に直面しています。最近遭遇した問題を振り返ってみると、いくつかの問題は似ていることがわかりました。新年を迎える前に、それらをケースとして整理しておくのは良い時期です。データベースが停止し、応答しなくなるという現象が発生します。これは、ビジネス上のプレッシャーが大きい場合や、ビジネスが正常に実行されているが突然問題が発生した場合に発生します。

問題の説明

Tencent Cloud Database MySQL 自体には障害検出と高可用性のメカニズムがあるため、これらの問題が発生したとき、ユーザーが問題を報告してからトラブルシューティングのための実際の介入が行われるまでに数分が経過しましたが、高可用性スイッチはトリガーされませんでした。これは、問題はデータベース自体の障害ではなく、データベースが利用できなくなる外部的な原因でもないことを示しています。

当時のデータベースの状態を確認すると、非常に異常な指標が見つかりました。

問題が発生した頃、接続の総数と threads_running の数が短期間で急増し始め、約 30 秒間、監視プラグインでもデータを収集できなくなりました。同じ期間中に、CPU 使用率 (100% に到達) と遅いクエリの数も急増しました。基本的には、CPU 使用率、遅いクエリ、接続数が関連していることが確認できます。この 3 つの指標に基づいて、この問題の原因を分析できます。

原因分析

99% の場合、遅いクエリの数が急増している限り、問題は遅いクエリに関連していますが、ケース分析では結論を急ぐことはできません。さて、話を元に戻して、目標が3つの指標に絞られたので、3つの指標の意義を個別に考え、これらの指標の異常がどのような問題を引き起こすのかを見てみましょう。

CPU

CPU 使用率が高いということは、MySQL の計算能力が十分に利用されていることを意味します。MySQL の計算リソースを占有できるのは、ユーザースレッドと MySQL 自身のシステムスレッドだけです。この問題は明らかに MySQL システムスレッドとは関係がなく、ユーザースレッドが大量の CPU 計算リソースを占有していることを示しており、使用率が 100% に達し、このリソースの競争度が非常に深刻であることを示しています。CPU リソースの不足により、元々効率の高いクエリが非常に遅くなり、クエリが効率の高いクエリから非効率で遅いクエリに変わり、データベースの擬似死またはハングアップという現象が発生する可能性があります。

クエリが遅い

クエリの低速化はよくある問題です。クエリ効率が低いため、CPU、IO、メモリなどのリソースを過剰に占有し、他の通常のクエリに影響を与えます。監視指標から、CPU 使用率、IO 使用率、メモリ使用率がさまざまな程度に増加する可能性があります。深刻な場合には、これらの指標も急上昇し、データベース全体の応答が遅くなります。

接続数

接続数は通常、「実際の障害」の指標です。たとえば、接続数が max_connections の上限に達すると、データベース全体が新しい接続を作成できなくなります。プログラム側は応答しないのではなく、直接エラーを報告します。 thread_running インジケーターについては、公式ドキュメントの説明を参照してください。

スリープ状態ではないスレッドの数。

簡単に言えば、このメトリックの急上昇は、その時点で MySQL インスタンスに接続しているアクティブユーザーが多数いることを意味します。そして、今回のケースの監視チャートから判断すると、急上昇傾向が見られ、短期間に大量のアクティブな接続が発生したことを示しています。

分析する

これら 3 つの指標を簡単に分析すると、それらが相互に影響し合っていることがわかります。

クエリの蓄積が遅いと CPU 使用率が高くなる可能性があります。
CPU が高すぎると、全体的なクエリ効率が低下し、一部の効率的なクエリが低速なクエリになってしまいます。
遅いクエリの実行効率が低すぎるため、長時間アクティブなままになるため、Threads_running インジケーターは確実に増加します。
過剰な同時実行が突然発生すると、多数のアクティブなクエリによって Threads_running インジケーターが急上昇します。同時に、このスパイクのようなピークによって CPU が簡単にいっぱいになる可能性があります。

3 つの指標の急上昇の理由は一貫しているようで、この 3 つの指標だけに頼っていては、問題の原因を真に特定することはできないようです。では、これらの指標の急上昇の理由がなぜ一貫しているのか、よく考えてみましょう。核となる現象、つまり共通点があることがわかります。それは、クエリを蓄積できる必要があるということです。もし：

蓄積されたクエリはそもそも効率的ではないため、この問題の原因は基本的にクエリの遅さにあります。
蓄積されたクエリは非常に効率的であるため、この問題の原因は、瞬間的な同時実行性が過剰であるか、CPU 使用率が急上昇するその他の理由である可能性があり、その結果、これらの非常に効率的なクエリに影響を及ぼします。

したがって、蓄積されたクエリを確認することで、より直接的に問題を特定できます。上図のケースでは、蓄積されたクエリで group by と order by が大量に使用されており、クエリの効率が比較的低いため、根本的な原因は依然としてクエリの遅さにあります。

拡大する

冒頭でも述べたように、最近、同様の原因による問題がいくつか発生しています。このサージ事例以外にも、以下のような現象も発生しています。

threads_running は比較的安定した値を維持しています。前回の記事の分析を参照すると、この現象は平常時に長時間アクティブなクエリが約 10 個あることを意味していることがわかります。障害シナリオを予測できます。業務量が増加し続け、アクティブなクエリの数が増加します。効率的なクエリが影響を受け、効率がある程度低下すると、フロントエンドプログラム/ユーザーは、タイムアウトまたは応答が遅いために再試行を開始します。その後、クエリの効率が低下するため、再試行が繰り返しトリガーされ、雪崩効果を引き起こし、データベースを徐々に低下させます。

幸いなことに、同様の現象が複数発生した中で、問題が発生したのは予測されたシナリオの 1 つだけで、その他は時間内に最適化されました。