Postgres ajustado corretamente para uma instância EPYC de 64 GB

O que isto constrói

Uma única instância Postgres em um E-8 que usa o hardware que recebeu: oito núcleos Zen 4 dedicados, 64 GB de ECC DDR5 registrada e NVMe Gen4 com proteção contra perda de energia. Fora da caixa, o Postgres é configurado para iniciar em um laptop de 2009. Deixado assim, ele usará cerca de um centésimo da memória desta máquina e se perguntará por que está lento.

Cada número abaixo é derivado, não copiado. Se a sua instância tiver uma quantidade diferente de memória, a aritmética é mostrada para que você possa refazê-la. Isto é escrito para uma carga de trabalho que é majoritariamente transacional com algum relatório, que é o que a maioria das pessoas realmente tem, independentemente do que dizem.

Antes de começar

• Um E-8 ou maior. Abaixo de 64 GB, as proporções ainda valem, mas os números absolutos não, e huge pages deixam de valer a pena.
• Memória ECC, que a linha EPYC tem e a linha Ryzen não tem. Um erro de memória em shared buffers é uma página corrompida gravada de volta no disco sem aviso.
• Debian 13, que empacota o PostgreSQL 17.

1. Instalar e inicializar

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 precisam ser escolhidos no momento da inicialização e custam alguns por cento de throughput. Eles são a diferença entre uma página corrompida ser relatada e uma página corrompida ser servida, então aceite os dois por cento.

2. Memória

Quatro configurações, quatro cálculos:

• shared_buffers em um quarto da RAM: 16GB. Valores maiores costumavam ser desencorajados; em uma máquina dedicada com kernel moderno, um quarto é o ponto de partida bem testado e um terço é defensável se o seu working set for maior.
• effective_cache_size em três quartos da RAM: 48GB. Isso não aloca nada; informa ao planner o quanto o kernel provavelmente tem em cache, e definir muito baixo faz o planner recusar index scans que deveria escolher.
• work_mem por nó de ordenação, não por conexão: com 200 conexões e talvez duas ordenações cada, 32MB é um teto defensável em uma máquina de 64 GB. Aumente por sessão para consultas de relatório, em vez de globalmente.
• maintenance_work_mem para vacuum e construção de índices: 2GB, com autovacuum_work_mem deixado para herdar.

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

3. Huge pages

Dezesseis gigabytes de shared buffers mapeados em páginas de quatro kilobytes significam quatro milhões de entradas de tabela de páginas por backend. Páginas de dois megabytes reduzem isso por um fator de quinhentos, e a vitória aparece como CPU mais baixa sob concorrência, em vez de um número de manchete.

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

Pegue o número que imprimir, adicione dez por cento para margem de segurança e anote:

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

Huge pages explícitas sim, transparent huge pages não. A segunda desfragmenta a memória em momentos imprevisíveis e produz picos de latência que as pessoas passam semanas culpando seu armazenamento.

4. Write-ahead log e checkpoints

Configurações padrão de checkpoint forçam um flush a cada poucos segundos sob carga, o que transforma seu NVMe em uma fila de pequenas gravações 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

Deixe synchronous_commit ligado. Desligá-lo é o maior ganho de throughput disponível e significa reconhecer transações que uma perda de energia pode consumir. Nossas unidades têm write caches com proteção por capacitor, então o fsync é genuinamente barato aqui; compre o throughput em outro lugar honesto.

5. Armazenamento 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 em quatro é um número de disco giratório e é a configuração incorreta única mais comum na natureza. Em NVMe, uma leitura aleatória custa quase exatamente o mesmo que uma sequencial, e dizer o contrário ao planner faz com que ele evite índices em tabelas grandes. A compilação JIT está desativada porque, para consultas transacionais curtas, ela gasta mais tempo compilando do que executando; ative-a por sessão para as analíticas.

6. Paralelismo, autovacuum e conexões

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

As contagens de workers correspondem aos oito núcleos dedicados, porque eles são dedicados. Em um host oversold, esses números seriam uma mentira e você os ajustaria para baixo para a fração de núcleo que realmente foi vendida; esse não é um problema que você tem aqui.

O fator de escala padrão do autovacuum de 0,2 significa que uma tabela de cem milhões de linhas espera vinte milhões de tuplas mortas antes que algo aconteça. Dois por cento em vez de vinte mantém o vacuum rodando com frequência e brevemente, em vez de raramente e catastroficamente.

7. Pooling

Duzentos backends em oito núcleos não é paralelismo, é uma fila com overhead extra de memória. Coloque um pooler na frente:

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

Transaction pooling com trinta e duas conexões de servidor dá quatro por núcleo, que é aproximadamente onde o throughput para de melhorar neste silício. Aplicações que usam recursos de nível de sessão, como advisory locks ou prepared statements entre transações, precisam de session pooling e obterão um número menor.

systemctl restart postgresql pgbouncer

Verifique

Configurações primeiro. Qualquer coisa que não tenha efeito será óbvia aqui, em vez de em três 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 deve estar vários milhares de páginas abaixo de HugePages_Total, o que significa que o Postgres realmente as mapeou. Valores iguais significam que ele voltou para páginas pequenas e huge_pages = try engoliu a falha silenciosamente.

Então meça. Construa um dataset grande o suficiente para ser interessante, mas pequeno o suficiente para caber nos shared buffers:

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

Em um E-8, a execução somente leitura atinge as centenas de milhares de transações por segundo, e a execução de leitura-escrita fica na casa dos milhares médios. Os números precisos dependem do site e do dataset, então o sinal útil é a ordem de magnitude: se o teste somente select retornar dezenas de milhares em vez de centenas de milhares, shared_buffers não teve efeito ou você ainda está testando através de um cache frio na primeira execução.

Finalmente, confirme se a extensão de estatísticas está carregada, porque é a coisa que você realmente usará toda semana depois:

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

Depois

Ative o arquivamento WAL para uma segunda instância em outra cidade antes que este banco de dados tenha algo que você sentiria falta. Restaurar um backup de base que você nunca testou não é um backup, é uma esperança, e a página de localizações existe em parte para que seu standby não esteja no mesmo prédio que seu primário.