Vấn đề thực tế: CPU tăng đột biến và nghẽn I/O trên Production
Mọi chuyện thường bắt đầu bằng một cảnh báo PagerDuty lúc 2 giờ sáng: CPU database vọt lên 98%, disk read IOPS chạm ngưỡng phân bổ 3.000 IOPS, và p99 latency của API giảm nghiêm trọng từ 45ms xuống 4,2 giây. Application log ngập tràn lỗi 504 gateway timeout. Tuy nhiên, các dòng log đó hiếm khi cho bạn biết chính xác câu lệnh SQL nào đã gây ra sự cố.
Bật query logging truyền thống giữa lúc hệ thống đang gặp sự cố thường chỉ khiến tình hình tồi tệ hơn. Thiết lập log_min_duration_statement = 250ms trên một instance đang xử lý 1.500 transaction mỗi giây có thể sinh ra tới 15GB raw text log mỗi giờ. Tình trạng disk thrashing này sẽ ngốn sạch lượng băng thông I/O quý giá mà database đang cần để phục hồi, đồng thời vẫn để lại hai câu hỏi hóc búa chưa có lời giải:
- Câu truy vấn nào tiêu tốn nhiều thời gian CPU tổng thể nhất trong khoảng thời gian cao điểm kéo dài 6 tiếng vừa qua?
- Độ trễ cao là do một vài tác vụ batch operation nặng nề, hay do một query nhẹ chỉ mất 2ms nhưng bị gọi tới 500.000 lần bên trong vòng lặp ORM N+1?
Đoán mò index khi hệ thống đang chịu tải cũng nguy hiểm không kém. Mỗi index thừa thãi sẽ làm chậm thao tác INSERT và UPDATE trong khi vẫn bỏ sót nguyên nhân gốc rễ. Bạn cần dữ liệu telemetry về quá trình thực thi với overhead cực thấp, được thu thập trực tiếp từ shared memory.
Khái niệm cốt lõi: Cách pg_stat_statements theo dõi quá trình thực thi truy vấn
PostgreSQL cung cấp khả năng quan sát này ngay từ đầu thông qua pg_stat_statements. Khác với cách ghi log text thông thường vốn phải chặn các truy vấn rồi ghi xuống đĩa, extension này móc trực tiếp vào query executor bên trong shared memory, chỉ gây thêm dưới 1-2% CPU overhead trên các production workload thông thường.
1. Chuẩn hóa truy vấn và ẩn tham số (Query Normalization)
Các ứng dụng dynamic liên tục hoán đổi giá trị đối số trong truy vấn. Việc lưu lại từng chuỗi ký tự riêng biệt sẽ nhanh chóng làm tràn bộ nhớ cache. Hãy xem hai câu lệnh tra cứu sau:
SELECT email, status FROM users WHERE id = 1042;
SELECT email, status FROM users WHERE id = 9811;
pg_stat_statements sẽ hash cây phân tích cú pháp (parse tree) của câu truy vấn thành một số nguyên 64-bit gọi là queryid. Nó thay thế các hằng số bằng các placeholder tham số liên kết ($1, $2), gom hàng triệu lần gọi riêng lẻ về cùng một fingerprint duy nhất: SELECT email, status FROM users WHERE id = $1.
2. Các chỉ số Telemetry quan trọng
Với mỗi câu lệnh đã được chuẩn hóa, extension này cung cấp các bộ đếm runtime thiết yếu:
- calls: Tổng số lần câu lệnh được thực thi.
- total_exec_time & mean_exec_time: Tổng thời gian và thời gian thực thi trung bình tính bằng mili-giây. So sánh các chỉ số này giúp phân biệt rõ các vi truy vấn tần suất cao với các tác vụ batch job nặng nề nhưng ít khi chạy.
- shared_blks_hit & shared_blks_read: Hiệu quả của buffer cache. Một “hit” nghĩa là PostgreSQL tìm thấy trang dữ liệu 8KB ngay trong RAM (
shared_buffers). Một “read” nghĩa là nó phải đọc từ OS page cache hoặc lấy dữ liệu từ ổ lưu trữ NVMe/EBS. - shared_blks_dirtied & shared_blks_written: Lượng dữ liệu ghi phát sinh do các thao tác biến đổi dữ liệu (mutation).
- blk_read_time & blk_write_time: Thời gian (mili-giây) phải chờ I/O đĩa (yêu cầu bật
track_io_timing = on).
Thực hành từng bước: Cài đặt, Phát hiện và Khắc phục
Bước 1: Kích hoạt pg_stat_statements trong PostgreSQL
Vì module này cần cấp phát một bảng hash cố định trong shared memory khi khởi động, bạn phải preload nó trước. Thêm các dòng sau vào postgresql.conf:
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
# Thiết lập dung lượng và phạm vi theo dõi
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = off
# Kích hoạt chỉ số thời gian I/O ở mức micro-giây
track_io_timing = on
Thiết lập track_utility = off sẽ loại bỏ các câu lệnh DDL như VACUUM hay CREATE INDEX, giúp dữ liệu thống kê chỉ tập trung hoàn toàn vào các truy vấn của ứng dụng. Áp dụng thay đổi bằng cách khởi động lại service:
sudo systemctl restart postgresql
Sau khi khởi động lại, kích hoạt extension trong database ứng dụng của bạn:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
Bước 2: Xác định Top 5 câu truy vấn chiếm nhiều tổng thời gian chạy nhất
Một truy vấn chạy mất 12ms trông có vẻ vô hại khi đứng riêng lẻ. Nhưng nếu worker gọi nó 800 lần mỗi giây, nó sẽ âm thầm bóp nghẹt server của bạn. Chạy truy vấn sau để tìm ra những kẻ ngốn CPU tích lũy nhiều nhất:
SELECT
queryid,
calls,
round(total_exec_time::numeric, 2) AS total_time_ms,
round(mean_exec_time::numeric, 2) AS avg_time_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage_overall,
substr(query, 1, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
Hãy chú ý kỹ đến cột percentage_overall. Nếu một câu truy vấn duy nhất chiếm tới 35% tổng thời gian chạy của toàn bộ cluster, việc tối ưu câu lệnh đó sẽ ngay lập tức giảm một phần ba tải cho database.
Bước 3: Phát hiện điểm nghẽn I/O và Cache Miss
Đọc dữ liệu từ RAM chỉ mất vài nano-giây, trong khi đọc từ ổ đĩa mất hàng mili-giây. Khi shared_blks_read vượt trội so với shared_blks_hit, PostgreSQL đang rất thiếu cache hit:
SELECT
queryid,
calls,
shared_blks_read,
shared_blks_hit,
round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 2) AS cache_hit_ratio,
round(blk_read_time::numeric, 2) AS io_read_wait_ms,
substr(query, 1, 120) AS query_preview
FROM pg_stat_statements
WHERE (shared_blks_read + shared_blks_hit) > 5000
ORDER BY shared_blks_read DESC
LIMIT 5;
Đối với các transactional OLTP workload, hãy đặt mục tiêu tỷ lệ cache hit đạt trên 99%. Bất kỳ con số nào dưới 95% đều báo hiệu một lệnh quét không có index khiến database phải scan toàn bộ bảng (full table sweep), hoặc working set đã vượt quá dung lượng vùng nhớ shared_buffers hiện tại.
Bước 4: Case Study thực tế: Xử lý Sequential Scan gây tốn tài nguyên
Trên một nền tảng thương mại điện tử xử lý 12.000 đơn hàng mỗi giờ, câu truy vấn kiểm tra I/O đã gắn cờ câu lệnh tra cứu này vì thời gian chờ đĩa quá cao:
SELECT order_id, amount, status FROM orders WHERE customer_id = $1 AND status = $2;
Chạy EXPLAIN (ANALYZE, BUFFERS) với các đối số tương đương trên production để xem execution plan thực tế:
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, amount, status
FROM orders
WHERE customer_id = 91842 AND status = 'COMPLETED';
Query planner đã bỏ qua các index đơn cột hiện có:
Seq Scan on orders (cost=0.00..84520.10 rows=4 width=28) (actual time=42.105..48.310 rows=3 loops=1)
Filter: ((customer_id = 91842) AND (status = 'COMPLETED'::text))
Rows Removed by Filter: 1850200
Buffers: shared read=38410
PostgreSQL đã phải nạp 38.410 buffer page—tương đương khoảng 300MB dữ liệu—từ ổ đĩa chỉ để tìm ra 3 bản ghi phù hợp trong tổng số 1,85 triệu dòng. Để khắc phục mà không làm khóa các thao tác ghi trên bảng đang hoạt động, chúng tôi tạo một composite index theo phương thức concurrent:
CREATE INDEX CONCURRENTLY idx_orders_customer_status ON orders (customer_id, status);
Kiểm tra lại plan lần thứ hai đã xác nhận hiệu quả ngay lập tức:
Bitmap Heap Scan on orders (cost=4.30..18.40 rows=4 width=28) (actual time=0.045..0.052 rows=3 loops=1)
Recheck Cond: ((customer_id = 91842) AND (status = 'COMPLETED'::text))
Buffers: shared hit=4
-> Bitmap Index Scan on idx_orders_customer_status (cost=0.00..4.30 rows=4 width=0) (actual time=0.021..0.021 rows=3 loops=1)
Buffers: shared hit=2
Database không còn phải đọc block nào từ đĩa, phục vụ toàn bộ truy vấn chỉ với 6 lần buffer hit trong RAM. Thời gian thực thi giảm mạnh từ 48,3ms xuống còn 0,05ms—tăng tốc tới 960 lần.
Bước 5: Reset số liệu thống kê Baseline
Các số liệu cũ sẽ làm mờ tác động của các index mới tạo cũng như những tinh chỉnh cấu hình vừa triển khai. Hãy xóa các số liệu thống kê cũ để đo lường chính xác hiệu năng của các đợt deployment tiếp theo:
-- Đặt lại tất cả bộ đếm trên toàn bộ database cluster
SELECT pg_stat_statements_reset();
-- Hoặc chỉ xóa query ID cụ thể (PostgreSQL 12+)
SELECT pg_stat_statements_reset(userid, dbid, 3421890241);
Tổng kết
Đừng tối ưu database dựa trên cảm tính. Kết hợp pg_stat_statements với track_io_timing mang lại cho bạn dữ liệu telemetry chính xác về bộ nhớ cache hit, số lần thực thi và tình trạng nghẽn đĩa mà hầu như không tốn thêm tài nguyên. Hãy kiểm tra các truy vấn hàng đầu theo tổng thời gian thực thi trước, loại bỏ các lệnh sequential scan tồi tệ nhất bằng composite index, và để các số liệu thực nghiệm dẫn dắt quá trình tối ưu hiệu năng của bạn.

