※ 2020-04-09　追記

本記事に「スロット消費は実行時のサービスアカウントに紐づく」という内容で一部実際の仕様とは異なる・誤解をまねく内容があったため、一部内容を修正・加筆しました。（修正後は「スロット消費はデータのあるプロジェクトと別にできる」という見出しになっています）

こんにちは。エンジニアのg0eです。

今回は、6,000スロットを使うBigQueryのリソース配分最適化への挑戦と題して、Google BigQueryの主にスロット周りの話について書いてみたいと思います。

背景

当社ではプロダクトの中でBigQueryをかなりヘビーに活用しています。どのような使い方をしているか、どのような工夫をしているか、については「プレイドのCTOが登壇しました！〜Google Cloud Next '19 in SFレポート〜」を是非見て頂ければと思います。

登壇の中でも軽く触れているのですが、当社ではBigQueryを深く活用するために、Flat-rate（定額料金）で6,000スロットのコミットメントを購入して、組織内の複数のプロジェクトに割り当てて利用しています。

BigQueryの特性上リアルタイムに結果が返ってくることは期待してはいけませんが、なるべく最新のデータを参照するために高頻度で更新クエリを実行しているところなど、クエリの実行遅延に対してセンシティブにならざるを得ない箇所もあったりします。

まれに重いクエリが複数同時に実行されて他のクエリへの影響が気になることもあるため、今回はBigQueryでクエリを実行する際のリソース量の単位として使われるスロットについて調査して、6,000スロットのリソース配分の最適化について考えてみようと思います。

そもそもスロットとは？

BigQueryの公式ドキュメントによると以下のように書いています。

BigQuery スロットとは、SQL クエリの実行に必要な演算能力の単位です。BigQuery はクエリのサイズと複雑さに基づいて、クエリごとに必要なスロットの数を自動的に計算します。

詳細については上述の公式ドキュメントに任せるとして、なぜスロットが重要なのかと言うと、BigQueryではクエリを実行する際に、処理を一度キューに積んで、同時実行可能なスロット数に応じた実行制御が行われる仕組みになっているからです。

新しいクエリが到着した際に、既に利用可能なスロットがいっぱいだった場合でも、実行中のクエリ間でスロットの再割当てが行われるため、まったく処理が進まないということはなさそうですが、同時実行されるクエリが多ければ多いほど1クエリあたりのスロットが少なくため、結果としてクエリの実行完了までの所要時間が伸びてしまいます。

スロットとは、いわゆるクエリの重さをはかる単位であり、重いクエリ（＝スロット消費の多いクエリ）が同時にたくさん実行されると、クエリの実行完了までの時間が伸びてしまうと理解しておけば良いのではないでしょうか。

利用可能なスロットの上限

次に同時実行可能なスロットの上限数についてみていきましょう。

従量課金の場合

BigQueryの割り当てと上限によると、通常の従量課金での利用（オンデマンド料金）の場合は、2,000スロットとあります。0現在使っているプロジェクトでどれだけ割り当てられているかは、GCPのモニタリングのBigQueryのダッシュボードの Slot Utilization の項目で確認できます。1（自分も個人で利用しているGCPのアカウントを見たところ、チャート中に total_available: 2000 という記載がありました）

また、ドキュメントをよく読むと以下のような記載があり、同一プロジェクト内で実行されるクエリ内で上限を共有する形であることがわかると思います。

BigQuery スロットは、単一のプロジェクト内のすべてのクエリで共有されます

定額料金の場合

BigQueryでは従量課金（オンデマンド料金）以外に、月定額契約によるスロットのコミットメントを購入することができます。背景で書いた通り、当社ではこの定額契約を利用して、6,000スロットをコミットメント枠として購入しています。

月定額契約の場合、クエリ毎の処理データ量による課金が発生しないため、極端に重いクエリでなければ、処理データ量自体はそこまで意識しなくても良いのですが、コミットメントにより購入しているスロット数による同時実行制御は、使えるスロット数が多いとはいえ従量課金の場合と同様に発生するため、重いクエリを複数実行する時は、消費するスロット数を意識して使う必要があります。

従量課金の場合は、スロットは同一プロジェクト内のクエリで共有されるとありましたが、定額契約の場合は購入したコミットメントを同じ組織内の複数のプロジェクトで共有することができます。（詳しくは公式ドキュメントの予約の項目にある図をみて頂けると分かりやすいかと思います）

ここでポイントとなるのが、予約(Reservation)を使ってスロットを分配する際に、アイドル状態（使われずに余っている状態）にあるスロットは、 ignore_idle_slots が false であるという点です。逆に、特定のプロジェクトで利用するスロットを予約の範囲内に制限したい場合は、 ignore_idle_slots を true しておくことで実現できます。

特定プロジェクトの上限を制限したい場合

特定のプロジェクト（下記例ではプロジェクトB）で重いクエリが投げられる時があり、その影響が他プロジェクトに及ぶのを制限したい場合、以下のように設定します。

合計1,000スロットをプロジェクトAとプロジェクトBで使うと仮定
プロジェクトBに200スロットを予約として割り当てて ignore_idle_slots を true に設定
プロジェクトAは default の予約を ignore_idle_slots が false のまま利用
期待される挙動は以下の通り
- プロジェクトBのスロット消費が0の時、プロジェクトAは1,000スロットまで使うことができる
- プロジェクトBで重いクエリが実行された場合でも、プロジェクトBは200スロットまでしか利用できず、プロジェクトAは800スロット分を利用できる

特定プロジェクトで下限を保証したい場合

特定のプロジェクト（下記例ではプロジェクトB）で定期的に実行するクエリ（700スロットあれば十分に実行可能）をなるべく遅延させずに実行したい場合、以下のように設定します。2※ 定期的に実行するクエリに必要なスロットが500以下で、2プロジェクトしかない場合は、最終的にプロジェクト間での消費スロットが均等になるはずなので、初期設定のままでも良さそうです

合計1,000スロットをプロジェクトAとプロジェクトBで使うと仮定
プロジェクトBに700スロットを予約として割り当てて ignore_idle_slots は false のまま利用
プロジェクトAは default の予約を ignore_idle_slots が false のまま利用
期待される挙動は以下の通り
- プロジェクトBのスロット消費が0の時、プロジェクトAは1,000スロットまで利用できる
- プロジェクトAのスロット消費が0の時、プロジェクトBは1,000スロットまで利用できる
- プロジェクトAで重いクエリが実行されている場合でも、プロジェクトBは700スロットを最低限利用できる

スロット利用量の確認方法

プロジェクト単位の確認方法

各プロジェクトでどの程度のスロットが利用されているかは、GCPのダッシュボード上から簡単に確認できます。先程のプロジェクトに割り当てられているスロットを確認した時と同様に、GCPのモニタリングのBigQueryのダッシュボードの Slot Utilization の項目で確認できます。

クエリ単位の確認方法

昔は監査ログの深いネストの中からがんばって取り出す必要がありましたが（詳しくはこちら）、つい最近、INFORMATION_SCHEMA.JOBS_BY_*で簡単に見ることができるようになりました。

組織別、プロジェクト別、ユーザ別のテーブルがあるみたいですが、今回はプロジェクト単位で確認したスロット利用量をブレークダウンしてクエリ単位でみるという前提にたって、プロジェクト別のテーブルを詳しくみてみました。（スキーマはどの単位でも変わらないみたいなので、あまり気にしなくて良さそうです）

JOBSテーブルの中身を確認

スキーマ定義を眺めるよりも、まずは実データを見てみるのが早いかと思います。3 creation_time が Partitioning column になっているので、データ処理量を減らすために指定しましょう。 referenced_tables や job_states 、 timeline といったRECORD型も、データ量が増えてプレビュー画面が重くなるので一旦抜いています。total_slot_ms** が今回のテーマである当該クエリで消費されたスロット量になります。**

（何も考えずに、Exampleのクエリを実行したら end_time しか指定がなくて全期間のデータがクエリ対象になっていてデータ量すごいなと思ったのですが、罠でした）

SELECT
  creation_time,
  project_id,
  project_number,
  user_email,
  job_id,
  job_type,
  statement_type,
  start_time,
  end_time,
  query,
  state,
  reservation_id,
  total_bytes_processed,
  total_slot_ms,
  error_result,
  cache_hit,
  destination_table
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time BETWEEN
  	TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
    AND CURRENT_TIMESTAMP()
LIMIT 100;

クエリ中の文字列で分類してスロット使用率を算出

JOBSテーブルには実行されたクエリ(SQL)がそのまま保存されているため、クエリに一定の規則性があれば、その規則性を使って分類して集計することができます。クエリ中のコメント文字列もそのまま保存されているため、コメント中に調査用にユニークな文字列を埋め込んでおくと便利かもしれません。

当社の場合だと以下のようにクエリの先頭にクエリを識別できるコメントを記載しています。

/* UPDATE_XXX_TABLE_QUERY */
SELECT
  hoge,
  ...

以下がクエリサンプルになります。

WITH categorized_jobs AS (
  SELECT
    CASE
      WHEN REGEXP_CONTAINS(query, "xxx") THEN "xxx"  --- ここで分類を列挙
      WHEN REGEXP_CONTAINS(query, "yyy") THEN "yyy"
      WHEN REGEXP_CONTAINS(query, "zzz") THEN "zzz"
      ELSE "others"
    END AS query_category,
    total_slot_ms
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE
    creation_time BETWEEN
    	"2020-03-04 09:00:00+09" AND "2020-03-4 10:00:00+09"  --- 1時間分
)
SELECT
  query_category,
  SUM(total_slot_ms) / (1000 * 60 * 60) AS avg_slot_usage  --- 単位を変換
FROM
  categorized_jobs
GROUP BY query_category
LIMIT 100;

スロット利用量として total_slot_ms を参照していますが、これは単位がミリ秒なので、一定期間のスロットの平均利用率を見るためにはその期間で割り戻す必要があります。そうすることで、理論上は上述のスロット数（従量課金なら2,000スロットなど）と比較可能な数字になります。

実際にクエリを実行した際に消費されるスロットは、クエリの実行計画の各ステージやステップ毎に異なるため、実行期間中に一様に分布しているわけではない点や、より厳密に見る場合は start_time と end_time を使ってその期間でのスロット消費量を按分するなどした方がより厳密である点には、ご注意ください。（厳密な数字はどうやっても出すことは出来ないので、全データスキャンを避けるために簡易的に creatiion_time だけで見るという割り切りもありだと思います）

参照されているテーブルで分類してスロット使用率を参照

JOBSテーブルには、クエリ中で参照されたテーブルの一覧も保存されているため、場合によってはそれを使ってクエリを分類して集計する方が良いかもしれません。4データとしては、RECORD型カラムの中に埋まっているため、ちょっと加工が面倒ですが、以下のような感じで出してみました。

WITH categorized_jobs AS (
  SELECT
    STRING_AGG(
      DISTINCT CONCAT(
        referenced_table.dataset_id,
        ".",
        referenced_table.table_id
      ),
      ", " ORDER BY
        CONCAT(
          referenced_table.dataset_id,
          ".",
          referenced_table.table_id
        )
    ) AS query_category,  --- 参照しているテーブルで分類
    ANY_VALUE(total_slot_ms) AS total_slot_ms_per_job
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS jobs,
    UNNEST(referenced_tables) AS referenced_table
  WHERE
    creation_time
      BETWEEN "2020-03-04 09:00:00+09" AND "2020-03-4 10:00:00+09"
  GROUP BY jobs.job_id
)
SELECT
  query_category,
  COUNT(1) AS query_cnt,
  SUM(total_slot_ms_per_job) / (1000 * 60 * 60) AS avg_slot_usage
FROM
  categorized_jobs
GROUP BY query_category
ORDER BY avg_slot_usage DESC
LIMIT 100;

referenced_tables にはクエリの実行中に参照されたテーブルが入っているのですが、クエリ中で指定されていたがテーブルが存在しなかった場合は出力されないようです。5また、日付別テーブルが hoge_* みたいにまとまって入るケースと、 hoge_20200301 と hoge_20200302 のように分けて出力されるケースがあるみたいで、クエリを確認したところ、前者はStandard SQLで _TABLE_SUFFIX による指定をした場合で、後者はLegacy SQLで TABLE_DATE_RANGE のようです。ですので、Legacy SQLで日付別テーブルを活用している場合は出力結果がかなり見にくくなってしまうかもしれません。

あと細かいですが、 STRING_AGG を行なう際に ORDER BY をしているのは、同じテーブルを参照していても referenced_tables みたいで、そこをまとめるために追加しています。6日付別テーブルが個別に出力される場合も、ORDER BY をつけないと日付順にすらなっていなかったので、存在しないテーブルは出力されていないことも含めて考えると、クエリの実行フェーズで結果として参照されたテーブルを集めてきて出力しているような挙動なのかなと理解しています。

スロットに関する考察

調査をしていく中で、同じようなクエリでも、日にちや時間帯によってスロット消費にバラツキがある点が気になったため、調査してみました。

同じクエリでもスロット消費量に幅がある

まったく同じクエリを複数回実行して消費したスロットや実行時間をまとめてみます。

調査方法

SELECT
  *
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time BETWEEN "2020-03-04 09:00:00+09" AND "2020-03-4 10:00:00+09"

調査結果

スロット消費量の結果(n=20)

※スロット消費量 = total_slot_ms

平均: 102,062
最大: 260,231
最小: 71,198

ジョブの実行時間ミリ秒の結果(n=20)

※ジョブの実行時間 = TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)

平均: 4,331
最大: 5,389
最小: 3,374

両指標を分布図にすると以下のようになりました。スロット消費量とジョブの実行時間に相関はなさそうです。

total_slot_ms に関しては1件だけはずれ値のように大きな数字になってしまっていたので、最大の値と最小の値を、1件ずつ除いて見ても約1.53倍（117,834 / 76,629）の開きがあるという結果になりました。

最大と最小となった各クエリについて、 job_stages と job_stages.steps をみてクエリ実行の各ステージや各実行ステップを見比べてみたのですが、結果として実行時間や消費スロットが異なってはいるものの、実行内容自体はまったく同じでした。

あらためてスロットとは何か？

公式ドキュメントの「スロットを使用したクエリ実行」には、こんな記載があります。

BigQuery では、複数のステージを同時に実行できます。BigQuery は投機的実行を活用してクエリを高速化し、ステージを動的に再パーティション分割して最適な並列化を実現できます。
BigQuery スロットにより、クエリの各ステージで個々の作業単位が実行されます。たとえば、ステージの最適並列化係数が 10 であると BigQuery が判断した場合、ステージの処理に 10 スロットがリクエストされます。

スロット消費量は、基本的には「どのようなクエリを実行したか」に大きく影響されるという大枠の考え方は問題なさそうですが、投機的実行（必要になるかもしれないし、必要じゃないかもしれない処理も含めて実行）の結果として消費されたリソース量くらいに考えておいた方が良いのかもしれません。

これまでスロット消費量についていくつか見てきましたが、少なくともクエリ単位のスロット消費をみる際は、細かい数字自体にこだわらず1.5倍程度のバラつきがある前提で、傾向把握のためくらいに割り切ってみた方が良さそうです。

リソース配分の最適化

長くなってしまいましたが、本題のリソース配分の最適化について考えてみます。定額料金でコミットメントを購入しており、かつ複数のプロジェクトでそれを共有しているという前提です。

おおまかな流れとしては以下のような手順になると思います。

プロジェクト毎のスロット割当設定を確認
プロジェクト毎のスロットの利用量を確認
スロット消費量の多いクエリの特定・傾向把握
クエリのスロット効率の改善
クエリを実行するプロジェクトやその割当設定の見直し

1.〜3.については既に書いてきた通りで、4.については今回は着手できなかったので、5.について最後に少し書いてみたいと思います。

スロット消費はデータのあるプロジェクトと別にできる

BigQueryではクエリやジョブを実行するプロジェクトのスロットが消費される仕様ですが、スロットが消費されるプロジェクトと以下のプロジェクトは別に指定することができます。

クエリやジョブを実行するサービスアカウントの発行元プロジェクト
クエリ対象のデータセットが存在するプロジェクト
ジョブの実行結果を保存する先のテーブルが存在するプロジェクト

具体的な話は、Node.jsのコードサンプルを例に進めます。

実際にスロットが消費されるプロジェクトは、以下のようにBigQueryインスタンスを生成する際に指定した projectId になります。 keyFilename でサービスアカウントの情報を渡す場合、サービスアカウントの発行元プロジェクトは projectId で指定したプロジェクトと一致する必要はなく、IAMで必要な権限をサービスアカウントに付与していれば問題なく動作します。

const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery({
  projectId: "{ここで指定したプロジェクトのスロットが消費される}",
  keyFilename: "{ここのサービスアカウントの発行元プロジェクトは、上記projectIdと一致しなくてもOK}"
});

クエリ中で参照するデータセットも、実行するサービスアカウントにIAMで必要な権限が割当てられていれば別のプロジェクトに存在するデータセットを参照できます。

const query = `SELECT hoge FROM \`{ここでも別のプロジェクトを指定可}.{データセット名}.{テーブル名}\` LIMIT 100`;

ジョブの実行結果を出力するテーブルについても同様に、IAMで必要な権限を付与しておけばスロットが消費されるプロジェクトとは別のプロジェクトのデータセットに出力できます。

const destBigquery = new BigQuery({
  projectId: "{destinationのデータセットがあるプロジェクトを指定}",
  ...
});
const destTable = destBigquery.dataset(destDatasetName).table(destTableName)

bigquery.createQueryJob({
  query,
  destination: destTable,
}, (err, job) => { ... });

実施予定の改善アクション

本記事の背景となった改善業務としては、以下の2点の取り組みを進めています。

また、今回は着手できなかったクエリのスロット効率の改善についても時間があれば、取り組んでみたいと思っています。（通常のRDMSではインデックス設計がキモになりますが、BigQueryはインデックスという概念がないため、新しい改善ノウハウが必要になりそうですね）

以上