[PostgreSQL] 주요 파라미터 역할 및 산정방법

Parameter 역할 및 산정방법

 

PostgreSQL 파라미터별 역할에 대해 잘 이해하고 있어야지 데이터베이스 성능 저하 문제가 나왔을 때 문제를 더욱 빠르게 파악하여 운영 간 더 좋은 성능을 낼 수 있도록 DB 성능 튜닝을 할 수 있게 될 것입니다. 운영, 성능관련 등 필수로 설정해줘야하는 일부 대표적인 파라미터들만 추려 작성한 점 참고 부탁드립니다.

 

※ 재기동이 필요한 파라미터는 [restart], 필요없는 파라미터는 [reload]로 표시해두었습니다.

 

* listen_addresses [restart]

PostgreSQL를 처음 설치하면, 보안을 위해 socket 외부 접속이 막혀있습니다.

외부 접속을 허용하려면 이 파라미터의 값을 해당 PostgreSQL이 설치되어있는 서버의 IP로 수정해주면 됩니다.

예) listen_addresses= ‘192.168.0.158’

 

* port [restart]

서버가 수신하는 TCP 포트를 설정합니다. 보안을 위해 포트넘버를 변경하시는 것을 권장합니다.

기본값(Default)은 5432입니다. 최소) 1 ~ 최대) 65535까지 포트넘버를 설정할 수 있습니다.

 

* max_connections [restart]

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

기본값은 100이며, 최소) 1 ~ 최대) 262143 사이 값으로 설정할 수 있습니다.

산정방법 : 각 연결은 shared_buffer 메모리와 비공유 메모리를 사용하므로 시스템 메모리가 부족하지 않게

서버환경을 고려해서 설정해야 합니다.

 

* shared_buffers [restart]

데이터베이스 서버가 공유 메모리 버퍼에 사용하는 메모리양을 설정합니다.

기본값은 128MB이며, 최소) 128KB ~ 최대) 8589934584KB까지 설정 가능합니다.

산정방법 : total system memory * (0.25 ~ 0.5)

 

* temp_buffers [reload]

각 세션에서 사용하는 최대 임시 버퍼 수를 설정합니다. 현재 임시 테이블을 메모리에 보관하는 데만 사용됩니다.

기본값은 8MB이며, 최소) 800KB ~ 최대) 8589934584KB까지 설정 가능합니다.

산정방법 : total system memory * 0.0015

응용프로그램에서 임시 테이블을 많이 사용해야 하는 경우 이 파라미터 값을 늘리는 것이 좋습니다. 단, 세션

별로 할당되는 비공유 메모리이므로 주의하셔야 합니다. 그렇지 않다면 기본값을 권장합니다.

 

* maintenance_work_mem [reload]

Autovacuum, 수동 vacuum, 인덱스 빌드 및 기타 유지 관리에서 사용할 수 있는 양에 대한 제한을 설정합니다.

기본값은 64MB이며, 최소) 1024KB ~ 최대) 2147483647KB까지 설정 가능합니다.

산정방법 : total system memory * (0.045 ~ 0.062)

 

* wal_buffers [restart]

WAL에 사용되는 크기를 설정합니다.

기본값 –1은 shared_buffers의 약 3%의 크기로 자동 설정됩니다.

최소) -1 ~ 최대) 2097144KB까지 설정 가능합니다.

산정방법 : shared_buffers * 0.03

처리할 양이 많거나, 코어수가 높은 서버에서는 이것을 128MB까지 올리는 것이 유용할 수 있습니다.

 

* effective_cache_size [reload]

단일 쿼리에 사용할 수 있는 디스크 캐시의 유효 크기에 대한 플래너의 가정을 설정합니다.

이 파라미터는 인덱스 사용 비용의 추정치에 포함됩니다. 이 파라미터를 설정함으로써 플래너가 적절한 비용

추정을 하는데 도움이 됩니다. 실제로 메모리를 할당하지 않습니다.

기본값은 4GB이며, 최소) 8KB ~ 최대) 17179869176KB까지 설정 가능합니다.

산정방법 : OS Total System Memory의 1/2로 설정하나 메모리가 그리 크지 않은 곳에서는 1/3 아래로 설정하세요.

 

* min_wal_size [reload]

WAL 조각 파일들은 더 이상 보관할 필요가 없어지면 삭제되는 것이 아니라, 앞으로 사용될 파일로 이름을

변경합니다. 이 파라미터는 이렇게 남겨두는 WAL 최소값을 지정합니다.

기본값은 80MB이며, 최소) 2MB ~ 최대) 2147483647MB까지 설정 가능합니다.

산정방법 : 권장 1GB, 최대 100GB까지 환경에 맞게 조절하세요.

 

* max_wal_size [reload]

Checkpoint 작업을 자동으로 진행할 WAL 최대 크기를 설정합니다.

기본값은 1GB이며, 최소) 2MB ~ 최대) 2147483647MB까지 설정 가능합니다.

산정방법 : 이 파라미터 값이 커지면, 응급 복구에 필요한 시간이 늘어날 수 있습니다. 이 점 유의하시고

각 환경에 맞게 적절한 값으로 설정하세요.

 

* work_mem [reload]

정렬 및 해시를 포함하여 쿼리 작업에 사용할 수 있는 비공유 메모리양에 대한 제한을 설정합니다.

기본값은 4MB이며, 최소) 64KB ~ 최대) 2147483647KB까지 설정 가능합니다.

산정방법 : total system memory / (max_connections * 2)

work_mem의 크기를 너무 크게 잡으면 Out of Memory가 발생할 수 있습니다. max_connections 설정값과

OS 서버 총 메모리 크기를 확인하시고 적절한 값으로 설정하세요.

 

* checkpoint_timeout [reload]

Checkpoint 작업을 자동으로 진행할 최대 시간을 설정합니다.

기본값은 5분이며, 최소) 30초 ~ 최대) 1일까지 설정 가능합니다.

산정방법 : 운영환경에 맞게 조절하여 사용하세요. max_wal_size 파라미터와 마찬가지로 값이 늘어날수록

응급 복구에 필요한 시간이 늘어납니다.

 

* default_statistics_target [reload]

테이블 열에 대한 기본 통계 대상을 설정합니다.

값이 클수록 분석하는데 필요한 시간이 늘어나지만 플래너 추정의 품질이 향상될 수 있습니다.

기본값은 100이며, 최소) 1 ~ 최대) 10000까지 설정 가능합니다.

산정방법 : 대부분의 애플리케이션은 기본값을 사용합니다. 매우 작고 단순한 데이터베이스 같은 경우 50이하

로 줄입니다. 데이터 웨어하우징 애플리케이션은 500에서 1000사이를 사용합니다.

 

* autovacuum_work_mem [reload]

각 autovacuum 작업자 프로세스에서 사용할 최대 메모리를 설정합니다.

기본값은 –1이며, 최소) -1 ~ 최대) 2147483647KB까지 설정 가능합니다.

산정방법 : 기본값 (-1), 기본값으로 설정 시 maintenance_work_mem의 설정값을 사용합니다.

 

* autovacuum_vacuum_scale_factor [reload]

vacuum을 수행하는데 autovacuum_vacuum_threshold에 추가할 테이블 크기의 일부를 설정합니다.

기본값은 0.2(테이블 크기의 20%)이며, 최소) 0 ~ 최대) 100까지 설정 가능합니다.

 

* autovacuum_vacuum_threshold [reload]

하나의 테이블에서 vacuum을 수행하는데 필요한 업데이트 또는 삭제된 튜플의 최소 수를 설정합니다.

기본값은 50이며, 최소) 0 ~ 최대) 2147483647까지 설정 가능합니다.

 

* wal_level [restart]

WAL에 기록되는 정보의 수준(양)을 설정합니다.

기본값은 replica이며, minimal, replica, logical 중에 선택하여 설정 가능합니다.

- minimal : 충돌 또는 비정상종료로부터 복구하기 위해 필요한 정보만 기록합니다.

- replica : 트랜잭션 로그를 복구용으로 쓰기 위해 따로 보관하고, 대기 서버에서 읽기 전용 쿼리에 필요한

정보를 좀 더 추가합니다.

- logical : 논리적 디코딩을 지원하는데 필요한 정보를 추가합니다.

Archive Mode를 사용한다면 wal_level이 replica 레벨 이상이어야 합니다. 만약 복제나 아카이빙을 전혀 수행

하지않는다면 minimal로 설정하세요.

 

* archive_mode [restart]

archive_mode를 활성화함으로써 WAL 파일을 보관할 수 있습니다.

기본값은 off이며, 활성화하려면 on으로 변경하면 됩니다.

 

* archive_command [reload]

완료된 WAL 파일을 보관하기 위해 실행할 로컬 쉘 명령입니다.

예) archive_command = 'cp %p /pg_arch/%f’

 

* archive_timeout [reload]

다음 WAL 파일로 강제 전환하기 전에 대기하는 시간을 설정합니다.

기본값은 0(disabled)이며, 최대 1073741823초까지 설정 가능합니다.

archive_command는 완료된 WAL 파일에 대해서만 호출하기 때문에, 보관되지 않은 데이터의 기간을 제한

하려면 이를 설정해줘야 합니다. 일반적으로 1~2분정도의 설정이 합리적입니다.

 

* search_path [reload]

스키마 이름에 대한 검색 순서를 설정합니다. 기본값은 “$user”,public 으로 설정되어 있습니다.

 

* log_destination [reload]

서버 로그 출력 대상을 설정합니다.

기본값은 stderr이며, stderr, csvlog, syslog, eventlog 중 선택하여 설정 가능합니다.

 

* logging_collector [restart]

백그라운드 프로세스인 logging_collector를 활성화/비활성화 설정합니다.

기본값은 off이며, on 시 활성화됩니다.

 

* log_directory [reload]

로그파일의 저장될 디렉토리를 지정합니다.

예) log_directory = '/postgres/app/postgres/pgsql12/pg_log/'

 

* log_filename [reload]

저장될 로그파일의 이름을 설정합니다.

예) log_filename = 'postgresql-%Y-%m-%d.log’

 

* log_file_mode [reload]

생성될 로그파일의 권한을 설정합니다.

예) log_file_mode = 0600

 

* log_truncate_on_rotation [reload]

로그 파일 회전 중에 동일한 이름의 기존 로그파일을 덮어쓸지 활성화/비활성화를 설정합니다.

기본값은 off입니다.

 

* log_rotation_age [reload]

로그 파일을 사용할 최대 시간을 설정합니다. 이 설정값이 지나면 새 로그파일이 생성됩니다.

기본값은 24시간이며, 최대 35791394분까지 설정 가능합니다.

 

* log_rotation_size [reload]

로그 파일이 회전되기 전에 도달할 수 있는 최대 크기를 설정합니다.

기본값은 10MB이며, 최대 2097151KB까지 설정 가능합니다. 0으로 설정 시 비활성화됩니다.

 

* superuser_reserved_connections [restart]

수퍼유저용으로 예약된 연결 슬롯 수를 설정합니다.

 

* cluster_name [restart]

다양한 목적으로 데이터베이스를 식별할 수 있도록 클러스터 이름을 설정합니다.

설정하게되면 서버 프로세스에 대한 프로세스 제목이 나타나게 됩니다.

 

* password_encryption [reload]

비밀번호 암호화 알고리즘을 선택하는 파라미터입니다. md5와 SCRAM-SHA-256중 값을 선택하여 사용할 수

있는데, 현재 PostgreSQL에 제공되는 암호화 방법 중 제일 안전한 것은 SCRAM-SHA-256입니다.

PostgreSQL 13버전 이후부터는 SCRAM-SHA-256이 Default 값이나 13 이전버전에서는 md5가 Default값

입니다.

오래된 클라이언트는 SCRAM 인증 메커니즘에 대한 지원이 부족할 수 있어 SCRAM-SHA-256으로 암호화된

암호로 작동하지 않을 수 있습니다.

 

※ 파라미터별 각 산정방식은 어디까지나 보편적인 기준을 제공하는 참고용일뿐, 정답은 아닙니다.

 

DB 파라미터 설정 예시 (Memory 32GB 기준)

cluster_name = 'pg_database'
listen_addresses = '*'
port = 5432
max_connections = 1000              
superuser_reserved_connections = 10
shared_buffers = 10GB
temp_buffers = 50MB
work_mem = 16MB
maintenance_work_mem = 1500MB
wal_buffers = 300MB
effective_cache_size = 8GB
min_wal_size = 1GB
max_wal_size = 1GB
default_statistics_target = 100
autovacuum_vacuum_scale_factor = 0.0
autovacuum_vacuum_threshold = 20000
archive_mode = on
archive_command = 'cp %p /arch/%f'
archive_timeout = 120
log_destination = 'stderr'
logging_collector = on
log_directory = '/postgres/app/postgres/pgsql12/pg_log/'
log_filename = 'postgresql-%Y-%m-%d.log'
log_file_mode = 0600
log_truncate_on_rotation = off
log_rotation_age = 43200
log_rotation_size = 0
search_path = '"$user",public'
password_encryption = scram-sha-256