Trace cho bạn một span db.query với một con số. PostgreSQL có pg_stat_statements cũng cho một con số cho đúng truy vấn đó. Hai con số không bằng nhau, và khoảng cách giữa chúng là thứ đáng đo nhất trong bài này — vì nó quyết định việc tối ưu SQL có ích hay vô ích.

Span nói một đằng, pg_stat_statements nói một đằng khác

Bảng số liệu

PostgreSQL 16 thật, bảng orders 200 000 dòng có chỉ mục, chạy từ một client Python qua mạng Docker. Mỗi truy vấn chạy 2 000 lần; trung vị của ba lần chạy.

Truy vấn Client đo PostgreSQL tự báo Chênh Phần ở ngoài CSDL
SELECT theo chỉ mục 0,060 ms 0,007 ms 0,053 ms 88,3%
INSERT một dòng 0,103 ms 0,004 ms 0,099 ms 96,1%
SELECT gộp cả bảng 85,110 ms 84,861 ms 0,249 ms 0,3%

Điều đáng nhớ

Nói lại theo cách khác, vì đây là chỗ quyết định bạn đi tối ưu cái gì:

Với truy vấn nhanh, phần lớn thời gian không nằm trong cơ sở dữ liệu. SELECT theo chỉ mục mất 0,060 ms nhìn từ ứng dụng, nhưng PostgreSQL chỉ tốn 0,007 ms để thực thi nó. 88,3% còn lại là dựng câu lệnh, đi qua mạng, quay về, và driver phân tích kết quả.

Với INSERT thì còn cực đoan hơn: 96,1%. PostgreSQL chèn một dòng trong 0,004 ms — bốn micro giây. Mọi thứ khác là chi phí đi lại.

Chi phí cố định đó gần như không đổi dù truy vấn nặng hay nhẹ. Nó là 0,053 ms với SELECT chỉ mục, 0,099 ms với INSERT, và 0,249 ms với truy vấn gộp cả bảng. Cùng bậc độ lớn, trong khi phần thực thi trong CSDL trải từ 0,004 ms tới 84,861 ms — chênh hơn hai vạn lần.

Nên với truy vấn gộp cả bảng, bức tranh lật ngược hoàn toàn: chỉ 0,3% nằm ngoài. Đây mới là truy vấn đáng đi tối ưu SQL.

Hai con số không mâu thuẫn nhau — chúng đo hai thứ khác nhau. Span đo thứ người dùng cảm nhận. pg_stat_statements đo thứ SQL của bạn tốn. Hiệu của chúng mới là con số đáng xem, và không công cụ nào tự tính nó cho bạn.

Vì sao

Một lời gọi cơ sở dữ liệu từ ứng dụng đi qua nhiều chặng, và chỉ một chặng trong đó là thực thi SQL: driver dựng thông điệp giao thức, ghi vào socket, gói tin đi qua ngăn xếp mạng, PostgreSQL đọc và phân tích, tra kế hoạch đã lưu, thực thi — đây là phần duy nhất pg_stat_statements đếm — rồi tuần tự hoá kết quả, gửi ngược lại, và driver dựng đối tượng Python.

Với truy vấn quét 200 000 dòng và tính trung bình, phần thực thi là 84,861 ms và nó nuốt trọn mọi thứ khác. Với truy vấn tra một chỉ mục rồi lấy mười dòng, phần thực thi là 7 micro giây — nhỏ hơn cả thời gian gói tin đi một vòng trên mạng ảo của Docker.

Đó là lý do vì sao "tối ưu truy vấn" thường không cải thiện gì. Nếu SQL của bạn đã chỉ tốn 7 µs, làm nó nhanh gấp đôi tiết kiệm được 3,5 µs trên tổng 60 µs — cải thiện 6%. Còn gộp hai lời gọi thành một tiết kiệm trọn 53 µs.

Đây cũng là nền tảng số học của vấn đề N+1 truy vấn: một trang gọi 100 truy vấn nhỏ tốn 100 × 0,060 = 6 ms, trong đó 5,3 ms là chi phí đi lại. Viết lại thành một truy vấn duy nhất, dù truy vấn đó chậm gấp mười lần trong CSDL, vẫn nhanh hơn nhiều lần.

Nghĩa là gì trong thực tế

  • Đặt cả hai con số cạnh nhau trước khi quyết định tối ưu gì. Span cho tổng, pg_stat_statements cho phần thực thi. Nếu hiệu của chúng lớn, vấn đề là số lần gọi chứ không phải câu lệnh.
  • Với truy vấn dưới một mili giây, đừng tối ưu SQL — hãy đếm số lần gọi. Ngưỡng thô: nếu mean_exec_time trong pg_stat_statements nhỏ hơn khoảng 0,1 ms thì bạn đang ở vùng mà chi phí cố định chi phối.
  • Truy vấn calls cao và mean_exec_time thấp là dấu hiệu N+1. Một câu lệnh gọi hàng chục nghìn lần với thời gian thực thi vài micro giây gần như luôn là một vòng lặp đáng gộp lại.
  • Chi phí cố định của bạn sẽ lớn hơn của tôi. 0,053 ms là mạng ảo trên cùng một máy. Qua mạng thật, qua bộ gộp kết nối, qua TLS — con số đó tăng, và tỷ lệ 88,3% cũng tăng theo.
  • Nhớ lại phần trước của sê-ri: chi phí một chặng HTTP nội bộ là 0,70 ms. Một lời gọi cơ sở dữ liệu 0,060 ms rẻ hơn thế mười lần — nhưng ứng dụng thường gọi CSDL nhiều hơn gọi dịch vụ khác hàng chục lần.

Có một cách khác để thấy cùng chuyện đó, và nó giải thích vì sao hai công cụ hay bị coi là mâu thuẫn. pg_stat_statements gom mọi lần chạy của cùng một hình dạng câu lệnh lại — WHERE user_id = 42 và WHERE user_id = 99 được đếm chung vì tham số đã được thay bằng $1. Nó trả lời câu "câu lệnh này tốn bao nhiêu, trung bình, trên toàn hệ thống". Span thì trả lời câu "lần gọi này, của yêu cầu này, mất bao lâu".

Vì thế khi một người mở trace và thấy 0,060 ms còn người kia mở bảng thống kê của cơ sở dữ liệu và thấy 0,007 ms, cả hai đều đang đọc đúng con số của mình. Cuộc tranh luận "cơ sở dữ liệu có chậm không" thường là hai người trả lời hai câu hỏi khác nhau mà tưởng là một. Đặt hai con số cạnh nhau và lấy hiệu sẽ chấm dứt tranh luận đó trong ba mươi giây.

Điều đáng nói thêm: hiệu số ấy còn là một chỉ số sức khoẻ tự thân. Nếu nó đột nhiên tăng mà mean_exec_time không đổi, cơ sở dữ liệu vẫn khoẻ và vấn đề nằm ở mạng, ở bộ gộp kết nối, hoặc ở chính ứng dụng — ba chỗ mà không ai nghĩ tới khi biểu đồ ghi là "truy vấn chậm".

Chỗ tôi không kết luận được

Client và PostgreSQL chạy trên cùng một máy, nối bằng mạng ảo Docker. Không có chuyển mạch, không có TLS, không có bộ gộp kết nối. Chi phí cố định thật trong hệ thống production sẽ lớn hơn — nghĩa là các tỷ lệ 88,3% và 96,1% là cận dưới, không phải cận trên.

pg_stat_statements chỉ đếm thời gian thực thi. Nó không tính thời gian chờ khoá ở tầng khác, không tính thời gian gửi kết quả về, và với truy vấn trả nhiều dòng thì phần gửi kết quả có thể đáng kể. Nên "phần trong CSDL" của tôi thực ra là cận dưới của phần trong CSDL, và chi phí cố định 0,053 ms là cận trên tương ứng.

Tôi chạy tuần tự, một kết nối, không có tải đồng thời. Dưới tải, mean_exec_time tăng vì tranh chấp, còn chi phí cố định cũng tăng vì xếp hàng ở bộ gộp kết nối. Tỷ lệ giữa chúng có thể đổi theo cả hai hướng và tôi không đo.

Con số INSERT 0,004 ms là đáng ngờ theo hướng tốt. Nó nhanh tới mức tôi phải kiểm lại: PostgreSQL ghi vào bộ đệm và WAL rồi trả về, còn việc đẩy xuống đĩa thật xảy ra sau — với synchronous_commit mặc định thì INSERT không chờ đĩa. Đó là hành vi đúng của PostgreSQL, không phải lỗi đo, nhưng nó có nghĩa là con số này không bao gồm chi phí bền vững hoá.

Thử ba mươi giây

# Chay tren PostgreSQL cua ban - can pg_stat_statements
psql -c "SELECT
    substring(query, 1, 50)          AS truy_van,
    calls,
    round(mean_exec_time::numeric, 4) AS pg_ms,
    round(total_exec_time::numeric/1000, 1) AS tong_giay
  FROM pg_stat_statements
  WHERE calls > 1000
  ORDER BY calls DESC
  LIMIT 10;"

Nhìn cột pg_ms. Mọi dòng dưới 0,1 ms là một dòng mà tối ưu câu lệnh gần như không giúp được gì — phần lớn thời gian người dùng chờ nằm ở chỗ khác. Đối chiếu cột calls của chính những dòng đó: đấy mới là chỗ có thể cắt.