BigQuery 16 — Giám sát chi phí & hiệu năng

Vì sao giám sát chi phí là việc của kỹ sư, không chỉ của kế toán

Ở các bài trước ta đã học cách một câu query tốn tiền: on-demand tính theo bytes billed, capacity/Editions tính theo slot-time (xem BigQuery — Cách tính tiền & bytes). Nhưng biết cơ chế tính tiền của một query chưa đủ. Trong một project BigQuery của ngân hàng NCB, mỗi ngày có hàng nghìn job từ hàng trăm người và service account đâm vào — analyst chạy ad-hoc, dashboard BI làm mới mỗi 15 phút, pipeline ETL đêm nạp cả trăm bảng. Câu hỏi quản trị không còn là "query này tốn bao nhiêu" mà là "trong biển job đó, cái nào đang đốt tiền, ai chạy nó, và nó có lặp lại mỗi ngày không".

May mắn là BigQuery tự ghi lại metadata của mọi job vào một bộ view có cấu trúc: INFORMATION_SCHEMA.JOBS. Đây là hộp đen (flight recorder) của hệ thống — mỗi query để lại một bản ghi với bytes billed, slot-time, người chạy, thời điểm, và cả cây stage thực thi. Giám sát chi phí thực chất là biết đọc hộp đen này, biến nó thành báo cáo, và dựng hàng rào (cost control) để tiền không chảy quá trần. Bài này xây mô hình tinh thần đó từ view gốc tới cơ chế quota và reservation.

INFORMATION_SCHEMA.JOBS — hộp đen của mọi job

INFORMATION_SCHEMA.JOBS là một họ view chứa metadata một dòng cho mỗi job (query, load, extract, copy). Có vài biến thể theo phạm vi, và chọn đúng biến thể là bước đầu tiên:

Điểm bắt buộc nhớ: các view này phải được prefix bằng region dưới dạng `region-<location>`.INFORMATION_SCHEMA.JOBS_BY_PROJECT — ví dụ `region-asia-southeast1`. Sai region là không thấy dữ liệu. Dữ liệu job được giữ khoảng 180 ngày; muốn lưu lâu hơn phải tự export ra bảng.

Các cột quan trọng nhất

Hai cột tiền cần phân biệt rạch ròi: total_bytes_billed là thứ bạn nhìn khi ở on-demand; total_slot_ms là thứ bạn nhìn khi ở capacity/Editions (vì lúc đó bạn trả theo slot-time chứ không theo bytes). Chi tiết slot và slot-time xem BigQuery — Slots & mô hình thực thi. Nhiều đội giám sát cả hai để vừa thấy chi phí, vừa thấy áp lực tính toán.

Luồng giám sát chi phí end-to-end

Giám sát không phải một câu query đơn lẻ mà là một vòng lặp: thu thập metadata → tổng hợp thành báo cáo → phát hiện bất thường → hành động (chặn/tối ưu/đổi reservation). Sơ đồ dưới đây mô tả các nguồn tín hiệu và nơi chúng chảy về:

Ba nguồn bổ trợ nhau: JOBS cho chi tiết từng job (đến tận cây stage), Cloud Monitoring cho tín hiệu thời gian thực (slot đang dùng bao nhiêu %), và Billing export cho con số tiền tệ thật đã lên hóa đơn theo SKU. Phần dưới đào sâu cột lõi: khai thác JOBS để tìm query đắt.

SQL 1 — Top query đắt nhất theo bytes billed (on-demand)

Câu hỏi kinh điển của FinOps: "7 ngày qua, câu query nào tốn bytes billed nhiều nhất, ai chạy?" Với project on-demand, đây chính là bảng xếp hạng đốt tiền:

-- Top 20 query tốn bytes billed nhất 7 ngày qua (mô hình on-demand)
SELECT
  job_id,
  user_email,
  creation_time,
  total_bytes_billed,
  ROUND(total_bytes_billed / POW(1024, 4), 3) AS tib_billed,
  cache_hit,
  TIMESTAMP_DIFF(end_time, start_time, SECOND)  AS runtime_sec,
  SUBSTR(query, 0, 100)                          AS query_preview
FROM `region-asia-southeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type       = 'QUERY'
  AND state          = 'DONE'
  AND error_result   IS NULL          -- chỉ job chạy thành công (job lỗi vẫn có thể tính bytes)
ORDER BY total_bytes_billed DESC
LIMIT 20;

Vài điểm thực dụng trong câu này:

• Lọc creation_time trước tiên — đây là cột phân vùng của view; giới hạn khoảng thời gian làm query metadata rẻ và nhanh, đừng quét toàn bộ lịch sử.
• Chia cho POW(1024, 4) để đổi sang TiB dễ đọc; nhân với đơn giá on-demand (tra BigQuery pricing) để ước ra tiền.
• cache_hit = true nghĩa là bytes_billed = 0 — nếu top toàn cache thì đó là tin tốt, không tốn tiền.
• Nhìn query_preview để nhận ra ngay câu SELECT * không WHERE — thủ phạm phổ biến nhất.

SQL 2 — Top query ngốn slot-time (capacity/Editions)

Nếu project chạy trên reservation (capacity/Editions), bytes billed không còn là thước đo tiền — bạn đã trả cố định cho slot mua trước. Lúc này thứ cần soi là total_slot_ms: query nào ngốn nhiều slot-time nhất, tức chiếm nhiều năng lực tính toán và đẩy các query khác vào hàng đợi:

-- Top 20 query ngốn slot-time nhất 7 ngày qua (mô hình capacity/Editions)
SELECT
  job_id,
  user_email,
  reservation_id,
  total_slot_ms,
  ROUND(total_slot_ms / 1000, 1)                              AS slot_seconds,
  -- slot trung bình = slot_ms / thời gian chạy (ms)
  SAFE_DIVIDE(total_slot_ms,
              TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS avg_slots,
  total_bytes_processed,
  SUBSTR(query, 0, 100)                                       AS query_preview
FROM `region-asia-southeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type       = 'QUERY'
  AND state          = 'DONE'
  AND error_result   IS NULL
ORDER BY total_slot_ms DESC
LIMIT 20;

avg_slots (≈ total_slot_ms chia thời gian tường) cho biết query đã trải rộng trên bao nhiêu slot trung bình — một query dùng 2.000 slot trung bình trong 30 giây có thể "hút" cạn reservation và làm ETL khác chờ. Kết hợp reservation_id để biết query nặng đó rơi vào pool nào (ETL hay BI). Muốn hiểu vì sao một stage ngốn slot, xem tiếp phần job_stages.

Phân tích job_stages — chi phí bên trong một query

Cột job_stages là mảng các stage của query plan — chính là cây thực thi ta đã học đọc ở BigQuery — Các pha & thông số mỗi stage. Để soi từng stage, ta UNNEST mảng này. Ví dụ tìm các stage tốn slot nhất và có dấu hiệu data skew (chênh lệch avg vs max lớn) hoặc spill to disk:

-- Soi các stage nặng nhất của một job cụ thể
SELECT
  stage.id                       AS stage_id,
  stage.name                     AS stage_name,
  stage.slot_ms,
  stage.records_read,
  stage.records_written,
  stage.shuffle_output_bytes,
  stage.shuffle_output_bytes_spilled,   -- > 0 = tràn ra đĩa, dấu hiệu shuffle quá lớn
  stage.wait_ms_avg, stage.wait_ms_max,
  stage.compute_ms_avg, stage.compute_ms_max   -- avg << max => data skew
FROM `region-asia-southeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(job_stages) AS stage
WHERE job_id = 'bquxjob_xxxxxxxx_xxxxxxxxxxxx'   -- thay job_id lấy từ SQL 1/2
ORDER BY stage.slot_ms DESC;

Đọc panel dưới đây như soi Execution Details ở dạng bảng — mỗi dòng một stage, chú thích ngưỡng cảnh báo:

stage_id  name                slot_ms   records_read  shuffle_bytes   spilled   compute_avg/max
--------  ------------------  --------  ------------  -------------  --------  ----------------
   S00    READ transactions   1,240,000   820,000,000   4.2 GB          0 B     3800 / 4100   ok
   S01    JOIN customers      6,900,000   820,000,000  38.0 GB       12.0 GB *  2100 / 9800  ** skew + spill
   S02    AGGREGATE           1,100,000     4,300,000   0.9 GB          0 B     1500 / 1650   ok
   S03    WRITE result           90,000       120,000    -              0 B      210 /  240   ok
                                              ^ S01 ngốn ~70% slot_ms:
   *  spilled > 0            → shuffle vượt bộ nhớ, tràn xuống đĩa → chậm & tốn slot
   ** compute_max ≫ avg      → data skew: vài worker ôm phần dữ liệu lệch

Ở ví dụ này stage S01 (JOIN) ngốn ~70% slot-time, có spill và skew — đây là chỗ cần tối ưu (đổi thứ tự join, lọc sớm, hoặc phân cụm lại bảng). Đọc job_stages hàng loạt cho phép trả lời "loại query nào (theo pattern) đang ngốn slot toàn hệ thống", không chỉ một job.

Phát hiện query lặp lại đắt tiền

Một query 500 GB chạy một lần thì tốn; nhưng một query 50 GB chạy 200 lần/ngày (dashboard làm mới liên tục) còn tốn hơn nhiều mà lại dễ lọt lưới vì mỗi lần trông "nhỏ". Chìa khóa là gom nhóm theo hình dạng query rồi nhân với số lần chạy:

-- Nhóm query theo pattern, tìm loại lặp lại tốn tổng bytes billed nhiều nhất
SELECT
  -- chuẩn hóa thô: bỏ literal số/chuỗi để gom các lần chạy cùng "hình dạng"
  REGEXP_REPLACE(SUBSTR(query, 0, 200), r"'[^']*'|\b\d+\b", '?') AS query_shape,
  COUNT(*)                              AS run_count,
  COUNT(DISTINCT user_email)            AS distinct_users,
  SUM(total_bytes_billed)               AS sum_bytes_billed,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS total_tib_billed,
  COUNTIF(cache_hit)                    AS cache_hits
FROM `region-asia-southeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY' AND state = 'DONE'
GROUP BY query_shape
HAVING run_count > 10
ORDER BY sum_bytes_billed DESC
LIMIT 20;

Nếu một query_shape có run_count cao, cache_hits thấp và total_tib_billed lớn — đó là ứng viên số một để: (1) bật lại query cache (bỏ hàm phi tất định), (2) chuyển sang materialized view hoặc bảng tổng hợp nạp một lần, hoặc (3) thêm partition pruning. Đây thường là nơi tiết kiệm được nhiều nhất mà không ai để ý vì từng lần chạy trông vô hại.

Cost controls — phân tầng hàng rào chặn chi phí

Giám sát cho biết đã tốn bao nhiêu; cost control ngăn sẽ tốn quá mức. BigQuery cung cấp nhiều lớp hàng rào, từ mềm (cảnh báo) tới cứng (chặn job), đặt ở nhiều cấp:

• Lớp 1 — maximum_bytes_billed: cầu dao từng query, đặt trần bytes; vượt là hủy trước khi tính tiền. Chi tiết cách đặt (per-query, session, project default) xem BigQuery — Cách tính tiền & bytes. Đây là hàng rào on-demand cơ bản nên bật ngay.
• Lớp 2 — Custom cost control quota: giới hạn tổng bytes truy vấn mỗi ngày ở cấp project và cấp user trong project. Ví dụ đặt trần 5 TB/ngày cho mỗi analyst — người nào vượt sẽ bị chặn phần còn lại của ngày. Ngăn "một người lỡ tay" làm hỏng cả tháng.
• Lớp 3 — Reservations & slot commitment: chuyển từ on-demand sang capacity. Bạn mua trước một lượng slot (commitment) và phân bổ vào các reservation; chi phí trở nên cố định và dự đoán được bất kể quét bao nhiêu bytes. Đây vừa là mô hình giá vừa là cost control — trần slot là trần cứng năng lực.
• Lớp 4 — Budget alert & Monitoring: hàng rào mềm, không chặn nhưng cảnh báo qua email/Slack khi chi phí thực (từ Billing) hay slot utilization (từ Monitoring) chạm ngưỡng.

Reservations & slot commitments — chi phí dự đoán được

Với khối lượng lớn và đều, reservations (mô hình Editions/capacity) thường rẻ và ổn định hơn on-demand. Cơ chế ba tầng:

1. Commitment: cam kết mua một lượng baseline slot (theo giây/tháng/năm), giá thấp hơn khi cam kết dài.
2. Reservation: chia số slot đã mua thành các pool đặt tên, ví dụ pool etl và pool bi, mỗi pool có baseline riêng.
3. Assignment: gán project / folder / loại workload vào reservation — job của nó chỉ ăn slot trong pool đó.

BigQuery còn cho autoscaling slot (tự thêm slot khi tải cao) và idle slot sharing (pool rảnh cho pool khác mượn), giúp không phí slot mua thừa. Với on-demand thì không có khái niệm này — bạn chỉ trả theo bytes từng query. Chi tiết mô hình xem trang Reservations/slots.

Cloud Monitoring & Billing export — bức tranh toàn cảnh

INFORMATION_SCHEMA.JOBS cực chi tiết nhưng chỉ giữ ~180 ngày và không phải con số tiền tệ chính thức. Hai nguồn bổ sung:

• Cloud Monitoring: expose metric thời gian thực như slots/allocated_for_project, slots/total_available, số job đang chạy, độ dài hàng đợi. Đây là nơi đặt alert theo slot utilization — ví dụ báo khi reservation bi liên tục chạm 100% (cần thêm slot hoặc autoscale).
• Billing export → BigQuery: bật export hóa đơn Cloud Billing thành một dataset BigQuery. Đây là con số tiền tệ thật theo SKU/ngày/project (không phải ước lượng từ bytes). Join nó với bảng tổng hợp từ JOBS cho ra dashboard FinOps đối chiếu "bytes/slot đã dùng" với "tiền thực trên hóa đơn".

Thực hành tốt: mỗi ngày chạy một job export JOBS_BY_PROJECT (đã tổng hợp theo user/reservation/ngày) ra một bảng lịch sử của riêng bạn — để giữ dữ liệu quá 180 ngày và dựng xu hướng dài hạn.

Best practice chi phí trong môi trường ngân hàng

• Tách reservation ETL và BI: pipeline ETL đêm (nặng, chịu được chậm) và dashboard BI ban ngày (nhẹ nhưng cần phản hồi nhanh) nên nằm ở hai reservation riêng. Nếu chung một pool, một ETL nặng lúc 9h sáng có thể làm dashboard của lãnh đạo treo. Tách pool đảm bảo BI luôn có slot dành riêng.
• Cảnh báo ngân sách nhiều tầng: đặt budget alert ở 50% / 80% / 100% ngân sách tháng; ở 100% nên có runbook (ai được báo, chặn gì).
• Quota theo user cho analyst: mọi tài khoản người thật đặt trần bytes/ngày; service account production đặt riêng cao hơn nhưng vẫn có trần.
• maximum_bytes_billed mặc định project làm sàn an toàn, không phụ thuộc người dùng nhớ đặt.
• Review query lặp lại hằng tuần: chạy SQL nhóm theo query_shape để bắt các query high-frequency chưa cache — thường là nguồn tiết kiệm lớn nhất.
• Attribution bằng label: gắn label (team, cost_center) lên job/query để JOBS chia được chi phí về từng phòng ban — quan trọng khi cần chargeback nội bộ.

Use case thực tế

Cuối quý, FinOps của NCB thấy hóa đơn BigQuery on-demand tăng ~35% so với quý trước dù số analyst không đổi. Họ chạy SQL 1 (top theo total_bytes_billed, 30 ngày) và thấy top 3 đều là cùng một SELECT * trên transactions (~2.4 TB/lần) từ một service account BI. Chạy tiếp SQL nhóm theo query_shape: query đó chạy 192 lần/ngày (dashboard làm mới 15 phút × 2 ca), cache_hit gần như 0 vì có CURRENT_TIMESTAMP() trong câu → mỗi lần đều tính tiền lại. Tổng: ~2.4 TB × 192 ≈ 460 TB billed/ngày chỉ cho một dashboard.

Hành động: (1) bỏ hàm phi tất định để bật lại cache, (2) chọn cột + partition pruning đưa mỗi lần chạy đầu buổi xuống ~45 GB, (3) đặt maximum_bytes_billed = 100 GB cho service account đó làm cầu dao, (4) thêm quota 3 TB/ngày cho mỗi analyst. Sau hai tuần, bytes billed của dashboard này giảm hơn 99%, và vì tải giảm mạnh, đội chuyển hẳn workload BI sang một reservation riêng 500 slot tách khỏi ETL đêm — chi phí trở nên cố định, dự đoán được, và không còn nguy cơ một pipeline nặng làm treo dashboard ban lãnh đạo.

Ghi nhớ

• INFORMATION_SCHEMA.JOBS* là hộp đen ghi metadata mỗi job; dùng JOBS_BY_PROJECT (prefix region, cần quyền) để giám sát toàn project, giữ ~180 ngày.
• Hai cột tiền: total_bytes_billed cho on-demand, total_slot_ms cho capacity/Editions; kèm user_email, query, creation_time, cache_hit, error_result.
• Luôn lọc creation_time trước để query metadata rẻ; lọc state='DONE' và error_result IS NULL để lấy job hợp lệ.
• UNNEST(job_stages) để soi từng stage: slot_ms, shuffle_output_bytes_spilled (>0 = spill), chênh compute_ms_avg vs max (= data skew).
• Gom query theo hình dạng (query_shape) × số lần chạy để bắt query lặp lại đắt — thủ phạm bị bỏ sót phổ biến nhất.
• Cost control phân tầng: maximum_bytes_billed (chặn/query) → custom quota (bytes/ngày theo user & project) → reservations/slot commitment (trần slot cứng) → budget alert (mềm).
• Bổ sung bằng Cloud Monitoring (slot utilization thời gian thực) và Billing export (tiền thật theo SKU); tự export JOBS ra bảng để giữ dữ liệu >180 ngày.
• Ngân hàng nên tách reservation ETL vs BI và đặt cảnh báo ngân sách nhiều tầng để chi phí dự đoán được và BI không bị ETL bóp nghẹt.

Nguồn tham khảo

• INFORMATION_SCHEMA JOBS: https://cloud.google.com/bigquery/docs/information-schema-jobs
• Reservations / slots (Editions): https://cloud.google.com/bigquery/docs/reservations-intro
• BigQuery pricing: https://cloud.google.com/bigquery/pricing
• Best practices — control costs: https://cloud.google.com/bigquery/docs/best-practices-costs
• Best practices (performance/compute overview): https://cloud.google.com/bigquery/docs/best-practices-performance-overview
• Query plan and timeline (execution details): https://cloud.google.com/bigquery/docs/query-plan-explanation
• Monitoring BigQuery (Cloud Monitoring): https://cloud.google.com/bigquery/docs/monitoring
• "Google BigQuery: The Definitive Guide" — Lakshmanan & Tigani, O'Reilly