Danh sách kiểm và chốt sê-ri PostgreSQL

Sáu mươi phần, mỗi phần một phép đo. Phần này gom chúng thành một câu SQL chạy được, và chốt lại sê-ri.

Câu SQL chấm điểm

Chạy trên một máy chủ vừa dựng bằng image Docker mặc định:

10 trên 13 mục cần sửa. Sau khi áp dụng đủ: 13/13 đạt.

Đây là câu SQL:

select 'BỀN VỮNG' as nhom, 'wal_log_hints' as muc, setting as gia_tri,
       case when setting='on' then 'OK' else 'CẦN SỬA — phần 53' end as ket_luan
from pg_settings where name='wal_log_hints'
union all select 'BỀN VỮNG','fsync',setting,
  case when setting='on' then 'OK' else 'NGUY HIỂM — phần 48' end
  from pg_settings where name='fsync'
union all select 'BỀN VỮNG','archive_mode',setting,
  case when setting='on' then 'OK' else 'không có PITR — phần 50' end
  from pg_settings where name='archive_mode'
union all select 'HIỆU NĂNG','shared_buffers',setting||' x8kB',
  case when setting::bigint*8192 > 1073741824 then 'OK' else 'kiểm lại — phần 44' end
  from pg_settings where name='shared_buffers'
union all select 'HIỆU NĂNG','max_wal_size',setting||' MB',
  case when setting::bigint >= 2048 then 'OK' else 'nhỏ — phần 46,47' end
  from pg_settings where name='max_wal_size'
union all select 'HIỆU NĂNG','checkpoint_completion_target',setting,
  case when setting::float >= 0.9 then 'OK' else 'CẦN SỬA — phần 47' end
  from pg_settings where name='checkpoint_completion_target'
union all select 'GIÁM SÁT','track_io_timing',setting,
  case when setting='on' then 'OK' else 'CẦN BẬT — phần 44' end
  from pg_settings where name='track_io_timing'
union all select 'GIÁM SÁT','pg_stat_statements',coalesce(nullif(setting,''),'(rỗng)'),
  case when setting like '%pg_stat_statements%' then 'OK' else 'CẦN BẬT — phần 54' end
  from pg_settings where name='shared_preload_libraries'
union all select 'GIÁM SÁT','log_min_duration_statement',setting||' ms',
  case when setting::bigint between 0 and 5000 then 'OK' else 'chưa bật — phần 54' end
  from pg_settings where name='log_min_duration_statement'
union all select 'AN TOÀN','idle_in_transaction_timeout',setting||' ms',
  case when setting::bigint between 1 and 3600000 then 'OK' else 'CẦN ĐẶT — phần 21' end
  from pg_settings where name='idle_in_transaction_session_timeout'
union all select 'AN TOÀN','statement_timeout',setting||' ms',
  case when setting::bigint > 0 then 'OK' else 'CẦN ĐẶT — phần 26' end
  from pg_settings where name='statement_timeout'
union all select 'AN TOÀN','lock_timeout',setting||' ms',
  case when setting::bigint > 0 then 'OK' else 'CẦN ĐẶT — phần 26' end
  from pg_settings where name='lock_timeout'
union all select 'AN TOÀN','max_slot_wal_keep_size',setting||' MB',
  case when setting::bigint > 0 then 'OK' else 'không giới hạn — phần 51' end
  from pg_settings where name='max_slot_wal_keep_size';

Ba ngưỡng trong đó là giả định của tôi, không phải chân lý: shared_buffers trên 1 GB, max_wal_size từ 2 GB, log_min_duration_statement trong khoảng 0–5000 ms. Sửa cho hợp với máy chủ của bạn.

Và một chi tiết đáng kể: bản đầu tiên của câu này báo integer out of range khi shared_buffers = 2GB, vì setting::int*8192 tràn số 32 bit. Câu kiểm tra cấu hình cũng cần được kiểm tra.

Sáu việc không kiểm bằng SQL được

Bản sao lưu đã từng được khôi phục thử chưa. Phần 49 đo được khôi phục 3 triệu dòng mất 3 giây. Không có lý do gì để chưa từng thử.

pg_dumpall --globals-only có trong kịch bản sao lưu không. Phần 49: pg_dump không lấy vai trò và mật khẩu. Khôi phục xong, dữ liệu đủ, và không ai đăng nhập được.

Khoá ngoại nào thiếu chỉ mục. Phần 39 đo được xoá một dòng cha chậm 120 lần.

Ứng dụng biết máy chính là ai bằng cách nào. Phần 53: nếu câu trả lời là "địa chỉ ghi trong tệp cấu hình", mỗi lần chuyển đổi là một lần sửa cấu hình ở mọi máy.

Kích thước pool ở tầng ứng dụng. Phần 42 đo được đỉnh nằm ở đúng số lõi CPU; từ 16 lên 256 kết nối thì thông lượng không tăng mà độ trễ tăng 20 lần.

pg_hba.conf có còn trust không. Phần 59: image Docker mặc định cho phép mọi kết nối cục bộ vào không cần mật khẩu.

Năm con số đáng nhớ nhất

Điểm chung của cả năm: không cái nào cần viết lại ứng dụng. Bốn cái là một dòng cấu hình hoặc một câu CREATE INDEX.

Bốn kết quả đi ngược lời khuyên phổ biến

Đây là phần tôi thấy giá trị nhất của việc đo thay vì đọc.

"Tràn ra đĩa thì phải tăng work_mem." Ba lần đo độc lập — Hash Join (phần 29), HashAggregate (phần 32), sắp xếp (phần 45) — đều cho thấy cấu hình có ghi tệp tạm nhanh hơn cấu hình chạy hết trong bộ nhớ. Lý do: tệp tạm không chạm đĩa thật, chỉ tốn 4,7 micro giây mỗi khối.

"Tỉ lệ trúng đệm phải trên 99%." Phần 44 đo được tỉ lệ 0% mà truy vấn chỉ chậm hơn 23%, vì "read" không có nghĩa là đọc đĩa.

"Ít checkpoint hơn thì mượt hơn." Phần 47 đo được cấu hình có 36 checkpoint mượt hơn hẳn cấu hình có 3 — vì mỗi lần được trải ra 90% chu kỳ.

"count(distinct) chậm, hãy viết lại bằng group by." Phần 32 đo được lời khuyên đó làm chậm thêm 46%. Nó đúng ở thời PostgreSQL 12 trở xuống và sai từ 13.

Điểm chung: cả bốn đều từng đúng, hoặc đúng trong một hoàn cảnh cụ thể, rồi được chép lại mà không kèm hoàn cảnh.

Ba bài học về cách đo

Rút ra từ chính những lần tôi đo sai trong sê-ri này.

1. Đừng nuốt lỗi. Bốn lần một phép đo cho ra số 0 hoặc số quá đẹp, và nguyên nhân là lệnh chưa từng chạy:

• psql -c "vacuum analyze a; vacuum analyze b" báo VACUUM cannot run inside a transaction block — và vì không có thống kê, phép đo JOIN sai 800 lần (phần 29)
• docker exec thiếu cờ -i nên không nhận stdin, mọi khối do $$ … $$ im lặng không chạy (phần 19)
• Biến plpgsql v trùng tên cột v (phần 55)
• $RANDOM không tồn tại trong shell Alpine nên mọi khoá ngẫu nhiên đều là SP1 (phần 38)

2. Con số giống nhau quá mức là dấu hiệu sai. Bốn cấu hình pages_per_range khác nhau 77 lần về kích thước cho ra đúng 11.648 dòng kiểm lại — vì cả bốn đều dùng chung một chỉ mục cũ chưa xoá (phần 17). Số sai thì còn tranh luận được; số trùng khít thì chỉ có một lời giải thích.

3. Kết quả ngược trực giác phải lặp lại trước khi tin. "JSONB nhanh hơn cột thường" hoá ra là bộ đệm lạnh; lặp ba lần thì hai bên bằng nhau đúng 9,7 ms (phần 34). Ngược lại, "tràn đĩa nhanh hơn" thì lặp ba lần vẫn đúng, và đó là lúc tin được.

Những gì sê-ri không trả lời

Để công bằng với người đọc: có ba chỗ tôi không dựng được phép đo đáng tin.

Mất giao dịch đã xác nhận với synchronous_commit = off (phần 48). Vòng lặp ghi của tôi vẫn tiếp tục sau tín hiệu giết nên số liệu trước và sau không so được. Tôi ghi lại giới hạn đó thay vì bịa một con số.

Mất trạng thái phiên trong PgBouncer chế độ transaction (phần 43) — tôi dựng được ca rò rỉ work_mem sang client khác, nhưng không dựng được ca mất trạng thái, vì nó cần các client xen kẽ đúng thời điểm.

Trường hợp SP-GiST thắng B-tree (phần 18). Tôi kết luận rằng trên dữ liệu của tôi nó không thắng, không phải rằng nó vô dụng.

Sáu mươi phần, và cái còn lại

Nếu phải rút gọn cả sê-ri thành một câu: PostgreSQL cho bạn công cụ để tự đo, và phần lớn lời khuyên bạn đọc được đều có thể kiểm chứng trong vài phút.

EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, track_io_timing, và một container Docker là đủ để kiểm lại mọi con số trong sáu mươi phần này — kể cả những con số của tôi.

Quy trình gỡ lỗi ở phần 56 là năm bước, và nó là thứ duy nhất trong sê-ri áp dụng được cho câu hỏi mà tôi chưa đo:

1. Cái gì tốn nhiều thời gian máy chủ nhất
2. Trong câu đó, thời gian đi đâu
3. Thống kê có đúng không
4. Thử từng cách sửa, đo cả tốc độ lẫn dung lượng
5. Kiểm lại bằng chính công cụ của bước 1

Bước 5 rẻ nhất và bị bỏ nhiều nhất.

Thử ba mươi giây

Chép câu SQL ở đầu bài, chạy trên máy chủ của bạn, đếm số dòng không phải OK.

Mỗi dòng như vậy có một số phần đi kèm, và trong phần đó có con số đo được cho biết bạn đang trả giá bao nhiêu.