Postgres correct afgesteld voor een 64 GB EPYC-instantie

Wat dit bouwt

Een enkele Postgres-instantie op een E-8 die de hardware gebruikt die hij meekreeg: acht dedicated Zen 4-cores, 64 GB registered ECC DDR5 en Gen4 NVMe met power-loss protection. Uit de doos is Postgres geconfigureerd om te starten op een laptop uit 2009. Zo gelaten gebruikt het ongeveer een honderdste van het geheugen van deze machine en vraagt het zich af waarom het traag is.

Elk getal hieronder is afgeleid, niet gekopieerd. Als jouw instantie een andere hoeveelheid geheugen heeft, staat de rekenkunde erbij zodat je het opnieuw kunt doen. Dit is geschreven voor een workload die vooral transactioneel is met wat reporting, wat de meeste mensen daadwerkelijk hebben, ongeacht wat ze zeggen.

Voordat je begint

• Een E-8 of groter. Onder de 64 GB gelden de verhoudingen nog steeds, maar de absolute getallen niet, en huge pages worden de moeite niet meer waard.
• ECC-geheugen, dat de EPYC-lijn heeft en de Ryzen-lijn niet. Een geheugenfout in shared buffers is een beschadigde pagina die zonder waarschuwing terug naar schijf wordt geschreven.
• Debian 13, dat PostgreSQL 17 meebrengt.

1. Installeren en initialiseren

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 moeten bij initialisatie worden gekozen en kosten een paar procent van de doorvoer. Ze zijn het verschil tussen een beschadigde pagina die wordt gerapporteerd en een beschadigde pagina die wordt geserveerd, dus neem de twee procent.

2. Geheugen

Vier instellingen, vier berekeningen:

• shared_buffers op een kwart van het RAM: 16GB. Grotere waarden werden vroeger afgeraden; op een dedicated machine met een moderne kernel is een kwart het goed geteste startpunt en een derde is verdedigbaar als je werkset groter is.
• effective_cache_size op driekwart van het RAM: 48GB. Dit wijst niets toe; het vertelt de planner hoeveel de kernel waarschijnlijk heeft gecached, en als je het te laag zet, weigert de planner indexscans die het zou moeten kiezen.
• work_mem per sort-node, niet per verbinding: met 200 verbindingen en misschien twee sorts per verbinding is 32MB een verdedigbaar maximum op een 64 GB-machine. Verhoog het per sessie voor reporting-queries in plaats van globaal.
• maintenance_work_mem voor vacuum en index-builds: 2GB, met autovacuum_work_mem om het te erven.

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

3. Huge pages

Zestien gigabyte aan shared buffers in vier-kilobyte-pagina's betekent vier miljoen page table entries per backend. Twee-megabyte-pagina's verminderen dat met een factor vijfhonderd, en de winst toont zich als lagere CPU onder concurrency, niet als een headline-getal.

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

Neem het getal dat wordt geprint, tel tien procent op voor headroom en schrijf het op:

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

Expliciete huge pages ja, transparante huge pages nee. De tweede defragmenteert geheugen op onvoorspelbare momenten en produceert latentiepieken waar mensen weken de schuld van geven aan hun opslag.

4. Write-ahead log en checkpoints

Standaard checkpoint-instellingen dwingen onder belasting elke paar seconden een flush af, wat je NVMe verandert in een wachtrij van kleine synchrone writes.

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

Laat synchronous_commit aan. Het uitzetten is de grootste doorvoerwinst die beschikbaar is, en het betekent dat je transacties bevestigt die een stroomuitval kan opeten. Onze schijven hebben capacitor-backed write caches, dus de fsync is hier echt goedkoop; koop de doorvoer ergens eerlijk in plaats daarvan.

5. Opslag en 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 op vier is een draaiende-schijf-getal en het is de meest voorkomende enkele misconfiguratie in het wild. Op NVMe kost een willekeurige lees bijna precies hetzelfde als een sequentiële, en de planner anders vertellen zorgt ervoor dat het indexen op grote tabellen vermijdt. JIT-compilatie staat uit omdat het voor korte transactionele queries meer tijd besteedt aan compileren dan aan uitvoeren; zet het per sessie aan voor de analytische queries.

6. Parallelisme, autovacuum en verbindingen

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

Workeraantallen matchen de acht dedicated cores, omdat ze dedicated zijn. Op een oversold host zouden deze getallen een leugen zijn en zou je ze terugschroeven naar welk deel van een core je ook echt verkocht kreeg; dat is een probleem dat jij hier niet hebt.

De standaard autovacuum scale factor van 0.2 betekent dat een tabel met honderd miljoen rijen wacht op twintig miljoen dode tuples voordat er iets gebeurt. Twee procent in plaats van twintig houdt vacuum vaak en kort draaiend in plaats van zelden en catastrofaal.

7. Pooling

Tweehonderd backends op acht cores is geen parallelisme, het is een wachtrij met extra geheugenoverhead. Zet een pooler ervoor:

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

Transactiepooling met tweeëndertig serververbindingen geeft je vier per core, wat ongeveer het punt is waar de doorvoer op dit silicium stopt met verbeteren. Applicaties die sessie-level functies gebruiken zoals advisory locks of prepared statements over transacties heen, hebben sessiepooling nodig en krijgen een lager getal.

systemctl restart postgresql pgbouncer

Verifieer het

Instellingen eerst. Alles wat niet is ingegaan, zal hier duidelijk zijn in plaats van over drie weken:

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 moet enkele duizenden pagina's onder HugePages_Total liggen, wat betekent dat Postgres ze daadwerkelijk heeft gemapt. Gelijke waarden betekenen dat het terugviel op kleine pagina's en huge_pages = try de fout stil heeft doorgeslikt.

Meet dan. Bouw een dataset die groot genoeg is om interessant te zijn, maar klein genoeg om in shared buffers te passen:

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

Op een E-8 komt de read-only run uit op lage honderdduizenden transacties per seconde, en de read-write run op midden duizenden. Precieze cijfers hangen af van de site en de dataset, dus het nuttige signaal is de orde van grootte: als de select-only test tienduizenden oplevert in plaats van honderdduizenden, is shared_buffers niet ingegaan of benchmark je nog steeds door een koude cache bij de eerste run.

Bevestig ten slotte dat de statistiekenextensie is geladen, want dat is het ding dat je daarna elke week echt zult gebruiken:

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

Daarna

Zet WAL-archivering aan naar een tweede instantie in een andere stad voordat deze database iets bevat dat je zou missen. Het terugzetten van een base backup die je nooit hebt getest is geen backup, het is een hoop, en de locatiepagina bestaat deels zodat je standby niet in hetzelfde gebouw staat als je primary.