こんにちは。
サイボウズ株式会社 生産性向上チームのたかみん (@takamin55) です。
生産性向上チームはスローガンとして「ビーチへ行く余裕をあなたに…」を掲げている、開発生産性を高めることを使命としたチームです。
そんな生産性向上チームの取り組みの 1 つとして、Four Keys 計測と改善の取り組みがあります。
これは（今となっては Archived されてしまった） dora-team/fourkeys をフォークした基盤をもとに要件に合わせて開発し、社内に展開することで実現しています。
この基盤は Google Cloud の BigQuery をデータベースとして用いているのですが、開発を続ける中で BigQuery のクエリ料金がデータ規模の割には高額になっていることに気づきました。
今回はそのクエリコスト改善の過程で得た知見を記事にしたいと思います。
- BigQuery でコストが掛かっているクエリを特定する方法
- 改善する方法
の 2 部構成で記事にしていきます。
BigQuery でコストが掛かっているクエリを特定する方法
0. 予算アラートを設定しておく
チームが BigQuery のコストが上がっていることに気づけたのは予算と予算アラートを設定しているおかげでした。
予算はサービスごとに設定することもでき、例えば BigQuery に対して予算を設けることが可能です。チームではそこまで細かく設定せず、プロジェクトに対して予算と予算アラートを設定しました。
10 月に料金が大きく伸び、その結果予算アラートが反応して BigQuery の料金の上がり方の異常に気づくことができました。以下の写真は全サービスの料金を合計した料金推移ですが、もちろんここから個別のサービスの推移を見ることが可能です。
1. 原因となっているクエリを特定する
とにかく BigQuery の料金が伸びていることが分かったので、次はどのクエリが悪さをしているのかを特定します。
料金体系を改めて理解する
まずは BigQuery の料金体系を改めて確認すると良さそうです。
大きく「1. コンピューティング料金」と「2. ストレージ料金」の 2 つに分かれます。提供している基盤のデータ量は大したことないと分かっているので、コンピューティング料金の方を細かく分析していきました。
コンピューティング料金とはクエリでスキャンされたデータに対して発生する料金です。つまりデータを大量にスキャンするような非効率なクエリがあると料金が高くなるということです。
非効率なクエリを特定する
では、どのクエリがコストをかけてしまっているのでしょうか。それを調べるために以下のドキュメントを参考にしました。
これは BigQuery のジョブのメタデータを扱うマネージドテーブルです。少し覗いてみるとこのテーブルはクエリ文字列や料金に関するカラムを持っていることが分かります。
そこで以下のようなクエリを組み立てて実行してみました。
SELECT
t.table_id,
query,
sum(total_bytes_billed)/1024/1024/1024 as total_bytes_billed__GB,
COUNT(*) AS num_references
FROM
`region-asia-northeast1`.INFORMATION_SCHEMA.JOBS, UNNEST(referenced_tables) AS t
WHERE
creation_time BETWEEN '2024-10-01' AND '2024-10-31'
AND dataset_id = '<target_dataset>'
GROUP BY
t.table_id,
query
ORDER BY
total_bytes_billed__GB DESC
つまり、
- どのテーブルに対する、
- どんなクエリが、
- 1ヶ月の間で合計どれくらいコンピューティング容量を消費していて、
- 1ヶ月の間で何回実行されているのか、
- をクエリでグルーピングし、
- さらにコンピューティング容量が多い順に並べ変える
といったクエリです。
このクエリを実行して一番上にやってきたレコードの query にあるクエリが最もコンピューティング料金をかけているクエリになります。
注意事項としては、例えば以下のようなクエリを実行している場合は、料金計算としては同じクエリとして合算したいところですがレコードとして別々になってしまいます。
SELECT * FROM hoge WHERE date = '2024-12-06'
と
SELECT * FROM hoge WHERE date = '2024-12-07'
ただ、変数部分をプリペアードステートメントの形式で書いている場合（SELECT * FROM hoge WHERE date = @date など）はパラメータが実行のたびに異なるでしょうがこのプリペアードステートメントが query カラムに入るようなので、変数に左右されずに料金を算出することができました。
BigQuery でコストが掛かっているクエリを改善する方法
クエリまでは特定できたので、次はなぜそのクエリが料金がかかっているのかを分析し、そして改善していきます。簡単にですが紹介します。
1. 原因を分析する
コストの原因を分析します。
とにかくシンプルな分析方法は、コンソールにクエリを入力することです。入力すると BigQuery がこのクエリを実行したときに発生する消費容量を事前に計算してくれます。
26 GB も消費されるとんでもクエリであることが分かります。どうしてこんなに消費されるのでしょうか。もちろん SQL を眺めて分析するのもよいですが、BigQuery はコンソール上から実行したクエリについて「実行の詳細」や「実行グラフ」という形で視覚的に情報を与えてくれます。
実行グラフをみてみると 250 万行も 1 つのテーブルから読み込んでいる怪しい箇所がありました。テーブルはパーティショニングしているのですが、どうやらうまく機能していないようです。
なぜパーティショニングがうまく機能していないのかをクエリを読んで調べてみると、パーティショニングに指定したカラムではないデータに対して WHERE 条件を書いているためでした。具体的には
json_extract_scalar() という関数を使って json から日付を抜き出し、その日付に対して WHERE 条件を付けていました。
当然ながら、パーティショニングで指定しているカラムに対して WHERE 条件を持つクエリを実行する分には効きますが、json を持つカラムの中にあるキーの日付値に対して WHERE 句を書いてもパーティショニングは効きません。しかもこの json がかなり巨大なデータでした。
まとめると、
json のデータ量が大きいという特徴を持つテーブルに対し、フルスキャンを走らせていること
が消費容量が跳ね上がっている原因でした。
2. 仮説を立て、先に検証しておく
以下が改善の方針になりそうです。
- json の容量を小さくする
- フルスキャンを走らせないようにする
1 と 2 を両方満たす方法として、そもそも json_extract_scalar()の実行が終わった後の状態のデータをテーブルとして定義して運用すれば良いのでは、という仮説が出ました。今まで WHERE で条件にしていた日時データに対してパーティショニングを切れば、いつでも一定量のクエリに抑えることができます。
ここでいきなりプログラムを変更するのではなく、まずはそのようなテーブルを手動で作って仮説が正しいかどうか検証します。検証の結果、消費コンピューティング容量はざっくりと KB 単位まで削減できそうでした。 1/10,000,000 ですね。
仮説は正しいので、後は実現するために必要な情報（例えば実装の変更に関する情報やリスクなど）を詰めるだけです。
3. 改善を確定させる
効果があることはもう分かっているので、安心して改善を確定させました。
4. その他の改善テク
よくある改善方法は嬉しいことにドキュメントにまとまっています。
これらも参考に、自分たちのプロジェクトにあった改善方法を実施するのが良いでしょう。
例えば「履歴ベースの最適化」などは有効にするだけで BigQuery が勝手にクエリを最適化してくれるように感じました。とても便利ですね。
おまけ: ほかに実施した改善
BigQuery は 24 時間有効なキャッシュテーブルを持っています。同じクエリを実行したときに参照するテーブルが変更されていなければ、キャッシュテーブルの結果を返してくれて消費コンピューティング容量が 0 になります。
詳細：https://cloud.google.com/bigquery/docs/cached-results?hl=ja
この仕組みを最大限利用できるように、Four Keys 基盤のビューを司る Grafana が BigQuery に対して実行するクエリに工夫を施しました。詳細はこちらの記事で紹介しています。
終わりに
BigQuery でコストが掛かっているクエリを特定し、改善する方法をご紹介しました。今後は監視などでクエリ料金の異常検知を常に行っている状態を作り出したいですね。
最後までお読みいただきありがとうございました。
Discussion