Postgres réglé correctement pour une instance EPYC 64 Go

Ce que cela construit

Une instance Postgres unique sur un E-8 qui utilise le matériel qui lui est donné : huit cœurs Zen 4 dédiés, 64 Go de DDR5 ECC registrée, et NVMe Gen4 avec protection contre les pertes de courant. Par défaut, Postgres est configuré pour démarrer sur un ordinateur portable de 2009. Si on le laisse ainsi, il utilisera environ un centième de la mémoire de cette machine et se demandera pourquoi il est lent.

Chaque chiffre ci-dessous est dérivé plutôt que copié. Si votre instance a une quantité de mémoire différente, le calcul est montré pour que vous puissiez le refaire. Ceci est écrit pour une charge de travail principalement transactionnelle avec un peu de reporting, ce que la plupart des gens ont en réalité, quoi qu'ils en disent.

Avant de commencer

• Un E-8 ou plus grand. En dessous de 64 Go, les ratios tiennent toujours, mais les nombres absolus ne tiennent pas, et les grandes pages ne valent plus la peine.
• Mémoire ECC, que la gamme EPYC possède et que la gamme Ryzen n'a pas. Une erreur mémoire dans les tampons partagés est une page corrompue réécrite sur le disque sans avertissement.
• Debian 13, qui inclut PostgreSQL 17.

1. Installation et initialisation

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

Les sommes de contrôle des données doivent être choisies au moment de l'initialisation et coûtent quelques pour cent du débit. Elles font la différence entre une page corrompue signalée et une page corrompue servie, alors prenez les deux pour cent.

2. Mémoire

Quatre paramètres, quatre calculs :

• shared_buffers à un quart de la RAM : 16 Go. Des valeurs plus grandes étaient autrefois déconseillées ; sur une machine dédiée avec un noyau moderne, un quart est le point de départ bien testé et un tiers est défendable si votre jeu de travail est plus grand.
• effective_cache_size à trois quarts de la RAM : 48 Go. Cela n'alloue rien ; cela indique au planificateur combien le noyau a probablement mis en cache, et le régler trop bas fait refuser au planificateur des analyses d'index qu'il devrait choisir.
• work_mem par nœud de tri, pas par connexion : avec 200 connexions et peut-être deux tris chacune, 32 Mo est un plafond défendable sur une machine de 64 Go. Augmentez-le par session pour les requêtes de rapport plutôt que globalement.
• maintenance_work_mem pour le vide et les constructions d'index : 2 Go, avec autovacuum_work_mem laissé pour en hériter.

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

3. Grandes pages

Seize gigaoctets de tampons partagés mappés en pages de quatre kilo-octets signifie quatre millions d'entrées de table de pages par processus backend. Des pages de deux méga-octets réduisent cela d'un facteur cinq cents, et le gain se manifeste par une CPU plus faible sous concurrence plutôt que par un chiffre en titre.

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

Prenez le nombre qui s'affiche, ajoutez dix pour cent de marge, et notez-le :

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

Grandes pages explicites oui, grandes pages transparentes non. La seconde défragmente la mémoire à des moments imprévisibles et produit des pics de latence que les gens passent des semaines à attribuer à leur stockage.

4. Journal de journalisation et points de contrôle

Les paramètres de point de contrôle par défaut forcent un vidage toutes les quelques secondes sous charge, ce qui transforme votre NVMe en une file d'écritures synchrones minuscules.

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

Laissez synchronous_commit activé. Le désactiver est le plus grand gain de débit disponible et cela signifie accuser réception de transactions qu'une perte de courant peut avaler. Nos disques ont des caches d'écriture à condensateurs, donc le fsync est vraiment bon marché ici ; achetez le débit ailleurs honnêtement.

5. Stockage et planificateur

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 à quatre est un chiffre de disque rotatif et c'est la mauvaise configuration unique la plus courante en production. Sur NVMe, une lecture aléatoire coûte presque exactement ce que coûte une lecture séquentielle, et dire le contraire au planificateur lui fait éviter les index sur les grandes tables. La compilation JIT est désactivée car pour les requêtes transactionnelles courtes, elle passe plus de temps à compiler qu'à exécuter ; activez-la par session pour les requêtes analytiques.

6. Parallélisme, autovacuum et connexions

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

Les nombres de workers correspondent aux huit cœurs dédiés, car ils sont dédiés. Sur un hôte sur-vendu, ces nombres seraient un mensonge et vous les réduiriez à la fraction de cœur qui vous a été réellement vendue ; ce n'est pas un problème que vous avez ici.

Le facteur d'échelle par défaut d'autovacuum de 0,2 signifie qu'une table de cent millions de lignes attend vingt millions de tuples morts avant que quoi que ce soit ne se produise. Deux pour cent au lieu de vingt maintient le vacuum fréquent et bref plutôt que rare et catastrophique.

7. Pooling

Deux cents processus backend sur huit cœurs n'est pas du parallélisme, c'est une file d'attente avec un surcoût mémoire. Mettez un pooler devant :

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

Le pooling de transactions avec trente-deux connexions serveur vous donne quatre par cœur, ce qui est approximativement là où le débit cesse de s'améliorer sur ce silicium. Les applications qui utilisent des fonctionnalités au niveau de la session telles que les verrous advisory ou les instructions préparées sur plusieurs transactions ont besoin de pooling de session à la place, et obtiendront un nombre plus petit.

systemctl restart postgresql pgbouncer

Vérifier

D'abord les paramètres. Tout ce qui n'a pas pris effet sera évident ici plutôt que dans trois semaines :

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 devrait être plusieurs milliers de pages en dessous de HugePages_Total, ce qui signifie que Postgres les a réellement mappées. Des valeurs égales signifient qu'il est retombé sur les petites pages et que huge_pages = try a avalé l'échec silencieusement.

Ensuite, mesurez. Construisez un ensemble de données assez grand pour être intéressant mais assez petit pour tenir dans les tampons partagés :

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

Sur un E-8, l'exécution en lecture seule atteint des centaines de milliers de transactions par seconde, et l'exécution en lecture-écriture des milliers. Les chiffres précis dépendent du site et de l'ensemble de données, donc le signal utile est l'ordre de grandeur : si le test de sélection seule renvoie des dizaines de milliers plutôt que des centaines de milliers, shared_buffers n'a pas pris effet ou vous benchmarkez encore à travers un cache froid lors de la première exécution.

Enfin, confirmez que l'extension de statistiques est chargée, car c'est ce que vous utiliserez réellement chaque semaine par la suite :

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

Ensuite

Activez l'archivage WAL vers une deuxième instance dans une autre ville avant que cette base de données ne contienne quoi que ce soit qui vous manquerait. Restaurer une sauvegarde de base que vous n'avez jamais testée n'est pas une sauvegarde, c'est un espoir, et la page des emplacements existe en partie pour que votre standby ne soit pas dans le même bâtiment que votre primaire.