Performance Tuning using Query Profile - Databricks Community - 96779の翻訳です。
本書は著者が手動で翻訳したものであり内容の正確性を保証するものではありません。正確な内容に関しては原文を参照ください。
イントロダクション
ビッグデータの高ペースの世界において、効率性を維持し、コストを削減するためには、パフォーマンスの最適化が重要となります。Databricks SQL(DBSQL)ウェアハウスは、データアナリスト、データエンジニア、データサイエンティストが大規模なデータセットに対して効率的なSQLクエリーの実行を可能にし、信じられない能力を提供するDatabricksプラットフォームにおける堅牢な機能です。しかし、適切なチューニングなしには、実行時間は遅延し、リソースの無駄や洞察の遅れにつながります。
パフォーマンスチューニングを促進し、実行時間を削減し、利用効率を改善するために、DBSQLはジョブに対する深い洞察を提供するツールであるDatabrickクエリープロファイルを提供します。クエリープロファイルを活用することで、データエンジニアやアナリストはボトルネックを特定し、クエリー性能を最適化し、全体的な実行時間を改善することができます。本書では、Databricksクエリープロファイルの完全なポテンシャルを活用することで、どのようにああなたのワークフローを変化させ、より高速なデータ処理やよりタイムリーな意思決定につながるのかを探ります。
何を議論するのか
ここでは、サンプルを用いてユーザーがDBSQLのクエリープロファイリングに慣れ親しみ、自身のクエリーをどのようにしてより理解できるのかにフォーカスしたいと思います。ここでは、
- DBSQLとクエリープロファイルの概要を説明します。
- 実行されたクエリーのクエリープロファイルをナビゲートします。
- シンプルなパフォーマンスチューニングのユースケースを議論します。
- プロファイルのメトリクスを探索します。
Databricks SQLのご紹介
Databricksのクエリープロファイルのパワーを効果的に活用するためには、オペレーションを行う文脈であるDatabricks SQLウェアハウスを理解することが重要です。高パフォーマンスのデータ分析の目的で設計されているSQLウェアハウスは、ユーザーが大規模なデータセットに対して複雑なクエリーをシームレスに実行できるようにするスケーラブルな環境を提供します。
DBSQLあるいはDatabricks SQLは、ユーザーがデータに対して直接SQLクエリーを実行できるようにするために、Databricksによって提供されるSQLベースのインタフェースです。馴染み深いSQL構文を用いて大規模データセットを分析するためのパワフルな手段を提供しており、データアナリストやデータサイエンティストによってアクセスできるようになっています。
このオファリングをより理解するには、こちらにアクセスしてください。SQLウェアハウスの詳細を理解するには、こちらにアクセスしてください。
クエリープロファイルのご紹介
Databricks SQL(DBSQL)において、クエリープロファイルは特定のSQLクエリーの実行に対する洞察を提供する詳細なレポートです。ユーザー自身のクエリーがどのように実行されているのか、潜在的なボトルネックや非効率性の特定の助けになります。
クエリープロファイルの主要なコンポーネント
- 実行: これは、クエリーがどのように実行されたのかの概要を示しており、実行されたステージやオペレーションの順序、それぞれの実行時間が含まれます。
- メトリクス: これには、合計実行時間、CPU時間、I/O時間、シャッフル時間のような重要なメトリクスが含まれており、ユーザーはリソース使用量を計測することができます。
- キャッシュ情報: 実行時にどのデータがキャッシュされたのかに関する詳細情報が含まれており、パフォーマンス改善を説明します。
- ステージのブレークダウン: クエリー実行のさまざまなステージのブレークダウンであり、さまざまなビューでそれぞれのステージにどれだけの時間が費やされたのかを表示します。
- 処理の詳細: スピル(溢れ)、使用されたファイルやパーティション、プルーニングなどに関する詳細です。
クエリープロファイルを分析することで、ユーザーは性能や効率性の改善のために自分のSQLクエリーを最適化することができ、最終的にはデータ処理の高速化やより効果的なデータ分析につながります。
クエリープロファイルは何の役に立つのか
クエリー実行の詳細を可視化するためにクエリープロファイルを活用することができます。クエリープロファイルは、クエリー実行におけるパフォーマンスのボトルネックのトラブルシュートの役に立ちます。例えば、
- それぞれのクエリータスクと処理時間や処理行数、メモリー消費のような関連メトリクスを可視化することができます。
- パッと見てクエリー処理で最も遅い箇所を特定し、クエリーを修正することによるインパクトを評価することができます。
- joinの爆発やフルテーブルスキャンのようなSQL文における一般的な間違いを発見、修正することができます。
クエリープロファイルとSpark UIは何が違うのか
あなたがSQLクエリーのパフォーマンスにフォーカスしており、アクセス可能でビジュアル的な洞察を持つユーザーフレンドリーなインタフェースを必要としているなら、DBSQLのクエリープロファイルを使いましょう。
特にSQL以外のさまざまなSparkオペレーションを含む複雑なジョブを取り扱っている場合には、Sparkアプリケーションに対するより包括的なビューを持つSpark UIを使いましょう。
加えて、必要であれば、クエリーの上部からアクセスできるオプションを通じて、Spark UIにアクセスすることも可能です。このオプションはサーバレスでは使えないことに注意してください。
Databricksのクエリープロファイルへのアクセス
クエリープロファイルへのアクセスは直感的なものです。ユーザーは、Databricks UIを通じて直接アクセスすることができます。
クエリー履歴
クエリー履歴はSQLウェアハウスを用いて実行されたSQLクエリーを表示します。このクエリー履歴は過去30日のクエリーデータを保持し、以降は自動で削除されます。
以下の手順でクエリー履歴にアクセスできます:
- サイドバーのクエリー履歴をクリックします - ユーザー、コンピュート、期間などのメトリクスでフィルタリングすることができます。
- SQLウェアハウスのモニタリング画面を通じて以下の操作を行います。
- 実行クエリー数やキューイングされたクエリー数をクリックします。
- 実行クエリー数やキューイングされたクエリー数をクリックします。
CAN_MANAGE権限を持たない非管理者ユーザーの場合、クエリー履歴では自分のクエリーしか参照することができません。
クエリーを実行するノートブック
- クエリーを実行します: ノートブックであなたのSQLクエリーを実行することからスタートします。
- 「パフォーマンスを表示」をクリックし、あなたのクエリーをクリックします。
クエリープロファイルの表示
クエリー履歴のリストでクエリーをクリックします。
- それぞれのタスクで要した時間、返却された行数、I/O性能のようなクエリー性能に関する簡単な情報が表示されます。
- 実行計画のようなクエリー性能に関するより詳細な情報に関しては、ページ下部にあるクエリープロファイルの表示をクリックします。
効果的なクエリーのプロファイリング
パフォーマンスチューニングでクエリープロファイルがどのように役立つのかについて、シンプルな例を見てみましょう。
このユースケースでは、3行のみをフィルタリングするために約2.07GBのDeltaテーブルをスキャンする以下のクエリーを小規模なSQLウェアハウスで実行することを考えます。
SELECT *
FROM test.data.trip
WHERE hvfhs_license_num = 'HV0003' AND
PULocationID = 199
ここでは、最初にスキャンのオペレーションに着目すると、70.59M行がスキャンされていることを確認でき、データスキッピングアルゴリズムがスキャンを最適化できていないことを示しています。このスキャンオペレーションにディープダイブするために、グラフビューのScanノードをクリックします。
これによって、このアルゴリズムは適切にデータセットをプルーニング(刈り込み)できておらず、大規模なスキャンのアウトプットになってしまっているという明確な像を得ることができます。それでは、この問題を解決するために、適切なカラムに対してZ-Orderを適用することでこのテーブルを最適化しましょう。クエリーの述語で一般的に使用されるカラムが予測でき、そのカラムのカーディナリティが高いのであれば、そのカラムを使いましょう。
OPTIMIZE test.data.trip
ZORDER BY (hvfhs_license_num, PULocationID);
それでは、同じSELECTのクエリーを実行し、プロファイルをチェックしましょう。
プルーニングの改善によって、スキャンの結果を得るのに要する時間が大幅に削減(1.81mから660ms)されており、これは"Bytes puned"で明確に確認することができ、より効率的なフィルタリングと全体的な実行時間につながっています。
プロファイルメトリクスの探索
ビジュアル表現
ハイレベルのアイデアを得る助けとなるグラフビュー、ツリービューでオペレーションを参照することができます。それぞれのオペレーションで要した時間やその他の有用な詳細情報も提供されます。
経過実時間のブレークダウン
- Wall-clock duration(経過実時間) - スケジューリングの開始とクエリー実行の終了の間の経過実時間です。
- Waiting for Compute(計算資源の待ち時間) - 計算リソースがプロビジョニング(SQLウェアハウスの作成、再開、リサイズなど)されるまでに要した待ち時間です。
- Waiting In Queue(キューの待ち時間) - 計算キャパシティが利用できるようになるまでに要した時間です。プロビジョニングされた全てのクラスターがビジーで追加のクエリーを受け付けられない場合に待ちが発生します。
- Optimizing query & pruning files(クエリーの最適化とファイルのプルーニング) - データスキッピング、クエリーの変更、セキュリティチェックによるクエリーパフォーマンスの改善に要した時間です。
- Executing(実行) - ソースデータのスキャンに要した時間を含む実行に要した時間です。
- Result fetching by client(クライアントによる結果取得) - 実行のリクエストを起動したクライアントが結果の取得に要した時間です。
トップレベルのプロファイルメトリクス
- Tasks total time(タスク合計時間) - これはクエリーのすべてのタスクを実行するのに要した合計時間を表現します。これは、個々のタスク全ての実行時間の合計となります。
- Tasks time in Photon(Photonタスク合計時間) - これは、クエリー性能を向上するために設計されたDatabricksネイティブのベクトル化クエリーエンジンであるPhotonを用いて実行されたタスクの合計時間のパーセンテージを示します。
- Rows returned(返却行数) - クエリーによって返却された行数の合計です。この場合、3行のみが返却されています。
- Rows read(読み込み行数) - クエリー実行の過程でデータソースから読み込まれた行数の合計です。
- Bytes read(読み込みバイト数) - データソースから読み込まれたデータ総量をメガバイトで表現します。
- Bytes read from cache(キャッシュからの読み込みバイト数) - キャッシュから読み込まれたデータのパーセンテージです。100%の場合、全てのデータがキャッシュから読み込まれたことを示しており、クエリー性能を劇的に改善することがあります。
- Bytes written(書き込みバイト数) - クエリー実行の過程でストレージに書き込まれたデータの総量です。この場合はデータは書き込まれていません。
- Files read(読み込みファイル数) - クエリー実行の過程でデータソースから読み込まれたファイル数です。
- Partitions read(読み込みパーティション数) - データソースから読み込まれたパーティションの数です。この場合はパーティションは読み込まれていません。
- Bytes spilled to disk(ディスクに溢れたバイト数) - クエリー実行の過程で一時的にディスクに書き込まれたデータの総量です。中間データを保持するのに十分なメモリーがない場合にスピルが発生します。この場合、ディスクへの溢れは発生していません。
オペレーターレベルのプロファイルメトリクス
- Time spent(所要時間) - スキャンオペレーションを完了するのに要した合計時間です。
- Rows(行数) - スキャンされたテーブルの行数です。
- Cache misses size(キャッシュにヒットしなかったサイズ) - キャッシュに無かったため、背後のストレージから読み込む必要があったデータの総量です。ここでは、キャッシュに無かったので2.07GBのデータがストレージから読み込まれています。
- Cloud storage request duration(クラウドストレージへのリクエスト時間) - クラウドストレージへのリクエストを行うために要した合計時間です。
- Filesystem read data size(ファイルシステムのデータ読み込みサイズ) - ファイルシステムから読み込まれたデータ総量です。これはゼロなので、ローカルファイルシステムではなくクラウドストレージから全てのデータが読み込まれたことを意味しています。
- Metadata time(メタデータの時間) - メタデータ読み込みに要した時間です。この場合、メタデータのオペレーションで時間は要していません。
- Scan time(スキャン時間) - スキャンオペレーションの実行に要した時間であり、全体的な処理時間と一致しています。
- Size of files read(読み込みファイルのサイズ) - スキャンの過程で読み込まれた全てのファイルの合計サイズです。
以下のように冗長モードを有効化することで、より詳細なメトリクスを参照できることに注意してください。
クエリープロファイルの共有
クエリー履歴のセクションで話したように、非管理者ユーザーはクエリー履歴を参照するには、ウェアハウスに対するCAN_MANAGE権限を持っている必要があります。アクセス権を共有することなしに、クエリープロファイルを共有する必要がある場合には、クエリープロファイルはjsonとしてプロファイルのダッシュボードをエクスポート、インポートする選択肢を提供しています。
あなたのクエリープロファイルの共有可能なjsonバージョンをダウンロードするには、シンプルにクエリー履歴ページでクエリーを選択し、以下のように画面右上のオプションからダウンロードを選択します。
他のユーザーはクエリー履歴ページでこのjsonをインポートすることができます。jsonのクエリープロファイルをインポートするには、以下のようにクエリー履歴ページの右上のオプションからImport query profile (JSON)を選択します。jsonファイルを選択するとクエリープロファイルがインポートされます。
クエリー履歴システムテーブル
Databricksでは、システムスキーマ内であなたのアカウントにおける過去の観測可能性を実現する分析目的のストアを提供しています。システムテーブルの詳細に関してはこちらをチェックしてください。システムテーブルをどのように有効化するのかを理解するために、要件セクションを確認するようにしてください。
特に我々のスコープにおいては、SQLウェアハウスで実行された全てのクエリーを追跡する助けとなるsystem.query.historyのシステムテーブルを議論したいと思います。これはパブリックプレビューで利用可能であり、あなたがテーブルにアクセスするのと同じリージョンにあるすべてのワークスペースのアカウントレベルの記録を保持しています。このテーブルのスキーマの詳細についてはこちらをご覧ください。
これは全体的に実行されたクエリーに関する詳細な情報を提供し、クエリー履歴を分析し、利用パターンやトレンドを特定し、キャパシティ計画やリソース割り当てを分析することによるディープダイブで活用でき、あるクエリーグループのパフォーマンスを追跡するための詳細メトリクスの定義で活用することができます。
さらに、DatabricksではList Queries APIを通じてこの情報にアクセスすることができます。詳細に関してはこちらをご覧ください。