Đo thời gian psql qua VPN: tách thời gian chờ mạng khỏi công việc của truy vấn

Báo cáo của bạn xong trong khoảng một giây trên máy chủ cơ sở dữ liệu và bốn giây qua VPN, và con số psql in ra chỉ thẳng vào cơ sở dữ liệu. Con số đó là đồng hồ bấm giây phía máy khách, không phải phép đo của máy chủ. Nó bao gồm câu lệnh trên đường ra, việc chờ phản hồi và các hàng trên đường về. Tách ba thứ này ra là cách duy nhất để biết nên chỉnh bên nào.

Bộ đếm thời gian của psql thực sự đo gì

\timing on khởi động đồng hồ bấm giây ngay trước khi máy khách ghi câu lệnh vào socket và dừng lại khi hàng cuối cùng của kết quả đó được đọc. Việc thực thi của chính máy chủ nằm đâu đó trong cửa sổ đó, và mỗi mili giây mà các gói tin dành trong đường hầm cũng vậy. Không có gì trong đầu ra tách chúng ra. EXPLAIN cũng in một con số, nhưng con số đó do máy chủ tạo ra và loại trừ thời gian chờ của máy khách. Hai con số, hai câu hỏi khác nhau.

Bắt đầu một phiên, để việc đo thời gian bật, và chạy câu lệnh bạn quan tâm từ bên trong phiên đó. Dùng lại một phiên giữ chi phí kết nối ngoài phép đo đầu tiên.

psql "host=db.internal port=5432 dbname=appdb user=app" \
  -c "\timing on" \
  -c "select count(*) from orders where created_at > now() - interval '1 day';"

Con số vẫn bao gồm việc truyền kết quả, đây chính là điều bạn muốn ở đây. Nó không còn bao gồm việc trao đổi kết nối và xác thực mà một lần gọi mới sẽ thêm vào.

So sánh một câu lệnh trên ba đường

Chạy cùng câu lệnh qua socket Unix, qua địa chỉ LAN và qua tên máy chủ VPN, rồi so sánh cả ba. Nếu chỉ đường cuối chậm, truy vấn không phải là biến số.

Mỗi lần gọi kết nối trước và chỉ sau đó mới đo câu lệnh, nên chi phí kết nối nằm ngoài con số trong khi việc truyền nằm trong. Giữ mệnh đề WHERE, số hàng và máy khách giống hệt nhau, nếu không bạn đang so sánh khối lượng công việc chứ không phải các đường.

psql -h /var/run/postgresql -U app -d appdb -c "\timing on" -c "select count(*) from orders;"
psql -h 10.40.0.12 -U app -d appdb -c "\timing on" -c "select count(*) from orders;"
psql -h db.vpn.internal -U app -d appdb -c "\timing on" -c "select count(*) from orders;"

Trên LAN mức chênh thường đủ nhỏ để bỏ qua; qua VPN thường không như vậy. Sắp xếp ba kết quả sau đó sẽ cho biết nên tấn công vào thời gian chờ nào.

Socket cục bộ chậm nghĩa là công việc của máy chủ hoặc việc lập kế hoạch. Kết quả LAN chậm nghĩa là truy vấn hoặc khối lượng mà nó di chuyển. Chỉ kết quả VPN chậm nghĩa là độ trễ, MTU hoặc băng thông khả dụng trên đường, và không thay đổi chỉ mục nào khắc phục được điều đó.

Lấy thời gian thực thi của chính máy chủ

Máy chủ có thể báo cáo thời gian nó đã dành, và đó là con số cần đặt cạnh con số của máy khách. EXPLAIN với ANALYZE chạy câu lệnh và in thời gian lập kế hoạch và thực thi bên trong kế hoạch, còn BUFFERS thêm hành vi bộ đệm vốn thường giải thích một lần chạy nóng so với lần chạy nguội. Đừng hướng nó vào các câu lệnh ghi trừ khi bạn định ghi. Trên một bản sao chỉ đọc, đây là nơi an toàn nhất để bắt đầu, và tài liệu tham chiếu psql ghi lại phạm vi phía máy khách của bộ đếm thời gian.

Đối với những câu lệnh mà ứng dụng đã chạy, pg_stat_statements tổng hợp việc thực thi phía máy chủ mà không chạy lại bất cứ thứ gì. Các hàng tính theo truy vấn đã chuẩn hóa, nên một mục có thể bao trùm nhiều điểm gọi.

select calls, round(mean_exec_time::numeric, 2) as mean_ms, rows
from pg_stat_statements
order by mean_exec_time desc
limit 5;

Tên cột khác nhau theo phiên bản: PostgreSQL 13 và mới hơn hiển thị mean_exec_time, trong khi các phiên bản cũ gọi nó là mean_time, nên hãy kiểm tra bằng \d pg_stat_statements nếu truy vấn đó báo lỗi. Những giá trị này loại trừ việc lập kế hoạch và loại trừ chuyến đi tới máy khách của bạn. Chính sự loại trừ đó khiến chúng hữu ích cho việc so sánh.

Đếm số lần đi về, không chỉ mili giây

Một báo cáo dựng từ hai trăm câu lệnh nhỏ phụ thuộc lẫn nhau nhân mọi độ trễ mà đường đi thêm vào với hai trăm. Một câu lệnh trả về một triệu hàng trả độ trễ đó một lần rồi trả băng thông trong thời gian dài. Cả hai trông giống hệt nhau trong bộ đếm thời gian phía máy khách, và chúng cần những cách sửa ngược nhau.

Đo số câu lệnh thay vì ước lượng nó. Đọc tổng, chạy báo cáo một lần, rồi đọc lại và trừ đi.

select sum(calls) as calls from pg_stat_statements;
-- run the report here
select sum(calls) as calls from pg_stat_statements;

Nếu delta lớn và mỗi câu lệnh đều rẻ trên máy chủ, gom lô là cách sửa có lãi. Kết hợp các truy vấn phụ thuộc, dùng một câu lệnh dựa trên tập hợp hoặc dẫn vòng lặp từ con trỏ. Trong psql tương tác, \set FETCH_COUNT 1000 khiến một kết quả lớn đến theo lô và thay đổi hành vi của việc truyền và bộ nhớ máy khách. Nó không giảm công việc của máy chủ, nên sẽ không cứu một truy vấn chậm.

Kiểm tra đường hầm về truyền lại và tắc MTU

Khi các câu lệnh nhỏ nhanh mà kết quả lớn lại khựng giữa đường, truy vấn thường ổn và đường đi thì không. Một đường hầm có MTU thấp hơn liên kết bên dưới, kết hợp với ICMP bị chặn, tạo ra đúng kiểu mẫu đó: các gói đầu tiên đến, các gói lớn biến mất, và việc truyền bị treo cho đến khi thử lại. Các bộ đếm truyền lại phía máy khách cho thấy điều đó.

Quan sát socket và các bộ đếm của nhân trong khi một lần truyền cố ý chậm đang chạy. Tổng truyền lại là tích lũy, nên hãy ghi lại trước và sau thay vì đọc một mẫu duy nhất.

ss -tin dst 10.40.0.12
netstat -s | grep -iE "retransmit|segments retransmitted"
ping -M do -s 1400 -c 3 10.40.0.12

Truyền lại tăng trong lúc truyền, cùng với một ping thành công ở 1400 byte nhưng thất bại ở 1472, chỉ ra vấn đề MTU chứ không phải vấn đề cơ sở dữ liệu. Hạ MTU của giao diện đường hầm hoặc siết MSS tại cổng là cách sửa thông thường, và ba lệnh đó xác nhận lại sau đó. Nếu truyền lại vẫn phẳng trong khi việc truyền vẫn chậm, hãy nhìn băng thông được phép cho đường hầm chứ không phải mất mát.

Một trình tự ngắn bao trùm báo cáo chậm tiếp theo: so sánh một câu lệnh cục bộ, trên LAN và qua VPN; đọc việc thực thi của máy chủ từ EXPLAIN hoặc pg_stat_statements; đếm số lần gọi trước và sau một lần chạy; và quan sát truyền lại trong khi kết quả lớn di chuyển. Mỗi bước loại trừ một bên của đường đi. Không bước nào đòi phải đoán xem nên khởi động lại thành phần nào.