PostgreSQL에서 데이터베이스 매개변수 및 구성을 조정하는 방법에 대한 종합 가이드
스왑닐 수야완시
10월 11, 2024
PostgreSQL은 리소스가 부족한 환경과 다양한 다른 애플리케이션과 공유되는 환경에서도 효율적으로 실행할 수 있는 매우 다재다능한 데이터베이스 시스템입니다. 다양한 환경에서 제대로 실행되도록 하기 위해 기본 구성은 매우 보수적으로 되어 있으며 고성능 프로덕션 데이터베이스에는 적합하지 않습니다. 여기에 지리공간 데이터베이스는 사용 패턴이 다르고 데이터가 비지리공간 데이터베이스보다 훨씬 적은 수의 훨씬 큰 레코드로 구성되는 경향이 있다는 사실을 더하면 기본 구성이 우리의 목적에 완전히 적합하지 않다는 것을 알 수 있습니다.
모두가 빠른 데이터베이스를 원한다는 점에는 동의할 수 있을 것입니다. 문제는 어떤 측면에서 빠르냐는 것입니다.
데이터베이스의 관점에서 “빠름”에는 적어도 두 가지 방향이 있습니다:
- 초당 트랜잭션 수
- 처리량 또는 데이터 처리량
이 두 가지는 서로 연관되어 있지만 확실히 동일하지는 않습니다. 두 가지 모두 I/O 측면에서 완전히 다른 요구 사항을 가지고 있습니다. 일반적으로 I/O는 어떤 대가를 치르더라도 피하고 싶을 것입니다. I/O는 메모리, 다양한 수준의 CPU 캐시 또는 CPU 레지스터에 대한 데이터 액세스에 비해 항상 느리기 때문입니다. 경험상 모든 계층은 약 1:1000의 액세스 속도를 느리게 합니다.
초당 많은 수의 트랜잭션에 대한 수요가 많은 시스템의 경우, 가능한 한 많은 동시 IO가 필요합니다. 처리량이 많은 시스템의 경우, 초당 최대한 많은 바이트를 전송할 수 있는 IO 하위 시스템이 필요합니다.
따라서 가능한 한 많은 데이터를 CPU 가까이에, 예를 들어 RAM에 저장해야 합니다. 적어도 허용 가능한 시간 내에 답을 제공하는 데 필요한 데이터 집합인 작업 집합은 적합해야 합니다.
각 데이터베이스 엔진은 특정 메모리 레이아웃을 가지고 있으며 목적에 따라 서로 다른 메모리 영역을 처리합니다.
요약하자면, IO를 피해야 하고 데이터베이스가 효율적으로 작동할 수 있도록 메모리 레이아웃의 크기를 조정해야 합니다(그리고 다른 모든 작업은 적절한 스키마 설계 측면에서 수행된다고 가정합니다).
이러한 모든 구성 매개변수는 postgresql.conf 데이터베이스 구성 파일에서 편집할 수 있습니다. 이 파일은 일반 텍스트 파일이며 메모장이나 다른 텍스트 편집기를 사용하여 편집할 수 있습니다. 변경 사항은 서버를 다시 시작할 때까지 적용되지 않습니다.
참고: 이 값은 권장 사항일 뿐입니다. 환경마다 다르므로 최적의 구성을 결정하려면 테스트가 필요합니다. 하지만 이 섹션을 통해 좋은 시작을 할 수 있습니다.
데이터베이스 매개변수
다음은 시스템과 워크로드에 따라 최적의 성능을 발휘하도록 조정할 수 있는 몇 가지 매개변수입니다.
shared_buffer
대부분의 운영 체제에서 가장 효과적으로 조정할 수 있는 매개변수는 shared_buffer라고 하는 PostgreSQL 버퍼입니다. 이 매개 변수는 PostgreSQL이 캐시에 사용할 전용 메모리의 양을 설정합니다.
shared_buffer의 기본값은 매우 낮게 설정되어 있어 큰 이점을 얻지 못합니다. 특정 머신과 운영 체제가 더 높은 값을 지원하지 않기 때문에 이 값이 낮게 설정되어 있습니다. 그러나 대부분의 최신 시스템에서는 최적의 성능을 위해 이 값을 늘려야 합니다.
1GB 이상의 RAM이 있는 전용 데이터베이스 서버를 사용하는 경우, 공유버퍼의 적절한 시작 값은 시스템 메모리의 25%입니다. 경우에 따라 최적의 성능을 얻으려면 25% 이상의 설정이 필요하므로 더 낮은 값과 더 높은 값을 시도해 보아야 합니다. Windows 시스템에서 공유버퍼의 유용한 범위는 일반적으로 64MB에서 512MB입니다. 구성은 컴퓨터, 작업 데이터 세트, 컴퓨터의 워크로드에 따라 다릅니다.
프로덕션 환경에서는 공유_버퍼 값이 크면 성능이 매우 좋은 것으로 관찰되지만, 항상 벤치마킹을 통해 적절한 균형을 찾아야 합니다.
Check shared_buffer value :
edb=# Show shared_buffers;
shared_buffers
----------------
128MBwal_buffers
PostgreSQL은 WAL(미리 쓰기 로그) 레코드를 버퍼에 쓴 다음 이 버퍼를 디스크로 플러시합니다. wal_buffers로 정의되는 버퍼의 기본 크기는 16MB이지만 동시 연결이 많은 경우 값이 클수록 성능이 향상될 수 있습니다.
effective_cache_size
effective_cache_size 매개변수는 디스크 캐싱에 사용할 수 있는 메모리의 추정치를 제공합니다. 이는 정확한 할당 메모리나 캐시 크기가 아닌 가이드라인일 뿐입니다. 실제 메모리를 할당하지는 않지만 커널에서 사용 가능한 캐시의 양을 최적화 프로그램에 알려줍니다. 이 값은 인덱스 사용 비용의 추정치에 반영되며, 값이 클수록 인덱스 스캔이 사용될 가능성이 높고, 값이 작을수록 순차 스캔이 사용될 가능성이 높습니다. 값이 너무 낮게 설정되면 쿼리 플래너는 일부 인덱스가 도움이 되더라도 사용하지 않기로 결정할 수 있습니다. 또한 다른 테이블에 대한 예상 동시 쿼리 수를 고려해야 하는데, 사용 가능한 공간을 공유해야 하기 때문입니다. 따라서 큰 값을 설정하는 것이 항상 유리합니다.
effective_cache_size의 몇 가지 실제적인 의미를 살펴보겠습니다.
예제
Set effective_cache_size = 1MB
edb=# SET effective_cache_size TO '1 MB';
edb=# explain SELECT * FROM bar ORDER BY id LIMIT 10;
QUERY PLAN
--------------------------------------------------------------------------
Limit (cost=0.00..39.97 rows=10 width=4)
-> Index Only Scan using idx_in on bar (cost=0.00..9992553.14 rows=2500000 width=4)
(2 rows)보시다시피 이 쿼리의 비용은 39.97 페널티 포인트로 추정됩니다.
effective_cache_size를 엄청나게 높은 값으로 변경하면 어떻게 될까요?
SET effective_cache_size = 10000MB
edb=# SET effective_cache_size TO '10000 MB';
edb=# explain SELECT * FROM bar ORDER BY id LIMIT 10;
QUERY PLAN
--------------------------------------------------------------------------
Limit (cost=0.00..0.44 rows=10 width=4)
-> Index Only Scan using idx_in on bar (cost=0.00..109180.31 rows=2500000 width=4)
(2 rows)보시다시피 비용이 급격히 감소합니다. RAM이 1MB밖에 없는 경우 커널이 데이터를 캐시할 것으로 기대하지 않기 때문에 이는 당연하지만, OS에서 데이터를 캐시할 것으로 기대하면 커널 측의 캐시 적중률이 크게 높아질 것으로 예상할 수 있습니다. 랜덤 I/O는 가장 비용이 많이 들며, 이 비용 매개변수를 변경하면 기획자의 생각에 심각한 영향을 미칩니다. 더 복잡한 쿼리에서 비용 추정치가 달라지면 완전히 다른 계획이 나올 수 있다고 상상해 보세요.
work_mem
이 구성은 복잡한 정렬에 사용됩니다. 복잡한 정렬을 수행해야 하는 경우 work_mem의 값을 높이면 좋은 결과를 얻을 수 있습니다. 인메모리 정렬은 디스크에 유출되는 정렬보다 훨씬 빠릅니다. 이 매개변수는 사용자별 정렬 작업이므로 값을 매우 높게 설정하면 배포 환경에 메모리 병목 현상이 발생할 수 있습니다. 따라서 정렬 작업을 실행하려는 사용자가 많은 경우 시스템에서 모든 사용자에 대해 work_mem * 총 정렬 작업을 할당합니다. 이 매개변수를 전역적으로 설정하면 메모리 사용량이 매우 높아질 수 있습니다. 따라서 세션 수준에서 이 매개변수를 수정하는 것이 좋습니다.
예제
Set work_mem = 2MB
edb=# SET work_mem TO "2MB";
edb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
QUERY PLAN
-----------------------------------------------------------------------------------
Gather Merge (cost=509181.84..1706542.14 rows=10000116 width=24)
Workers Planned: 4
-> Sort (cost=508181.79..514431.86 rows=2500029 width=24)
Sort Key: b
-> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 width=24)
(5 rows)
Set work_mem = 256MB초기 쿼리의 정렬 노드의 예상 비용은 514431.86입니다. 비용은 임의의 계산 단위입니다. 위 쿼리의 경우 work_mem이 2MB에 불과합니다. 테스트 목적으로 이를 256MB로 늘려서 비용에 어떤 영향이 있는지 확인해 보겠습니다.
edb=# SET work_mem TO "256MB";
edb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
QUERY PLAN
-----------------------------------------------------------------------------------
Gather Merge (cost=355367.34..1552727.64 rows=10000116 width=24)
Workers Planned: 4
-> Sort (cost=354367.29..360617.36 rows=2500029 width=24)
Sort Key: b
-> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 width=24)쿼리 비용이 514431.86에서 360617.36으로 30% 감소합니다.
maintenance_work_mem
maintenance_work_mem 매개 변수는 유지 관리 작업에 사용되는 메모리 설정입니다. 기본값은 64MB입니다. 값을 크게 설정하면 VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY 및 ALTER TABLE과 같은 작업에 도움이 됩니다.
예제
Set maintenance_work_mem = 10MB
edb=# CHECKPOINT;
edb=# SET maintenance_work_mem to '10MB';
edb=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 170091.371 ms (02:50.091)
Set maintenance_work_mem = 256MB
edb=# CHECKPOINT;
edb=# set maintenance_work_mem to '256MB';
edb=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 111274.903 ms (01:51.275)maintenance_work_mem을 10MB로만 설정한 경우 인덱스 생성 시간은 170091.371ms이지만 maintenance_work_mem 설정을 256MB로 늘리면 111274.903ms로 단축됩니다.
synchronous_commit
클라이언트에 성공 상태를 반환하기 전에 WAL이 디스크에 기록될 때까지 커밋이 대기하도록 강제하는 데 사용됩니다. 이는 성능과 안정성 간의 절충안입니다. 애플리케이션이 안정성보다 성능이 더 중요하도록 설계된 경우 synchronous_commit을 해제해야 합니다. 동기화 커밋을 끄면 성공 상태와 디스크에 대한 쓰기 보장 사이에 시간 간격이 생깁니다. 서버 충돌이 발생하면 클라이언트가 커밋 성공 메시지를 받았음에도 불구하고 데이터가 손실될 수 있습니다. 이 경우 트랜잭션은 WAL 파일이 플러시될 때까지 기다리지 않기 때문에 매우 빠르게 커밋되지만 안정성이 저하됩니다.
max_connections
이 매개변수는 현재 연결의 최대 수를 설정합니다. 한도에 도달하면 더 이상 서버에 연결할 수 없습니다. 모든 연결은 리소스를 사용하므로 너무 높게 설정해서는 안 됩니다. 세션이 오래 실행되는 경우에는 세션이 대부분 짧은 시간 동안 실행되는 경우보다 더 높은 숫자를 사용해야 할 수도 있습니다. 연결 풀링에 대한 구성과 일치하도록 유지하세요.
max_prepared_transactions
준비된 트랜잭션을 사용하는 경우 모든 연결에 준비된 트랜잭션이 하나 이상 있을 수 있도록 이 매개 변수를 max_connections 수와 최소한 같게 설정해야 합니다. 이에 대한 특별한 힌트가 있는지 확인하려면 선호하는 ORM(객체 관계형 매퍼)의 설명서를 참조하세요.
max_worker_processes
PostgreSQL에 대해 독점적으로 공유할 CPU 수로 설정합니다. 데이터베이스 엔진이 사용할 수 있는 백그라운드 프로세스의 수입니다. 이 매개변수를 설정하려면 서버를 다시 시작해야 합니다. 기본값은 8입니다.
대기 서버를 실행하는 경우 이 매개 변수를 마스터 서버와 동일한 값 또는 그 이상으로 설정해야 합니다. 그렇지 않으면 대기 서버에서 쿼리가 허용되지 않습니다.
max_parallel_workers_per_gather
수집 또는 수집 병합 노드가 사용할 수 있는 최대 워커 수입니다. 이 매개변수는 max_worker_processes와 동일하게 설정해야 합니다. 쿼리 실행 중에 Gather 노드에 도달하면 사용자 세션을 구현하는 프로세스는 플래너가 선택한 워커 수와 동일한 수의 백그라운드 워커 프로세스를 요청합니다. 플래너가 사용을 고려하는 백그라운드 워커의 수는 최대_parallel_workers_per_gather 이하로 제한됩니다. 한 번에 존재할 수 있는 백그라운드 워커의 총 수는 max_worker_processes와 max_parallel_workers 모두에 의해 제한됩니다. 따라서 병렬 쿼리가 계획보다 적은 수의 워커로 실행되거나 심지어 워커가 전혀 없는 상태에서도 실행될 수 있습니다. 최적의 계획은 사용 가능한 워커 수에 따라 달라질 수 있으므로 쿼리 성능이 저하될 수 있습니다.
이 문제가 자주 발생하는 경우, 더 많은 워커를 동시에 실행할 수 있도록 max_worker_processes 및 max_parallel_workers를 늘리거나 플래너가 더 적은 수의 워커를 요청하도록 max_parallel_workers_per_gather를 줄이는 것을 고려하세요.
max_parallel_workers
병렬 쿼리에 대한 최대 병렬 작업자 프로세스 수입니다. max_worker_processes와 동일합니다. 기본값은 8입니다.
이 값을 max_worker_processes보다 높게 설정하면 해당 설정에 의해 설정된 작업자 프로세스 풀에서 병렬 작업자를 가져오므로 이 값은 아무런 영향을 미치지 않습니다.
effective_io_concurrency
IO 하위 시스템에서 지원하는 실제 동시 IO 작업의 수입니다. 일반 HDD의 경우 2, SSD의 경우 200, 강력한 SAN을 사용하는 경우 300으로 설정하는 것이 좋습니다.
random_page_cost
이 요소는 기본적으로 순차 액세스에 비해 임의 페이지에 액세스하는 것이 얼마나 더 많은 비용이 드는지(또는 더 적은 비용이 드는지) PostgreSQL 쿼리 플래너에 알려줍니다. SSD나 강력한 SAN의 경우, 이것은 그다지 관련이 없어 보이지만 전통적인 하드 디스크 드라이브 시대에는 중요했습니다. SSD 또는 SAN의 경우 1.1로 시작하고, 일반 디스크의 경우 4로 설정하세요.
min_ and max_wal_size
이 설정은 PostgreSQL의 트랜잭션 로그에 크기 경계를 설정합니다. 기본적으로 체크포인트가 발행될 때까지 기록할 수 있는 데이터의 양이며, 이를 통해 인메모리 데이터를 온디스크 데이터와 동기화합니다.
max_fsm_pages
이 옵션은 여유 공간 맵을 제어하는 데 도움이 됩니다. 테이블에서 무언가가 삭제되면 디스크에서 즉시 제거되지 않습니다. 여유 공간 맵에서 ‘사용 가능’으로 표시될 뿐입니다. 그러면 이 공간은 테이블에 새로 삽입할 때 재사용할 수 있습니다. 설정에서 DELETE 및 INSERT의 비율이 높은 경우 테이블 부풀림을 방지하기 위해 이 값을 늘려야 할 수 있습니다.
본문: Comprehensive Guide on How to Tune Database Parameters and Configuration in PostgreSQL
EDB 영업 기술 문의: 02-501-5113
이메일: salesinquiry@enterprisedb.com
홈페이지 문의하기
