Sau năm mươi ba phần đo từng cơ chế một, phần này đo cách tìm ra chỗ nào đáng đo. Và kết quả đầu tiên là cách xếp hạng phổ biến nhất dẫn tới tối ưu nhầm truy vấn.
Bật
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
Cần khởi động lại — đây là extension nạp cùng máy chủ. Rồi:
create extension pg_stat_statements;
track = all thay vì mặc định top để đếm cả câu lệnh chạy bên trong hàm.
Hai cách xếp hạng, hai kết luận trái ngược
Tải giả lập: một truy vấn rất chậm chạy 2 lần, một truy vấn rất nhanh chạy 100.000 lần.
Xếp theo thời gian mỗi lần — cách người ta hay làm:
118,559 ms/lần 2 lần select count(*) from d where t like $1
0,007 ms/lần 100.000 lần select count(*) from d where kh = $1
Kết luận: sửa câu LIKE.
Xếp theo tổng thời gian:
708 ms tổng 100.000 lần 0,007 ms/lần select count(*) from d where kh = $1
237 ms tổng 2 lần 118,559 ms/lần select count(*) from d where t like $1
Kết luận ngược lại: câu kh tốn gấp ba lần thời gian máy chủ.
Truy vấn 0,007 ms mỗi lần ngốn nhiều tài nguyên hơn truy vấn 118 ms mỗi lần. Bỏ ra một buổi tối ưu câu LIKE xuống một nửa thì tiết kiệm được 118 ms; giảm số lần gọi câu kh đi một nửa thì tiết kiệm 354 ms.
mean_exec_time chỉ ra câu chậm nhất. total_exec_time chỉ ra câu đáng sửa nhất. Hai câu đó thường khác nhau, và cột thứ hai là cột đúng.
Điều này cũng giải thích một hiện tượng quen thuộc: log truy vấn chậm (log_min_duration_statement) chỉ bắt được câu vượt ngưỡng, nên nó không bao giờ thấy câu kh — và đó lại chính là câu chiếm nhiều thời gian máy chủ nhất.
Chuẩn hoá
Chạy năm câu với năm giá trị khác nhau:
select count(*) from d where kh = 1;
select count(*) from d where kh = 2;
select count(*) from d where kh = 3;
select count(*) from d where kh = 999;
select count(*) from d where kh = 12345;
pg_stat_statements chỉ có một dòng:
queryid 6297190510576004653 gọi 5 lần select count(*) from d where kh = $1
Mọi hằng số bị thay bằng tham số, và các câu giống nhau về cấu trúc được gộp lại. Đó là lý do 100.000 lần gọi ở trên hiện thành một dòng chứ không phải 10.000 dòng.
queryid ổn định giữa các lần khởi động lại máy chủ (từ PostgreSQL 14), nên bạn lưu lại được và so sánh trước sau khi triển khai.
Hai lưu ý về chuẩn hoá:
Cùng câu lệnh viết khác cách sẽ ra hai queryid. Thêm một dấu cách thì không sao, nhưng đổi thứ tự cột trong select hoặc đổi cách viết join thì ra dòng khác.
Danh sách IN với số lượng khác nhau ra queryid khác nhau ở PostgreSQL 15 trở xuống. Từ PostgreSQL 16, IN (1,2,3) và IN (1,2,3,4,5) được gộp lại.
Chi phí của chính extension
| tps | Độ trễ | |
|---|---|---|
track = all |
85.223 | 0,094 ms |
track = none |
83.252 | 0,096 ms |
Bản bật còn nhanh hơn một chút — nghĩa là chi phí nhỏ hơn cả dao động giữa hai lần đo.
Đây là kết quả đáng nói vì "sợ tốn hiệu năng" là lý do phổ biến nhất khiến người ta không bật nó. Ở quy mô đo được, chi phí không tồn tại.
Bộ nhớ thì có: extension giữ tối đa pg_stat_statements.max dòng trong bộ nhớ chia sẻ, mặc định 5.000. Mỗi dòng vài trăm byte cộng phần văn bản câu lệnh.
Khi vượt 5.000, câu ít dùng nhất bị đẩy ra. Với ứng dụng dùng ORM sinh nhiều dạng câu lệnh, con số này nên nâng lên 10.000 — và kiểm bằng:
select count(*) from pg_stat_statements;
show pg_stat_statements.max;
Nếu hai số bằng nhau, bảng đang đầy và bạn đang mất dữ liệu ở đuôi.
Bốn câu truy vấn đáng lưu lại
1. Tốn nhiều thời gian máy chủ nhất — câu quan trọng nhất:
select round(total_exec_time::numeric) as tong_ms,
calls,
round(mean_exec_time::numeric, 3) as tb_ms,
round(100.0 * total_exec_time / sum(total_exec_time) over (), 1) as phan_tram,
left(regexp_replace(query, '\s+', ' ', 'g'), 80) as cau_lenh
from pg_stat_statements
order by total_exec_time desc limit 20;
Cột phan_tram cho biết mỗi câu chiếm bao nhiêu phần trăm tổng thời gian. Thường ba câu đầu chiếm hơn nửa.
2. Gọi nhiều lần nhất — tìm truy vấn N+1:
select calls, round(mean_exec_time::numeric, 3) as tb_ms,
left(regexp_replace(query, '\s+', ' ', 'g'), 80)
from pg_stat_statements order by calls desc limit 20;
Câu nào có calls rất lớn và tb_ms rất nhỏ thường là dấu hiệu ứng dụng đang chạy một vòng lặp gọi cơ sở dữ liệu — thứ mà phần 31 đã đo là chậm hơn 13,6 lần so với làm trong SQL.
3. Đọc nhiều trang nhất — tìm truy vấn thiếu chỉ mục:
select shared_blks_read, shared_blks_hit,
round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) as ti_le_trung,
left(regexp_replace(query, '\s+', ' ', 'g'), 60)
from pg_stat_statements order by shared_blks_read desc limit 20;
Nhớ cảnh báo ở phần 44: read không có nghĩa là đọc đĩa. Kiểm cùng blk_read_time để biết nó có thật sự chạm đĩa không.
4. Sinh tệp tạm nhiều nhất — tìm truy vấn tràn work_mem:
select temp_blks_written, calls,
left(regexp_replace(query, '\s+', ' ', 'g'), 60)
from pg_stat_statements where temp_blks_written > 0
order by temp_blks_written desc limit 20;
Và cảnh báo ở phần 45: tràn tệp tạm chưa chắc là vấn đề. Chia temp_blk_write_time cho temp_blks_written để biết mỗi khối tốn bao nhiêu.
Các cột thời gian có sẵn
total_plan_time total_exec_time
blk_read_time blk_write_time
temp_blk_read_time temp_blk_write_time
jit_generation_time jit_inlining_time jit_optimization_time jit_emission_time
Hai cột đầu tách bạch thời gian lập kế hoạch với thời gian chạy. Phần 36 đã đo rằng với 1.000 phân vùng, lập kế hoạch mất 6,85 ms — nhiều hơn thời gian chạy của phần lớn truy vấn tra cứu. Cột total_plan_time là chỗ duy nhất phát hiện chuyện đó ở quy mô toàn hệ thống.
Bốn cột jit_* đáng nhìn nếu bạn thấy truy vấn ngắn bỗng chậm bất thường — biên dịch JIT có thể tốn nhiều hơn phần nó tiết kiệm được trên truy vấn nhỏ.
Cách dùng thực dụng
Đặt lại rồi đo một khoảng xác định:
select pg_stat_statements_reset();
-- đợi một giờ tải thật
select ... from pg_stat_statements order by total_exec_time desc limit 20;
Số liệu tích luỹ từ lúc khởi động máy chủ trộn lẫn giờ cao điểm với ban đêm. Đặt lại rồi đo một cửa sổ cụ thể cho bức tranh rõ hơn nhiều.
Lưu lại ảnh chụp định kỳ vào một bảng riêng, mỗi giờ một lần, rồi so hai ảnh chụp liên tiếp để có số liệu theo khoảng thay vì số tích luỹ. Đây là điều mà các công cụ giám sát chuyên dụng làm sẵn.
So trước và sau mỗi lần triển khai. queryid ổn định nên bạn ghép được hai ảnh chụp và thấy ngay câu nào đắt lên.
Thử ba mươi giây
select round(100.0 * total_exec_time / sum(total_exec_time) over (), 1) as phan_tram,
calls,
round(mean_exec_time::numeric, 2) as tb_ms,
left(regexp_replace(query, '\s+', ' ', 'g'), 70) as cau_lenh
from pg_stat_statements
order by total_exec_time desc limit 5;
Năm dòng này là năm truy vấn quyết định tải của cơ sở dữ liệu bạn. Nếu dòng đầu chiếm trên 30%, bạn vừa tìm được việc đáng làm nhất.
Và nếu extension chưa được bật: số đo ở bài này cho thấy chi phí của nó nằm dưới ngưỡng nhiễu. Không có lý do kỹ thuật nào để không bật.
Phần sau đo các khung nhìn thống kê còn lại: pg_stat_activity, pg_stat_user_tables, và những con số báo động sớm nhất.
