PostgreSQL(9.1 기준) 서버 중요 설정(3년 전 자료 업로드)

shared_buffers ( 버퍼 캐시 )

• SQL 쿼리 결과를 블록(메모리)에 저장해주는 공간이다. 캐시 메모리와 비슷하게 생각하면 괜찮다. SQL의 경우, 디스크에서 직접 결과를 얻어올 경우, 디스크 I/O와 디스크의 느린 탐색 속도로 인해 성능 저하가 온다. 그런 저하를 방지하기위한 완충제 역할이 버퍼 캐시이며, 버퍼 캐시에 결과물이 있으면 디스크까지 조회를 안하고 빠른 메모리 조회만 한다.
• 디폴트 값은 32MB이며, 서버 메모리 기준으로 25~40%이다.
• https://www.postgresql.org/docs/9.1/static/runtime-config-resource.html

wal_buffers

• Commit 하기 전, 즉 아직 디스크에 적재되지 않은 데이터(로그) 값들을 보관
• 디폴트 값은 shared_buffers의 1/32 값이지만, 최소 64KB? 32KB? 이상이며, 최대 16MB이다.
• 디폴트 값으로 지정한다.
• https://www.postgresql.org/docs/9.1/static/runtime-config-wal.html

work_mem

• 정렬 distinct 또는 해시/머지 조인 등을 할 때 사용하는 임시 메모리이다. 위의 작업이 지정한 값보다 많은 양의 메모리가 요구될 경우, 리눅스의 SWAP처럼 디스크에 접근하여, 느려지기 때문에 위의 작업이 빈번할 경우, 부하 테스트를 통해 값을 올려주는 것이 좋다.
• 디폴트 값은 1MB이다.
• 대략 ( OS cache memory / connections ) * 0.5 예를 들어, OS cache memory가 28GB이고, 접속량이 20이면 (28/20) *0.5 = 700MB 이다.
• https://www.postgresql.org/docs/9.1/static/runtime-config-resource.html

default_statistics_target

• SQL 쿼리가 실행계획을 만들기 전, 참고하는 통계정보이다. 값이 높을수록 실행 계획의 품질은 올라가지만 ANALYZE의 시간 또한 그만큼 올라간다.
• 디폴트 값은 100이다. (1 ~ 10000)
• 기본 값을 지정해도 무방하다. ( 쿼리 복잡도에 맞춰 부여한다. )

effective_cache_size

• 쿼리 플랜의 캐시 사이즈를 지정한다. 보통 물리 메모리의 50%를 지정한다.
• 디폴트 값은 128MB이다.
• https://www.postgresql.org/docs/9.1/static/runtime-config-query.html
• https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

maintenance_work_mem

• CREATE INDEX, ALTER TABLE ADD FOREIGN KEY’ 그리고 ‘VACUUM’ 작업을 할 때 사용하는 최대 메모리를 지정한다.
• 디폴트 값은 16MB이다(9.4부터 64MB).
• 값은 시스템 메모리의 5%가 적당하며, 512MB 이상 지정한다. 그 이상의 높은 값은 성능 개선이 되질 못한다.
• https://www.postgresql.org/docs/9.1/static/runtime-config-resource.html

checkpoint_segments

• (오라클의 CKPT와 같은? 기능으로 추정)
• 디폴트 값은 3이다.
• 작은 규모의 application이라도 최소 10으로 지정하는 것을 추천한다(좀 더 공부할 필요 있음).
• https://www.postgresql.org/docs/9.1/static/runtime-config-wal.html#GUC-CHECKPOINT-SEGMENTS
• https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

max_connections

• PostgreSQL을 사용하는 유저의 최대 동시 접속 수이다. 보통 Application에서 Connection Pool을 쓸 때 Max Connection Pool이 이에 해당한다. 100 이하 숫자로 지정할 경우, 커널이 지원하지 못할 수 있다.
• 디폴트 값은 100이다.
• 해당 값을 올리기 위해서는 Connection Pool, 현장의 접속 최대 수, 서버의 RAM, 환경 등을 고려하여, 값을 올린다.
• https://www.postgresql.org/docs/9.1/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS

superuser_reserved_connections

• 슈퍼 유저가 접속할 수 있는 자리를 설정한다.
• 예를 들어, max_connections 값이 100일 때 해당 값을 2로 설정하면, 최대 98개의 세션만 접속을 허용한다. 남은 두 자리는 postgres 롤을 갖고 있는 유저만 할당할 수 있는 값이다.
• 2? 정도?

archive_mode

• 데이터베이스의 영속성을 지속시키고 백업/복구에 능하기 위해서는 꼭 on을 해줘야한다. 오라클을 예를 들면, LGWR을 통해 LogFile이 채워진다. LogFile의 경우 휘발하는 데이터이며, LogFile의 데이터는 ARCH를 통해 아카이브 파일에 지속적으로 보관되며, 이를 통해 redo 로그 복구 및 undo 세그먼트 복구를 실시할 수 있다(undo 세그먼트 또한 redo로그에 보관된다.).
• archive_mode를 사용하여, 특정시점 복구(PITR)를 하기 위해서는 wal_level을 최소 archive 또는 hot_standby 이상으로 지정해야 한다(minimal->(archive, hot_standby)->logical?).
• 디폴트는 off이다.
• archive_mode = on
• https://www.postgresql.org/docs/9.1/static/runtime-config-wal.html
• http://postgresql.kr/docs/9.4/continuous-archiving.html

archive_command

• archive_command = 'cp %p /home/postgres/pgsql/archive/%f'
• http://postgresql.kr/docs/

Logging

log_destination

• log_destination = 'stderr', 디폴트는 stderr이기 때문에 설정을 하지 않아도 괜찮다.

log_line_prefix

• 로그 내용을 출력하기 전에 표시할 정보를 나타낸다.
• log_line_prefix = '%t %u@%r/%d(%p)'
• %t: 시간, %u: 사용자 이름, %r: ip, port, %d: 데이터베이스 이름, %p: 프로세스 아이디
• 2018-10-22 09:49:56 KST myname@10.10.10.108(2502)/myname2017(22462)ERROR:  column "pid" does not exist at character 12

logging_collector

• Conf 설정을 통해 logging 하는것을 할지 말지 선택을 한다.
• logging_collector = on

log_directory

• log_directory='pg_log'

log_filename

• Log 파일명 패턴 지정
• log_filename='test_postgresql-%Y-%m-%d_%H%M%S.log'   

log_file_mode

• 로그의 권한을 설정한다. 리눅스의 chmod 755 같은 개념. 리눅스의 경우 1이 시작이지만, 해당 설정은 0부터 시작이다. 아래의 0644는 755와 같은 개념
• log_file_mode = 0644

log_connections, log_disconnections

• 클라이언트의 연결 시도 및 인증 성공 여부에 대해 로깅한다(vice versa).
• on

log_statement

• 어떤 SQL문에 대해 로깅을 할지 지정한다.
• log_statement = ‘none’
• all 할 경우, 답없음(로그량 어마어마)

log_min_error_statement

• 실패된 쿼리에 대해 로깅을 한다.
• log_min_error_statement=’info’

그 외

• log_min_duration_statement=2000 or 2s (2초) : 2초 이상 지속된 sql문에 대해서만 로깅한다.
• log_truncate_on_rotation = false, 로그 파일 덮어쓰기 설정

정리 필요

• stats_temp_directory = '/run/shm'            # 실시간 통계 정보는 공유 메모리로
• RAM 베이스 파일 시스템에 지정할 경우, 물리 I/O를 감소시키며, 성능 개선의 요인이 된다 .절대/상대 경로를 지정할 수 있다.
• https://www.postgresql.org/docs/9.6/static/runtime-config-statistics.html#GUC-STATS-TEMP-DIRECTORY
• 정확하게 판단 및 테스트 필요

참고 자료

• http://linux.systemv.pe.kr/%EC%9D%BD%EA%B8%B0%EC%A0%84%EC%9A%A9-postgresql-%ED%8A%9C%EB%8B%9D/
• http://postgresql.kr/blog/simple_postgresql_conf.html
• http://database.sarang.net/?inc=read&aid=5834&criteria=pgsql&subcrit=tutorials&id=&limit=20&keyword=&page=1
• https://pgtune.leopard.in.ua/#/
• https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server