PostgreSQL 성능 최적화: 데이터베이스 매개변수 구성 및 튜닝 가이드
스왑닐 수야완시
10월 11, 2024
PostgreSQL은 리소스가 부족하거나 다른 애플리케이션과 시스템 자원을 공유하는 환경에서도 효율적으로 실행할 수 있는 범용 데이터베이스 시스템입니다. 다양한 환경에서 안정적으로 실행할 수 있도록 기본 구성은 비교적 보수적으로 설정되어 있어, 고성능 프로덕션 데이터베이스에서는 워크로드와 시스템 리소스에 맞는 별도의 PostgreSQL 성능 최적화가 필요할 수 있습니다.
특히 지리공간 데이터베이스는 일반적인 비지리공간 데이터베이스와 사용 패턴이 다르고, 상대적으로 적은 수의 큰 레코드로 데이터가 구성되는 경향이 있어 기본 구성이 목적에 완전히 적합하지 않을 수 있습니다.
모두가 빠른 데이터베이스를 원한다는 데에는 이견이 없을 것입니다. 하지만 PostgreSQL 튜닝에서는 단순히 '빠른 데이터베이스'를 목표로 하기보다 어떤 관점에서 빠른 성능이 필요한지를 먼저 파악해야 합니다.
PostgreSQL 성능을 결정하는 두 가지 측면
- 초당 트랜잭션 수
- 처리량 또는 데이터 처리량
이 두 가지는 서로 연관되어 있지만 동일하지는 않습니다. 두 요소는 I/O 측면에서도 서로 다른 요구 사항을 갖습니다. 일반적으로 I/O는 메모리, 다양한 수준의 CPU 캐시 또는 CPU 레지스터에 대한 데이터 액세스보다 항상 느립니다. 경험상 각 계층은 약 1:1000의 액세스 속도 차이를 보입니다.
초당 많은 트랜잭션을 처리해야 하는 시스템에서는 가능한 한 많은 동시 I/O가 필요합니다. 반면 처리량이 중요한 시스템에서는 초당 최대한 많은 바이트를 전송할 수 있는 I/O 하위 시스템이 필요합니다.
따라서 가능한 한 많은 데이터를 CPU 가까이에, 예를 들어 RAM에 저장해야 합니다. 적어도 허용 가능한 시간 내에 결과를 제공하는 데 필요한 데이터 집합인 작업 집합(Working Set)은 메모리에 적합해야 합니다.
각 데이터베이스 엔진은 목적에 따라 서로 다른 메모리 영역을 사용하는 고유한 메모리 레이아웃을 갖습니다.
요약하면 I/O를 가능한 한 줄이고, 데이터베이스가 효율적으로 작동할 수 있도록 메모리 레이아웃의 크기를 적절하게 조정해야 합니다. 여기서는 적절한 스키마 설계 등 다른 기본적인 최적화가 수행되어 있다고 가정합니다.
이러한 구성 매개변수는 postgresql.conf 파일에서 편집할 수 있습니다. 이 파일은 일반 텍스트 파일이므로 메모장과 같은 텍스트 편집기로 수정할 수 있습니다. 단, 변경 사항은 서버를 재시작해야 적용됩니다.
참고: 아래 값은 권장 사항일 뿐입니다. 환경마다 조건이 다르므로 최적의 구성을 결정하려면 테스트가 필요합니다. 다음 설정은 PostgreSQL 데이터베이스 튜닝을 시작하기 위한 참고값으로 활용할 수 있습니다.
PostgreSQL 데이터베이스 주요 성능 매개변수
다음은 시스템과 워크로드에 따라 PostgreSQL 성능을 최적화하기 위해 조정할 수 있는 주요 데이터베이스 매개변수입니다.
shared_buffers
대부분의 운영 체제에서 효과적으로 조정할 수 있는 주요 매개변수 중 하나가 shared_buffers입니다. 이 매개변수는 PostgreSQL이 캐시에 사용할 전용 메모리의 양을 설정합니다.
shared_buffers의 기본값은 매우 낮게 설정되어 있어 큰 이점을 얻지 못합니다. 특정 머신과 운영 체제가 더 높은 값을 지원하지 않기 때문에 이 값이 낮게 설정되어 있습니다. 그러나 대부분의 최신 시스템에서는 최적의 성능을 위해 이 값을 늘려야 합니다.
1GB 이상의 RAM이 장착된 전용 데이터베이스 서버라면, shared_buffers(공유 버퍼)의 시작값으로 시스템 메모리의 25%를 설정하는 것이 적절합니다. Windows 시스템에서 공유 버퍼의 유용한 범위는 일반적으로 64MB에서 512MB입니다. 구성은 컴퓨터, 작업 데이터 세트, 컴퓨터의 워크로드에 따라 다릅니다.
프로덕션 환경에서는 shared_buffers 값이 클 때 성능이 매우 좋은 것으로 관찰되지만, 항상 벤치마킹을 통해 적절한 균형을 찾아야 합니다.
Check shared_buffer value :
edb=# Show shared_buffers;
shared_buffers
----------------
128MBwal_buffers
PostgreSQL은 WAL(미리 쓰기 로그) 레코드를 버퍼에 쓴 다음 이 버퍼를 디스크로 플러시합니다. wal_buffers로 정의되는 버퍼의 기본 크기는 16MB이지만 동시 연결이 많은 경우 값이 클수록 성능이 향상될 수 있습니다.
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 정렬 작업 수만큼 메모리가 소모됩니다. 따라서 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
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
effective_io_concurrency는 I/O 하위 시스템에서 지원하는 실제 동시 I/O 작업의 수입니다. 일반 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의 비율이 높은 경우 테이블 부풀림을 방지하기 위해 이 값을 늘려야 할 수 있습니다.
PostgreSQL 성능 튜닝 FAQ
PostgreSQL 성능 최적화에서 shared_buffers는 어떻게 설정해야 하나요?
shared_buffers는 PostgreSQL이 캐시에 사용할 전용 메모리의 양을 설정합니다. 본문에서는 1GB 이상의 RAM이 장착된 전용 데이터베이스 서버의 시작값으로 시스템 메모리의 25%를 제시합니다. 실제 최적값은 시스템과 워크로드에 따라 달라질 수 있으므로 벤치마킹이 필요합니다.
effective_cache_size는 PostgreSQL 쿼리 성능에 어떤 영향을 주나요?
effective_cache_size는 디스크 캐싱에 사용할 수 있는 메모리의 추정치를 쿼리 플래너에 제공합니다. 실제 메모리를 할당하는 설정은 아니며, 값에 따라 인덱스 스캔과 순차 스캔의 비용 추정 및 실행 계획 선택에 영향을 줄 수 있습니다.
PostgreSQL work_mem을 높이면 성능이 좋아지나요?
복잡한 정렬 작업에서는 work_mem을 높여 인메모리 정렬을 활용하면 성능 개선에 도움이 될 수 있습니다. 다만 사용자별 정렬 작업마다 메모리가 사용되므로 지나치게 높은 값을 설정하면 전체 시스템의 메모리 병목을 유발할 수 있습니다.
maintenance_work_mem은 어떤 작업에 사용되나요?
maintenance_work_mem은 VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY, ALTER TABLE 등의 유지 관리 작업에 사용되는 메모리 설정입니다. 본문의 예제에서는 값을 높였을 때 CREATE INDEX 실행 시간이 단축되는 결과를 보여줍니다.
synchronous_commit을 끄면 PostgreSQL 성능이 향상되나요?
synchronous_commit을 끄면 트랜잭션이 WAL 플러시를 기다리지 않아 더 빠르게 커밋될 수 있습니다. 그러나 서버 충돌 시 클라이언트가 성공 메시지를 받은 트랜잭션도 손실될 가능성이 있어 성능과 안정성 사이의 절충이 필요합니다.
본문: Comprehensive Guide on How to Tune Database Parameters and Configuration in PostgreSQL
EDB 영업 기술 문의: 02-501-5113