[PostgreSQL] 성능 관련 Parameter Tunning(1)

 

Memory 관련 parameter

• shared_buffer

 
PostgreSQL은 자체 버퍼를 사용하며 커널 버퍼 IO도 사용합니다. 이는 데이터가 메모리에 두 번 저장된다는 것을 의미하며, 먼저 Postgres에 저장됩니다. SQL 버퍼 다음으로 커널 버퍼입니다. 다른 데이터베이스와 달리 PostgreSQL은 직접 IO를 제공하지 않습니다. 이를 이중 버퍼링이라고 합니다. PostgreSQL은 버퍼를 shared_buffer라고 하며 대부분의 운영 체제에서 가장 효과적인 조정 가능 매개 변수입니다. 이 매개 변수는 PostgreSQL은에서 사용할 전용 메모리의 양을 설정합니다 캐시에 대한 파라미터로 디스크의 엑세스를 줄여 성능을 확보할 수 있는 파라미터 입니다.
 
shared_buffer의 기본값은 매우 낮게 설정되어 있으므로 큰 이점을 얻을 수 없습니다. 특정 시스템과 운영 체제가 더 높은 값을 지원하지 않기 때문에 이 값이 낮습니다. 그러나 대부분의 최신 시스템에서는 최적의 성능을 위해 이 값을 늘려야 합니다.
 
권장 값은 총 시스템 RAM의 25%입니다. 경우에 따라 25% 이상의 설정으로 양호한 성능을 얻을 수 있으므로 더 낮은 값과 더 높은 값을 시도해야 합니다. 구성은 실제로 시스템과 작업 데이터 세트에 따라 달라집니다. 작업 중인 데이터 세트가 RAM에 쉽게 들어갈 수 있는 경우, 전체 데이터베이스를 포함하도록 shared_buffer 값을 늘려 전체 작업 데이터 세트가 캐시에 상주할 수 있도록 할 수 있습니다. 그렇긴 해도, 당신은 Postgre를 위해 모든 RAM을 사용하고 싶진 않을 겁니다. 그리고 PostgreSQL은 캐싱(이중 버퍼링)을 위해 운영 체제에 크게 의존합니다. 따라서 shared_buffers에 RAM의 40% 이상을 할당할 경우, 더 작은 값을 할당하는 경우에 비해 더 좋은 성능을 발휘할 가능성이 낮습니다.
 
운영 환경에서는 shared_buffer 값이 크면 성능이 매우 우수하지만 항상 벤치마크를 통해 적절한 균형을 찾아야 합니다.
 

• Default : 128MB
• 산정 방법 : 서버 메모리의 1/4~1/2
• 설정 주의 : 장시간에 걸쳐 대량의 데이터 또는 변경된 데이터 쓰기 프로세스를 실행하기 위해 shared_buffers를 더 크게 설정하면 checkpoint_segments(10미만 버전),

                       max_wal_size(10이상) 에서도 그에 맞게 설정을 증가시켜야 합니다.
               * PostgreSQL 10부터 checkpoint_segments가 max_wal_size로 대체되었습니다.
 
 
shared_buffer 확인
 

testdb=# SHOW shared_buffers;

shared_buffers

----------------

128MB0000000000000

(1 row)

• wal_buffers

 
PostgreSQL은 WAL(Write Ahead Log) 레코드를 버퍼에 기록한 다음 이러한 버퍼를 디스크에 플러시합니다. wal_buffer는 wal 로그를 메모리에 저장할 수 있는 크기를 지정한다.
Default : -1 ( 4MB) shared_buffers에 따라 자동적으로 설정된다. , shared_buffers가 128MB 면 4MB로 설정된다. ( shared_buffers / 32 )
* PostgreSQL 10부터 shared_buffers 크기에 따라 자동적으로 설정이 되기 때문에 설정해 줄 필요가 없어졌습니다.
 

• work_mem

 
이 파라미터는 sort, merge, join 에 사용는 메모리 사용량을 의미합니다. 만약 당신이 복잡한 정렬을 해야 한다면 좋은 결과를 얻기 위해 work_mem의 값을 늘려야 한다. 메모리 내 정렬은 디스크로 유출되는 정렬보다 훨씬 빠릅니다. 이 매개 변수는 사용자 정렬 작업당이므로 매우 높은 값을 설정하면 배포 환경에 메모리 병목 현상이 발생할 수 있습니다. 따라서 정렬 작업을 실행하려는 사용자가 많은 경우 시스템은 모든 사용자에게 (work_mem * 총 정렬 작업 개수)  만큼의 메모리 크기를 할당합니다. 이 매개 변수를 전체적으로 설정하면 메모리 사용량이 매우 높을 수 있습니다. 따라서 세션 수준에서 이를 수정하는 것이 좋습니다.
 

• Default : 4MB
• 산정 방법
  • 방법 1 : ( 시스템 전체 메모리) / ( max_connections * 16 )
  • 방법 2 : ( OS cache memory / max_connections ) * 0.5

testdb=# SET work_mem TO "2MB";
testdb=# 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)

초기 쿼리의 정렬 노드의 추정 비용은 514431.86이다. 비용은 임의의 계산 단위이다. 위 쿼리의 경우 work_mem은 2MB밖에 없습니다. 테스트를 위해 256MB로 늘리고 비용에 영향이 있는지 확인해 보겠습니다.
 

testdb=# SET work_mem TO "256MB";
testdb=# 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은 Vacuum, Create Index, Alter Table ADD Foreign key와 같은 유지 관리 작업에 사용되는 메모리 설정입니다. 기본값은 64MB입니다. 큰 값을 설정하면 VACUM, RESTORE, CREATE INDEX, ADD Foreign KEY, ALTER TABLE 등의 작업에 도움이 됩니다.
 

• Default : 64MB
• 산정 방법
  • 서버 메모리의 1/16 설정
  • 대형 데이터베이스의 경우 256MB에서 1GB 정도 값을 설정

postgres=# CHECKPOINT;
postgres=# SET maintenance_work_mem to '10MB';

postgres=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 170091.371 ms (02:50.091)

postgres=# CHECKPOINT;
postgres=# set maintenance_work_mem to '256MB';

postgres=# 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로 단축됩니다.

Disk 기록 관련 파라미터

• synchronous_commit

이는 클라이언트에 성공 상태를 반환하기 전에 디스크에 WAL이 기록될 때까지 대기하는 커밋을 시행하는 데 사용됩니다. 이는 성능과 신뢰성 사이의 균형입니다. 애플리케이션이 안정성보다 성능이 더 중요하도록 설계된 경우 synchronous_commit을 해제하십시오. 즉, 성공 상태와 보장된 디스크 쓰기 사이에 시간 차이가 발생합니다. 서버 충돌의 경우 클라이언트가 커밋 시 성공 메시지를 수신했음에도 데이터가 손실될 수 있습니다. 이 경우 트랜잭션은 WAL 파일이 플러시될 때까지 기다리지 않기 때문에 매우 빠르게 커밋되지만 신뢰성은 저하됩니다.
 

• Default : on

 

• checkpoint_timeout, checkpoint_completion_target

PostgreSQL은 WAL에 변경 사항을 기록합니다. 체크포인트 프로세스는 데이터를 데이터 파일로 플러시합니다. 이 활동은 CHECKPOINT가 발생할 때 수행됩니다. 이는 비용이 많이 드는 작업이며 엄청난 양의 IO를 유발할 수 있습니다. 이 전체 프로세스에는 값비싼 디스크 읽기/쓰기 작업이 수반됩니다. 사용자는 필요할 때마다 CHECKPOINT를 발행하거나 checkpoint_timeout 및 checkpoint_completion_target 매개 변수를 통해 자동화할 수 있습니다.
 
checkpoint_timeout 매개 변수는 WAL 체크포인트 사이의 시간을 설정하는 데 사용됩니다. 이 값을 너무 낮게 설정하면 Disk에 더 많은 데이터가 기록되므로 충돌 복구 시간이 단축되지만 모든 체크포인트가 귀중한 시스템 리소스를 사용하게 되므로 성능도 저하됩니다.
 
  checkpoint_completion_target은 체크포인트 완료를 위한 체크포인트 사이의 시간 비율입니다. 대량의 페이지 쓰기로 I/O 시스템이 플러딩되는 것을 방지하기 위해 체크포인트 동안 더티 버퍼 쓰기는 일정 기간 동안 분산됩니다. 지정된 checkpoint_timeout 초의 일부가 경과하거나 max_wal_size가 초과되기 전 중 더 빠른 시점에 체크포인트가 완료되도록 I/O 속도가 조정됩니다. 체크포인트의 빈도가 높으면 성능에 영향을 줄 수 있습니다. 원활한 체크포인트를 위해 checkpoint_timeout은 낮은 값이어야 합니다. 그렇지 않으면 OS는 비율이 충족될 때까지 모든 더티 페이지를 축적한 다음 큰 플러시를 수행합니다.
 

• Default
• checkpoint_timeout : 5min
• checkpoint_completion_target : 0.5

• fsync

이 매개 변수가 설정되어 있으면 PostgreSQL 서버는 fsync() 시스템 호출 또는 이와 동등한 다양한 메소드를 실행하여 업데이트가 실제로 디스크에 기록되도록 합니다. 이를 통해 운영 체제 또는 하드웨어 충돌 후 데이터베이스 클러스터가 일관된 상태로 복구 될 수 있습니다.
 
fsync= on - PostgreSQL서버는 fsync()시스템 콜을 통해서 변경분을 디스크에 물리적으로 바로 씁니다. 이는 데이터베이스클러스터가 OS나 하드웨어 장애시 consistent한 상태로 복구가 가능함을 보장합니다.
fsync= off - OS가 알아서 메모리에 있는 것을 디스크로 내려쓰게 됩니다. 성능상 이득을 볼수는 있겠지만, 전원장애나 system crash로가 발생했을때 복구가 불가능할 수도 있습니다. 외부 데이터로 전체 데이터베이스를 손쉽게 재생성할 수 있는 경우에만 fsync를 해제하는 것이 바람직합니다.
 

• Default : on
• 설정 주의만약 off하기로 했다면, full_page_writes도 off하는 것을 고려하도록 합니다.

 

• full_page_writes
• full_page_writes = on

• PostgreSQL 서버는 checkpoint 이후의 각 디스크 페이지를 처음 수정하는 도중에 해당 페이지의 전체 내용을 WAL에 기록합니다.
• 운영 체제 충돌 시 진행 중인 페이지 쓰기가 부분적으로만 완료되어 디스크 상의 페이지에 옛날 데이터와 새 데이터가 공존할 수 있기 때문에 필요합니다.
• 전체 페이지 이미지를 저장하면 페이지의 올바른 복구가 보장되지만 WAL에 기록해야 하는 데이터량의 증가를 감수해야 합니다.

• full_page_writes = off

• 정상적인 운영 속도가 빨라지지만 시스템 장애 발생 시 손상된 데이터가 복구 불가능하게 되거나 데이터 손상이 드러나지 않을 수 있습니다.

• Default : on

Database 접속 세션 수 관련

• max_connections

데이터베이스 서버에 대한 최대 동시 연결 수를 결정합니다.

• Default : 100

Optimizer 관련 파라미터

• random_page_cost

PostgreSQL 옵티마이저가 디스크에서 임의 페이지를 읽는 비용을 추정하고 인덱스 또는 순차 스캔의 사용을 결정할 수 있습니다. 값이 높을수록 순차 스캔이 더 많이 사용됩니다. 값이 낮을 수록 인덱스를 사용할 가능성이 높아집니다.
 

• Default : 4
• 설정 주의

 

• effective_cache_size

effective_cache_size는 단일 쿼리에 대해 쿼리당 데이터 캐싱을 위한 총 메모리 양을 결정합니다. 다시 말해 디스크 캐시의 효율적인 크기 (캐시할 수 있는 메모리 양) 이것은 정확하게 할당된 메모리나 캐시 크기가 아닌 지침일 뿐이다. 실제 메모리를 할당하지는 않지만 커널에서 사용 가능한 캐시의 양을 옵티마이저에 알려준다. 이 값이 너무 낮게 설정된 경우 쿼리 플래너는 일부 인덱스가 유용하더라도 사용하지 않기로 결정할 수 있습니다. 따라서 큰 값을 설정하는 것이 항상 유익합니다.
 

• Default : 4GB
• 산정 방법
• 설정 주의

 

• cpu_tuple_cost

쿼리 중 각 행을 처리하는 플래너의 추정 비용을 설정합니다. 기본값은 0.01입니다.

• Default : 0.01

 

• cpu_index_tuple_cost

인덱스 스캔 중 각 인덱스 항목을 처리하는 비용의 플래너의 추정치를 설정합니다.

• Default : 0.005

 

• cpu_operator_cost

플래너가 예상한, 쿼리 도중 실행된 각 연산자 또는 함수의 처리 비용을 설정한다.

• Default : 0.0025

 

실행계획  관련 파라미터

• default_statistics_target

Postgresql은 개별 테이블 통계가 필요합니다. ALTER TABLE SET STATISTICS를 통해 설정 하면 테이블 컬럼에 대한 기본 통계 타겟을 설정한다. 큰 값을 설정 하면 ANALYZE를 수행하는 데 필요한 시간이 늘어나지만 플래너 평가 수준을 높일 수 있다.

• Default : 100
• 설정 주의

 

• track_count

데이터베이스 작업에 대한 통계 수집을 활성화 합니다.

• Default : on
• 설정 주의

 
결론
더 나은 성능을 얻기 위해 조정할 수 있는 더 많은 매개 변수가 있지만 여기서 강조한 매개 변수보다 영향이 적습니다. 마지막으로, 모든 매개 변수가 모든 애플리케이션 유형과 관련이 있는 것은 아니라는 점을 항상 염두에 두어야 합니다. 또한 데이터베이스 매개 변수는 응용 프로그램 및 응용 프로그램이 실행되는 OS의 특정 요구에 대해 수행되어야 합니다.