Postgres richtig getunt für eine 64-GB-EPYC-Instanz

Was hier aufgebaut wird

Eine einzelne Postgres-Instanz auf einem E-8, die die Hardware nutzt, die ihr gegeben wurde: acht dedizierte Zen-4-Kerne, 64 GB registrierter ECC-DDR5 und Gen4-NVMe mit Verlustschutz. Standardmäßig ist Postgres so konfiguriert, dass es auf einem Laptop von 2009 startet. Wenn man es so lässt, wird es etwa ein Hundertstel des Speichers dieser Maschine verwenden und sich fragen, warum es langsam ist.

Jede Zahl unten ist abgeleitet und nicht kopiert. Wenn Ihre Instanz eine andere Speichermenge hat, wird die Rechnung gezeigt, damit Sie sie neu durchführen können. Dies ist für eine Arbeitslast geschrieben, die überwiegend transaktional mit etwas Reporting ist, was die meisten Menschen tatsächlich haben, egal was sie sagen.

Bevor Sie beginnen

• Ein E-8 oder größer. Unter 64 GB gelten die Verhältnisse weiterhin, aber die absoluten Zahlen nicht, und große Seiten sind den Aufwand nicht mehr wert.
• ECC-Speicher, den die EPYC-Linie hat und die Ryzen-Linie nicht. Ein Speicherfehler in Shared Buffers ist eine korrupte Seite, die ohne Warnung auf die Platte zurückgeschrieben wird.
• Debian 13, das PostgreSQL 17 paketiert.

1. Installieren und initialisieren

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

Datenprüfsummen müssen zur Initialisierungszeit gewählt werden und kosten ein paar Prozent des Durchsatzes. Sie sind der Unterschied zwischen einer gemeldeten korrupten Seite und einer ausgelieferten korrupten Seite, also nehmen Sie die zwei Prozent.

2. Speicher

Vier Einstellungen, vier Berechnungen:

• shared_buffers bei einem Viertel des Arbeitsspeichers: 16 GB. Größere Werte wurden früher abgeraten; auf einer dedizierten Maschine mit modernem Kernel ist ein Viertel der gut getestete Ausgangspunkt und ein Drittel ist vertretbar, wenn Ihre Arbeitsmenge größer ist.
• effective_cache_size bei drei Vierteln des Arbeitsspeichers: 48 GB. Dies weist nichts zu; es teilt dem Planer mit, wie viel der Kernel wahrscheinlich gecacht hat, und wenn man es zu niedrig setzt, lehnt der Planer Index-Scans ab, die er wählen sollte.
• work_mem pro Sortierknoten, nicht pro Verbindung: bei 200 Verbindungen und vielleicht zwei Sortierungen jeweils ist 32 MB eine vertretbare Obergrenze auf einer 64-GB-Maschine. Erhöhen Sie es pro Sitzung für Reporting-Abfragen anstatt global.
• maintenance_work_mem für VACUUM und Index-Erstellung: 2 GB, wobei autovacuum_work_mem übrig bleibt, um es zu erben.

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

3. Huge Pages

Sechzehn Gigabyte Shared Buffers, die in 4-Kilobyte-Seiten abgebildet sind, bedeuten vier Millionen Page-Table-Einträge pro Backend. Zweimbyte-Seiten reduzieren das um den Faktor fünfhundert, und der Gewinn zeigt sich als geringere CPU unter Parallelität und nicht als Schlagzahl.

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

Nehmen Sie die Zahl, die gedruckt wird, fügen Sie zehn Prozent für Headroom hinzu, und schreiben Sie sie auf:

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

Explizite Huge Pages ja, transparente Huge Pages nein. Die zweite defragmentiert den Speicher zu unvorhersehbaren Zeiten und erzeugt Latenzspitzen, für deren Schuldzuweisung an den Speicher Menschen Wochen aufwenden.

4. Write-Ahead-Log und Checkpoints

Standardmäßige Checkpoint-Einstellungen erzwingen unter Last alle paar Sekunden einen Flush, der Ihre NVMe in eine Warteschlange winziger synchroner Schreibvorgänge verwandelt.

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

Lassen Sie synchronous_commit an. Es auszuschalten ist der größte einzelne Durchsatzgewinn, den es gibt, und es bedeutet, Transaktionen zu bestätigen, die ein Stromausfall fressen kann. Unsere Laufwerke haben kondensatorgestützte Schreib-Caches, daher ist das fsync hier wirklich billig; kaufen Sie den Durchsatz woanders ehrlich.

5. Speicher und Planer

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 bei vier ist eine Zahl für rotierende Platten und die häufigste einzelne Fehlkonfiguration in freier Wildbahn. Auf NVMe kostet ein zufälliger Lesevorgang fast genau so viel wie ein sequenzieller, und dem Planer etwas anderes zu sagen, führt dazu, dass er Indizes auf großen Tabellen vermeidet. JIT-Kompilierung ist aus, weil sie bei kurzen transaktionalen Abfragen mehr Zeit mit Kompilieren als mit Ausführen verbringt; schalten Sie sie pro Sitzung für die analytischen ein.

6. Parallelität, Autovacuum und Verbindungen

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

Worker-Anzahlen entsprechen den acht dedizierten Kernen, weil sie dediziert sind. Auf einem überbuchten Host wären diese Zahlen eine Lüge, und Sie würden sie auf den Bruchteil eines Kerns reduzieren, den Sie tatsächlich verkauft bekommen; das ist hier kein Problem.

Der Standard-Autovacuum-Scale-Faktor von 0,2 bedeutet, dass eine Tabelle mit einhundert Millionen Zeilen auf zwanzig Millionen tote Tupel wartet, bevor etwas passiert. Zwei Prozent statt zwanzig halten VACUUM häufig und kurz laufen, anstatt selten und katastrophal.

7. Pooling

Zweihundert Backends auf acht Kernen ist keine Parallelität, sondern eine Warteschlange mit zusätzlichem Speicher-Overhead. Stellen Sie einen Pooler davor:

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

Transaktions-Pooling mit zweiunddreißig Server-Verbindungen gibt Ihnen vier pro Kern, was ungefähr der Punkt ist, an dem der Durchsatz auf diesem Silizium aufhört zu steigen. Anwendungen, die sitzungsbezogene Funktionen wie Advisory Locks oder vorbereitete Anweisungen über Transaktionen hinweg verwenden, benötigen stattdessen Sitzungs-Pooling und erhalten eine kleinere Zahl.

systemctl restart postgresql pgbouncer

Verifizieren

Zuerst Einstellungen. Alles, was nicht wirksam wurde, wird hier offensichtlich, anstatt in drei Wochen:

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 sollte mehrere tausend Seiten unter HugePages_Total liegen, was bedeutet, dass Postgres sie tatsächlich gemappt hat. Gleiche Werte bedeuten, dass es auf kleine Seiten zurückgefallen ist und huge_pages = try den Fehler still geschluckt hat.

Dann messen. Erstellen Sie einen Datensatz, der groß genug ist, um interessant zu sein, aber klein genug, um in Shared Buffers zu 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

Auf einem E-8 landet der reine Lese-Test bei einigen hunderttausend Transaktionen pro Sekunde und der Lese-Schreib-Test bei einigen tausend. Genaue Zahlen hängen vom Standort und vom Datensatz ab, daher ist das nützliche Signal die Größenordnung: Wenn der Select-Only-Test Zehntausende statt Hunderttausende zurückgibt, haben shared_buffers nicht gewirkt oder Sie testen noch durch einen kalten Cache beim ersten Lauf.

Bestätigen Sie schließlich, dass die Statistik-Erweiterung geladen ist, denn das ist das, was Sie danach tatsächlich jede Woche verwenden werden:

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

Danach

Aktivieren Sie WAL-Archivierung auf eine zweite Instanz in einer anderen Stadt, bevor diese Datenbank etwas enthält, das Sie vermissen würden. Die Wiederherstellung eines Basis-Backups, das Sie nie getestet haben, ist kein Backup, sondern eine Hoffnung, und die Standortseite existiert teilweise, damit Ihr Standby nicht im selben Gebäude wie Ihr Primärserver steht.