背景
データ量の爆発的な増加とユーザー要求の絶え間ない変化に伴い、データベースの管理と最適化はますます重要になっています。DBAや開発者として、よく次のような課題に直面します：
APシナリオでは、規模が大きく時間のかかるクエリが頻繁に発生し、大量のデータ処理と複雑なクエリ計算が伴います。そのため、実行状態をリアルタイムで監視する必要があります。
オンラインのマルチノード環境におけるスローSQLに直面した場合、分散実行計画の問題かどうかを判断し、どの長尾ノードが全体の計画を遅らせているかを特定する必要があります。
SQL実行計画が逸脱した原因を調査・判断する際、計画生成時のSQLパラメータが代表的でないか、統計データが不正確であることが原因である可能性があります。
従来の単一マシンデータベースと比較して、分散データベースが対処するシナリオはより複雑で、より多くのパスが関わります。1つのSQLの実行には数十個のノードの協調作業が必要となる場合があります。スローSQLが迅速に解決されない場合、正常なリクエストがブロックされたり、CPU負荷が急上昇したり、さらにはクラスタ全体の可用性に影響を及ぼす可能性があります。
シナリオの概要
ネイティブ分散データベースとして、OceanBaseはデータベースの管理・運用効率の向上、診断・チューニング体験の最適化に努めています。本記事では、OceanBaseがデータベースの効率的な診断に取り組む実践と考察を共有します。内容は以下の通りです：
APシナリオで直面する実行性能の課題について探求し、一般的な診断ツールを紹介します。
ケーススタディを通じて、real-time plan monitorを用いた分散実行計画の診断方法を示します。
診断・チューニングプロセスを簡素化・最適化する方法について考察し、OceanBaseのリアルタイムSQL診断アプリケーションを紹介します。
OceanBase APシナリオ診断の実践
APシナリオの実行性能が直面する課題
AP（Analytical Processing、分析処理）シナリオでは、実行ごとに通常大量のデータが関わり、複雑な多次元データモデリングが必要であり、大規模並列処理能力に依存してクエリを高速化します。
このようなシナリオでは、分散データベースにおける一般的な性能問題は主に以下の点に含まれます：
大規模データのスキャン
多くの分析クエリは大量のデータを処理する必要があり、しばしば全表スキャンや広範囲のデータスキャンを引き起こし、結果として高いI/O負荷と長い応答時間を招きます。不合理なパーティション設計により、パーティションプルーニングが無効になり、クエリ範囲が拡大して不要なデータをより多くスキャンすることがあります。OceanBaseデータベースでは、異なるパーティションが異なるノードに分散している可能性があるため、パーティションをまたいだクエリでは、システムがノード間やデータセンター間を効率的にデータスキャンできることが求められます。
複数テーブルの集計と結合
複雑な集計関数：分析シナリオでは集計関数（COUNT、SUM、AVG、MAX、MINなど）が頻繁に使用され、大規模データセットでの実行は非常に時間がかかります。
GROUP BY処理：基数の高いGROUP BY操作は大量のメモリとCPUリソースを消費し、クエリ性能に影響します。
大規模JOIN操作：分析クエリは通常、複数の大規模テーブルの結合を含みます。JOIN操作は大量のメモリとCPUリソースを占有し、特に結合条件の選択が不適切な場合、効率が低下する可能性があります。
不合理的なJOIN順序：オプティマイザーが選択したJOINの順序が合理的でない場合、中間結果セットが大きくなり、クエリ性能が低下します。
並列計算の性能が期待に達しない
データ分析では大量のデータを処理する必要があるため、AP業務は通常、高度に並列な計算能力に依存してクエリを高速化します。並列性能が期待に達しない場合、システム構成が不十分なのか、SQL自体に問題があるのかを判断する必要があります。さらに、APで使用されるSQLは通常実行時間が長く、その実行プロセスはユーザーには可視化されないため、実行中にそのリアルタイム情報と実行状態を監視する機能も、APシナリオでの利用において重要です。これらの課題には、システマティックな診断ツールと最適化戦略による効果的な対応が必要であり、それによってOceanBaseのAPシナリオにおける性能と信頼性を向上させることができます。
OceanBaseの一般的な診断ツール
上記の問題は避けられず、計画の複雑さを引き起こします。SQL実行のパフォーマンスが期待に達しない場合、しばしばその中のどこかの段階でボトルネックが発生している可能性があります。分散計画では複数のデータベースノードが処理を行うため、このような問題箇所の特定は特に困難です。
これらの課題を解決するために、OceanBaseはSQL Audit、Full Link Trace、DBMS_XPLANツールキットなど、一連のユーザー診断およびチューニングツールを提供しています。
例えば、以下の手順で、SQL Auditビューを用いてSQLレベルの診断を初歩的に行うことができます：
retry回数を確認する：retry回数が多い場合、ロック競合やリーダー切り替えなどが発生していないか検討する必要があります。
queue timeが非常に長いかどうかを確認する：ジッターが発生した場合、QUEUE_TIMEが非常に長いがEXCUTE_TIMEはそれほど長くないSQLは、通常、ジッターの影響を受けたSQLであり、ジッターを引き起こしたSQLではありません。ジッターが発生する原因は、通常、あるSQLの実行時間が非常に長くなり、他のSQLがCPUをタイムリーに取得して実行できず、キューで待機し続けることです。
GET_PLAN_TIMEが非常に長いかどうかを確認する：もしそうであれば、このSQLが計画にヒットせず、再び完全な計画生成プロセスを経由したことを示しており、通常は
IS_HIT_PLAN=0を伴います。EXCUTE_TIMEが非常に長いかどうかを確認する：非常に長い場合、以下のSQLを使用して、非常に時間のかかる待機イベントがないか確認できます。例えば、このSQLの待機イベントがIOを待つために長い時間を費やしていることが判明した場合、ジッターが発生した時点のディスク状態が正常であったか確認できます。
SQL Plan Monitorによるパフォーマンスボトルネックのリアルタイム分析
OceanBaseは多様な診断ツールを提供していますが、APシナリオでは依然としてSQL演算子レベルの実行監視サポートが不足しています。例えば、並列実行タスクがいくつに分割されたか、偏りがあるかどうか、HASH競合が深刻かどうか、実行がハングした際にどの演算子で停止したかなどの問題は、ログに依存して診断されます。
SQL全体の情報集計分析が完了しても、スローSQLが特定されない場合、問題はスケジューリング段階ではなくSQL自体にある可能性があります。このような場合、SQL_PLAN_MONITORビューを使用して演算子レベルの詳細な分析を行うことができます。SQL_PLAN_MONITORビューは、SQL文の実行状況をリアルタイムで監視するための非常に有用なツールです。このビューを通じて、SQL文の実行過程におけるリソース消費と実行状態を詳細に把握できます。これには、演算子の並列数、各スレッドでの行出力数、スキャン行数、実際の実行時間などが含まれます。ユーザーはこれらの情報を基にさらなる診断を行うことができます。
パフォーマンスボトルネックの調査
SQL_PLAN_MONITORビューは、実行過程における各演算子の行出力数および実行時間などの情報を記録しており、パフォーマンス診断時に最初に注目すべきデータ項目です。通常、各演算子がいくつのスレッド（並列度）を使用し、合計でいくつの行データを出力し、最初の行と最後の行のデータを出力する時間を確認することで、最も遅い演算子を特定できます。
以下では、TPC-H Q17を例に、1回の実行の記録を以下の図のように示します：
図中のデータと実行計画を組み合わせることで、LINEITEMのスキャンと7番目の演算子HASH JOINが今回の実行のボトルネックであることがわかります。一方、左テーブルPARTのスキャンはすぐに完了しています。このような場合、右テーブルのjoin列にインデックスを追加することができます。
インデックスを追加した後の実行記録は以下のようになり、接続方法はHASH JOINからNLJに調整され、LINEITEMテーブルの全表スキャンを回避し、実行時間は30秒から4秒に短縮されました。
リアルタイムI/Oデータ
4.2.4バージョン以降、SQL_PLAN_MONITORは、各演算子が各スレッド上で消費する時間やI/Oデータの詳細をリアルタイムで確認できるようになりました。I/OデータはOTHERSTAT_x_KEYとOTHERSTAT_x_VALUEを使用して取得できます。KEYとVALUEの対応関係はV$SQL_MONITOR_STATNAMEビューで取得でき、I/Oに関連するフィールドは以下のとおりです：
33：total io bytes read from disk 34：total bytes processed by ssstore 35：total rows processed by ssstore 36：total rows processed by memstore以下のスニペットから、その演算子のすべてのデータがsstableからスキャンされており、出力行数がスキャン行数の1%に満たないことがわかります。これは、現在のクエリがそのテーブルに対して最適化が必要であることを示しており、例えばフィルタ条件の調整や、現在のフィルタ列にインデックスを作成することが考えられます。
ロードスキュー問題
データが分散ノード間で均等に分布していない場合、一部のノードが大量のデータ処理タスクを担う一方で、他のノードはほとんどワークロードが発生しないことがあります。これにより、単一ノードのボトルネックが生じ、全体のパフォーマンスに影響を与えます。
例えば、数十個のパーティションを持つ大規模なテーブルがあるとします。そのうちの1つのパーティションに1日のピークデータが集中し、全体の90%に達する場合があります。このとき、各パーティションの特定フィールドにおける異なる値の数を並列して集計し、各スレッドが一部のパーティションの結果を計算してから集約する方法が考えられます。並列実行の効果を想像してみましょう。小さなパーティションを担当するスレッドは早々に終了して報告しますが、大きなパーティションを担当するスレッドは長時間かかり、他のパーティションはそのスレッドの終了を待つことになります。これにより、全体のパフォーマンスが向上しません。このようなシナリオでは、並列化のメリットは低くなります。なぜなら、各スレッドの負荷が均衡していないためです。
実際の業務シナリオでは、SQL_PLAN_MONITORビューを使用してロードスキュー問題を診断できます。plan monitorビューは、各ノード上の各スレッドの詳細データを記録しています。並列実行のパフォーマンスが期待通りでない場合、同一演算子内の異なるスレッドのレコードを比較することで、長尾ノードが実行プロセス全体を引きずっている可能性があるかどうかを分析できます。
OceanBaseリアルタイムSQL診断
開発者がスローSQLを分析する際の課題
OceanBaseは現在、いくつかの診断およびチューニングツールを提供していますが、特に開発者にとっては、いくつかの課題が残されています。
複数の手順とツールを使用する複雑さから、開発者はデータベースおよびOceanBaseカーネルに関する深い知識を持っている必要があります。
診断とチューニングのプロセスが複雑で煩雑であり、大量のSQLクエリとスクリプトを管理・維持する必要があります。
学習コストが高く、専門的なトレーニングと長期間の経験の蓄積がなければSQL診断手法を習得できません。
新設計のリアルタイムSQL実行解析
これらの問題を解決し、SQL診断のリアルタイム性と正確性をさらに向上させるため、OceanBaseはODC 4.3.1で全く新しいQuery Profile（実行解析）機能を導入しました。Query Profileは、OceanBase 4.xが提供するreal-time SQL plan monitor機能を可視化して表示し、SQL実行プロセス中に実際の実行計画の状態をリアルタイムで照会・表示するとともに、計画内の各演算子の実行時間とリソース使用状況を詳細に示します。
実行解析を通じて、ODC既存の実行計画可視化機能およびフルリンクトレース可視化機能と組み合わせることで、ユーザーは実行ボトルネックを直感的に把握し、各操作のパフォーマンスオーバーヘッドを理解することができ、クエリ性能を自ら分析・最適化できます。実行解析は、以下の重要な質問に答えるのに役立ちます：
どれだけのデータを処理する必要がありますか？
ボトルネックはどこにありますか？
フィルターや結合で最も頻繁に使用される列は何ですか？
これらの列は大部分のデータをフィルタリングしているのでしょうか、それとも常に大部分の行を抽出しているのでしょうか？
TPC-H Q15ステートメントクエリは、特定の期間内に総収入に最も貢献したサプライヤー（第1位）の情報を取得します。これにより、優先的なサプライヤーに対して報酬を与えたり、より多くの注文を与えたり、特別な認証を与えたり、激励を与えたりするなどのインセンティブを決定するために利用できます。Q15ステートメントの特徴は、グループ化、ソート、集約、集約サブクエリ操作が混在する一般的なテーブルとビューの結合操作です。このステートメントを例に、実行解析の機能と構造を説明します。ODCで実行後、実行結果のところで、実行プロファイルボタンをクリックします。
実行概要から、分散計画かどうか、計画キャッシュヒットの有無、総実行時間など、SQL実行の基本的な情報を確認できます。これは診断の背景情報として役立ちます。
グラフィカルビューは、実行計画がスケジュールされた順序を詳細に表示します。通常は下から上へと実行計画を読みますが、ODCの実行解析では、青い進捗バーが各演算子が実際に費やしたCPUコストを強調表示し、時間が最もかかった上位5ノードをソートして表示するため、パフォーマンスボトルネックの特定がはるかに容易になります。上記の可視化ビューから、迅速に導き出せる結論は以下のとおりです：
第一に、各ノードが費やした時間：時間はパフォーマンス問題を特定するための出発点です。各演算子の総実行時間から、ボトルネックを迅速に特定し、原因を分析できます。上記の例では、クエリで実行時間が最も長いノードは「LINEITEM」テーブルのスキャンノードです。また、主要なパフォーマンスボトルネックがルートノードの左側のサブプラン側にあることがわかります。
第二に、各ノードの行出力：次に、各ノードの行出力を確認します。LINEITEMテーブルは約230万件のレコードを出力しますが、右側のすべての演算子の出力行数は1です。
これで基本的な情報が得られ、大まかなパフォーマンスボトルネックの位置も把握できました。次に、実行解析の第二部分であるI/O統計と演算子属性について詳しく見ていきます。
上図の右側に示されているように、クエリ全体の概要部分が表示されています。確認できる主な簡単な指標には、総CPU時間、総I/O時間、実際の実行時間、読み取った行/バイト数（重要な質問、どれだけのデータを処理する必要があるか）などが含まれます。これらは診断の出発点であり、通常はほとんどのクエリの調整に役立ちます。上記の例に戻ると、図から重要な点に気づきます：ここのボトルネックは計画の左側のサブツリーです。確定したボトルネックである「TABLE FULL SCAN」ノードをクリックすると、クエリ演算子の実行詳細は以下のようになります：
右側のI/O統計にあるtotal rows processed by ssstoreフィールドから、現在のノードで完全なフルテーブルスキャンが行われたことがわかります。これはインデックスが使用されなかったことを意味します。L_SHAPEDATE列にはインデックスが作成されているにもかかわらずです。これは、オプティマイザーの評価によるものかもしれません。
ノードの形状から、現在の演算子が分散演算子であることがわかります。実行概要は、その演算子の並列度が15、傾斜度が0.1であることを示しています。傾斜度が小さいことから、スレッド間の負荷が比較的均等であり、全体のパフォーマンスを引きずる長尾ノードがないことが示されています。
各スレッドの実行詳細を確認したい場合は、右側で異なるスレッドに切り替え、各スレッドのスループット、スループット時間、再スキャン回数を確認することもできます。ODCは時間、メモリ、スループットの3つの軸でソートできるため、ボトルネックとなるスレッドを見つけやすくしています。
また、ODC 4.3.1ではSQL診断関連の機能が全面的に再設計され、1つのインタラクティブページで実行解析、実行計画、フルリンク診断の情報を同時に提供し、多様な形式のビューをサポートすることで、分散環境におけるユーザーのリクエスト問題診断効率を向上させています。
