[PostgreSQL] - 튜닝옵션 : 메모리 설정

postgresql.conf 

postgresql.conf 파일에서 데이터 베이스 전체 성능을 조절하는 많은 옵션들이 있다. 모든 항목들에 대해서 설명할 수는 없어서 본문에서는 몇번의 Postgresql 튜닝 작업을 통해서 정리한 내용들 위주로 설명한다.

https://kwomy.tistory.com/82

[PostgreSQL] 튜닝옵션 - postgresql.conf (Kor.ver)

work_mem

sort, merge, join 등에 사용하는 메모리 사용량을 의미합니다. default는 4M로 설정되어 있으며 각 서버 사양마다

산정방법이 다르다.

(1) (시스템 전체 메모리) / (최대 커넥션 수 * 16)  

(2) (OS cache memory / max_connections) * 0.5 

(3) 1 / (max_connections * 2)

예를들어 16G메모리 데이터베이스서버에 100대의 커넥션이 생긴다면

(16G * 1024) / (100대 * 16) = 16,384 / 1600 = 10.24M = 약 10M 로 설정

 

그렇다고 work_mem을 너무 크게 잡으면 한 프로세스가 사용하는 메모리는 적지만 work_mem이 많이 잡혀있다면 프로세스를 다 띄우지 못하고 초과되어 다 써버리게 되면 Out of Memory가 발생할 수 있다.

 

Out of Memory 에러를 발견하는 즉시 서버 RAM을 증설하거나 work_mem 사이즈를 줄여준다.

 

shared_buffers

 - Postgresql에서는 디스크에 메모리 값을 직접 변경하는 대신 공유 버퍼 캐시에 데이터를 읽게 요청함.

 - buffer(디스크) 사용량 : 너무 클 경우 파티션으로 변경 필요

 - shared_buffers 대비 사용률 : 너무 클 경우 메모리 조정 필요

 - 테이블 대비 캐시 비율

select  c.relname as relname , 
pg_size_pretty(count(*) * 8192) as buffered , 
round(100.0 * count(*) / ( select setting from pg_settings where name='shared_buffers')::integer,1) as buffer_percent ,
round(100.0*count(*)*8192 / pg_table_size(c.oid),1) as percent_of_relation 
from pg_class c inner join pg_buffercache b 
on b.relfilenode = c.relfilenode inner join pg_database d 
on ( b.reldatabase =d.oid and d.datname =current_database())
group by c.oid,c.relname 
order by 3 desc;

 - default : 4M 이며 공식 document에서는 전체 메모리의 40%를 넘지않도록 권장한다. 

 - FATAL : could not create shared memory segment : Invalid argument ERROR발생시
    Linux의 경우 / proc / sys / kernel / shmmax에 shared_buffers에서 사용하는 공유 메모리 크기보다 큰 값을 지정

EX) #echo 167772160 > /proc/sys/kernel/shmmax (167772160 = 160 * 1024 * 1024) => 공유메모리 160M 확보

 

-- 설정 값 확인
# ipcs -ml 

------ Shared Memory Limits -------- 
max number of segments = 4096max 
seg size (kbytes) = 163840max 
total shared memory (kbytes) = 0min 
seg size (bytes) = 1

 * 커넥션 메모리 견적 산정 : postmaster 크기 X max_connections 

 

pg_warm

 - 해당 테이블을 강제로 버퍼에 올림
 - create extension pg_prewarm;
 - select pg_prewarm('category');

log_min_duration_statement

 - default : 1000ms => 3000ms
 - 쿼리 실행 시간 3초 이상인 쿼리들은 log폴더 하위에 파일로 log 남김

effective_cache_size

 - 전체 메모리의 50~70%로 설정
 - default : 4G => 8G ?

wal_buffer

 - shared_buffers size의 1/32 크기로 지정
 - 데이터 업데이트 시 어떤 변경을 할 것인지를 log로 기록

check_point_segment

 - default : 3 => 12?
 - 3 * 16 = 48M가 쌓이면 disk에 기록
 - disk i/o를 줄이기 위해 값을 올림

default_statistics_target

 - 개별 테이블에 통계가 필요한 PostgreSQL.
 - 값이 낮으면? 쿼리 플래너의 결과가 달라짐
 - 값이 높으면? 개별 테이블 통계를 수집하는데 시간이 많이 걸림
 - LIKE 쿼리를 자주 사용하면 값을 증가시켜줄 필요가 있음

maintenance_work_mem

 - autovaccum_max_workers의 설정과 연관된 값(값이 작으면 자주 vaccum 발생)
 - 전체 메모리의 5%정도로 설정
 - default : 64M => 800M?

deadlock_timeout

 - 교착 상태 검출 처리
 - 1000 * max_connections 수로 설정하는것이 좋음

effective_cache_size 

 - 커널과 공유버퍼 등 PostgreSQL가 사용하는 버퍼 영역 크기 추정치
 - effective_cache_size * 8192 byte 로 환산한 값
 - 기본값 : 8MB
 - 컴퓨터 메모리 총량의 1/4 ~ 1/2 정도로 설정

wal_buffersWAL

 - 데이터의 공유 메모리 버퍼 수
 - default : 8 ( X 8kb = 64kb)
 - 32 ~ 64 정도로 설정

wal_sync_methodWAL

 - 업데이트 디스크 레코딩 방법 지정

cpu_index_tuple_cost

 - 하나의 인덱스 튜플 처리 비용 지정

random_page_count

 - 테이블의 1페이지 액세스 시간 기준, 인덱스를 이용한 액세스 시 걸리는 시간을 정하는 기준 수치
 - 2~3정도가 적당

max_fsm_pagesPostgreSQL

 - 갱신,삭제 후에도 오래된 행은 남아있음.
 - VACUUM 실행 후, 공유 메모리 상 FSM(free space map)에 기록.
 - 갱신, 발생이 삭제될 시 FSM을 검색하여 불필요한 공간 회수
 - 회수에 필요한 FSM 크기는 vacuumdb 로그에서 확인
   ex) $ vacuumdb -a -z -v ... DETAIL : A total of 3136 page slots are in use (including overhead) .3136 page slots are required to track all free space.Current limits are : 200000 page slots 1000 relations, using 1237 KB.VACUUM
       모든 불필요 공간 회수하는데 필요한 페이지 수 : 3136페이지