BigQueryで高コストなクエリを見つけ出す

BigQueryのコストとパフォーマンス最適化シリーズの抜粋として、環境内でコストが膨らんでいるクエリを特定する方法を解説します。

BigQueryのコスト・パフォーマンス最適化シリーズより

本シリーズの第1回ではBigQueryでクエリを実行する前に整えておきたい前提を、第2回ではBigQueryの利用を最適化する際に陥りがちな代表的なミスを取り上げました。今回は、環境内でコストが最もかさんでいるクエリを特定する方法を見ていきます。シリーズで使用するコードはすべてこちらのGitHubリポジトリに置いてあります。

全体で最もコストの高いクエリ

最初に取り組むべきクエリは top_costly_queries.sql です。コストという観点では本シリーズで最も重要なクエリと言ってもよく、対象期間中に同じクエリが何度実行されたかも含めた総コストを算出します。狙いは2つ。環境全体で最もコストのかかっているクエリを突き止めることと、高コストなクエリが必要以上の頻度で走っていないかを確認することです。

1回あたりのコストが高いクエリ

このセクションで使う2つ目のクエリは top_billed_queries.sql です。環境内で最もコストの高いクエリを、コスト降順で一覧化します。最初のクエリと違って実行回数は加味せず、1回あたりの実行コストだけを表示します。

このクエリを走らせれば、最もコストの高いクエリが結果セットの上位に現れます。最上位のクエリが見つかったら、まず処理データ量(total*Billed列)を確認し、その数値とクエリ本体を突き合わせて、何がコストを押し上げているのかを見極めます。

重複するクエリへの対処

同一のクエリが何度も返されて結果が偏ったり見にくくなったりする場合は、代わりに top_billed_queries_deduplicated.sql を参照してください。内容は同じですが、まったく同一のクエリを重複排除し、1件だけ表示します。実行時間は長くなりスキャン量も増えますが、重複のない結果が得られます。

BigQueryには現時点でハミング距離やレーベンシュタイン距離といったネイティブの「文字列類似度」集計関数がないため、日付の違いだけといったわずかな差分のあるクエリをまとめてフィルタするのは比較的難しい作業です。必要であれば「文字列類似度」アルゴリズムを実装したUDFがいくつか公開されていますが、この種の計算負荷が高いアルゴリズムをUDFで動かすとかなり遅いため、BigQueryの外側で処理する仕組みを組んだ方が現実的です。

最もコストを使っているユーザー

コスト分析に使える3つ目のクエリは top_cost_users.sql です。こちらも環境内のコストの高いクエリを一覧化しますが、ユーザー単位でグルーピングしたうえで、コストの高いクエリ順に並べ替えます。

狙いは、どのユーザーやサービスアカウントが、どのクエリにどれだけコストを使っているのかを可視化することです。このリストには、気づかぬうちに非効率なクエリを走らせ続けているプロセスが含まれていることが少なくありません。極端な例では、別プロジェクトや別クラウド上にあるプロセスが原因になっているケースもあるため、不要なものを除外するフィルタを追加すると効果的です。

リポジトリ内のその他のクエリ

GitHubリポジトリには、Looker由来のクエリの抽出、クエリの実行回数の集計、特定ラベル付きクエリのコスト算出など、より特化した用途のクエリも収録されています。用途は限定的ですが、いずれもDoiTが実際に使ってきたものなので、コミュニティに広く共有しています。

パフォーマンスに問題のあるクエリを見つける

次に取り上げるのは、必要以上にリソースを消費し、想定どおりのパフォーマンスを出せていないクエリの洗い出しです。こうしたクエリはコストの高いクエリと重なりやすく、両者には一定の重複が見られます。

このセクションでは「複雑度(complexity)」を、クエリ実行中に使用されたスロット数として定義します。値としては、処理に費やされたタイムスロットの合計を、クエリの総実行時間で割ったものです。JOBS_BY_PROJECT ビューで計算する例を以下に示します。

SELECT
SAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS slotCount
FROM
`<project-name>`.`<dataset-region>`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

これはあくまで近似値です。BigQueryは正確な値を返してくれないためですが、BigQueryのUI上に表示されている値もこの計算に基づいています。

複雑度とスロット数

どのデータベースでも、クエリの論理的な複雑さが増せば、内部的な実行の複雑さもそれに比例して増えるのが一般的です。BigQueryも例外ではなく、さらにこの複雑度はクエリのコストともおおむね直結します。つまり、複雑度を下げれば基本的にコストも下がるということです。

クエリの複雑度を見極める際にGitHubリポジトリから主に使うのは、top_complex_queries.sql です。実行時間中に最も多くのスロットを消費したクエリを返してくれるため、上位を拾えばパフォーマンス上の問題を抱えていそうなクエリを絞り込めます。複雑なクエリは複雑なまま正しく動かす必要があるケースも多く、厳密な手法とは言えませんが、要注意候補を洗い出すには有効な方法です。

長時間実行クエリ

パフォーマンス上の問題を見つけるもう1つのアプローチは、実行時間が長いクエリに着目することです。ただし、長時間かかるクエリは「そうならざるを得ない」だけのこともあります。

longest_running_queries.sql は対象期間中の全クエリを実行時間の長い順に返すため、時間のかかっているクエリが上位に並び、調査がしやすくなります。このクエリのruntimeToBytesBilledRatio列は、1ミリ秒あたりに処理されたバイト数の目安を示します。一般にこの値が1を超えるなら、処理データ量を減らせる最適化の余地がないか確認すべきでしょう。

パフォーマンス問題に関する補足

本シリーズの今後の回では、パフォーマンスチューニングをさらに掘り下げ、BigQueryのパフォーマンスにまつわる代表的かつ意外と知られていない落とし穴と、その克服方法を紹介します。

リポジトリの汎用クエリ

本パートで最後に紹介するのは、GitHubリポジトリに収められた、これまでより汎用的な情報やメタデータを取得するためのクエリ群です。

ジョブタイプ別のクエリ

ジョブにはクエリ、ロード、エクスポート、テーブルコピーなどの種類がありますが、その中で圧倒的に多いのはクエリとロードです。これに合わせて、リポジトリの load_job_information.sql および query_job_information.sql は、それぞれのジョブタイプと有用なメタデータを返します。さらに汎用的な general_job_information.sql は、すべての種類のジョブと一般的なメタデータをまとめて返します。

同時実行クエリ

2022年12月、GoogleはBigQueryの同時実行クエリの挙動を変更しました。今後の変更やクエリキューの展開を見据え、プロジェクトあたり100クエリという同時実行のハードリミットが、利用可能なスロット数に応じてスケールするようになっています。執筆時点ではドキュメント化が十分ではありませんでしたが、サポートと既存ドキュメントから情報を集めました。

デフォルト値はゼロで、これはBigQueryがプロジェクトに割り当て可能なリソースに応じて同時実行数を動的に決定することを意味します。定額料金に切り替えると、予約(reservation)側で「最大同時実行数(maximum concurrency)」を設定し、希望する上限値を要求できます。Googleは必ずその値で運用されることを保証するわけではありませんが、可能な限り合わせる努力をします。公式ドキュメントはクエリキューのページこちらから確認できます。前置きはこのくらいにして、同時実行クエリの問題に踏み込みましょう。

並列実行のクエリ数が多すぎると、お馴染みの503「Service Unavailable」、あるいは新しい「Query was not admitted as the maximum concurrency has been reached.」というエラーが返ってきます。同時実行数の上限を超え、クエリがスケジュールされなかったということで、当然ながら避けるべき状態です。

Googleの新しい変更を十分に解析し理解できた段階で、影響を抑える方法や、そもそもこの状態に陥らない設計のヒントを紹介する予定です。それまでの間は concurrent_queries_by_minute.sql と concurrent_queries_by_seconds.sql を使い、分単位・秒単位の同時実行数の平均を確認しておくとよいでしょう。指定した期間における1分(または1秒)ごとの同時実行クエリ数の平均値を返してくれます。

クエリの実行回数

クエリは、ユーザーや何らかの自動化プロセスによって何度も実行されることがよくあります。query_counts.sql は、対象期間内に各クエリが何回実行されたかを表示します。特定のクエリが必要以上に頻繁に走り、コストを無駄にしていないかを確認するのに非常に有効です。指定期間中の合計コストも併せて表示されます。

次のステップ

この記事は、私のBigQueryクエリ最適化に関する連載を凝縮したもので、BigQueryのアップデートに合わせて今後も内容を拡充していく予定です。すでにDoiTをご利用中のお客様であれば、ここで紹介したステップはすべてDoiT Cloud Intelligence™のBQ Lens機能内で実行できます。BigQueryはもちろん、機械学習やビジネスインテリジェンスを含む幅広い領域での深い知見を活用したい方は、ぜひDoiTまでお問い合わせください。