BigQueryのコンピューティングリソース管理の取り組み / Initiatives Involving BigQuery Computing Resource Management

メルカリグループではデータ分析基盤としてBigQueryを利用しています。大量データに対する分析処理もSQLクエリによって簡単に記述でき、実行速度も速く社内の多くのユーザが便利に活用しています。その反面、便利であるがゆえにリソースに余裕がなくなるほどに利用され、時折パファーマンスが低下したり、特定のJOBがタイムアウトしてしまうような問題も発生しております。
本セッションではBigQueryのコンピューティングリソース運用にあたって直面してきた課題と課題解決に向けた取り組みについてお話しします。

Mercari Group uses BigQuery as our data analysis infrastructure. It allows us to use SQL queries to easily describe the analysis process for large amounts of data, and because it executes quickly, many users at the company leverage it conveniently. On the other hand, precisely because it is convenient, our resources are pushed to their limits, and at times problems emerge such as decreased performance and jobs timing out.
In this session, we’ll talk about the issues we confronted involving BigQuery computing resource management and the initiatives we took to resolve them.

------
Merpay & Mercoin Tech Fest 2023は3日間のオンライン技術カンファレンスです。
IT企業で働くソフトウェアエンジニアおよびメルペイ・メルコインの技術スタックに興味がある方々を対象に2023年8月22日（火）、23日（水）、24日（木）の3日間、開催します。 Merpay & Mercoin Tech Fest は事業との関わりから技術への興味を深め、プロダクトやサービスを支えるエンジニアリングを知ることができるお祭りです。

今年のテーマは「Unleash Fintech」。 メルペイ・メルコインのこれまでの技術的な取り組みはもちろん、メルカリグループのFintech事業における新たな挑戦をお伝えします。 セッションでは事業を支える組織・技術・課題などへの試行錯誤やアプローチなど多面的にご紹介予定です。

メルペイ・メルコインが今後どのようにUnleash（解放）していくのか、ぜひ見に来てください。

■イベント関連情報
- 公式ウェブサイト：https://events.merpay.com/techfest-2023/
- 申し込みページ：https://mercari.connpass.com/event/286670/
- Twitterハッシュタグ： #MerpayMercoinTechFest
■リンク集
- メルカリ・メルペイイベント一覧：https://mercari.connpass.com/
- メルカリキャリアサイト：https://careers.mercari.com/
- メルカリエンジニアリングブログ：https://engineering.mercari.com/blog/
- メルカリエンジニア向けTwitterアカウント：https://twitter.com/mercaridevjp
- 株式会社メルペイ：https://jp.merpay.com/

Transcript

1. Session Title BigQueryのコンピューティングリ ソース管理の取り組み Go Kojima Merpay Data Management
2. Go Kojima / @gouki ITシステム全般の研究開発からキャリアをスタート し、BigData、機械学習システムに関わるコンサル ティングやソフトウェア開発・運用支援業務に携わ る。 2021年8月 メルペイ入社。 機械学習システムの基盤開発に携わったのち、 Data managementチームに異動。主にBigQuery のコンピューティングリソース最適化を担当中。 株式会社メルペイ ソフトウェアエンジニア
3. • BigQueryとは ◦ 課金モデル、SLOTについて • 我々のSLOT管理における課題 • 解決に向けた工夫・取り組み • 今後の取り組み Mercari / MerpayのBigQueryの コンピューティングリソース管理の取り組み
4. BigQueryとは = Googleが提供するデータウェアハウスサービス https://cloud.google.com/bigquery/docs/introduction より BigQuery は、機械学習、地理空間分析、ビジネス インテリジェンスなどの組み込み機能を使用して データの管理と分析を支援する、フルマネージドのエンタープライズ データ ウェアハウスです。 BigQuery のサーバーレス アーキテクチャにより、SQL クエリを使用して、インフラストラクチャ管理な しで組織の最も大きな課題に対応できます。 BigQuery のスケーラブルな分散型分析エンジン を使用 すると、数テラバイト、数ペタバイトのデータに対し、数秒もしくは数分でクエリを完了できます。
5. BigQueryの課金モデル 処理 オンデマンド型 処理データ量に基づいて課金 ◦ 毎月1TBまで無料 ◦ ただし一度に利用できる SLOT は2000まで 定額型 処理に使う仮想CPUの 一定期間利用権を予約購入 ◦ 月単位 ◦ 年単位 ◦ Flex Slots: 秒単位 （最初の60秒をすぎると 　キャンセル可能） ※ただし料金体系は今後変更になる予定 • 処理: BigQuery Editions （3レベルStandard, Enterprise, Enterprise Plus / 自動スケールSLOT） • ストレージ: Compressed Storage ストレージ 保存領域ごとのデータ量で課 金 ◦ アクティブストレージ ◦ 長期保存
6. BigQueryのSLOTとは = SQLクエリを処理する仮想CPU 定額型の課金モデルは一定期間一定数の SLOTの利用料金を支払う方式 • 使っていなくても料金はかかる • クエリごとにどれだけSLOTが必要なのかはBigQueryが実行時に判断 => 一定期間に必要な分だけのSLOTを予約しておくことが重要 余計に買ってしまうと無駄になる （足りない分を補完するのがFLEX SLOTや自動スケールSLOTだが、 　　通常のSLOTよりも単価は高い）
7. SLOT管理の三つの概念 • Commitment: 予約したSLOT • Reservation: 利用するSLOTを割り当てるグループ ◦ 複数のprojectを割り当て可能（1project - 1group） • Assign: ReservationにコミットメントからSLOTを割り当てること
8. Reservation 利用例 https://cloud.google.com/bigquery/docs/reservations-workload-management より - 合計で 1,000 スロットのCommitment - データ サイエンス、ELT、BI という 3 つのワークロードの種類 - ReservationへのAssign: ds 500 スロット、elt 300 スロット、bi 200 スロット Reservation Aで利用していないSLOTがあるとき、 Reservation B, CからそのSLOTを利用できる
9. BigQuery SLOT管理の課題 • Mercari/MerpayのBigQuery環境 ◦ 定額 = コンピューティング容量ベースの料金モデル ◦ 規模（過去90日間内でアクセスされている範囲、システムアクセス含む） ▪ データセット数:1500超、JOB数: 30万超job/日、ユーザ数: 700人超/日 • BigQueryが便利すぎるがゆえの課題 ◦ SQLさえ書けば、大量データでもパフォーマンスを気にした書き方の工夫をさほどせずとも 欲しいデータを得ることができる ◦ => ユーザ多数、自由なクエリ多数、 ◦ どれだけクエリを実行してもなんのペナルティもないのでとても便利に使われている ◦ => 大量のSLOT消費発生 => SLOT枯渇により全体のクエリ実行のパフォーマンスが低下 => CS業務や分析業務で必要なクエリが業務時間内に完了しない問題が発生 => 追加のSLOTコミットメントが必要に …
10. BigQuery SLOT管理のために • まずは状況把握 ◦ ダッシュボード整備 ◦ 異常状態の通知 • 管理効率化 ◦ Reservationの整理 • SLOT消費削減 ◦ 無駄なJOBの停止 ◦ SLOT消費を削減する技術 /機能活用 ◦ 中間テーブルの作成・活用
11. 状況把握 • ReservationごとのCapacity要件を定義 ◦ 「重さ」を表現するメトリクス ▪ データ処理量あたりの実行時間 ▪ 一定SLOTを消費するのにかかる時間 ◦ 事象として避けたい要件のメトリクス ▪ タイムアウト発生有無 • 定めたCapacity要件をモニタリング ◦ モニタリング用のWeb UIもあるが、規定のメトリクスのみ ◦ 便利なJOBビュー / タイムスライス別ビュー ▪ JOBS_BY_ORGANIZATION view ▪ JOBS_TIMELINE_BY_ORGANIZATION view GCP提供のモニタリング用の Web UI
12. 状況把握に便利なView ◦ JOBS_BY_ORGANIZATION view ▪ Organizationの範囲で1job 1レコード => job単位の状態把握に活用 ◦ JOBS_TIMELINE_BY_ORGANIZATION view ▪ Organizationの範囲で1jobの1秒間の状況 1レコード => 時間帯単位の状況把握に活用
13. JOBビュー / タイムスライス別ビュー利用例 DECLARE base_time TIMESTAMP; SET base_time = TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 MINUTE); WITH errors AS ( SELECT TIMESTAMP_TRUNC(end_time, SECOND) AS time_sec , reservation_id , COUNT(DISTINCT job_id) AS num_errors FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION WHERE base_time <= creation_time AND base_time <= end_time GROUP BY 1, 2 ) , jobs AS ( SELECT period_start AS time_sec , reservation_id , COUNT(DISTINCT job_id) AS num_jobs , SUM(period_slot_ms) / 1000 AS slots FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION WHERE base_time <= period_start GROUP BY 1, 2 ) SELECT * except(num_errors) , coalesce(num_errors, 0) AS num_errors FROM jobs LEFT OUTER JOIN errors USING(time_sec, reservation_id) ORDER BY time_sec, reservation_id 一定時間枠ごと、Reservationごとのjob数、エラー発生数、利用 SLOT数
14. JOBビュー / タイムスライス別ビュー利用例（結果） time_sec reservation_id num_jobs slots num_errors 1 2023-06-15 10:14:10 UTC bi 4 378.957 0 2 2023-06-15 10:14:10 UTC ds 7 43.047 0 3 2023-06-15 10:14:10 UTC elt 4 2424.004 0 4 2023-06-15 10:14:11 UTC bi 1 0.058 0 5 2023-06-15 10:14:11 UTC ds 5 372.278 0 6 2023-06-15 10:14:11 UTC elt 15 10820.851 3
15. Capacity要件の状況把握 • Capacity要件の状況をモニタリング ◦ ダッシュボード作成 => 管理者・ユーザに公開 ◦ • Capacity要件違反をSlack通知 ◦ 10分間隔でチェックして違反判明次第即通知 ◦ 24時間単位でサマリ通知
16. 管理効率化/Reservationの整理 一つのReservationで使い方が大きく二種類に別れていた • Ad-hocな分析 • システム用途のデータパイプラインの開発 観点 Ad-hocな分析 システム開発用途 使われ方 比較的処理時間短め、 素早く結果を得て活用する システムが必要とするデータを 整備するパイプラインを開発する SLOT消費の特徴 SLOT消費が少ない大量のJOB SLOT消費の大きい 少量のJOB 要件 長時間クエリを禁止してSLOT消費を抑え、素早 くデータが得られるように維持したい 長時間かかっても安定的にデータ処理群を運 用したい => 異なる要件が目的のプロジェクトが一つの Reservationに同居しておりSLOT管理がしづらい状態に • 「SLOT管理」例: SLOT配分の増減、ダッシュボード・通知、強制 JOB停止 • => 目的に応じてReservationを分割することで適切なReseravtion単位でSLOT管理を実施可能に
17. SLOT消費削減に向けた取り組み 無駄なSLOT消費をしている可能性のあるJOBに注意喚起・停止相談の通知 • 通知対象 ◦ SLOT消費の大きなJOB ▪ => 注意喚起、クエリの工夫・停止を促す ◦ SLOT消費の大きなテーブル作成 JOBでそのテーブルのアクセスがないもの ▪ => 必要性の確認、JOBの停止を促す • 実装 ◦ JOBS_BY_ORGANIZATIONを使ってSLOT消費の大きいjobをモニタリング ◦ Job作成者のuser_emailからslackのuser idを取得し、@mention付きで通知 ◦ 通知対象（SQLで表現）や通知メッセージ（ jinja2 templateで表現）をカスタマイズ可能な仕組みをフ レームワーク化して @mention付きの通知を容易に拡充
18. SLOT消費削減に向けた取り組み • SLOT消費を削減する技術/機能活用 ◦ Partition filter requirement ◦ BI Engine • 中間テーブルの作成・活用 ◦ dbt ◦ => よく使うテーブル群をあらかじめ作成しておくことでクエリの重複実行を削減 • ダッシュボードの活用（Looker） ◦ 閲覧者が多い情報に対してキャッシュ利用しやすい（クエリ実行自体が不要に） ◦ 分析作業用にも利用 ▪ SQLを記述しなくても済む分析作業には、効率的なクエリ実行にしやすい
19. 今後の課題 • Auto scaling SLOTの活用 ◦ 新料金体系 BigQuery Enterprise / Enterprise plusの機能 ◦ コミットメントから設定する固定の baseline slotに加え、baseline slotおよび他reservationのidle slotでSLOTが不足した場合に利用される auto scaling slot（の上限）を設定できる ◦ Auto scaling slot分は利用されたSLOTの分だけ課金（通常よりも単価高い） ⇒ Baseline SLOTおよびAuto scaling SLOTのコスト最適設定 • BI Engineの活用 ◦ インメモリキャッシュ機能 ◦ 指定した容量分（BI Engine Reservation）のインメモリを利用し、 クエリ処理上キャッシュヒットする分については SLOTを消費しない ◦ 実行時間短縮効果以上に SLOT消費低減効果があることがわかってきている ⇒ BI Engine ReservationおよびReservationごとのSLOT 割り当てのコスト最適設定