この記事は、ジモティー Advent Calendar 2018 の12日目の記事です。
事件発生
東京五反田、2018年の師走に入って1件の Slack 通知が飛び込んできた。
「GCP のコストが先月から増加している模様」
警ら中のインフラ隊員は GCP と聞いて何かを感じ取ったようだ。すぐさま GCP コンソールの Cloud Billing Reports を開き、フィルタ「期間: 過去3か月, プロダクト: BigQuery, SKU: Analysis」をかけて結果を確認した。
他のプロダクトも一通り確認したが、BigQuery のスキャンによるコストだけ先月から大幅に上昇していた。
一体何があったのか。
捜査開始
BigQuery のコスト体系は大きく2種類に分かれる。1つはデータを保存しておく際にかかるコスト、2つめはクエリを実行した際にスキャンしたデータサイズにかかるコストだ1 2。データ分析の特性上、コスト影響への比重は後者が高くなるケースが多い。
弊社のほとんどのメンバーは Redash を経由して BigQuery のクエリを実行している。この時、常に一定のスキャンサイズを超えないように上限を設けているため、問題となる可能性は低い。
念の為、Redash の実行ログからクエリを確認したが、特に怪しそうなものは見つからなかった。ただし、実行ログにはスキャンサイズが保存されていないため、不安は残る。
そこで、BigQuery API の Jobs: list から、クエリ実行のジョブごとにスキャンしたサイズとそのクエリを取得する作戦に出た。レスポンスに含まれる jobs[].statistics.query.totalBytesBilled がコストに直結するバイトサイズだ。
ちなみにこのジョブ自体を BigQuery のテーブルとしてアクセスできないか調べたが、有力な手がかりは見つからなかった。
BigQuery ジョブ捜索作戦
BigQuery API の Jobs list に対して、次の「ジョブの取得」と「結果の集計」の2部構成で運用する事にした。
その1: ジョブの取得
新たに集計スクリプトを作成し、このスクリプトが API 経由でジョブを取得して、そのデータを DB に保存する。
その2: 結果の集計
ジョブの取得が終われば、結果の集計として Redash 経由でデータの入った DB を参照する。Redash を経由するのは、非エンジニアも含めて手軽に結果の可視化を実現するためだ。
集計スクリプト「bqcop」
今回、集計スクリプトとして bqcop という CLI を作成した。
bqcop によるジョブの取得は次のように実行する。
bqcop -project-id=project-id -auth-json=auth-json [options...]
-project-id と -auth-json は必須のパラメータとなる。それぞれ取得対象の BigQuery のプロジェクト ID と認証用の JSON ファイルのパスを指定すると、実行時から24時間前までに作成されたジョブを取得し、bq_jobs テーブルに保存される。
この時、データベースは SQLite でファイル名は sqlite.db となる。テーブルのスキーマは sqlite の次の出力の通りだ。
% sqlite3 sqlite.db
sqlite> .schema bq_jobs --indent
CREATE TABLE IF NOT EXISTS "bq_jobs"(
"id" integer primary key autoincrement,
"created_at" datetime,
"updated_at" datetime,
"deleted_at" datetime,
"job_id" varchar(255),
"query" varchar(255),
"user_email" varchar(255),
"total_bytes_billed" bigint,
"start_time" datetime,
"end_time" datetime
);
CREATE INDEX idx_bq_jobs_deleted_at ON "bq_jobs"(deleted_at);
ORM3 を使用していて PostgreSQL、MySQL、MSSQL にも対応している（と思うが確認はしていない）。
注意点
コマンド実行時の認証用 JSON ファイルは BigQuery 管理者権限が必要となる。実行した結果「一部のジョブしか取得できない」「件数が0件になる」という場合は JSON に紐づくサービスアカウントの権限が BigQuery 管理者であることを確認する。
また、権限の強さから、その認証ファイルの取り扱いには細心の注意を払う必要がある。
容疑クエリ確保
bqcop の実行を開始して数分、Redash からクエリを実行すると、ある事実が判明する4。
SELECT 1.0 * total_bytes_billed / 1000 / 1000 /1000 as 走査サイズ（GB）,
(1.0 * total_bytes_billed / 1000 / 1000 / 1000/ 1000) * 5 as 金額（ドル）,
user_email,
start_time as 実行開始日時,
query
FROM bq_jobs
WHERE datetime(start_time) >= datetime('now', '-24 Hours')
ORDER BY total_bytes_billed DESC
LIMIT 1000;
スキャンサイズが TB 級のクエリが1日に複数回実行されていたのだ。
詳しい状況を確認したところ、これらのクエリは GAS (Google Apps Script) による実行で上限がかかっていなかった。上限がかけられる Redash や、Dry Run が実行できる BigQuery ではなく GAS で実行しているのは柔軟なクエリ実行を実現するためだが、これが思わぬ死角になっていた。
対策
検討の結果、GAS による実行は許容して、高額なクエリに対して次の2つの対策実施を決定した。
1つ目は不要なクエリの定期実行を停止。最低限の実行回数にする事で、コストの浪費が抑えられる。
2つ目はクエリのチューニングを実施。分割テーブルへのクエリなどで改善の余地がある事が分かった。
これに加えて、改善のモニタリングのために今回作成した bqcop を cron に登録し、自動実行した結果を Redash で定期的に確認するようにした。
仮に大幅な高額クエリが発生しても、プロジェクトとして1日単位、1週間単位の上限を設けているため大事故にはつながらない想定だ。
また、BigQuery ではユーザに対して1日単位でスキャンサイズの上限を設ける事もできる。ただし、実行者によって扱うサイズが大きく異なるため、ユーザ一律となるこの対策は見送った。
弊社では、安心安全な基盤の実現に向けてさらなる改善を目指している。
-
厳密にはデータのストリーミングインサート時にも若干コストがかかる。 ↩
-
スキャン時のコスト対象は集計時に存在する実データで、集計時に作成されたデータは対象にはならないとの事。BigQueryでレインボーテーブル攻撃をしてみた - Qiita ↩
-
デフォルトの SQLite3 の場合のクエリなので要注意。 ↩