Postgres 메모리 설정을 최대한 활용하는 방법

• Postgres 데이터베이스는 많은 양의 RAM을 사용함. 결과 집합을 만들 때 인덱스 매칭, 테이블에서 관련 행 검색, 튜플 병합/필터링/집계/정렬 등의 단계를 거치며, 이 모든 단계에서 메모리에 의존
• Postgres의 메모리 사용량을 최적화하기 위해서는 사용 가능한 RAM을 최대한 활용하면서도, 다양한 유형의 메모리 할당을 효율적으로 조정하고, OS가 과도한 메모리 사용으로 프로세스를 종료시키는 것을 방지해야 함

Sharing is Caring

• Postgres와 관련된 RAM의 가장 큰 부분은 shared_buffers라고 불리며, 가장 자주 검색되는 모든 테이블과 인덱스의 행을 나타냄. 사용 빈도에 따라 점수를 매기는 휴리스틱에 의해 지원됨
  • shared_buffers는 Postgres 시작 시 할당되는 고정값이며, 예기치 않은 메모리 문제에 기여하지 않음
  • 기본값은 128MB
  • 하지만 OS는 이를 사전에 할당된 메모리로 간주하지 않을 수 있으므로, 인스턴스의 RAM 양까지 매우 높은 값을 지정하면 위험할 수 있음
• 프로덕션 시스템에서 shared_buffers의 가장 일반적인 권장 값은 사용 가능한 RAM의 25%임. 이는 하드웨어에 맞게 조정되므로 대부분의 시스템에 적합한 시작점임
  • 벤치마크 결과, 25% 조언이 일반적으로 충분하지만 데이터베이스 사용 방식에 따라 달라질 수 있음
  • 예를 들어 리포팅 시스템은 복잡한 임시 쿼리로 인해 캐시 적중률이 낮아 오히려 더 낮은 설정에서 약간 더 나은 성능을 보임
• pg_buffercache 확장을 사용하면 공유 버퍼에 할당된 테이블과 인덱스를 정확히 파악할 수 있음. 버퍼의 사용된 페이지 수를 확인하여 shared_buffers 값을 조정 가능
  • 버퍼 캐시가 100% 활용되지 않으면 설정이 너무 높을 수 있으므로 인스턴스 크기나 shared_buffers 값을 줄일 수 있음
  • 100%이고 많은 테이블의 일부만 캐시된 경우, 수익 체감이 있을 때까지 순차적으로 높은 값을 지정하는 것이 유리할 수 있음
• Postgres 16의 새로운 pg_stat_io 뷰도 shared_buffers 조정에 도움이 될 수 있음. 적중률과 클라이언트 백엔드 읽기/쓰기를 확인할 수 있음
  • 읽기 대 쓰기 비율이 1에 가까우면 Postgres가 동일한 페이지를 shared_buffers에서 계속 순환시키고 있음을 나타낼 수 있음. 이런 스레싱을 줄이기 위해 shared_buffers를 늘리는 것이 좋음
• 시스템 RAM의 50%를 넘어가기 시작하면 인스턴스 크기를 늘리는 것을 고려해야 함. Postgres는 사용자 세션 및 관련 쿼리에 대한 메모리도 여전히 필요하기 때문임

Working Memory

• Postgres가 실제로 작업을 수행하는 데 사용하는 메모리의 다른 절반은 work_mem이라는 매개변수로 제어되는 작업 메모리에 해당함
  • 기본값은 4MB이며, 사용자가 쿼리 실행 속도를 높이기 위해 가장 먼저 수정할 수 있는 값 중 하나임
  • 하지만 "메모리 부족" 메시지로 인해 OS가 Postgres를 종료하는 경우 work_mem를 늘리고 싶을 수 있지만, 이는 문제를 악화시킬 뿐임. Postgres가 사용하는 RAM 양을 증가시켜 이러한 종료에 직면할 가능성이 더 높아짐
• 많은 사람들이 "작업 메모리"를 Postgres가 쿼리에서 작업을 수행하는 동안 수행할 수 있는 모든 작업에 할당된 단일 할당으로 해석하지만, 실제로는 그 이상이 될 수 있음
  • 각 단계(노드)는 work_mem의 별도 인스턴스가 할당됨. 예를 들어 work_mem 기본값인 4MB를 사용하는 경우 4개의 노드가 필요한 쿼리는 최대 16MB의 RAM을 소비할 수 있음
  • 보통 바쁜 서버에서 이러한 쿼리가 100개 동시에 실행되는 경우 결과 계산에만 최대 1.6GB의 RAM을 사용할 수 있음. 더 복잡한 쿼리는 쿼리 실행에 필요한 노드 수에 따라 더 많은 RAM이 필요할 수 있음
• EXPLAIN 명령을 사용하여 쿼리의 실행 계획을 확인하면, Postgres가 쿼리를 실행하는 방법과 출력을 생성하는 데 필요한 모든 노드를 보여줌
  • pg_stat_statements 확장과 함께 사용하면 가장 활성화된 쿼리를 분리하고 work_mem로 인한 전체 메모리 사용량을 추정할 수 있음
• work_mem가 너무 낮게 설정되면 RAM에 맞지 않는 행이나 중간 결과는 디스크로 넘치게 되어 훨씬 느려짐
  • pg_stat_database 뷰를 확인하여 디스크에 기록된 모든 임시 파일의 누적 크기와 수를 확인하고, 평균 크기가 적절하면 work_mem를 이 양만큼 늘릴 수 있음
• 세션당 사용 가능한 RAM 양을 대략적으로 알려면 다음 공식을 사용: (전체 RAM의 80% - shared_buffers) / (max_connections)
  • 예를 들어 16GB RAM, 4GB 공유 버퍼, 100개 최대 연결이 있는 경우 세션당 약 88MB를 사용할 수 있음
  • 이 값을 쿼리 계획 노드의 평균 수로 나누어 work_mem에 대한 좋은 설정을 얻을 수 있음

Ongoing Maintenance

• Postgres RAM 사용량에 대한 튜닝 가능한 마지막 부분은 작업 메모리와 유사하지만 특히 유지 관리와 관련이 있으며, maintenance_work_mem라는 유사한 매개변수 이름을 가짐
  • 기본값은 64MB이며, VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY와 같은 작업을 수행하는 데 전용되는 RAM 양을 지정함
• 세션당 하나의 작업으로 제한되고 많은 동시 작업이 발생할 가능성이 낮기 때문에 더 높은 값을 사용하는 것이 충분히 안전한 것으로 간주됨
  • 이러한 유지 관리 작업은 메모리를 매우 많이 사용할 수 있으며 RAM에서 전적으로 작동할 수 있다면 훨씬 더 빠르게 완료될 수 있으므로, 1GB 또는 2GB까지 설정하는 것이 매우 일반적임
• 중요한 주의사항은 나중에 재사용하기 위해 죽은 튜플을 표시하는 Postgres 자동 청소(autovacuum) 프로세스임
  • 자동 청소는 autovacuum_max_workers 제한까지 백그라운드 작업을 시작하며, 각각 maintenance_work_mem의 전체 인스턴스를 사용할 수 있음
  • 대부분의 여유 RAM이 있는 서버는 1GB의 유지 관리 작업 메모리로 안전하지만, RAM이 부족한 경우 더 신중해야 함
  • 특히 자동 청소 작업자를 제한하려면 autovacuum_work_mem 매개변수가 별도로 있음
  • Postgres 자동 청소 작업자는 1GB 이상 사용할 수 없으므로 autovacuum_work_mem을 이 값 이상으로 구성해도 효과가 없음

Session Pooling

• 메모리 소비를 줄이는 가장 쉬운 방법은 잠재적 할당에 논리적 제한을 두는 것임
  • Postgres는 현재 프로세스 기반 엔진이므로 모든 사용자 세션에 스레드가 아닌 물리적 프로세스가 할당됨
  • 따라서 모든 연결에는 특정 RAM 오버헤드가 수반되고 컨텍스트 전환에 기여함
  • 그 결과, 일반적인 권장 사항은 max_connections를 사용 가능한 CPU 스레드 수의 4배 이하로 설정하는 것임. 이는 활성 세션을 CPU 간에 전환하는 데 소요되는 시간을 최소화하고 세션이 총체적으로 소비할 수 있는 RAM 양을 자연스럽게 제한함
• 모든 세션이 쿼리를 실행 중이고 각 노드가 work_mem의 하나의 할당을 나타내는 경우, 이론적인 최대 작업 메모리 사용량은 connections * nodes * work_mem임
  • 쿼리 복잡성을 줄이는 것은 항상 가능한 것은 아니지만, 일반적으로 연결 수를 줄일 수 있음
  • 응용 프로그램이 항상 특정 확장된 양의 세션을 열거나 여러 개별 마이크로서비스가 Postgres에 의존하는 경우 말처럼 쉽지 않을 수 있음
• work_mem * max_connections * 5 공식은 모든 연결이 활성화되어 있다고 가정할 때 기본 쿼리를 처리하기 위해 Postgres 인스턴스가 사용자 세션에 할당할 수 있는 최대 RAM 양에 대한 대략적인 추정치임
  • 서버에 이 값에 충분한 RAM이 없는 경우 요인 중 하나를 줄이거나 RAM을 늘리는 것을 고려해야 함
  • 평균 쿼리당 5개의 노드에 대한 추정은 애플리케이션에 맞지 않을 수 있으므로, 쿼리 실행 계획을 더 잘 파악한 후에 필요에 따라 조정해야 함
• 다음 단계는 PgBouncer와 같은 커넥션 풀러를 도입하는 것임
  • 이는 클라이언트 연결을 데이터베이스에서 분리하고 비용이 많이 드는 Postgres 세션을 클라이언트 간에 재사용함
  • 적절하게 구성되면 수백 명의 클라이언트가 애플리케이션에 영향을 주지 않고 수십 개의 Postgres 연결을 공유할 수 있음
  • PgBouncer가 이런 식으로 1000개 이상의 연결을 40-50개로 다중화하여 프로세스 오버헤드로 인한 전체 메모리 소비량을 크게 줄이는 것으로 확인됨

Reducing Bloat

• 메모리 사용량을 추적하는 데 가장 어려운 측면은 아마도 테이블 팽창(bloat)일 것임
  • Postgres는 저장 시스템에서 데이터를 표현하기 위해 다중 버전 동시성 제어(MVCC)를 사용함
  • 즉, 테이블 행이 수정될 때마다 Postgres는 테이블 어딘가에 행의 다른 복사본을 만들고 새 버전 번호로 표시함
  • Postgres의 VACUUM 프로세스는 오래된 행 버전을 "사용되지 않음" 공간으로 표시하여 새 행 버전을 배치할 수 있도록 함
• Postgres에는 이러한 재사용 가능한 할당을 지속적으로 찾고 테이블이 무제한으로 증가하지 않도록 하는 자동 청소 백그라운드 프로세스가 있음
  • 하지만 때로는 특히 대용량 시스템의 경우 이에 대한 기본 구성으로는 충분하지 않을 수 있으며, 이러한 유지 관리가 뒤처질 수 있음
  • 그 결과 테이블에 살아있는 행보다 죽은 행이 더 많이 채워져 오래된 데이터로 "팽창"된 테이블이 될 수 있음
• 테이블이 극도로 팽창된 경우 공유 버퍼에 미치는 영향을 고려해야 함
  • 각 페이지에 하나의 실행 행과 여러 개의 죽은 행만 포함된 경우 특정 쿼리에 10개의 행이 필요하다면 10개의 페이지를 공유 버퍼로 가져와야 하므로 다른 용도로 사용될 수 있는 많은 메모리를 낭비하게 됨
  • 이러한 행에 대한 수요가 특히 높으면 사용 횟수가 이를 공유 버퍼에 유지하여 캐시 효율성을 훨씬 떨어뜨림
• 테이블 팽창을 추정할 수 있는 쿼리가 인터넷에 많이 떠돌고 있지만, 테이블의 페이지가 어떻게 보이는지 구체적으로 확인하는 유일한 방법은 pgstattuple 확장을 사용하는 것임
• free_percent가 30%보다 크면 자동 청소를 더 적극적으로 수정해야 할 수 있음. 30%보다 훨씬 큰 경우에는 팽창을 완전히 제거하는 것이 좋음
  • 현재 이를 위한 유일한 지원 방법은 VACUUM FULL 명령을 사용하여 테이블을 본질적으로 재구축하는 것임. 이는 모든 실행 행을 새 위치로 재배치하고 이전 팽창된 사본을 버림
  • 이 프로세스는 수행 기간 동안 배타적 액세스 잠금을 할당하므로 거의 모든 경우 일종의 가동 중지 시간이 필요함
• 이에 대한 대안은 Tembo에서 지원하는 pg_repack 확장임
  • 이 명령줄 도구는 배타적 잠금 없이 완전히 온라인 방식으로 팽창을 제거하기 위해 테이블을 재구성할 수 있음
  • 이 도구는 Postgres 코어 외부에 존재하고 테이블 및 인덱스 저장소를 수정하므로 종종 고급 사용으로 간주됨
  • 사용하기 전에 프로덕션이 아닌 환경에서 충분한 테스트를 권장함
• 열 순서를 재구성하여 페이지당 행 수를 최대화하는 열 테트리스를 수행하여 더 나아갈 수 있음
  • 이는 아마도 극단적인 수준의 최적화일 수 있지만, 이런 식으로 테이블을 재구축할 자유가 있는 환경에서는 실행 가능한 전략임

The Balancing Act

• 이러한 모든 매개변수와 리소스를 적절하게 구성하는 것은 예술이자 과학임
  • 공유 버퍼의 실제 사용량을 측정하는 방법과 작업 메모리가 너무 낮은지 확인하는 방법을 살펴봤음
  • 하지만 대부분의 경우처럼 사용 가능한 하드웨어나 예산에 제한이 있다면 어떻게 될까? 이것이 "예술"이 필요한 부분임
• 메모리가 부족한 상황에서는 더 많은 work_mem을 위한 공간을 확보하기 위해 shared_buffers를 약간 줄여야 할 수도 있음. 또는 둘 다 줄여야 할 수도 있음
  • 애플리케이션에 많은 세션 수가 필요한 경우, 동시 세션이 광범위한 RAM 할당을 누적하는 것을 방지하기 위해 work_mem을 줄이거나 연결 풀을 도입하는 것이 더 합리적일 수 있음
  • 모든 것에 충분한 RAM이 있다고 가정하고 과거에 maintenance_work_mem을 늘렸다면 이를 줄이는 것이 더 합리적일 수 있음. 고려해야 할 것이 많음
• 저메모리 인스턴스에서는 위의 권장 사항으로도 충분하지 않을 수 있음. 이러한 상황에서는 메모리 사용을 최대화하고 리소스 고갈을 피하기 위해 다음 작업 순서를 따르는 것이 좋음:
  1. 커넥션 풀러를 추가하고 max_connections를 줄임. 최대 리소스 소비를 줄이는 가장 빠르고 쉬운 방법임
  2. pg_stat_statements에서 보고한 가장 빈번한 쿼리에 EXPLAIN을 사용하여 평균이 아닌 쿼리의 최대 노드 수를 찾음. 그런 다음 work_mem을 (전체 RAM의 80% - shared_buffers) / (max_connections * 최대 계획 노드 수) 이하로 설정함
  3. maintenance_work_mem과 autovacuum_work_mem을 기본값인 64MB로 되돌림. 유지 관리 작업이 너무 느리고 더 많은 RAM을 사용할 수 있는 경우 8MB 단위로 증가시키는 것을 고려
  4. pg_buffercache 확장을 사용하여 shared_buffers에 저장된 테이블의 양을 확인함. 각 테이블과 인덱스를 면밀히 검토하고 데이터 아카이빙, 정보 사용량을 줄이도록 쿼리 수정 등을 통해 이를 줄일 수 있는 방법이 있는지 확인함. 활성 팽창 테이블에서 사용되는 페이지를 압축하기 위해 VACUUM FULL 또는 pg_repack을 포함할 수 있음
  5. pg_buffercache가 shared_buffers가 꽉 차고 활성 페이지를 제거하지 않고는 더 이상 줄일 수 없음을 보여주는 경우 usagecount 열을 사용하여 가장 활성화된 페이지의 우선순위를 정함. 이 열의 값은 1-5이므로, 3-5번 사용된 페이지에 집중하면 성능에 큰 영향을 주지 않고 shared_buffers를 줄일 수 있음
  6. 마지막으로 더 강력한 하드웨어를 프로비저닝함. 데이터베이스에 현재 워크로드에 더 많은 RAM이 필요하고 위의 매개변수를 줄이면 시스템 성능에 너무 크게 악영향을 미칠 경우 일반적으로 업그레이드하는 것이 더 합리적임