개발관련/(과거)메모

PostgreSQL 서버 중요 설정 정보

동팡 2021. 1. 18. 21:31

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

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

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

max_connections

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

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, 로그 파일 덮어쓰기 설정

  

정리 필요

 

참고 자료

 

저작자표시 비영리