BigQuery スロット需給バランスの改善〜クエリのパフォーマンス改善の事例から〜

2023年3月31日 10:00

メルカリ Analytics Infra チームの na0 です。この記事では、メルカリにおける BigQuery クエリの改善によるスロット需給バランスの改善について紹介します。

2023-03-30 には、新料金体系 BigQuery Editions も発表されています。こちらには、読み取りデータ量課金（オンデマンドモデル）の値上げも含まれており、スロット量課金（BigQuery Editions）との損益分岐点の変化から、クエリのパフォーマンスについて意識する組織は増えていくことでしょう。

今回紹介するクエリの改善は、データ利用者が意識することで、メリットとなる施策です。この記事をきっかけに、BigQuery 利用者がパフォーマンスを意識してクエリを書くことや、必要に応じて詳しい人に相談できるようになることを期待しています。

「クエリが遅い！」問題

メルカリ社内の BigQuery の月間利用者数が 1,000 人を超え、データ活用の機会が増加していく中で、BigQuery のクエリが遅い！と言った報告が度々挙がるようになりました。

BigQuery の計算リソース（スロット）の確保された量（供給量）を、使いたい量（需要量）が上回った結果、業務上必要なクエリに時間がかかり、タイムアウトエラーなどの問題が発生しました。業務上必要なクエリが回り切らない場合、お客様へメルカリ利用の価値を届けることが遅くなる、十分なサポートが提供できなくなるなど、多くの体験毀損が発生する可能性がありました。

こういった状況を避けるため、メルカリでは BigQuery を定額料金モデルで利用し、スロット需要量に対して十分なスロットを供給することで体験毀損が発生しにくい環境を作っていました。しかし、スロット供給量を増やし続け、利用料金が青天井に増え続けることは持続可能ではありません。そこで、持続可能な体験を届けるための「スロット需要量を減らす」施策の検討が進んできました。

利用状況の確認

スロットが不足していることは問題の報告や、実際の体感から明らかでしたが、効果的な対策のために問題の解像度を上げることが必要です。特に「いつ」、「なぜ」発生しているのかを調査することが重要です。

BigQuery の監査ログを使って、「いつ」、「なぜ」スロットが不足したのかを確認しました。当時、夜間を除きほぼ全ての時間帯でスロットが上限一杯使われていました（2023-03-30 にスロットの自動スケーリングが GA になっていますが、当時は一定量を購入していました）。

また長期間で見ると、緩やかにスロット時間の消費量が増加していることと、特定のチームで新規にスロット時間の消費が急増したことが明らかになりました。特に、一部のチームで極めて大きくスロット時間を消費していました。

これらに加え、重要度ごとにスロットのグループ（予約）に分けていましたが、重要度の低い予約から、重要度の高い予約のスロット時間が大きく消費されていました。BigQuery はデフォルトで他のアイドルスロットを自動的に使用するため、予約に沿った消費が行われるとは限らないことに注意が必要でした。

以上のような状態から、対応策を以下のように整理しました。

ほぼ全ての時間帯でスロットが上限いっぱい使用されている
全体のスロットを買い増す

夜間は比較的スロットに余裕がある
オフピーククエリを促す

特定のチームが新規に大きくスロット時間を消費している
スロット時間を大きく消費するクエリを改善する

重要度の低い予約から、重要度の高い予約のスロット時間が消費されている
重要なクエリのみが使えるスロットを供給する or 重要でないクエリが消費するスロット時間を減らす

スロット需給バランスの改善

「クエリが遅い！」問題への対応策を大まかに区分すると、以下のような施策が考えられます。

スロット供給量を増やす
スロット需要量を減らす
スロット消費量を平準化する

スロット供給量を増やす施策は青天井を避けるために適切に管理されるべきです。本記事では、スロット需要量を減らす検討を通して、スロット供給量を増やさずに済む方法を探っていきます。

スロット需要量の中から、簡単に減らせるジョブを見つける指標として、失敗し続けていることや、閲覧されていないテーブルを作成していることが挙げられます。また、スロット時間を極めて大きく消費するクエリの場合は、改善できた場合の効果が高いです。そのため、今回は、監査ログから以下のようなランキングを作成し、クエリが有効に使われているか、スロット時間を大きく消費するかを判別しました。

クエリあたりスロット時間消費量ランキング
スロット時間を大きく消費するクエリを特定する

ユーザあたりスロット時間消費量ランキング
中規模なクエリを複数実行し、スロット時間を大きく消費するクエリの塊（ワークロード）を特定する

参照テーブルあたりスロット時間消費量ランキング
複数ユーザーが似たようなクエリを実行し、スロット時間を大きく消費するワークロードを特定する

エラークエリあたりスロット時間消費量ランキング
エラーで機能を果たせない何らかの問題を持つクエリを特定する

作成されたテーブルの参照クエリ回数 ÷ 作成に使われるスロット時間消費量ランキング
参照コストと作成コストがアンバランスなクエリを特定する

これらのスロット時間消費量ランキングをもとに、利用者へのヒアリングを行い、クエリとワークロードの整理し、以下のような対策を行いました。

ほとんど使われていないか、失敗しているワークロード
ジョブを廃止する

リアルタイム性は低くて良いワークロード
クエリの実行スケジュールを夜間に移動する

スロット消費量が極めて大きく、リアルタイム性が求められるワークロード
クエリを改善する

これらの施策は監査ログを使って BigQuery 管理者から提案していますが、実際の対策はデータパイプラインの作成者が実施する必要があり、全社で協力して実施しています。ほとんどの施策は、BigQuery 管理者とデータ利用者双方が協力して、実施することが必要です。

民主的にクエリを改善する

ジョブの廃止や、スケジュールの変更は、呼びかけなどで比較的単純に取り組むことができます。一方で、クエリの改善は呼びかけだけで取り組むことは難しく、また全ての場合について BigQuery 管理者から提案することも難しいです。しかし、クエリの改善のほとんどは知ってさえいればデータ利用者自身が気づき、改善できる民主的な対策の一つです。

社内向けには、以下のようにデータを使う人が書くクエリの複雑さに応じて2 種類のドキュメントを提供して、取り組む問題に合ったクエリ改善を手助けしています。

クエリを書く全員向け
SELECT * を避けるといった比較的単純な改善案を含むドキュメント

アナリストなど複雑なクエリを高頻度に書く人向け
「スロット時間の消費量が極めて大きく、リアルタイム性が求められる」クエリに出くわした際の、ワークロード全体の再設計を含む複雑な改善ドキュメント

ここでは、複雑なクエリを高頻度に書く人向けのドキュメントのもとになったクエリ改善の一つの過程で出てきた観点についてまとめて紹介します。事例の中から他のクエリでも使える点を共有することで、データ利用者が自主的に改善に取り組むきっかけとアイデアを提供します。

クエリ改善の対象の例

この記事では、メルカリアプリの検索における A/B テスト評価に使われているクエリの改善について取り上げます。このクエリは、消費したスロット時間が 200 日スロット超と大きいクエリで、クエリあたりスロット時間消費量ランキングでも上位でした。また、類似するクエリを複数実行する必要があることから、ワークロード全体の消費したスロット時間は極めて大きくなっていました。

メルカリでは大量の A/B テストをおこなっており、この A/B テスト評価クエリは、詰まらせずに毎日安定して実行したいクエリでした。しかし、メルカリアプリの検索における様々な指標を計算するため、メルカリアプリのログを大量に参照しており、自己結合などの重い計算も含むことから、スロット時間を大きく消費するクエリになっていました。結果として重要なクエリにもかかわらず、A/B テストの対象期間が長い場合やスロットの混み合っている時間帯に実行する場合にはタイムアウトエラーが発生していました。

このように、スロット時間の消費量が大きく、毎日安定して実行したいクエリでないと、時間をかけてクエリを改善するメリットが出ない場合もあります。クエリの改善をどのくらい丁寧にやるかは、その規模や回数、安定性によって判断しましょう。

ワークロードの改善の流れ

今回は以下のような流れで、ワークロードのパフォーマンスを改善して、BigQuery スロット環境の改善を目指しました。

パフォーマンス計測
パフォーマンス改善の実施
パフォーマンス改善結果の計測

今回のパフォーマンス改善の目標は、実行時間を減らしタイムアウトしない安定したクエリにすることでした。ただ、今回使ったテクニックは、個別に消費するスロット時間を減らしたい場合にも十分利用することができるはずです。

クエリ群のパフォーマンス計測

最初にやることは現在のクエリ / ワークロードのパフォーマンス計測です。現在地を知り、何故時間がかかるのか仮説を立てることが必要です。

今回のクエリの一つのパフォーマンスを簡単に可視化したものがこちらです。

実行時間は 2.8 時間、消費したスロット時間は 205 日スロットでした。経過時間別に見ると、クエリ全体を通して多くのスロットを使い続けていることが分かりました。このことから、消費するスロット時間を十分に小さくすることで、実行時間を短くなると判断しました。

当時は、この図のような BigQuery System Tables Reports を利用してジョブを可視化していました。2023-03-30 現在では、クエリ結果の実行グラフがプレビューとなっており、BigQuery Web UI のクエリ実行結果の実行グラフタブからパフォーマンスの悪い部分を発見するのがおすすめです。

改善テクニック

この大きなクエリジョブを改善していきますが、BigQuery のクエリ改善の前にやれることがないか、以下の順に確認することをおすすめします。

スロットの買い足しやワークロードの整理は十分に検討されたか
クエリの分割はできないか
- クエリの汎用部分をテーブルにして、スロット時間の消費タイミングを分けたり、I/O や Compute コストを節約する
- 実験ごとに反復されるが、パラメータ等が共通化できない部品のみクエリチューニングが重要
BigQuery 公式ドキュメントに載っている対策を全て行う
- ただし、クエリが読みにくくなるクエリチューニングは避ける

パフォーマンスを改善する上で、公式ドキュメントに載っている対策のうち、特によく効いたものを紹介します。

よく効き、クエリ書き換えが少ない改善

公式ドキュメントのうち、「フィルタリングと並べ替えに関するベストプラクティス」、「関数のベストプラクティス」、「クエリで処理されるデータ量を減らす」は、効きが良く、クエリの書き換えも少ないため、誰にでも推奨できます。まずはこれらに取り組み、それでも満足するパフォーマンスが得られない場合は、クエリ書き換えが大きい改善に取り組みましょう。

今回、これらのドキュメントの中から、特に良く効いたものは以下の 4 つです。

内側のクエリで ORDER BY を使わない

最も外側のクエリでデータの配り直しが発生する場合は、中間のデータの並べ替えは無駄になる
参考：パフォーマンスが最大化するようにクエリオペレーションを並び替える | BigQuery | Google Cloud

最も外側のクエリで SELECT * を使わない

必要な列のみを参照することで I/O コストを節約できる
参考：SELECT * を避ける | BigQuery | Google Cloud

不要な行は早めに WHERE でフィルタする

必要な行のみを参照することで I/O と Compute コストを節約できる
参考：WHERE 句を使用する | BigQuery | Google Cloud
参考：JOIN を使用する前にデータを削減する | BigQuery | Google Cloud

JOIN の結合順序を考える

基本的には大きいテーブルに小さいテーブルを JOIN する方が最適化されやすい
参考：結合パターンを最適化する | BigQuery | Google Cloud

今回は当てはまりませんでしたが、テーブルのパーティション分割やシャーディングをするとクエリを書き換えずに、I/O コストを減らせる場合もあります。

しかし、今回のクエリはこれらの改善を行なっても十分なパフォーマンスが得られなかったため、クエリの書き換えが大きいものにも取り組みました。

よく効くが、クエリ書き換えが大きい改善

公式ドキュメントのうち、「ネストされ繰り返されているフィールドを使用する」「クエリ計算の最適化」、「クエリ出力を管理する」、「SQL アンチパターンの回避」は、効きは良いものの、クエリの書き換えが多くなるため、BigQuery のクエリを十分に理解している人に推奨されます。

これらの取り組みはクエリに対する十分な理解が必要で、クエリ内部の細かい実行単位（ステージ）で見ながら、特にどのサブクエリの Compute が遅いか、I/O をもっと効率化できないかを考えていきます。今回特に良く効いたものは以下の 3 つです。

複数回同じビューやサブクエリを評価することを避けて、テーブル化する

参考：同じ共通テーブル式(CTE)を複数回評価するのを避ける | BigQuery | Google Cloud

Not Awesome
スロット時間を大きく消費するビューやサブクエリを複数回評価している

-- アプリ内の購入直前のイベントを確認する
WITH
all_activity AS (
  SELECT
    *,
    -- 分析関数などの重い処理
    LAG(event)
      OVER(
        PARTITION BY user_id
        ORDER BY client_time) AS previous_event,
  FROM
    -- 非常に大きなログテーブル
    events.client_log
),
purchase_activity AS (
  SELECT
    *
  FROM
    all_activity
  WHERE
    previous_event.type = "PURCHASE_START"
)
-- 2回、previous_eventが計算される
SELECT
  *
FROM
  activity
LEFT JOIN
  purchase_activity
USING(purchase_session_id)

Awesome
複数回同じビューやサブクエリを評価することを避けて、テーブル化する

-- アプリ内の購入直前のイベントを確認する
CREATE TABLE your_dataset.activity_with_previous_event AS
  SELECT
    *,
    -- 分析関数などの重い処理
    LAG(event)
      OVER(
        PARTITION BY user_id
        ORDER BY client_time) AS previous_event,
  FROM
    -- 非常に大きなログテーブル
    events.client_log;

WITH
purchase_activity AS (
  SELECT
    *
  FROM
    your_dataset.activity_with_previous_event
  WHERE
    previous_event.type = "PURCHASE_START"
)
-- previous_eventの計算が1回で済む
SELECT
  *
FROM
  your_dataset.activity_with_previous_event
LEFT JOIN
  purchase_activity
USING(purchase_session_id)

UNNEST で行が増えるのを直前まで避け、最小箇所で使う

参考：ネストされ繰り返されているフィールドを使用する | BigQuery | Google Cloud

Not Awesome
UNNEST が不要な計算を、UNNEST で行数を増やしてから行う

-- アプリ内の特定の商品種別のインプレッションを ARRAY から求める
WITH activity AS (
  SELECT
    client_events_v2.*,
    impression
  FROM
    events.client_log
  LEFT JOIN
    UNNEST(impressions) AS impression
  WHERE
    -- impressions の展開を必要としない条件
    DATE(server_time, 'Asia/Tokyo') = "2022-11-01"
)
SELECT
  COUNT(DISTINCT IF(event.type = "ITEM_VIEW", event.id, NULL)) AS n_view,
  -- impressions の展開を必要とする条件
  COUNTIF(impression.type = "ITEM_TYPE_X") AS n_imp_x
FROM
  activity

Awesome
UNNEST で行が増えるのを直前まで避け、最小箇所で使う

-- アプリ内の特定の商品種別のインプレッションを ARRAY から求める
WITH activity AS (
  SELECT
    *
  FROM
    events.client_log
  WHERE
    -- impressions の展開を必要としない条件
    DATE(server_time, 'Asia/Tokyo') = "2022-11-01"
)
SELECT
  COUNTIF(event.type = "ITEM_VIEW") AS n_view,
  -- impressions の展開を必要とする条件
  SUM((
    SELECT
      COUNTIF(impression.type = "ITEM_TYPE_X")
    FROM
      UNNEST(impressions) AS impression)) AS n_imp_x
FROM
  activity

複数回同じサブクエリが評価されることと自己結合を避けて、分析関数を選ぶ

参考：自己結合 | BigQuery | Google Cloud

Not Awesome
複数回同じサブクエリ user_metrics を評価しているかつ、自己結合を行なっている

-- 購入数が全体の 99.99% を超える人をキャップする
/* 前略 */

user_metrics as (
  /* 重い集計処理 */
),

threshold as (
  SELECT
    type,
    APPROX_QUANTILES(n_buy, 10000)[OFFSET(9999)] AS threshold_n_buy
  FROM
    user_metrics
  GROUP BY
    type
),

user_metrics_with_capping as (
  SELECT
    *,
    IF(n_buy < thre_n_buy, n_buy, thre_n_buy) AS adjusted_n_buy
  FROM
    user_metrics
  LEFT JOIN
    threshold
    USING(type)
)

Awesome
複数回同じサブクエリが評価されることと自己結合を避けて、分析関数を選び計算量を削減する

-- 購入数が全体の 99.99% を超える人をキャップする
/* 前略 */

user_metrics as (
  /* 重い集計処理 */
),

threshold AS (
  SELECT
    *,
    PERCENTILE_DISC(n_buy, 0.9999)OVER(PARTITION BY type) AS threshold_n_buy
  FROM
    user_metrics
),

user_metrics_with_capping AS (
  SELECT
    *
    , IF(n_buy < threshold_n_buy, n_buy, threshold_n_buy) AS adjusted_n_buy
  FROM
    threshold
)

繰り返しになりますが、クエリが読みにくくなるクエリ書き直しは避けましょう。特に、書いた人が読めなくなるクエリ改善は、一時的にパフォーマンスが良くなったとしても、その後メンテナンスできなくなるリスクが高くなります。

クエリのパフォーマンス改善結果の計測

上記の改善の結果、消費したスロット時間をチューニング前の 7% 弱の 14 日スロットに削減しました。実行時間も 40 分程度に収まり、タイムアウトエラーも発生しなくなりました。

また、この情報をもとに、汎用的なクエリ改善案をまとめ、複雑なクエリを書くことの多い Analytics チーム内に提供しました。A/B テストの評価は他にも多くの Analytics チームメンバーが実施しており、今回のドキュメントをもとに、似たような遅いクエリも改善できました。

改善後のスロット需給バランス

今回のクエリ改善や、それに関するドキュメントの配布によって、全社全てのスロット需給バランスが改善したわけではありませんが、スロット時間消費量ランキング常連だった Analytics チームのメンバーが、ランクインする回数や順位は格段に下がりました。

他のスロット供給増や、オフピーク施策と合わせて、「クエリが遅い！」といった声は（一時的に）解消されました。今後も、良いサービスを作るための BigQuery クエリ需要増を支えられるように、スロット供給増だけでなく、スロット時間の消費効率を高めるためのサポートを提供していきます。

まとめ

社内のデータ活用の機会が増加していく中で、BigQuery のクエリが遅く、必要なサービスやサポートが提供できないというメルカリ利用者の体験を毀損しかねない状態に対応するため、以下のような取り組みを行いました。

スロット供給量を増やす
スロット需要量を減らす
スロット消費量を平準化する

特に、スロット需要量を減らす取り組みは、データ利用者の努力によって環境が改善されるものです。スロットのモニタリング体制を整え、様々な取り組みを協力して行なったことで、スロット消費を意識してもらえる良いきっかけになりました。また、クエリ改善の例を共有することで、主たるスロット時間消費者の Analytics チームのスロット利用が効率化されました。

一方で、データ利用者全員がクエリのパフォーマンスを意識しなければならない状態になるのは健全と言えません。引き続き、誰に何を意識してもらうべきか設計、発信しながら、データ活用を推進し、全社で協力してメルカリの利用者体験を改善していきます。

▼採用情報サイトはこちらから

この記事が気に入ったらサポートをしてみませんか？