[PostgreSQL] VACUUM 작업

참고

• https://blog.gaerae.com/2015/09/postgresql-vacuum-fsm.html

VACUUM 청소 기준

• 사람마다 청소하는 방법이 있듯 PostgreSQL도 청소 기준을 가지고 있습니다.
• 그 기준을 FSM(Free Space Map) 이라고 하는데, 더 이상 필요하지 않는 행의 정보를 보유하고 있습니다.
• 이 정보는 실제로 사용되지는 않지만 용량을 차지하고 있고, 새로운 행이 삽입욀 때 DBMS는 FSM의 여유 공간을 확인하여 해당 행을 사용하게 됩니다.
• 그리고 FSM 공간은 용량이 제한되어 있기 때문에 주기적으로 청소하는게 좋습니다.
• 아래 쿼리는 튜플(Tuple) 에 대한 정보를 확인할 수 있는 쿼리문입니다.

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
    pg_stat_get_live_tuples(c.oid) AS live_tuple,
    pg_stat_get_dead_tuples(c.oid) AS dead_tupple,
    round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
    round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
    pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
    pg_size_pretty(pg_relation_size(c.oid)) as relation_size
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
WHERE pg_stat_get_live_tuples(c.oid) > 0
AND c.relname NOT LIKE 'pg_%'
ORDER BY dead_tupple DESC;

• 다음 쿼리는 Vacuum 통계 정보를 확인할 수 있는 쿼리문이며, VACUUM ANALYZE 명령어 실행 시 갱신됩니다.

SELECT * FROM pg_stat_all_tables ORDER BY schemaname, relname;

Dead Tuple 들을 Free Space 로 바꾸는 작업

• Dead Tuple 이란, Delete 작업으로 Commit 된 자료나 Update 작업으로 Commit 된 Old Version 자료, 즉 이미 지워진 자료입니다.

• 예시

  • Vacumm 작업 전
    • 테이블의 크기는 총 10MB

  create table test (a int, b int);
  alter table test set (autovacuum_enabled = false); -- autovacuum 비활성화
  insert into test select generate_series, generate_series from generate_series(1, 100000);
  \dt+ test -- table 크기 : 3568 kB(10만건) / 총 10만개(10만:live tuple)의 dead tuple 생성
  update test set b = b + 1 ;
  \dt+ test -- table 크기 : 7104 kB(20만건) / 총 20만개(10만:dead tuple + 10만:live tuple)의 dead tuple 생성
  update test set b = b + 1 ;
  \dt+ test -- table 크기 : 10 MB(30만건) / 총 30만개((10만 + 10만):dead tuple + 10만:live tuple)의 dead tuple 생성

  • Vacuum 작업 후
    • 테이블의 크기는 총 10MB
    • 테이블의 실 자료는 3.5kB
    • 나머지는 빈 공간이 되며, 다음 update 작업 시 New Version Row 가 빈 공간에 저장

  vacuum test;

테이블의 Visibility Map, Free Space Map 파일의 내용을 갱신

• 데이터베이스 자료 파일이 있는 디렉터리(/data) 안에 있는 NNNN_vm, NNNN_fsm 파일입니다.
• NNNN_vm - vm 파일은 해당 블럭의 해당 자료가 실자료인지, 그 자료가 영구보관처리가 되었는지에 대한 정보를 의미합니다.
• NNNN_fsm - fsm 파일은 해당 블럭에 어느 영역이 빈 공간으로 처리되었는지에 대한 정보를 의미합니다.

테이블의 자료 통계 정보를 갱신

• pg_class 에 담기는 각종 테이블 통계 정보와, 실시간 통계 정보 파일(pg_stat_tmp 디렉터리에 담기는 각 데이터베이스별 통계정보) 의 내용을 갱신합니다.

트랜잭션 ID 겹침 문제 예방

• AutoVacuum : VACUUM public.x (to prevent wraparound) 프로세스의 우선순위가 높습니다.
• 사용자가 같은 테이블을 대산으로 vacuum 명령을 수행시 겹침 방지 작업을 통해 사용자 vacuum 작업은 대기 상태로 변경됩니다.
• 빈번하게 자료가 바뀌는 데이터베이스라면, autovacuum_freeze_max_age, autovacuum_max_workers 서버 환경 설정 매개변수값을 상황에 맞게 조장해야 할 필요가 발생합니다. (PostgreSQL 데이터베이스 환경 설정 매개변수 기본값 - 특별히 지정하지 않은 중소규모 서비스를 대상으로 하고 있습니다.)

VACUUM 수동 작업

vacuum 테이블명;

AUTOVACUUM 작업

# postgresql.conf 파일의 환경 변수 설정
AUTOVACUUM = on -- autovacuum subprocess 실행 설정
TRACK_COUNTS = on -- 테이블 및 인덱스 액세스에 대한 통계 수집여부를 설정

• autovacuum 이라는 백그라운드 프로세스가 담당하여 자동으로 수행합니다.
• Daemon 이라고 불리는 여러 개의 프로세스들에 의해 수행되며, 이 중 Autovacuum Launcher 프로세스는 Worker 프로세스를 관리하고 지시하는 역할을 수행합니다.
• Autovacuum 이 동작하기 위해서는 AUTOVACUUM, TRACK_COUNTS Parameter 가 켜져 있어야 합니다. TRACK_COUNTS 는 프로세스에서 통계정로를 활용해서 vacuum 을 수행합니다.
• 이때 Autovacuum Launcher 프로세스는 AUTOVACUUM_NAPTIME 을 주기로 최대 AUTOVACUUM_MAX_WORKERS 개수 만큼의 Worker 프로세스를 깨워서 Vacuum 작업을 수행하게 됩니다.
• 이 작업 관련 환경 설정은 autovacuum_ 으로 시작하는 여러 환경 변수들입니다.

select name,short_desc from pg_settings where name like 'autovacuum%';

• 환경 설정 (다음의 설정은 관리하는데 중요한 설정값 입니다.)
  • autovacuum_max_workers(동시에 실행 될 수 있는 autovacuum 작업 >프로세스 최대 개수, default 3개)
  • autovacuum_freeze_max_age(트랙잭션 ID 겹침 방지 작업을 시작하는 테이블 나이, default 2억살)
  • autovacuum_vacuum_scale_factor(autovacuum 작업 대상 선정 기준이 되는 테이블 변화량, 백분율, default 20%)

AUTOVACUUM 관리

• pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_autovacuum 모니터링 필요
• pg_stat_all_tables.n_dead_tup 값이 계속 커지는데 pg_stat_all_tables.last_autovacuum 값이 변경되지 않으면 autovacuum 작업이 원활하지 않음을 의미합니다.
• 사용자가 직접 vacuum 명령 수행하는 방법이 있습니다.
  • 사용자가 실행하는 vacuum 명령은 autovacuum과 달리 시스템 자원을 100% 활용해서 최대한 빠른 시간 안에 처리하므로 운영 서비스에서 영향을 미칠 수 있습니다.
• 안정적인 운영 방법은 autovacuum_max_workers 값을 늘리는 것인데, 이것도 시스템 자원의 한계가 있기 때문에 서비스에 영향을 주지 않는 범위에서 지정하는 것이 좋습니다. 통상 CPU 코어 수의 1~2 배 범위 안에서 지정하는 것을 추천합니다.