Postgres ajustado correctamente para una instancia EPYC de 64 GB

Qué construye esto

Una instancia única de Postgres en un E-8 que usa el hardware que se le ha dado: ocho núcleos dedicados Zen 4, 64 GB de ECC DDR5 registrada y NVMe Gen4 con protección contra pérdida de energía. De serie, Postgres está configurado para arrancar en un portátil de 2009. Si se deja así, usará aproximadamente una centésima parte de la memoria de esta máquina y se preguntará por qué va lento.

Cada número que aparece a continuación está derivado, no copiado. Si tu instancia tiene una cantidad diferente de memoria, se muestra la aritmética para que puedas recalcularla. Esto está escrito para una carga de trabajo mayormente transaccional con algo de informes, que es lo que la mayoría de la gente tiene realmente, digan lo que digan.

Antes de empezar

• Un E-8 o superior. Por debajo de 64 GB las proporciones se mantienen, pero los números absolutos no, y las páginas grandes dejan de merecer la pena.
• Memoria ECC, que la línea EPYC tiene y la línea Ryzen no. Un error de memoria en los búferes compartidos es una página corrupta escrita de vuelta al disco sin aviso.
• Debian 13, que incluye PostgreSQL 17.

1. Instalación e inicialización

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

Las sumas de verificación de datos deben elegirse en el momento de la inicialización y cuestan un par de puntos porcentuales de rendimiento. Son la diferencia entre que una página corrupta se informe y que se sirva, así que acepta el dos por ciento.

2. Memoria

Cuatro ajustes, cuatro cálculos:

• shared_buffers a un cuarto de la RAM: 16 GB. Antes se desaconsejaban valores mayores; en una máquina dedicada con un kernel moderno, un cuarto es el punto de partida bien probado y un tercio es defendible si tu conjunto de trabajo es mayor.
• effective_cache_size a tres cuartos de la RAM: 48 GB. Esto no asigna nada; le dice al planificador cuánto ha cacheado probablemente el kernel, y ponerlo demasiado bajo hace que el planificador rechace los escaneos de índice que debería elegir.
• work_mem por nodo de ordenación, no por conexión: con 200 conexiones y quizá dos ordenaciones cada una, 32 MB es un techo defendible en una máquina de 64 GB. Súbelo por sesión para consultas de informes en lugar de globalmente.
• maintenance_work_mem para vacuum y construcción de índices: 2 GB, con autovacuum_work_mem dejado para heredarlo.

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

3. Páginas grandes

Dieciséis gigabytes de búferes compartidos mapeados en páginas de cuatro kilobytes significa cuatro millones de entradas de tabla de páginas por backend. Las páginas de dos megabytes reducen eso por un factor de quinientos, y la ventaja se manifiesta como una CPU más baja bajo concurrencia, no como un número llamativo.

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

Toma el número que se imprime, añade un diez por ciento de margen, y anótalo:

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

Páginas grandes explícitas sí, páginas grandes transparentes no. La segunda desfragmenta la memoria en momentos impredecibles y produce picos de latencia que la gente pasa semanas culpando a su almacenamiento.

4. Registro de escritura anticipada y puntos de control

La configuración de punto de control por defecto fuerza un vaciado cada pocos segundos bajo carga, lo que convierte tu NVMe en una cola de pequeñas escrituras síncronas.

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

Deja synchronous_commit activado. Apagarlo es la mayor ganancia de rendimiento disponible y significa reconocer transacciones que una pérdida de energía puede comer. Nuestras unidades tienen cachés de escritura respaldadas por condensadores, por lo que fsync es genuinamente barato aquí; compra el rendimiento en un lugar honesto en su lugar.

5. Almacenamiento y planificador

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 en cuatro es un número de disco giratorio y es la falta de configuración individual más común en la naturaleza. En NVMe, una lectura aleatoria cuesta casi exactamente lo mismo que una secuencial, y decirle al planificador lo contrario hace que evite índices en tablas grandes. La compilación JIT está desactivada porque para consultas transaccionales cortas gasta más tiempo compilando que ejecutando; actívala por sesión para las analíticas.

6. Paralelismo, autovacío y conexiones

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

Los números de trabajadores coinciden con los ocho núcleos dedicados, porque son dedicados. En un host sobresuscrito estos números serían una mentira y los ajustarías a la fracción de núcleo que realmente te vendieron; ese no es un problema que tengas aquí.

El factor de escala de autovacío por defecto de 0.2 significa que una tabla de cien millones de filas espera veinte millones de tuplas muertas antes de que ocurra algo. Dos por ciento en lugar de veinte mantiene el vacío funcionando con frecuencia y brevemente en lugar de raramente y catastróficamente.

7. Pooling

Doscientos backends en ocho núcleos no es paralelismo, es una cola con sobrecarga de memoria extra. Pon un pooler delante:

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

El pooling de transacciones con treinta y dos conexiones de servidor te da cuatro por núcleo, que es aproximadamente donde el rendimiento deja de mejorar en este silicio. Las aplicaciones que usan características a nivel de sesión, como bloqueos de advisory o sentencias preparadas a través de transacciones, necesitan pooling de sesiones en su lugar, y obtendrán un número más pequeño.

systemctl restart postgresql pgbouncer

Verifícalo

Ajustes primero. Cualquier cosa que no haya surtido efecto será obvia aquí en lugar de en tres semanas:

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 debería estar varios miles de páginas por debajo de HugePages_Total, lo que significa que Postgres realmente las mapeó. Valores iguales significan que cayó en páginas pequeñas y huge_pages = try se tragó el fallo silenciosamente.

Luego mide. Construye un conjunto de datos lo suficientemente grande como para ser interesante, pero lo suficientemente pequeño como para caber en los búferes compartidos:

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

En un E-8, la ejecución de solo lectura aterriza en los cientos de miles de transacciones por segundo, y la de lectura-escritura en los miles medios. Las cifras precisas dependen del sitio y del conjunto de datos, por lo que la señal útil es el orden de magnitud: si la prueba de solo select devuelve decenas de miles en lugar de cientos de miles, shared_buffers no surtió efecto o todavía estás haciendo benchmark a través de una caché fría en la primera ejecución.

Finalmente, confirma que la extensión de estadísticas está cargada, porque es lo que realmente usarás cada semana después:

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

Después

Activa el archivado de WAL a una segunda instancia en otra ciudad antes de que esta base de datos tenga algo que echarías de menos. Restaurar una copia de seguridad base que nunca has probado no es una copia de seguridad, es una esperanza, y la página de ubicaciones existe en parte para que tu standby no esté en el mismo edificio que tu primario.