PostgreSQL Server configuration

1. parameter names and values

모든 매개변수 이름은 대소문자를 구분하며, boolean, string, integer, floating point 또는 enumerated(enum)의 5가지 타입 중 하나이다. 

2. Parameter Interaction via Comfiguration File

매개변수를 설정하는 가장 기본적인 방법은 일반적으로 데이터 디렉터리에 있는 postgresql.conf 파일을 편집하는 것이다. 데이터베이스 클러스터 디렉터리가 초기화된 경우 기본 사본이 설치된다. 이 파일과 유사한 예시는 다음과 같다.

# Connection Settings listen_addresses = 'localhost' port = 5432 max_connections = 100

 

메인 서버 프로세스가 SIGHUP 신호를 수신할 때마다 환경 설정 파일이 다시 읽히게 된다.  사용자가 단일 서버 프로세스에 직접 신호를 전송할 수도 있다. 일부 매개변수는 서버 시작 시에만 설정 가능하다. 환경 설정 파일의 항목 변경은 서버가 재시작되기 전까지 무시된다. 마찬가지로, 환경 설정 파일에서 잘못된 매개변수 설정도 SIGHUP 처리 중에 무시된다(단, 로그에는 기록된다).

postgresql.conf 외에 postgresql.auto.conf 파일이 포함되어 있으며, ALTER SYSTEM 명령을 통해 설정되는 내용에 대한 설정이 포함되어 있으므로, 해당 파일을 변경해서는 안된다.

3. Parameter Interaction via SQL

• SHOW 명령으로 모든 매개변수의 현재 값을 확인할 수 있다. 해당 함수는 current_setting(setting_name text)이다.

• SET 명령으로는 세션에 로컬로 설정할 수 있는 이 매개변수의 현재 값을 수정할 수 있다. 다른 세션에는 영향을 미치지 않는다. 해당 함수는 set_config(setting_name, new_value, is_local)이다.

• pg_setting은 설정 값을 확인할 수 있는 시스템 뷰 이다.

 

다음은 performance 와 관련된 parameter 에 대한 목록이다.

Connection Setting

• max_connections

  데이터베이스 서버로의 동시 연결 최대 수를 결정한다. 동시 연결로 인한 성능 저하를 막기 위해 연결 수를 제한한다.

  Type	integer
  Default Value	100
  Recommend Value	서비스에서 사용할 최대 접속 개수보다 20%정도의 여분을 가지도록 설정.
  Setting	postgresql.conf 파일에 설정. server start 시에 적용됨.

Resource

• shared_buffers

  데이터베이스 서버가 공유 메모리 buffer 용으로 사용하는 메모리 양을 설정한다. 일부 부하 작업에서는 shared_buffer에 대한 설정이 클수록 효과적이지만 시스템 효율을 위해 RAM의 40% 이상을 할당하는 것은 좋지 않다.

  Type	integer
  Default Value	128 MB
  Recommend Value	일반적으로 RAM의 1/4 또는 effective_cache_size의 3/4 로 설정.
  Setting	postgresql.conf 파일에 설정. server start 시에 적용됨.

• work_mem

  임시 디스크 파일을 쓰기 전에 내부 정렬 명령 및 hash 테이블에서 사용되는 메모리 양을 지정한다. 실행 중인 몇 개의 session 은 동시에 실행 될 수도 있으므로, 사용된 총 메모리는 work_mem의 배수가 될 수 있다. 

  Type	integer
  Default Value	4 MB
  Recommmend Value	최적의 값은 OLTP, OLAP 및 DW에 따라 크게 다를 수 있음.
  Setting	postgresql.conf 파일에 설정. server start 시에 적용됨.

• maintenance_work_mem
  

  VACUUM, CREATE INDEX 및 ALTER TABLE ADD FOREIGN KEY 같은 유지 보수 명령에서 사용되는 최대 메모리 양을 지정한다. work_mem 보다 설정 값을 크게 하는 것이 안전하다. 설정 값이 클 수록  vacuuming 및 데이터베이스 dump 복구 성능이 개선 될 수 있다. 

  Type	integer
  Default Value	64 MB
  Recommend Value	work_mem 값보다 크게 설정. 대형 데이터베이스의 경우 256MB에서 1GB 정도로 값을 설정.
  Setting	postgresql.conf 파일에 설정. server start 시에 적용됨.

Write Ahead Log

• synchronous_commit
  

  클라이언트에게 "success"를 반환하기 전, 디스크에 WAL 값이 쓰여질 때까지 transaction commit을 대기 시킬 것인지 여부를 설정하는 변수이다. off로 설정할 경우 클라이언트에게 success를 반환하는 시점과 서버 crash에 대해 transaction 의 안전이 보장되는 시점 사이에 지연이 발생할 수 있다. 하지만 fsync 설정과 달리 이 설정 값을 off로 설정해도 DB 불일치가 발생하진 않는다: OS나 DB crash로 인해 commit된 transaction 일부가 손실 될 수 있지만, DB는 해당 transaction이 정상적으로 종료된 것과 같은 상태이다. 따라서 transaction의 내구성보다 성능이 우선 시 되는 경우에 synchronous_commit을 끄면(off) 유용하다. 

  Type	enum (on, remote_apply, remote_write, local, off)
  Default Value	on
  Recommend Value	off
  Setting	postgresql.conf 파일에 설정 또는 set local synchronous_commit to off/on 수행으로 설정됨.

• max_wal_size

  checkpoint 작업을 자동으로 진행할 wal log 최대 크기를 설정한다. 이 값이 커지면, 서버가 비정상적으로 종료된 뒤 다시 시작하려고 할 때 진행하는 transaction 로그 기반 복구 작업 시간이 길어 진다. 

  Type	integer
  Default Value	1 GB
  Recommend Value
  Setting	postgresql.conf 파일에 설정 또는 alter system set 명령으로 설정 가능.

• min_wal_size

  WAL 조각 파일들은 더 이상 보관할 필요가 없어지면, 지워지는 것이 아니라, 앞으로 사용될 파일로 그 이름을 바꾼다. 이 설정은 이렇게 남겨두는 WAL 최소값을 지정한다. 앞에서 설명한 것처럼 배치 작업 같은 것으로 갑자기 많은 transaction 로그가 쌓이게 되면 이 값 기준으로 다시 적정 수준의 디스크 공간을 사용하게 된다. 

  Type	integer
  Default Value	80 MB
  Recommend Value
  Setting	postgresql.conf 파일에 설정 또는 alter system set 명령으로 설정 가능.

Default

 

Query Planning

• seq_page_cost
  

  planner 가 예상한, 순차적(sequentially-fetched)으로 가져온 디스크 페이지의 처리 비용을 설정한다. 

  Type	floating point
  Default Value	1.0
  Recommend Value	이상적인 값을 결정하는 제대로 정의된(well-defined) 방법은 없다.
  Setting	postgresql.conf 파일에 설정. server start 시에 적용됨.

• random_page_cost

  planner 가 예상한, 비순차적(non-sequentially-fetched)으로 가져온 디스크 페이지의 처리 비용을 설정한다. 이 값을 seq_page_cost에 비례하여 줄이면 시스템이 index scan 쪽으로 치우치게 된다. 양쪽 값을 함께 늘리거나 줄여서 CPU 비용에 비례하여 디스크 I/O 비용의 중요도를 변경할 수 있다

  Type	floating point
  Default Value	4.0
  Recommend Value	이상적인 값을 결정하는 제대로 정의된(well-defined) 방법은 없다.
  Setting	postgresql.conf 파일에 설정. server start 시에 적용됨.

• effective_cache_size
  단일 쿼리에 사용할 수 있는 disk cache의 효율적인 크기에 대한 planner의 가정을 설정한다. 이것은 인덱스를 사용하는 비용 추정에 반영된다. 값이 클수록 index scan이 사용될 가능성이 높다. 값이 작을수록 sequential scan이 사용될 가능성이 높다. 

  Type	floating point
  Default Value	4 GB
  Recommend Value	RAM 값의 50% 이상
  Setting	postgresql.conf 파일에 설정. server start 시에 적용됨.

Automatic Vacuuming

• autovacuum_max_workers

  한 번에 실행할 수 있는 autovacuum 프로세스(autovacuum 제외)의 최대 수를 지정한다. 해당 매개 변수와 함께 multiple_autovacuum_workers를 설정해야 한다.

  Type	integer
  Default Value	3
  Recommend Value	코어의 1/2 보다 큰 값 또는 코어 수와 동일한 값으로 설정
  Setting	postgresql.conf 파일에 설정. server start 시에 적용됨.

• autovacuum_vacuum_scale_factor
  VACUUM의 트리거 여부를 결정할 때 autovacuum_vacuum_threshold에 추가할 테이블 크기의 부분을 지정한다.

  Type	floating point
  Default Value	0.2 (20% of table size)
  Recommend Value	-
  Setting	postgresql.conf 파일에 설정 또는 alter system set 명령으로 설정 가능.