Đã đăng vào khoảng 20 giờ trước 10 phút đọc

Google BigQuery - Bài học 28: Đọc hiểu Execution Plan (Query Plan Explanation) – Phân tích các Stage, Slot time consumed và phát hiện nút thắt cổ chai

Chào anh em! Qua Bài học 27, chúng ta đã biết cách sắp xếp dữ liệu đa chiều bằng Table Clustering kết hợp với Table Partitioning để giúp bộ máy quét của BigQuery bỏ qua hàng đống dữ liệu thừa.

Nhưng giả sử bạn đã tối ưu hóa bảng cẩn thận, viết một câu lệnh SQL phức tạp gồm nhiều lớp JOIN, GROUP BY, WINDOW FUNCTIONS, và CTE. Bạn bấm Run, kết quả trả về đúng, nhưng thời gian chờ lên tới vài chục giây, và lượng Slot time consumed nhảy vọt lên hàng chục phút chỉ vì một câu lệnh đơn giản!

Làm sao để biết chính xác câu lệnh SQL của bạn đang "nghẽn cổ chai" ở đâu? Dremel đang xử lý nó như thế nào? Bước nào tốn nhiều tài nguyên CPU nhất? Câu trả lời nằm ở công cụ chẩn đoán tối thượng của BigQuery: Execution Plan (Biểu đồ thực thi truy vấn).

Hôm nay, chúng ta sẽ mặc áo blouse trắng, cầm dao mổ để đọc bức tranh X-Quang của một câu lệnh SQL trên BigQuery!

1. Bản chất của Execution Plan trong BigQuery là gì?

Khi bạn viết một câu lệnh SQL (declarative language – bạn chỉ nói cho hệ thống biết bạn muốn lấy cái gì), cơ sở dữ liệu không tự động chạy ngay theo đúng từng câu chữ đó.

Trình tối ưu hóa truy vấn (Query Optimizer của động cơ Dremel) sẽ thực hiện các bước sau:

Phân tích cú pháp SQL.
Lên kế hoạch thực thi tối ưu nhất (Execution Plan), chia nhỏ câu lệnh thành một cây thực thi phân tán gồm nhiều tầng (Stages).
Phân phối các Stage này xuống hàng ngàn máy chủ (Slots) để chạy song song.

Trong giao diện BigQuery Studio, khi một câu lệnh chạy xong, bạn đừng chỉ nhìn vào tab Results. Hãy bấm ngay sang tab Execution details hoặc Execution graph. Tại đây, bạn sẽ thấy một biểu đồ trực quan mô tả toàn bộ hành trình mà câu lệnh SQL của bạn đã trải qua.

2. Giải mã các chỉ số "Sống còn" trong Execution Details

Khi mở bảng thông tin chi tiết của một câu lệnh đã chạy, bạn sẽ thấy các thông số cốt lõi sau:

Elapsed Time (Thời gian thực thi thực tế): Khoảng thời gian từ lúc bạn bấm nút Run cho đến khi nhận được kết quả (Ví dụ: 1.8 seconds). Đây là thời gian bạn phải ngồi chờ ngoài đời thực.
Slot Time Consumed (Tổng thời gian CPU tiêu thụ): Đây là con số cực kỳ quan trọng! Nó là tổng thời gian tính toán của tất cả các Slots (CPU ảo) cộng lại trên toàn bộ cụm máy chủ trong suốt quá trình chạy.
- Ví dụ: Nếu câu lệnh của bạn chạy mất 2 seconds (Elapsed time), nhưng nó huy động đồng thời 300 Slots cùng làm việc song song, thì Slot Time Consumed sẽ là khoảng 10 minutes (600 giây). Nếu con số này quá cao so với giá trị thực tế của báo cáo, câu lệnh của bạn đang có vấn đề về tối ưu hóa!
Bytes Read / Bytes Shuffled: Lượng dữ liệu thực tế đọc từ ổ cứng Colossus và lượng dữ liệu phải bắn qua mạng lưới Jupiter giữa các bước tính toán.

3. Đọc hiểu các "Stages" (Các giai đoạn thực thi)

Một Execution Plan trong BigQuery được chia thành nhiều Stages (thường đánh số từ S00, S01, S02...). Mỗi Stage đại diện cho một bước xử lý phân tán của hàng ngàn Slots.

Hãy nhìn vào sơ đồ dòng chảy dữ liệu (Execution Graph) để đọc các giai đoạn chính:

Stage 1: Read (Đọc dữ liệu thô từ Colossus)

Các Leaf Nodes thò tay xuống kho lưu trữ Colossus, đọc các cột dữ liệu cần thiết từ các khối Capacitor.
Điểm cần soi: Nhìn vào thông số Input rows và Output rows của Stage này. Nếu bạn thấy Input đọc lên hàng trăm triệu dòng, nhưng điều kiện WHERE chỉ lọc lại được vài dòng ở đầu ra, nghĩa là bạn chưa tận dụng tốt Partitioning hoặc Clustering.

Stage 2: Compute / Aggregate / Join (Tính toán cục bộ)

Các Slots xử lý các phép tính cơ bản (SUM, COUNT, WHERE) trên từng phân vùng dữ liệu mà chúng đang nắm giữ.

Stage 3: Shuffle (Tráo đổi dữ liệu qua mạng Jupiter)

Đây là nơi các thảm họa nghẽn cổ chai thường xuyên xảy ra!
Khi có lệnh GROUP BY hoặc JOIN, dữ liệu từ các Node phải được băm (Hash) và bắn chéo qua mạng Jupiter để gom các giá trị có cùng khóa về chung một Node xử lý (như chúng ta đã học ở Bài 4).
Điểm cần soi: Nhìn vào cột Bytes Shuffled. Nếu con số này lớn hơn rất nhiều so với Bytes Read ban đầu, câu lệnh của bạn đang thực hiện một phép JOIN bùng nổ hoặc phân tán dữ liệu không hiệu quả.

4. Phát hiện 3 "Nút thắt cổ chai" (Bottlenecks) thường gặp qua Execution Graph

Khi nhìn vào biểu đồ trực quan của Execution Graph, nếu bạn thấy một Stage có màu đỏ sẫm hoặc thời gian xử lý dài đột biến so với các Stage khác, đó chính là nút thắt cổ chai. Dưới đây là 3 "bệnh lý" kinh điển:

Bệnh lý 1: Data Skew (Lệch dữ liệu khi Shuffle)

Biểu hiện: Trong biểu đồ của một Stage có phép JOIN hoặc GROUP BY, BigQuery hiển thị một cột thống kê cho biết thời gian xử lý của các Slots (Min time, Average time, Max time). Bạn thấy phần lớn các Slots đã hoàn thành trong 0.5 giây, nhưng có đúng 1 Slot chạy mất 45 giây mới xong!
Nguyên nhân: Hiện tượng Data Skew. Cột bạn dùng để JOIN hoặc GROUP BY bị lệch dữ liệu nghiêm trọng (ví dụ: 80% đơn hàng có user_id = 0 hoặc giá trị NULL). Toàn bộ dữ liệu của giá trị 0 đó bị dồn hết về cho duy nhất 1 Slot xử lý, trong khi các Slot khác ngồi chơi xơi nước.
Cách chữa trị: Lọc bỏ các giá trị NULL hoặc giá trị mặc định gây lệch (WHERE user_id IS NOT NULL AND user_id != 0) trước khi thực hiện bước JOIN hoặc GROUP BY.

Bệnh lý 2: Quét thừa dữ liệu do thiếu Partition Filter

Biểu hiện: Stage đầu tiên đọc dữ liệu (S00: Input) báo cáo số lượng Byte đọc lên tới vài Terabyte, mặc dù bạn chỉ muốn xem dữ liệu của tuần này.
Nguyên nhân: Quên cấu hình điều kiện lọc WHERE trên cột phân vùng (Partition column) hoặc kiểu dữ liệu so sánh bị ép kiểu sai (ví dụ cột created_at kiểu TIMESTAMP nhưng bạn lại dùng hàm DATE(created_at) = ... sai cách làm mất tác dụng của Partition Pruning).

Bệnh lý 3: Cartesian Product (Nhân chéo bùng nổ dòng)

Biểu hiện: Số lượng Output rows ở một Stage tăng vọt gấp hàng trăm lần so với Input rows, dẫn đến tràn bộ nhớ hoặc lỗi Resources exceeded during query execution.
Nguyên nhân: Viết điều kiện JOIN bị thiếu khóa liên kết chính xác (Thiếu điều kiện ON t1.id = t2.id), khiến BigQuery phải nhân chéo toàn bộ bảng A với bảng B.

Tổng kết Bài học 28

Đọc hiểu Execution Plan là kỹ năng "phẫu thuật thẩm mỹ" giúp nâng tầm câu lệnh SQL của bạn từ trạng thái "chạy được" sang trạng thái "chạy cực kỳ tối ưu":

Phân tích chỉ số: Đánh giá tương quan giữa Elapsed Time và Slot Time Consumed để nhận diện mức độ ngốn tài nguyên CPU.
Soi biểu đồ Execution Graph: Theo dõi luồng dịch chuyển dữ liệu từ khâu Read $\rightarrow$ Compute $\rightarrow$ Shuffle $\rightarrow$ Output.
Bắt bệnh hệ thống: Phát hiện và xử lý dứt điểm các hiện tượng nghẽn cổ chai kinh điển như Data Skew (lệch dữ liệu) hay Full Table Scan.

Nhưng ngay cả khi câu lệnh SQL của bạn đã được tối ưu hoàn hảo, đối với các hệ thống Dashboard điều hành doanh nghiệp (như Looker Studio hay Power BI) đòi hỏi thời gian phản hồi phải dưới 1 giây liên tục cho hàng ngàn người dùng truy cập cùng lúc, việc bắt Dremel chạy lại query từ đầu mỗi lần load trang vẫn chưa phải là giải pháp tối ưu nhất.

Có công nghệ nào giúp lưu trữ đệm và tăng tốc độ phản hồi truy vấn dưới 1 giây mà không tốn chi phí quét dữ liệu không? Hãy đón xem Bài học 29: BigQuery BI Engine – Tăng tốc độ phản hồi truy vấn dưới 1 giây với bộ nhớ đệm In-memory thông minh!

bigquery