Postgres được tinh chỉnh đúng cách cho instance EPYC 64 GB

Nội dung bài viết này

Một phiên bản Postgres duy nhất trên E-8 sử dụng phần cứng được cấp: tám lõi Zen 4 chuyên dụng, 64 GB ECC DDR5 registered, và NVMe Gen4 với bảo vệ mất điện. Theo mặc định, Postgres được cấu hình để chạy trên một chiếc laptop từ năm 2009. Nếu để nguyên như vậy, nó sẽ chỉ sử dụng khoảng một phần trăm bộ nhớ của máy này và tự hỏi tại sao nó chậm.

Mỗi con số dưới đây đều được suy ra thay vì sao chép. Nếu phiên bản của bạn có dung lượng bộ nhớ khác, phép tính được trình bày để bạn có thể tính lại. Bài viết này dành cho khối lượng công việc chủ yếu là giao dịch với một chút báo cáo, điều mà hầu hết mọi người thực sự có bất kể họ nói gì.

Trước khi bắt đầu

• E-8 hoặc lớn hơn. Dưới 64 GB, tỷ lệ vẫn đúng nhưng con số tuyệt đối không đúng, và huge pages không còn đáng công sức.
• Bộ nhớ ECC, mà dòng EPYC có còn dòng Ryzen thì không. Lỗi bộ nhớ trong shared buffers là một trang bị hỏng được ghi lại vào đĩa mà không có cảnh báo.
• Debian 13, cung cấp PostgreSQL 17.

1. Cài đặt và khởi tạo

apt update && apt install -y postgresql postgresql-contrib numactl
systemctl stop postgresql
pg_dropcluster 17 main
pg_createcluster 17 main -- --data-checksums --encoding=UTF8 --locale=C.UTF-8

Data checksums phải được chọn tại thời điểm khởi tạo và tốn một vài phần trăm thông lượng. Chúng là sự khác biệt giữa một trang hỏng được báo cáo và một trang hỏng được phục vụ, vì vậy hãy chấp nhận hai phần trăm đó.

2. Bộ nhớ

Bốn cài đặt, bốn phép tính:

• shared_buffers bằng một phần tư RAM: 16GB. Giá trị lớn hơn từng bị khuyến cáo; trên một máy chuyên dụng với kernel hiện đại, một phần tư là điểm khởi đầu đã được kiểm chứng và một phần ba là hợp lý nếu working set của bạn lớn hơn.
• effective_cache_size bằng ba phần tư RAM: 48GB. Cài đặt này không cấp phát gì; nó cho planner biết kernel có khả năng đã cache bao nhiêu, và đặt quá thấp sẽ khiến planner từ chối index scan mà nó nên chọn.
• work_mem cho mỗi sort node, không phải mỗi kết nối: với 200 kết nối và có lẽ hai sort mỗi cái, 32MB là một giới hạn hợp lý trên máy 64 GB. Tăng nó cho mỗi phiên cho các truy vấn báo cáo thay vì toàn cục.
• maintenance_work_mem cho vacuum và index builds: 2GB, với autovacuum_work_mem để kế thừa nó.

shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 32MB
maintenance_work_mem = 2GB
huge_pages = try

3. Huge pages

Mười sáu gigabyte shared buffers được ánh xạ trong các trang bốn kilobyte có nghĩa là bốn triệu page table entries cho mỗi backend. Các trang hai megabyte giảm điều đó xuống hệ số năm trăm, và lợi ích hiện ra dưới dạng CPU thấp hơn khi có sự đồng thời thay vì một con số nổi bật.

systemctl start postgresql
sudo -u postgres psql -c "SHOW shared_memory_size_in_huge_pages;"

Lấy con số in ra, cộng thêm mười phần trăm để dự phòng, và viết nó xuống:

echo "vm.nr_hugepages = 8600" > /etc/sysctl.d/60-postgres.conf
echo "vm.overcommit_memory = 2" >> /etc/sysctl.d/60-postgres.conf
echo "vm.overcommit_ratio = 90" >> /etc/sysctl.d/60-postgres.conf
echo "vm.swappiness = 1" >> /etc/sysctl.d/60-postgres.conf
sysctl --system
echo never > /sys/kernel/mm/transparent_hugepage/enabled

Huge pages tường minh có, transparent huge pages không. Cái thứ hai chống phân mảnh bộ nhớ tại những thời điểm không thể đoán trước và tạo ra các đột biến độ trễ mà mọi người dành hàng tuần để đổ lỗi cho lưu trữ của họ.

4. Write-ahead log và checkpoints

Cài đặt checkpoint mặc định buộc flush mỗi vài giây khi có tải, biến NVMe của bạn thành một hàng đợi các ghi đồng bộ nhỏ.

wal_level = replica
max_wal_size = 16GB
min_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = zstd
wal_buffers = 64MB
synchronous_commit = on
full_page_writes = on

Để synchronous_commit bật. Tắt nó là mức tăng thông lượng lớn nhất hiện có và nó có nghĩa là xác nhận các giao dịch mà mất điện có thể nuốt mất. Ổ đĩa của chúng tôi có write cache chống tụ điện nên fsync thực sự rẻ ở đây; hãy mua thông lượng ở nơi trung thực hơn.

5. Lưu trữ và planner

random_page_cost = 1.1
seq_page_cost = 1.0
effective_io_concurrency = 200
maintenance_io_concurrency = 200
default_statistics_target = 200
jit = off

random_page_cost ở mức bốn là con số dành cho đĩa quay và nó là sai lầm cấu hình đơn lẻ phổ biến nhất ngoài thực tế. Trên NVMe, một lần đọc ngẫu nhiên tốn gần như chính xác như một lần đọc tuần tự, và nói với planner khác đi sẽ khiến nó tránh index trên các bảng lớn. JIT compilation tắt vì đối với các truy vấn giao dịch ngắn, nó tốn nhiều thời gian biên dịch hơn thực thi; hãy bật nó cho mỗi phiên cho các truy vấn phân tích.

6. Song song, autovacuum và kết nối

max_connections = 200
max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4

autovacuum_max_workers = 4
autovacuum_naptime = 15s
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01
autovacuum_vacuum_cost_limit = 3000

shared_preload_libraries = 'pg_stat_statements'
track_io_timing = on
log_min_duration_statement = 500ms
log_checkpoints = on
log_autovacuum_min_duration = 0

Số lượng worker khớp với tám lõi chuyên dụng, vì chúng là chuyên dụng. Trên một máy chủ oversold, những con số này sẽ là dối trá và bạn sẽ phải giảm chúng xuống theo phần lõi bạn thực sự được bán; đó không phải là vấn đề bạn gặp ở đây.

Scale factor mặc định của autovacuum là 0.2 nghĩa là một bảng trăm triệu dòng chờ hai mươi triệu dead tuple trước khi bất cứ điều gì xảy ra. Hai phần trăm thay vì hai mươi phần trăm giữ vacuum chạy thường xuyên và ngắn gọn thay vì hiếm hoi và thảm khốc.

7. Pooling

Hai trăm backend trên tám lõi không phải là song song, nó là một hàng đợi với chi phí bộ nhớ thêm. Đặt một pooler phía trước:

apt install -y pgbouncer

[databases]
app = host=/var/run/postgresql dbname=app

[pgbouncer]
listen_addr = 10.9.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 32
max_client_conn = 2000
server_idle_timeout = 60

Transaction pooling với ba mươi hai kết nối máy chủ cho bạn bốn mỗi lõi, đó là nơi thông lượng ngừng cải thiện trên silicon này. Các ứng dụng sử dụng các tính năng cấp phiên như advisory locks hoặc prepared statements qua các giao dịch cần session pooling và sẽ nhận được con số nhỏ hơn.

systemctl restart postgresql pgbouncer

Xác minh nó

Cài đặt trước. Bất cứ điều gì không có hiệu lực sẽ rõ ràng ở đây hơn là trong ba tuần:

sudo -u postgres psql -c "SELECT name, setting, unit FROM pg_settings WHERE name IN ('shared_buffers','effective_cache_size','work_mem','huge_pages','random_page_cost','max_wal_size');"
grep -E "HugePages_(Total|Free)" /proc/meminfo

HugePages_Free nên thấp hơn HugePages_Total vài nghìn trang, điều đó có nghĩa Postgres thực sự đã ánh xạ chúng. Giá trị bằng nhau có nghĩa là nó quay lại các trang nhỏ và huge_pages = try nuốt lỗi một cách im lặng.

Sau đó đo lường. Xây dựng một tập dữ liệu đủ lớn để thú vị nhưng đủ nhỏ để nằm trong shared buffers:

sudo -u postgres createdb bench
sudo -u postgres pgbench -i -s 800 bench
sudo -u postgres pgbench -c 32 -j 8 -T 60 -S bench
sudo -u postgres pgbench -c 32 -j 8 -T 60 bench

Trên E-8, lần chạy read-only đạt hàng trăm nghìn giao dịch mỗi giây, và lần chạy read-write đạt vài nghìn. Con số chính xác phụ thuộc vào site và tập dữ liệu, vì vậy tín hiệu hữu ích là thứ tự độ lớn: nếu bài kiểm tra chỉ-select trả về hàng chục nghìn thay vì hàng trăm nghìn, shared_buffers đã không có hiệu lực hoặc bạn vẫn đang benchmark qua cache lạnh trong lần chạy đầu tiên.

Cuối cùng, xác nhận extension statistics được tải, vì nó là thứ bạn thực sự sẽ dùng mỗi tuần sau đó:

sudo -u postgres psql bench -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
sudo -u postgres psql bench -c "SELECT calls, round(mean_exec_time::numeric,2) AS ms, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"

Sau đó

Bật WAL archiving đến một instance thứ hai ở thành phố khác trước khi cơ sở dữ liệu này có bất cứ thứ gì bạn sẽ nhớ. Khôi phục một bản backup cơ sở chưa bao giờ kiểm tra không phải là backup, nó là một hy vọng, và trang locations tồn tại một phần để standby của bạn không ở cùng tòa nhà với primary của bạn.