Postgres 대기 시간을 단축하는 프로덕션 튜닝

postgresql.conf는 기본 상태에서 설치에 실패하지 않고 트래픽을 흡수하지 않도록 설계되었습니다. shared_buffers의 기록 값인 128MB를 사용하면 Postgres가 중요한 리소스를 예약하지 않고도 최소한의 시스템에서 시작할 수 있습니다. 100의 max_connections는 소규모 공유 서버에 적합합니다. 실제 부하에는 둘 다 선택되지 않았습니다.

따라서 문제는 Postgres가 기본적으로 잘못 설정되어 있다는 것이 아니라 사용자에게 맞게 설정되지 않았다는 것입니다. 다음 섹션에서는 가장 일반적인 병목 현상(연결)부터 가장 느리게 나타나는 병목 현상(WAL)까지 가장 효과가 좋은 순서대로 설정을 살펴봅니다.

첫 번째 프로젝트는 기억이 아니라 연결입니다. 각 Postgres 연결은 유휴 상태에서도 RAM 및 컨텍스트 CPU 시간을 소비하는 전용 서버 프로세스를 엽니다. "연결이 너무 많음" 유형 오류를 방지하기 위해 max_connections을 늘리면 문제가 전환됩니다. 특정 수의 동시 활성 연결을 초과하면 CPU 경합으로 인해 가장 빠른 요청을 포함하여 모든 요청의 대기 시간이 저하됩니다.

올바른 접근 방식은 일반적인 순서를 뒤집습니다. 즉, 실제 서버 동시성에서 max_connections를 확장한 다음 PgBouncer와 같은 풀러를 사용하여 애플리케이션 측 동시성을 pool_mode=transaction로 흡수합니다. 풀러는 수백 개의 클라이언트 연결을 실제로 활성화된 소수의 서버 연결로 다중화합니다. 우리의 전용 기사에서는 트랜잭션 모드 메커니즘과 그 제한(준비된 명령문, LISTEN/NOTIFY) 및 구현을 선택하기 위한 PgBouncer, Supavisor 및 PgCat 간의 비교를 자세히 설명합니다.

max_connections는 "postmaster" 컨텍스트 매개변수입니다. 이를 변경하려면 간단한 다시 로드가 아닌 전체 서버를 다시 시작해야 합니다. Aurabase 전용 Postgres 인스턴스(Pro 및 Enterprise 요금제, 프로젝트당 하나의 CNPG 클러스터)에서 이 설정은 기본값으로 유지되지 않고 요금제 계층별로 구성됩니다. 다시 시작은 프로덕션에서 반복하는 사소한 작업이 아니므로 고정된 값이 아닌 단계적으로 이 선택을 정당화합니다. 제한 사항이 있는 고유한 값을 선택하는 공식은 별도의 문서인 size max_connections의 주제입니다.

4가지 메모리 설정은 다른 모든 설정을 합친 것보다 더 큽니다: shared_buffers, effective_cache_size, work_mem 및 maintenance_work_mem. 처음 세 개는 Postgres가 디스크로 반환하기 전에 메모리에 보관하는 데이터의 양을 결정합니다. 네 번째는 VACUUM 또는 인덱스 생성 속도를 결정합니다.

shared_buffers는 모든 연결이 공유하는 내부 캐시를 설정합니다. PostgreSQL 프로젝트에서 일반적으로 문서화한 벤치마크는 전용 데이터베이스 서버에서 사용 가능한 RAM의 약 25%입니다. 그 이상에서는 이득이 줄어들고 운영 체제의 디스크 캐시가 대신하게 됩니다. effective_cache_size는 아무것도 할당하지 않습니다. 이는 쿼리 플래너에 제공되는 캐시(Postgres 및 OS 결합)에 사용할 수 있는 총 메모리의 추정치입니다. 크기를 줄이면 스케줄러가 순차 스캔쪽으로 푸시되는 반면 인덱스는 대부분 캐시됩니다. 현재 벤치마크는 RAM의 50~75% 사이입니다.

work_mem는 가장 일반적인 트랩입니다. 이는 전역 제한이 아닙니다. 쿼리의 각 정렬 또는 해시 작업은 자체 공유를 사용할 수 있으며 여러 조인이 포함된 쿼리는 자체 공유를 여러 번 예약할 수 있습니다. 높은 max_connections과 결합된 너무 관대한 값은 개별적으로 수행된 각 요청이 합리적으로 보이더라도 동시 로드 시 서버 RAM을 소모할 수 있습니다. 반대로 maintenance_work_mem는 훨씬 더 관대하게 유지될 수 있습니다. 서로 동시에 거의 발생하지 않는 유지 관리 작업(VACUUM, CREATE INDEX)에만 적용됩니다.

shared_buffers만 재부팅이 필요합니다. 격리된 세션을 포함하여 나머지 3개는 핫 리로드됩니다. SET work_mem = '64MB';는 전역 설정에 영향을 주지 않고 단일 탐욕 요청 기간 동안입니다.

최대 로드 중에 "리소스를 확보"하기 위해 일시적으로라도 프로덕션에서 자동 진공을 비활성화하지 마십시오. Postgres는 MVCC를 사용합니다. 각각의 UPDATE와 DELETE는 진공만 복구할 수 있는 데드라인을 남깁니다. 그렇지 않으면 테이블이 부풀어 오르고 인덱스가 저하되며 실행 계획이 점차 악화되어 늦을 때까지 오류가 표시되지 않습니다.

autovacuum_vacuum_scale_factor(트리거 전 데드 행 20%)의 기본값은 쓰기 집약적인 수백만 행 테이블이 아닌 작은 테이블에 적합합니다. 1,000만 행의 테이블에서 이 20%는 첫 번째 전달 전에 누적된 200만 개의 데드 행을 나타냅니다. 전체 데이터베이스의 전체 값을 변경하는 대신 테이블별로 이 임계값을 낮추십시오.

-- 전체 테이블이 아닌 쓰기 작업이 많은 테이블의 임계값을 낮추세요.
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

Aurabase Studio에 통합된 Advisor는 기본 키나 색인화되지 않은 외래 키가 없는 테이블과 동일한 방식으로 각 프로젝트 분석에서 이 구성을 확인합니다. 이는 너무 늦게 발견된 조용한 성능 저하라기보다는 명시적인 신호입니다.

프로덕션 환경에서 대기 시간 문제의 대부분은 CPU나 RAM에서 발생하지 않습니다. 인덱스가 없거나 제대로 선택되지 않은 경우 발생합니다. postgresql.conf의 단일 매개변수를 터치하기 전에 문제의 쿼리에 대한 EXPLAIN (ANALYZE, BUFFERS)이 가장 신뢰할 수 있는 진단으로 남아 있습니다. 리소스를 추가하기 전에 Postgres 요청 대기 시간을 훨씬 줄여줍니다.

-- 구성을 터치하기 전에 느린 쿼리를 진단하세요.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;

Index Scan이 예상되는 수백만 행 테이블의 Seq Scan은 거의 항상 인덱스 문제를 나타냅니다. 세 가지 원인이 가장 자주 발생합니다. 누락된 인덱스, 기존 인덱스와 호환되지 않는 열 유형 또는 ANALYZE없이 대량으로 가져온 후 사용되지 않는 통계입니다. RAM을 추가하거나 work_mem을 늘리면 소량의 데이터에서 이 증상이 숨겨지는 경우가 있습니다. 테이블이 커지자마자 문제가 다시 나타납니다.

이러한 요청을 하나씩 검색하지 않고 찾기 위해 pg_stat_statements는 모든 서버 요청의 실행 통계를 집계합니다. 일반적인 함정: 확장 ​​프로그램은 다시 시작해야 하는 "postmaster" 컨텍스트 매개변수인 shared_preload_libraries에 먼저 나열되어야 합니다. 이 단계가 없으면 CREATE EXTENSION pg_stat_statements;는 자동으로 성공하지만 아무것도 수집하지 않습니다.

# 완전히 다시 시작해야 합니다("postmaster" 매개변수).
shared_preload_libraries = 'pg_stat_statements'

-- 서버가 다시 시작되면 각 데이터베이스에서 다음을 감사합니다.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

이는 이 확장이 없을 때 Aurabase 백엔드가 반환하는 오류입니다. "느린 쿼리 없음"과 혼동될 수 있는 자동 빈 목록이 아닌 명시적인 메시지입니다. Studio Advisor는 더 나아가 평균 시간이 150ms를 초과하는 요청을 경고로, 500ms를 초과하는 요청을 중요로 자동 분류합니다. 이러한 임계값은 동일한 pg_stat_statements통계를 기반으로 합니다.

체크포인트는 Postgres가 이전 페이지 이후 메모리에서 수정된 모든 페이지를 디스크에 쓰도록 강제합니다. 기본적으로 이 글은 너무 짧은 기간에 초점을 맞출 수 있습니다. 그 결과 애플리케이션 측의 디스크 대기 시간이 눈에 띄게 급증하며, 이는 특정 요청과 관련되기 어려운 일종의 주기적인 속도 저하입니다.

checkpoint_completion_target는 두 체크포인트 사이의 간격에 걸쳐 이 쓰기의 확산을 제어합니다. 이전 체크리스트에서 누락된 세부 정보: 2021년에 출시된 PostgreSQL 14는 기본값을 0.5에서 0.9로 변경했습니다. 따라서 전용 Aurabase 테넌트 클러스터와 같은 PostgreSQL 16 인스턴스에서는 이 설정이 기본적으로 이미 정확합니다. 수동으로 조정하는 것은 14 이전 버전에서만 의미가 있습니다. 조정에 영향을 미치는 다른 버전 변경 사항은 PostgreSQL 16 대 17 대 18 비교를 참조하세요.

max_wal_size는 동일한 방향으로 작동합니다. 값이 너무 낮으면 checkpoint_timeout에 아직 도달하지 않은 경우에도 예상보다 더 자주 체크포인트가 트리거됩니다. 이를 늘리면 재생할 WAL이 더 많아 충돌 후 복구 시간이 길어지는 대신 체크포인트 빈도가 줄어듭니다. 보편적인 가치가 아닌, 가용성에 대한 허용 정도에 따라 결정되는 절충안입니다.

이 체크리스트는 생산에 들어가기 전에 한 번 확인하는 일회성 감사가 아닙니다. 볼륨이 두 배로 증가하거나 트래픽이 세 배로 증가하면 시작 시 선택한 벤치마크가 더 이상 사용되지 않게 되며 종종 명시적인 오류 없이 p95 대기 시간이 점진적으로 저하됩니다.

세 가지 신호는 지속적인 모니터링이 필요합니다. pg_stat_statements는 시간이 지남에 따라 성능이 저하되는 요청을 식별합니다. pg_stat_activity는 차단되거나 비정상적으로 오래 실행되는 쿼리를 보고하고 max_connections에 대한 활성 연결의 비율은 애플리케이션 측 오류가 발생하기 전에 포화 상태를 예상합니다.

Studio의 관찰 가능성 탭은 타사 도구를 설치할 필요 없이 모든 Aurabase 프로젝트에 대한 이 기반의 일부를 다룹니다. 느린 요청을 나열하고, PID를 통해 활성 요청을 취소하거나 종료할 수 있으며, 사용량이 80%를 초과하면 경고가 발생하는 풀 포화 표시기를 표시합니다. 자체 호스팅 인스턴스에서는 pg_stat_statements가 활성화되고 외부 모니터링 도구가 연결된 상태에서 이와 동일한 모니터링이 수동으로 구축됩니다.

가장 효과가 좋은 순서대로 8가지 설정을 터치하기 전에 알아야 할 사항이 포함되어 있습니다.

Aurabase Studio Advisor가 모니터링하는 150ms 및 80% 풀 포화도와 같이 여기에 인용된 임계값은 보편적인 진실이 아니라 코드 검증된 시작점입니다. 귀하의 실제 혐의가 유일한 최종 판단권을 갖습니다. 일반 규칙을 따르지 않고 max_connections의 크기를 정확하게 지정하려면 관련 기사에서 공식과 한계를 자세히 설명합니다.

체크리스트를 처음 적용하면 체계적으로 나오는 세 가지 질문.