Postgres disetel dengan benar untuk instance EPYC 64 GB

Apa yang dibangun ini

Sebuah instance Postgres tunggal di E-8 yang menggunakan perangkat keras yang diberikan: delapan inti Zen 4 khusus, 64 GB ECC DDR5 terdaftar, dan Gen4 NVMe dengan perlindungan kehilangan daya. Secara bawaan, Postgres dikonfigurasi untuk berjalan di laptop dari tahun 2009. Jika dibiarkan begitu, ia akan menggunakan sekitar seperseratus memori di mesin ini dan bertanya-tanya mengapa ia lambat.

Setiap angka di bawah ini diturunkan, bukan disalin. Jika instance Anda memiliki jumlah memori yang berbeda, aritmetikanya ditunjukkan sehingga Anda dapat mengulanginya. Ini ditulis untuk beban kerja yang sebagian besar transaksional dengan beberapa pelaporan, yang merupakan apa yang kebanyakan orang sebenarnya miliki terlepas dari apa yang mereka katakan.

Sebelum memulai

• E-8 atau lebih besar. Di bawah 64 GB rasionya masih berlaku tetapi angka absolutnya tidak, dan halaman besar berhenti sepadan dengan kerumitannya.
• Memori ECC, yang dimiliki jajaran EPYC dan tidak dimiliki jajaran Ryzen. Kesalahan memori di buffer bersama adalah halaman korup yang ditulis kembali ke disk tanpa peringatan.
• Debian 13, yang mengemas PostgreSQL 17.

1. Instal dan inisialisasi

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

Checksum data harus dipilih pada waktu inisialisasi dan menghabiskan beberapa persen throughput. Itu adalah perbedaan antara halaman korup dilaporkan dan halaman korup disajikan, jadi ambil dua persen itu.

2. Memori

Empat pengaturan, empat perhitungan:

• shared_buffers pada seperempat RAM: 16 GB. Nilai yang lebih besar dulu tidak disarankan; pada mesin khusus dengan kernel modern, seperempat adalah titik awal yang teruji baik dan sepertiga dapat dipertahankan jika set kerja Anda lebih besar.
• effective_cache_size pada tiga perempat RAM: 48 GB. Ini tidak mengalokasikan apa pun; ini memberi tahu perencana seberapa besar kemungkinan kernel telah meng-cache-nya, dan menyetelnya terlalu rendah membuat perencana menolak pemindaian indeks yang seharusnya dipilihnya.
• work_mem per node sortir, bukan per koneksi: dengan 200 koneksi dan mungkin dua sortir masing-masing, 32 MB adalah batas yang dapat dipertahankan pada kotak 64 GB. Naikkan per sesi untuk kueri pelaporan daripada secara global.
• maintenance_work_mem untuk vakum dan pembangunan indeks: 2 GB, dengan autovacuum_work_mem dibiarkan mewarisinya.

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

3. Halaman besar

Enam belas gigabyte buffer bersama yang dipetakan dalam halaman empat kilobyte berarti empat juta entri tabel halaman per backend. Halaman dua megabyte menguranginya dengan faktor lima ratus, dan keuntungannya muncul sebagai CPU yang lebih rendah di bawah konkurensi daripada sebagai angka utama.

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

Ambil angka yang dicetak, tambahkan sepuluh persen untuk ruang aman, dan tuliskan:

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

Halaman besar eksplisit ya, halaman besar transparan tidak. Yang kedua mendefrag memori pada saat yang tidak dapat diprediksi dan menghasilkan lonjakan latensi yang orang habiskan berminggu-minggu untuk menyalahkan penyimpanan mereka.

4. Log tulis-ahead dan checkpoint

Pengaturan checkpoint default memaksa flush setiap beberapa detik di bawah beban, yang mengubah NVMe Anda menjadi antrian penulisan sinkron kecil.

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

Biarkan synchronous_commit aktif. Mematikannya adalah peningkatan throughput terbesar yang tersedia dan itu berarti mengakui transaksi yang dapat dimakan oleh kehilangan daya. Drive kami memiliki cache tulis bertenaga kapasitor sehingga fsync benar-benar murah di sini; beli throughput di tempat yang jujur sebagai gantinya.

5. Penyimpanan dan perencana

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 pada empat adalah angka disk berputar dan itu adalah salah konfigurasi tunggal paling umum di dunia nyata. Di NVMe, pembacaan acak hampir sama persis dengan pembacaan sekuensial, dan memberi tahu perencana sebaliknya membuatnya menghindari indeks pada tabel besar. Kompilasi JIT mati karena untuk kueri transaksional pendek ia menghabiskan lebih banyak waktu untuk mengompilasi daripada mengeksekusi; nyalakan per sesi untuk yang analitis.

6. Paralelisme, autovacuum, dan koneksi

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

Jumlah pekerja cocok dengan delapan inti khusus, karena memang khusus. Pada host yang dijual berlebihan, angka-angka ini akan menjadi kebohongan dan Anda akan menurunkannya ke fraksi inti apa pun yang sebenarnya Anda beli; itu bukan masalah yang Anda miliki di sini.

Faktor skala autovacuum default 0,2 berarti tabel dengan seratus juta baris menunggu dua puluh juta tuple mati sebelum sesuatu terjadi. Dua persen sebagai ganti dua puluh persen membuat vakum berjalan sering dan singkat, bukan jarang dan bencana.

7. Pooling

Dua ratus backend pada delapan inti bukanlah paralelisme, itu adalah antrian dengan overhead memori ekstra. Letakkan pooler di depan:

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

Pooling transaksi dengan tiga puluh dua koneksi server memberi Anda empat per inti, yang kira-kira di mana throughput berhenti meningkat pada silikon ini. Aplikasi yang menggunakan fitur tingkat sesi seperti kunci penasehat atau pernyataan siap pakai di seluruh transaksi memerlukan pooling sesi sebagai gantinya, dan akan mendapatkan angka yang lebih kecil.

systemctl restart postgresql pgbouncer

Verifikasi

Pengaturan dulu. Apa pun yang tidak berlaku akan jelas di sini daripada dalam tiga minggu:

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 harus beberapa ribu halaman di bawah HugePages_Total, yang berarti Postgres benar-benar memetakannya. Nilai yang sama berarti jatuh kembali ke halaman kecil dan huge_pages = try menelan kegagalan secara diam-diam.

Lalu ukur. Bangun dataset yang cukup besar untuk menarik tetapi cukup kecil untuk berada di buffer bersama:

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

Di E-8, jalur hanya-baca mencapai ratusan ribu transaksi per detik, dan jalur baca-tulis di puluhan ribu. Angka pasti tergantung pada situs dan dataset, jadi sinyal yang berguna adalah urutan besarnya: jika tes hanya-pilih mengembalikan puluhan ribu daripada ratusan ribu, shared_buffers tidak berlaku atau Anda masih menguji melalui cache dingin pada percobaan pertama.

Akhirnya, konfirmasikan ekstensi statistik dimuat, karena itu adalah hal yang akan Anda gunakan setiap minggu setelahnya:

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;"

Setelahnya

Nyalakan pengarsipan WAL ke instance kedua di kota lain sebelum database ini memiliki apa pun yang akan Anda rindukan. Memulihkan backup dasar yang belum pernah Anda uji bukanlah backup, itu adalah harapan, dan halaman lokasi ada sebagian sehingga standby Anda tidak berada di gedung yang sama dengan primer Anda.