新世代クラウドネイティブデータウェアハウスAnalyticDBの「SQLインテリジェント診断」機能の詳細解説

SQL はビジネス ロジックを表現するためのシンプルで使いやすい言語ですが、スキャンされるデータの量とクエリの複雑さが増すにつれて、クエリのパフォーマンスはどんどん低下します。 SQL をチューニングするには、多くの場合、次の部分に重点を置く必要があります。

エンジン アーキテクチャを理解する必要がある: 多くの場合、ユーザーは、SQL エンジンのアーキテクチャ機能をある程度理解して、ビジネスのデータ分散特性やビジネス シナリオ特性と完全に組み合わせてデータ モデリングを実行し、SQL エンジンのアーキテクチャ機能に準拠したテーブル構造を設計する必要があります。

SQL 機能は多種多様です。アドホック クエリの SQL は、結合に関係するテーブルの数、結合条件、グループ化と集計のフィールドの数、フィルタリング条件など、さまざまな点で大きく異なります。

データ特性の大きな違い: ビジネス特性の変化に応じて、ユーザーのデータ分布特性も変化します。常に元のモデリング方法と SQL ステートメントに従うと、SQL エンジンの利点を最大限に活用できるという保証はありません。データ特性やビジネス モデルの変更により、SQL パフォーマンスが低下します。

これを基に、AnalyticDB For MySQL（新世代のクラウドネイティブリアルタイムデータウェアハウス、MySQLと構文互換、以下ADB）は、効率的でリアルタイム、機能豊富でインテリジェントな「SQLスマート診断」と「SQLスマートチューニング」機能をユーザーに提供し、SQLパフォーマンスチューニングのアイデア、指示、具体的な方法をユーザーに提供し、ユーザーの使用コストを削減し、ADBの使用効率を向上させます。

次に、ADB で新しくリリースされた「SQL インテリジェント診断」機能を、「遅いクエリの検出」 + 「遅いクエリの診断」の 2 つのステップとシナリオケースを通じて紹介します。 （追記：「SQLインテリジェントチューニング」は後続バージョンでリリース予定）

1. 遅いクエリの発見

遅いクエリを見つけるには、ユーザーはまず遅いクエリを見つける必要があります。 ADB のユーザー コンソールには、複数の次元で検索してユーザーが遅いクエリをすばやく見つけられるようにする「ガント チャート」や「クエリ リスト」など、ユーザーを支援するさまざまな方法が用意されています。さらに、診断ツールにより、ユーザーは過去 2 週間の完全なクエリ取得と分析を実行できるようになります。

1. ガントチャート

ユーザーは、「クラスター コンソール」 - 「診断と最適化」 - 「SQL 診断」を通じて SQL インテリジェント診断機能にアクセスできます。

まず、ガント チャート (スイムレーン チャートとも呼ばれます) が表示されます。クエリはさまざまなスイムレーンを通過します。ここでのスイムレーンは ADB クエリ キューではなく、異なる時間に実行されたクエリを区別するためにのみ使用されます。ガント チャートには、ADB インスタンス上のクエリの実行順序がグラフィカルに表示されます。各カラーブロックはクエリを表します。カラー ブロックの左側はクエリの送信時間、カラー ブロックの右側はクエリの終了時間です。カラー ブロックの相対的な長さは、クエリの実行時間を示します。カラー ブロックの色には特別な意味はなく、異なるスイムレーンを区別するためにのみ使用されます。

ガント チャートを使用すると、ユーザーは現在の時間範囲内で実行に時間のかかるクエリを非常に直感的に確認できるほか、どのクエリが並列で実行されているか、並列実行の期間も直感的に確認できるため、不良 SQL の影響を受けるクエリを判断するのに役立ちます。カラー ブロックの密度を使用すると、ADB インスタンスへの高負荷期間が特定のクエリの同時実行性の高さに関連しているかどうかを直感的に判断できます。

(II) クエリリスト

ガント チャートは、クエリ間の時間相関を直感的に反映できます。ただし、ユーザーが選択した時間範囲が広い場合、ガント チャート内のカラー ブロックが密集して区別が難しくなり、ガント チャート上のインジケーターが比較的制限されます。このとき、ユーザーは診断ツールのクエリ リスト機能を使用できます。クエリ リストには、データベース名、ユーザー名、クライアント セグメント IP、時間消費量、メモリ消費量、スキャン量など、10 を超える重要なクエリ レベルのインジケーターが表示されます。この情報と指標は、ユーザーが遅いクエリとリソース消費の原因をさらに特定するのに役立ちます。

高度な検索機能に関しては、診断ツールは次の 3 種類の検索方法を提供します。

1. あいまい検索と正確な検索:ユーザーは SQL 内のキーワードに基づいてあいまい一致を実行でき、正確な検索機能により、クエリ ID を決定した後でクエリを正確に検索できます。
2. 文字列型検索条件:検索ツールは、ユーザーが選択した時間範囲内で使用されたデータ名、ユーザー名、クライアント IP、リソース グループを自動的に識別し、ユーザーが選択できるドロップダウン ボックスを提供するため、ユーザーの検索効率が向上します。
3. 数値検索条件:ユーザーは手動で変換することなく、KB、MB、GB などの検索単位を自由に選択できます。

同時に、診断ツールを使用する場合、ユーザーは低速のクエリをダウンロードする必要があることがよくあります。ダウンロード後は、Excel などのツールでスロークエリをよりカスタマイズして管理・分析できるため、クエリリストのダウンロード機能も提供しています。

2. 遅いクエリの診断

(I) ADBにおけるクエリ実行プロセス

ADB 実行プロセスを紹介する前に、関連する 3 つの基本概念を簡単に紹介する必要があります。

ステージ

実行フェーズでは、ADB 内のクエリは、シャッフルが発生するかどうかに基づいて、まず複数のステージに分割されて実行されます。ステージは、実行計画の一部の物理的なエンティティです。ステージのデータ ソースは、基盤となるストレージ システム内のデータ、またはネットワークで転送されるデータになります。ステージは、異なるコンピューティング ノードに分散された同じタイプのタスクで構成され、複数のタスクがデータを並列に処理します。

タスク

タスクは、Executor ノード上のステージの実行エンティティです。同じタイプの複数のタスクがステージを形成し、クラスター内でデータを並列処理します。

オペレーター

演算子は、ADB の最小のデータ処理単位です。 ADB は、演算子によって表現されるセマンティクスまたは演算子間の依存関係に基づいて、データを処理するために並列実行を使用するか、シリアル実行を使用するかを決定します。

ADB でのクエリ実行プロセスを理解するために、典型的なブランチ集約クエリを例に挙げてみましょう。 SQL ステートメントは次のとおりです。

選択  COUNT (*), SUM (給与) 
 
 emplayereeより
 
年齢>30、年齢<32 
 
グループ 性別​

ADB 内では、フロントエンド コントローラー ノードが最初に SQL ステートメント要求を受信し、SQL ステートメントを解析および分析し、最後にオプティマイザーを使用して最終的な実行プランを生成します。全体の実行計画は、図の Plan0、Plan1、Plan2 のようにステージ分割の原則に従ってサブプランに分割され、それぞれ対応するノードに送信されます。

このうち、サブプラン Plan2 は、4 つのコンピューティング ノード上でタスク インスタンスの形式でデータを並列処理します。まず、データをスキャンしてフィルタリングし、次にデータのローカル集約を実行します。処理後、データは性別フィールドに従って下流のコンピューティング ノード、つまり Stage1 のノードに再分割され、サブプラン Plan1 の要件に従ってデータの最終的な集約が実行されます。最後に、データはステージ 0 のノードによって集約され、クライアントに返されます。

一般的なデータ ウェアハウスと同様に、ADB の実行プランは一般に「論理実行プラン」と「物理実行プラン」に分けられます。

論理実行プラン: マクロレベルでクエリ処理フローを理解する

論理実行プランは、クエリ処理ロジックをより高いレベルで表示します。ルールベースの実行プラン (RBO) は、フィルター条件をプッシュダウンできるかどうかを決定し、コストベースの実行プラン (CBO) は、複数のテーブルが関連付けられている場合の順序を決定します。したがって、論理実行プランは、実行中の関数呼び出しを減らすために複数の演算子を融合する必要があるかどうかや、自動生成されるコードの粒度など、物理的な実行中の特定の処理方法に焦点を当てていません。その結果、論理実行プランにはステージ レベルの実行統計のみが含まれることがよくあります。ただし、チューニングを実行する際には、ユーザーはオペレーター レベルに正確な統計情報を必要とすることがよくあります。

物理実行計画: 各オペレータの処理パフォーマンスをミクロレベルで把握する

論理実行プランと比較して、物理実行プランには、演算子間のデータ処理フローチャートと演算子の実行統計が含まれます。結合演算子や集計演算子によって占有されるメモリや、フィルター演算子によるフィルタリング前後のデータ量を正確に確認できます。ただし、すべての演算子がユーザーによって正しく理解される必要はありません。特に、一部の物理演算子はユーザーの SQL ステートメントに関連付けることができないため、物理実行プランのみを使用して問題を特定すると、ユーザーに大きな混乱が生じる可能性があります。

ADB の「SQL インテリジェント診断」機能は、論理実行プランと物理実行プランの融合ビューをユーザーに提供します。ユーザーは、融合実行プランを使用して、マクロ レベルからクエリ処理フローを理解し、ミクロ レベルから各演算子の処理パフォーマンスを把握できるため、クエリ パフォーマンスのボトルネックをより正確かつ迅速に特定できます。

（II）SQL自己診断機能

クエリ パフォーマンスの問題をユーザーが分析できるように、融合実行プランと階層実行プランを提供していますが、次の 2 つのシナリオでは、ユーザーが融合実行プランを使用する際に問題が発生することが判明しました。

ADB初心者ユーザー

MySQL ユーザーの学習および移行コストを削減するために、ADB は構文のほとんどを MySQL と互換性のあるものにしました。ただし、ADB のバックエンドは MySQL カーネルではなく、独自に開発された分散データ ストレージおよび分散コンピューティング システムです。 ADB の実行計画に直面したとき、ADB の初心者ユーザーは、最適化のどこに重点を置くべきか分からず、どこから始めればよいのか分からないことがよくあります。

ADB の複雑な SQL

複雑な SQL の場合、数百のテーブルの接続が含まれることが多く、ステージの数は数百に達し、演算子の数は数千に達します。実行計画図は非常に大きいです。上級の ADB ユーザーであっても、このような複雑な実行プランに直面すると、どこから始めればよいのかわからないことがよくあります。次の図は、196 ステージの実行プラン図です。

上記の問題に対処するために、実行プラン図に SQL 自己診断機能を追加しました。 SQL 自己診断機能は、実行プランのルールの形で専門家の経験を反映します。 ADB を初めて使用するユーザーは、診断結果に基づいてクエリ実行プロセスのパフォーマンスボトルネックを特定し、診断結果に基づいて ADB 実行プランで注意する必要がある主要な演算子を学習できます。複雑な実行プランの場合、SQL 自己診断により、ユーザーは実行プランの調整が必要な部分をすばやく見つけることができ、関連する調整方法とドキュメントが提供されるため、ユーザーは調整プロセス中にさらに的を絞った調整を行うことができます。

SQL 自己診断機能では、「クエリ レベルの診断結果」、「ステージ レベルの診断結果」、「演算子レベルの診断結果」の 3 つのレベルで診断結果と最適化の提案が表示されます。

複雑なオンライン SQL を例に、実行プランと SQL 自己診断ツールを使用してパフォーマンスの問題を特定する方法を紹介します。まず、低速クエリ検索ツールを使用して、メモリ消費量の多いクエリを検索します。 「診断」をクリックすると、クエリの診断ページが開き、「実行プラン」タブに切り替わります。次の図の 1 に示すように、クエリ レベルの診断結果により、現在のクエリ データはメモリ消費量の多いクエリであると判定されていることがわかります。

この時点で、大きなメモリ効果の原因を突き止める必要があります。 「メモリで並べ替え」をクリックすると、右側に、ステージで消費されたメモリの割合に応じてデータが逆順に並べ替えられていることがわかります。ステージ[1]が現在のクエリのメモリの87％以上を占めていることがはっきりとわかります。 Stage[1]をクリックすると、診断ツールは実行プランツリー内のStage[1]の位置に自動的にフォーカスします。ステージ[1]の実行統計を表示するには、ステージ[1]をクリックします。同時に、位置 5 では、Stage1 に大量のメモリを占有する演算子があることがわかりますが、詳細な情報はありません。次に、Stage[1]に入って、Stage[1]のどの演算子が大量のメモリを占有しているかを確認する必要があります。

「ステージ実行計画の表示」をクリックしてステージ[1]に入ります。まず、メモリによってソートします。 Join[317]演算子がステージ全体のメモリの99％以上を占めていることがわかります。演算子をクリックすると、演算子実行プラン ツリーによって現在の演算子が自動的に検索されます。すると、オペレータ診断結果の詳細情報が表示されます。この情報は、ハッシュ テーブル ユーザーの左結合を構築するときに大量のメモリが占​​有されることを通知します。診断結果には、公式のチューニング ドキュメントへのリンクも提供されます。ドキュメントに記載されているチューニング方法に従うと、オペレータのメモリ使用量を削減できます。

上記の例では、「クエリ レベルの診断結果」と「演算子レベルの診断結果」を使用して、SQL パフォーマンスの問題を特定します。 「ステージ別診断結果」の例を見てみましょう。

次の図に示すように、時間消費量でソートすると、ステージ[10]の時間消費率が最も大きいことがわかります。実行計画図のステージ[10]をクリックすると、診断結果列に「ステージ診断」と「オペレータ診断」の2種類の診断結果が表示されます。ステージ診断では、現在のステージの出力データが偏っていることがわかり、偏っているフィールドもわかります (データ偏りは、分散システムのパフォーマンスに重大な影響を与える問題です。ステージ出力データの偏りにより、現在のステージ処理データが時間的にロングテールになるだけでなく、下流のデータ処理にもロングテールが発生します)。同時に、テーブルスキャンが歪んでいることを通知するオペレータ診断結果があることがわかります。現在のステージ出力データ スキューは、データ スキューのあるテーブルをスキャンすることによって発生したものであると事前に判断できます。次に、位置決めと分析のためにステージ[0]に入ります。

ステージ メモリに入り、時間消費量で並べ替えると、TableScan 演算子に最も長い時間がかかることがわかります。次に、TableScan 演算子をクリックすると、診断結果でテーブルのデータ スキューに関する詳細な診断結果を確認できます。このテーブルには、データ分布フィールドの選択が不適切であるために、重大なデータスキューの問題があります。同時に、関連する公式チューニングドキュメントも確認できます。チューニング ドキュメントによると、適切な分散フィールドに調整することで、テーブル データの偏りがクエリ パフォーマンスに与える影響を軽減できます。

上記の 2 つの例から、統合実行プランと SQL 自己診断機能により、クエリ パフォーマンスの問題を迅速に特定し、特定のチューニング提案を提供できることがわかります。これにより、時間と労力の無駄が大幅に削減され、初心者ユーザーが ADB を使用するハードルが下がります。 SQL 自己診断の詳細な診断結果については、公式 Web サイトのドキュメント「SQL 自己診断」を参照してください。現在、クエリ関連のメモリ消費、時間消費、データスキュー、ディスク IO、実行プランなどの側面をカバーする 20 を超える診断ルールがリリースされています。今後さらに多くの診断ルールが導入される予定です。

3. その後の計画

上記の説明と例の分析から、現在の診断およびチューニング ツールは、さまざまな側面で SQL パフォーマンスの問題のトラブルシューティングに役立つことがわかります。しかし、実際のオンラインでの問題処理や勤務中の作業中に、インスタンスのパフォーマンスの問題を分析する際に、複数のユーザーのニーズが見つかり、まとめられました。

どの SQL をチューニングすればよいですか?

ユーザーが診断とチューニングのページを開くと、インスタンス上で実行されている数万、あるいは数千万もの SQL ステートメントが表示されます。時間の消費、メモリの消費、またはスキャン量で並べ替えることで、チューニングが必要な SQL ステートメントを事前にフィルター処理することはできますが、実際には特定の診断結果の観点が欠けています。例えば：

どの SQL ステートメントにデータ スキャン スキューがありますか?
インデックス フィルタリングに非効率的な SQL ステートメントはどれですか?
どの SQL ステートメントがステージ出力のスキューを引き起こしますか?
パーティション選択が不合理な SQL ステートメントはどれですか?
ユーザーは、特定の SQL の特定の診断結果のチューニングを完了した後、実際にチューニングする必要がある類似の SQL を把握する必要があります。そこで、特定の診断結果という次元から分析し、特定の問題を一気に解決できるツールをユーザーに提供していきます。

SQL に問題があります。これはテーブル作成方法が最適でないことに関係しているのでしょうか?

ADB バックエンドは、バックエンド ノード全体にデータが均等に分散されることに依存する分散データ ストレージおよび分散実行フレームワークです。 ADB は、パーティション テーブルやレプリケート テーブルなど、さまざまなビジネス シナリオに合わせてさまざまなテーブル タイプも設計します。一部のテーブル フィールドは保存時に集計されるため、クエリのパフォーマンスも向上します。ただし、多くの場合、ユーザーは、不適切なテーブル作成方法によってどのクエリが影響を受けるかを認識していません。後ほど、「データモデリング診断結果」と「クエリ診断結果」を連携させていただきます。ユーザーは、データ モデリング診断結果を通じて、不良なテーブル構造によって影響を受ける SQL ステートメントをすぐに知ることができます。逆に、ユーザーは特定の SQL ステートメントの診断結果を通じて、どのテーブルを最適化する必要があるかを知ることもできます。 2 種類の診断結果をリンクしてインスタンスを最適化することで、インスタンスのクエリ パフォーマンスの問題を根本から解決できます。