64GB EPYC 인스턴스에 맞게 Postgres 튜닝

이 글에서 만드는 것

E-8(/vps/epyc)에서 실행되는 단일 Postgres 인스턴스로, 제공된 하드웨어를 활용합니다: Zen 4 전용 코어 8개, 등록된 ECC DDR5 64GB, 정전 보호 기능이 있는 Gen4 NVMe. 기본 상태의 Postgres는 2009년 노트북에서 시작하도록 구성되어 있습니다. 그대로 두면 이 머신 메모리의 약 1/100만 사용하고 왜 느린지 궁금해할 것입니다.

아래의 모든 숫자는 복사한 것이 아니라 계산에서 나온 것입니다. 인스턴스의 메모리 용량이 다르면, 다시 계산할 수 있도록 산술식을 보여드립니다. 이 글은 대부분 트랜잭션 처리에 일부 리포팅이 있는 워크로드를 기준으로 작성되었으며, 이는 대부분의 사람들이 실제로 가지고 있는 워크로드입니다(무엇이라고 말하든).

시작하기 전에

• E-8 이상. 64GB 미만에서는 비율은 유지되지만 절대 수치는 맞지 않으며, 큰 페이지는 수고할 가치가 없어집니다.
• ECC 메모리: EPYC 라인에는 있고 Ryzen 라인에는 없습니다. shared buffers에서 메모리 오류가 발생하면 경고 없이 손상된 페이지가 디스크에 다시 쓰여집니다.
• Debian 13: PostgreSQL 17을 패키지로 제공합니다.

1. 설치 및 초기화

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

데이터 체크섬은 초기화 시 선택해야 하며 처리량의 몇 퍼센트를 희생합니다. 손상된 페이지가 보고되는 것과 제공되는 것의 차이이므로 2%를 감수하십시오.

2. 메모리

네 가지 설정, 네 가지 계산:

• shared_buffers: RAM의 1/4, 16GB. 더 큰 값은 이전에는 권장되지 않았지만, 최신 커널이 있는 전용 머신에서는 1/4이 잘 테스트된 시작 지점이며 작업 세트가 더 크다면 1/3도 방어 가능합니다.
• effective_cache_size: RAM의 3/4, 48GB. 이것은 아무것도 할당하지 않습니다. 플래너에게 커널이 캐시할 가능성이 얼마나 되는지 알려주며, 너무 낮게 설정하면 플래너가 선택해야 할 인덱스 스캔을 거부하게 됩니다.
• work_mem: 정렬 노드당 (연결당이 아님): 200개의 연결과 각각 대략 두 개의 정렬이 있다면, 64GB 머신에서 32MB가 방어 가능한 상한입니다. 리포팅 쿼리의 경우 전역으로 올리지 말고 세션별로 올리십시오.
• maintenance_work_mem: vacuum 및 인덱스 빌드용: 2GB, autovacuum_work_mem가 이를 상속하도록 둡니다.

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

3. 큰 페이지

16GB의 shared buffers를 4KB 페이지로 매핑하면 백엔드당 페이지 테이블 항목이 4백만 개가 됩니다. 2MB 페이지는 이를 500분의 1로 줄이며, 그 이점은 헤드라인 숫자보다는 동시성 하에서 더 낮은 CPU로 나타납니다.

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

출력된 숫자를 가져와 여유 공간을 위해 10%를 더하고 적어 두십시오:

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

명시적 큰 페이지는 켜고, 투명 큰 페이지는 끄십시오. 투명 큰 페이지는 예측할 수 없는 순간에 메모리 조각 모음을 수행하고 사람들이 몇 주 동안 스토리지 탓을 하는 지연 시간 스파이크를 만듭니다.

4. WAL(Write-ahead log) 및 체크포인트

기본 체크포인트 설정은 부하가 걸리면 몇 초마다 플러시를 강제하여 NVMe를 작은 동기 쓰기 큐로 만듭니다.

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

synchronous_commit은 켜 둡니다. 끄는 것은 얻을 수 있는 가장 큰 처리량 향상이지만 전원 손실 시 트랜잭션이 사라질 수 있음을 인정하는 것입니다. 우리 드라이브는 커패시터가 있는 쓰기 캐시가 있어 fsync가 실제로 저렴합니다. 대신 정직한 곳에서 처리량을 구매하십시오.

5. 스토리지 및 플래너

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를 4로 설정하는 것은 회전 디스크용 숫자이며 현장에서 가장 흔한 단일 잘못된 구성입니다. NVMe에서는 임의 읽기 비용이 순차 읽기와 거의 동일하며, 플래너에게 그렇지 않다고 알리면 큰 테이블에서 인덱스를 회피하게 됩니다. JIT 컴파일은 꺼 둡니다. 짧은 트랜잭션 쿼리의 경우 컴파일하는 시간이 실행하는 시간보다 길기 때문입니다. 분석 쿼리에는 세션별로 켜십시오.

6. 병렬성, autovacuum 및 연결

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

워커 수는 전용 8코어에 맞춥니다. 전용이기 때문입니다. 과잉 판매된 호스트에서는 이 숫자가 거짓이므로 실제로 판매된 코어의 일부에 맞게 낮추어야 합니다. 여기서는 그런 문제가 없습니다.

기본 autovacuum scale factor 0.2는 1억 행 테이블이 2천만 개의 죽은 튜플이 쌓일 때까지 기다린다는 뜻입니다. 2%는 가끔 그리고 재앙적으로 실행하는 대신 자주 그리고 짧게 실행하도록 유지합니다.

7. 풀링

8코어에 200개의 백엔드는 병렬 처리가 아니라 추가 메모리 오버헤드가 있는 큐입니다. 앞에 풀러를 두십시오:

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

트랜잭션 풀링과 서버 연결 32개는 코어당 4개로, 이 실리콘에서 처리량이 더 이상 개선되지 않는 지점입니다. advisory locks 또는 prepared statements와 같은 세션 수준 기능을 트랜잭션 간에 사용하는 애플리케이션은 세션 풀링이 필요하며 더 적은 수를 받게 됩니다.

systemctl restart postgresql pgbouncer

검증

먼저 설정. 적용되지 않은 것이 있다면 3주 후가 아니라 여기서 명확해집니다:

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은 HugePages_Total보다 수천 페이지 낮아야 합니다. 이는 Postgres가 실제로 매핑했다는 뜻입니다. 같으면 작은 페이지로 폴백되었고 huge_pages = try가 조용히 실패를 삼켰다는 뜻입니다.

그런 다음 측정. 흥미로울 만큼 충분히 크고 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

E-8에서 읽기 전용 실행은 초당 수십만 트랜잭션, 읽기-쓰기 실행은 수천 트랜잭션 수준입니다. 정확한 수치는 사이트와 데이터 세트에 따라 다르므로 유용한 신호는 크기 정도입니다. select-only 테스트가 수십만이 아니라 수만을 반환한다면 shared_buffers이 적용되지 않았거나 첫 실행 시 여전히 콜드 캐시로 벤치마킹하고 있는 것입니다.

마지막으로, 통계 확장이 로드되었는지 확인하십시오. 이후 매주 실제로 사용할 것이기 때문입니다:

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

그 후에

이 데이터베이스에 잃어서는 안 될 것이 생기기 전에 다른 도시의 보조 인스턴스로 WAL 아카이빙을 켜십시오. 테스트한 적 없는 기본 백업 복원은 백업이 아니라 희망이며, 위치 페이지는 기본 인스턴스와 같은 건물에 대기 인스턴스를 두지 않기 위한 목적도 있습니다.