Postgres odpowiednio wyregulowany dla instancji EPYC z 64 GB RAM

Co to buduje

Pojedyncza instancja Postgresa na E-8, która wykorzystuje otrzymany sprzęt: osiem dedykowanych rdzeni Zen 4, 64 GB pamięci ECC DDR5 z rejestracją oraz Gen4 NVMe z ochroną przed utratą zasilania. Po wyjęciu z pudełka Postgres jest skonfigurowany tak, aby działał na laptopie z 2009 roku. Pozostawiony w tym stanie będzie używał około jednej setnej pamięci tego serwera i będzie się zastanawiał, dlaczego działa wolno.

Każda liczba poniżej jest wyprowadzona, a nie skopiowana. Jeśli twoja instancja ma inną ilość pamięci, pokazano arytmetykę, abyś mógł ją przeliczyć. Tekst jest napisany dla obciążenia głównie transakcyjnego z odrobiną raportów, co jest tym, co większość ludzi faktycznie ma, niezależnie od tego, co mówią.

Zanim zaczniesz

• E-8 lub większy. Poniżej 64 GB proporcje nadal obowiązują, ale liczby bezwzględne nie, a duże strony przestają być warte zachodu.
• Pamięć ECC, którą ma linia EPYC, a linia Ryzen nie ma. Błąd pamięci w wspólnych buforach to uszkodzona strona zapisana z powrotem na dysk bez ostrzeżenia.
• Debian 13, który zawiera PostgreSQL 17.

1. Instalacja i inicjalizacja

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

Sumy kontrolne danych muszą być wybrane w czasie inicjalizacji i kosztują kilka procent przepustowości. To różnica między zgłoszeniem uszkodzonej strony a jej serwowaniem, więc warto zapłacić te dwa procent.

2. Pamięć

Cztery ustawienia, cztery obliczenia:

• shared_buffers na ćwierć pamięci RAM: 16GB. Większe wartości były kiedyś odradzane; na dedykowanym serwerze z nowoczesnym jądrem ćwierć to dobrze przetestowany punkt startowy, a jedna trzecia jest do obrony, jeśli twój zbiór roboczy jest większy.
• effective_cache_size na trzy czwarte pamięci RAM: 48GB. To niczego nie alokuje; mówi planiście, ile jądro prawdopodobnie ma w cache i ustawienie tego zbyt nisko sprawia, że planista odrzuca skany indeksów, które powinien wybierać.
• work_mem na węzeł sortowania, nie na połączenie: przy 200 połączeniach i być może dwóch sortowaniach każde, 32MB to do obrony górna granica na maszynie 64 GB. Podnieś to dla sesji zapytań raportowych, a nie globalnie.
• maintenance_work_mem dla vacuum i budowy indeksów: 2GB, z autovacuum_work_mem pozostawionym do dziedziczenia.

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

3. Duże strony

Szesnaście gigabajtów wspólnych buforów odwzorowanych w stronach czterokilobajtowych oznacza cztery miliony wpisów tablicy stron na proces zaplecza. Strony dwumegabajtowe zmniejszają to o współczynnik pięćset, a zysk objawia się jako niższe zużycie CPU przy współbieżności, a nie jako efektowna liczba.

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

Weź liczbę, która się wydrukuje, dodaj dziesięć procent zapasu i zapisz ją:

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

Jawne duże strony tak, przezroczyste duże strony nie. Te drugie defragmentują pamięć w nieprzewidywalnych momentach i powodują skoki opóźnień, które ludzie tygodniami obwiniają o swój dysk.

4. Dziennik zapisu i punkty kontrolne

Domyślne ustawienia punktów kontrolnych wymuszają flush co kilka sekund pod obciążeniem, co zamienia twoje NVMe w kolejkę małych synchronicznych zapisów.

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

Zostaw synchronous_commit włączone. Wyłączenie go to pojedynczy największy zysk przepustowości dostępny, ale oznacza potwierdzanie transakcji, które mogą zostać utracone po zaniku zasilania. Nasze dyski mają kondensatorowe podtrzymanie zapisu, więc fsync jest tutaj naprawdę tani; kup przepustowość gdzie indziej, uczciwie.

5. Pamięć masowa i planista

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 ustawione na cztery to wartość dla dysków talerzowych i jest to najczęstsza pojedyncza błędna konfiguracja spotykana w praktyce. Na NVMe losowy odczyt kosztuje prawie tyle samo co sekwencyjny, a powiedzenie planiście inaczej sprawia, że unika on indeksów na dużych tabelach. Kompilacja JIT jest wyłączona, ponieważ w przypadku krótkich zapytań transakcyjnych spędza więcej czasu na kompilacji niż na wykonywaniu; włącz ją dla sesji z zapytaniami analitycznymi.

6. Równoległość, autovacuum i połączenia

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

Liczby workerów odpowiadają ośmiu dedykowanym rdzeniom, ponieważ są dedykowane. Na hostingu z przepełnieniem te liczby byłyby kłamstwem i należałoby je zmniejszyć do ułamka rdzenia, który faktycznie kupiłeś; to nie jest problem, który tu masz.

Domyślny współczynnik skali autovacuum wynoszący 0,2 oznacza, że tabela ze stu milionami wierszy czeka na dwadzieścia milionów martwych krotek, zanim cokolwiek się stanie. Dwa procent zamiast dwudziestu utrzymuje vacuum działające często i krótko, a nie rzadko i katastrofalnie.

7. Pooling

Dwieście procesów zaplecza na ośmiu rdzeniach to nie równoległość, lecz kolejka z dodatkowym narzutem pamięci. Postaw pooler z przodu:

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

Pooling transakcyjny z trzydziestoma dwoma połączeniami serwerowymi daje cztery na rdzeń, co jest mniej więcej punktem, w którym przepustowość przestaje się poprawiać na tym krzemie. Aplikacje korzystające z funkcji na poziomie sesji, takich jak blokady doradcze czy przygotowane instrukcje między transakcjami, wymagają poolingu sesji i otrzymają mniejszą liczbę.

systemctl restart postgresql pgbouncer

Weryfikacja

Najpierw ustawienia. Wszystko, co nie zadziałało, będzie tu oczywiste, a nie za trzy tygodnie:

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 powinno być o kilka tysięcy stron poniżej HugePages_Total, co oznacza, że Postgres faktycznie je odwzorował. Równe wartości oznaczają, że nastąpił powrót do małych stron i huge_pages = try połknęło błąd po cichu.

Następnie zmierz. Zbuduj zestaw danych na tyle duży, aby był interesujący, ale na tyle mały, aby zmieścił się w wspólnych buforach:

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

Na E-8 przebieg tylko do odczytu osiąga niskie setki tysięcy transakcji na sekundę, a przebieg z zapisem do odczytu – kilka tysięcy. Dokładne liczby zależą od lokalizacji i zestawu danych, więc użytecznym sygnałem jest rząd wielkości: jeśli test tylko do odczytu zwraca dziesiątki tysięcy, a nie setki tysięcy, shared_buffers nie zadziałało lub wciąż testujesz na zimnym cache w pierwszym przebiegu.

Na koniec potwierdź, że rozszerzenie statystyk jest załadowane, bo to ono będzie ci służyć co tydzień później:

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

Po zakończeniu

Włącz archiwizację WAL do drugiej instancji w innym mieście, zanim ta baza danych będzie miała cokolwiek, co mogłoby cię zaboleć. Przywracanie kopii zapasowej, której nigdy nie testowałeś, to nie kopia zapasowa, lecz nadzieja, a strona z lokalizacjami istnieje częściowo po to, aby twój standby nie znajdował się w tym samym budynku co primary.