Postgres regolato correttamente per un'istanza EPYC da 64 GB

Cosa si realizza

Una singola istanza Postgres su una E-8 che sfrutta l'hardware che le è stato dato: otto core Zen 4 dedicati, 64 GB di ECC DDR5 registrata e NVMe Gen4 con protezione da perdita di corrente. Di default, Postgres è configurato per girare su un portatile del 2009. Lasciato così, userà circa un centesimo della memoria di questa macchina e si chiederà perché è lento.

Ogni numero qui sotto è derivato, non copiato. Se la tua istanza ha una quantità diversa di memoria, i calcoli sono mostrati così puoi rifarli. Questo è scritto per un carico di lavoro prevalentemente transazionale con un po' di reporting, che è ciò che la maggior parte delle persone ha davvero, indipendentemente da ciò che dicono.

Prima di iniziare

• Una E-8 o più grande. Sotto i 64 GB i rapporti reggono ma i numeri assoluti no, e le pagine grandi smettono di valere la pena.
• Memoria ECC, che la linea EPYC ha e la linea Ryzen no. Un errore di memoria nei buffer condivisi è una pagina corrotta riscritta su disco senza alcun avviso.
• Debian 13, che include PostgreSQL 17.

1. Installazione e inizializzazione

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

I checksum dei dati vanno scelti al momento dell'inizializzazione e costano un paio di punti percentuali di throughput. Sono la differenza tra una pagina corrotta segnalata e una pagina corrotta servita, quindi prendi i due punti percentuali.

2. Memoria

Quattro impostazioni, quattro calcoli:

• shared_buffers a un quarto della RAM: 16GB. Valori più grandi erano scoraggiati; su una macchina dedicata con kernel moderno, un quarto è il punto di partenza ben testato e un terzo è difendibile se il tuo working set è più grande.
• effective_cache_size a tre quarti della RAM: 48GB. Questo non alloca nulla; dice al planner quanto è probabile che il kernel abbia in cache, e impostarlo troppo basso fa sì che il planner rifiuti scan su indici che dovrebbe scegliere.
• work_mem per nodo di sort, non per connessione: con 200 connessioni e forse due sort ciascuna, 32MB è un tetto difendibile su una macchina da 64 GB. Alzalo per sessione per le query di reporting piuttosto che globalmente.
• maintenance_work_mem per vacuum e build di indici: 2GB, con autovacuum_work_mem lasciato per ereditarlo.

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

3. Pagine grandi

Sedici gigabyte di buffer condivisi mappati in pagine da quattro kilobyte significano quattro milioni di voci di tabella delle pagine per backend. Pagine da due megabyte riducono quel numero di un fattore cinquecento, e il vantaggio si manifesta come CPU più bassa sotto concorrenza piuttosto che come numero di punta.

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

Prendi il numero che viene stampato, aggiungi il dieci percento per margine, e scrivilo:

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

Pagine grandi esplicite sì, pagine grandi trasparenti no. La seconda deframmenta la memoria in momenti imprevedibili e produce picchi di latenza che la gente passa settimane a dare la colpa allo storage.

4. Write-ahead log e checkpoint

Le impostazioni predefinite del checkpoint costringono a un flush ogni pochi secondi sotto carico, il che trasforma il tuo NVMe in una coda di piccole scritture sincrone.

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

Lascia synchronous_commit attivo. Spegnerlo è il più grande guadagno di throughput disponibile e significa riconoscere transazioni che una perdita di corrente può mangiare. I nostri drive hanno cache di scrittura con condensatori, quindi il fsync è davvero economico qui; compra il throughput da qualche parte onesta invece.

5. Storage e 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 a quattro è un numero da disco rotante ed è la singola misconfigurazione più comune in giro. Su NVMe una lettura casuale costa quasi esattamente quanto una sequenziale, e dire al planner il contrario gli fa evitare gli indici su tabelle grandi. La compilazione JIT è spenta perché per query transazionali brevi passa più tempo a compilare che a eseguire; attivala per sessione per quelle analitiche.

6. Parallelismo, autovacuum e connessioni

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

I conteggi dei worker corrispondono agli otto core dedicati, perché sono dedicati. Su un host oversold questi numeri sarebbero una bugia e li abbasseresti a qualsiasi frazione di core ti abbiano davvero venduto; questo non è un problema che hai qui.

Il fattore di scala predefinito di autovacuum di 0.2 significa che una tabella con cento milioni di righe aspetta venti milioni di tuple morte prima che qualcosa accada. Due percento invece di venti mantiene il vacuum che gira spesso e brevemente piuttosto che raramente e catastroficamente.

7. Pooling

Duecento backend su otto core non è parallelismo, è una coda con overhead di memoria extra. Metti un pooler davanti:

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

Il pooling a livello di transazione con trentadue connessioni al server ti dà quattro per core, che è più o meno il punto in cui il throughput smette di migliorare su questo silicio. Le applicazioni che usano funzionalità a livello di sessione come advisory lock o prepared statement tra transazioni hanno bisogno di session pooling, e otterranno un numero più piccolo.

systemctl restart postgresql pgbouncer

Verifica

Prima le impostazioni. Qualsiasi cosa che non ha avuto effetto sarà ovvia qui piuttosto che tra tre settimane:

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 dovrebbe essere diverse migliaia di pagine sotto HugePages_Total, il che significa che Postgres le ha effettivamente mappate. Valori uguali significano che è ricaduto su pagine piccole e huge_pages = try ha inghiottito il fallimento in silenzio.

Poi misura. Costruisci un dataset abbastanza grande da essere interessante ma abbastanza piccolo da stare nei buffer condivisi:

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

Su una E-8 l'esecuzione in sola lettura si attesta sulle centinaia di migliaia di transazioni al secondo, e quella in lettura-scrittura sulle migliaia medie. Le cifre precise dipendono dal sito e dal dataset, quindi il segnale utile è l'ordine di grandezza: se il test di sola select restituisce decine di migliaia piuttosto che centinaia di migliaia, shared_buffers non ha avuto effetto o stai ancora facendo benchmark con cache fredda al primo run.

Infine, conferma che l'estensione di statistiche sia caricata, perché è la cosa che userai davvero ogni settimana in seguito:

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

Dopo

Attiva l'archiviazione WAL su una seconda istanza in un'altra città prima che questo database contenga qualcosa che ti mancherebbe. Ripristinare un backup di base che non hai mai testato non è un backup, è una speranza, e la pagina delle locations esiste in parte così che la tua standby non sia nello stesso edificio della tua primaria.