(PostgreSQL) PostgreSQL autovacuum을 튜닝할 때 유용한 Tip 💡

이번 글에서는 Working with PostgreSQL autovacuum on Amazon RDS을 정리한 내용을 다루고자 한다. 

 

 

---

Autovacuum은 자동으로 VACUUM과 ANALYZE 명령문을 실행한다. Autovacuum은 추가/수정/삭제된 튜플이 많은 테이블을 찾는다. 그리고 데이터베이스에서 더 이상 사용되지 않는 데이터를 정리하여 저장공간을 확보한다.

기본적으로 AWS RDS PostgreSQL에서 Autovacuum이 활성화되어있다. 그리고 autovacuum과 관련된 설정들을 적절한 값을 기본값으로 설정해준다. 이 기본값들은 일반적인 기준으로 설정한 값이다. 때문에 시스템 특성에 맞는 적절한 값으로 설정값을 튜닝하면 성능상 이점을 얻을 수 있다. 해당 포스팅에서는 autovacuum 튜닝할 때 적용할 수 있는 몇 가지 팁들에 대해서 설명하고자 한다.

 

Allocating memory for autovacuum

maintenance_work_mem은 autovacuum 퍼포먼스에 가장 영향력이 큰 설정값이다. 이 값은 autovacuum 작업에 할당할 메모리 크기를 의미한다. 이 메모리는 autovacuum 대상 테이블을 스캔할 때, 그리고 vacuum 대상이 되는 row ID를 저장해둘 때 사용된다. 이 값이 너무 적게 설정되어있는 경우, autovacuum 프로세스가 작업을 처리하기 위해 테이블을 여러 번 스캔해야 한다. 테이블을 여러 번 스캔하는 작업은 autovacuum 퍼포먼스를 떨어트리기 때문에, 적절한 값을 설정하는 것이 좋다.

maintenance_work_mem을 설정할 때, 유의 깊게 봐야 하는 요소는 다음과 같다.

• maintenance_work_mem은 KB 단위이다.
• maintenance_work_mem은 autovacuum_max_workers와 함께 고려하여 설정해야한다.
  • 만약, 작은 규모의 테이블이 여러 개 있는 경우에는 autovacuum_max_workers은 많이, 그리고 maintenance_work_mem은 적게 설정하는 게 좋다.
  • 만약, 크기가 100GB 이상되는 큰 테이블이라면 메모리는 더 많이 할당하고, 워커는 적게 설정하는 것이 좋다.
  • autovacuum_max_workers는 할당된 메모리를 각각 사용할 수 있다. 때문에 작업 프로세스 수와 할당한 메모리의 조합이 의도한 전체 메모리 크기와 일치하는지 고려해야 한다.

일반적으로 대형 서비스에서는 maintenance_work_mem을 1~2GB으로 설정한다. 초대형 서비스에서는 2~4GB으로 설정한다. 이 값은 DB 사용량에 따라 달라진다. AWS RDS 기본값은 다음과 같은 계산식으로 결정된다.

GREATEST({DBInstanceClassMemory/63963136*1024},65536).

 

Reducing the likelihood of transaction ID wraparound

때로는 autovacuum 설정 파라미터가 transaction ID wraparound를 막기에는 충분하지 않을 수 있다.

이를 해결하기 위해, AWS RDS PostgreSQL에는 autovacuum 설정값을 자동으로 튜닝하는 adaptive autovacuum 기능을 제공하고 있다. 기본적으로 rds.adaptive_autovacuum은 ON으로 설정되어있다.

autovacuum이 켜져 있는 상황에서도 transaction ID wraparound은 가능하다. 때문에 Amazon CloudWatch 알람을 사용하여, transaction ID wraparound에 대비하는 것이 좋다.

adaptive autovacuum parameter tuning을 설정한 경우, RDS는 다음과 같은 상황에서 autovacuum 설정값을 조정하기 시작한다.

CloudWatch 매트릭인 MaximumUsedTransactionIDs (데이터베이스 나이)를 기준으로 

• autovacuum_freeze_max_age에 도달한 경우
• 5억보다 높아지는 경우

테이블의 나이가 transaction ID wraparound가 되는 방향으로 계속 올라가는 추세가 지속되는 경우, RDS는 autovacuum 설정 값 계속해서 조정한다.

RDS는 아래의 설정값을 활용하여, wraparound를 피하기 위해 autovacuum에 리소스를 더 할당한다.

• autovacuum_vacuum_cost_delay
• autovacuum_vacuum_cost_limit
• autovacuum_work_mem
• autovacuum_naptime

RDS는 새로운 값이 autovacuum이 더 공격적으로 동작하게 할 때에만 파라미터를 변경한다. 설정 값은 인스턴스의 메모리에서 수정되기 때문에, 파라미터 그룹에서는 변경된 값을 확인할 수 없다. 현재 메모리 내의 설정값을 보기 위해서는 Postgresql의 SHOW SQL을 실행하여 확인해야 한다.

RDS에서는 autovacuum 파라미터를 수정할 때마다, 영향을 받는 DB 인스턴스에 대한 이벤트를 생성한다. 이는 AWS Management Console > RDS와 RDS API로 확인할 수 있다.

데이터베이스의 나이를 의미하는 MaximumUsedTransactionIDs 값이 트리거의 기준 (threshold)이 되는 값보다 낮아지게 되면, 자동으로 메모리에 설정한 autovacuum 관련 설정 값들을 파라미터 그룹에 설정되어있는 값들로 리셋시킨다. 그리고 이 변경사항에 해당하는 이벤트를 생성한다.

 

Determining if the tables in your database need vacuuming

데이터베이스 안에서 베큠 되지 않은 트랜잭션의 수는 다음 쿼리로 확인할 수 있다.

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;

그러면 다음과 같은 데이터베이스 별 age를 확인할 수 있다. 이 쿼리는 모니터링 메트릭을 만드는 데에 사용될 수 있다.

datname    | age
mydb       | 1771757888
template0  | 1721757888
template1  | 1721757888
rdsadmin   | 1694008527
postgres   | 1693881061
(5 rows)  

transaction ID (XID)가 20억에 도달하게 되면, 데이터베이스는 transaction ID wraparound 상태가 되고 DB는 read-only 상태가 된다. 기본적으로 autovacuum_freeze_max_age은 2억으로 설정되고, autovacuum은 XID가 이 이상 올라가지 않도록 XID를 유지하는 역할을 한다.

XID를 모니터링하는 전략의 예시는 다음과 같다.

1. autovacuum_freeze_max_age을 2억으로 설정한다.
2. 베큠 되지 않은 XID가 5억이 되는 경우, 약한 강도의 알람을 발생시킨다. 이 시그널은 autovacuum이 제대로 일어나지 않고 있는 상황으로 볼 수 있다.
3. 베큠 되지 않은 XID가 10억이 되는 경우, 이보다 좀 더 높은 강도의 알림을 발생시킨다. 이때에는 무언가 조치가 필요하다.
4. 베큠 되지 않은 XID가 15억에 도달하면, 강한 수준의 알람을 발생시킨다. 데이터베이스에서 XID를 사용하는 수준에 따라 autovacuum을 실행할 수 있는 시간이 부족할 수도 있다. 때문에 이때에는 즉시 문제를 해결해야 한다.

테이블의 XID가 알람 임계값을 지속적으로 넘어서는 경우, autovacuum 설정 파라미터를 변경하는 작업이 필요하다. 기본적으로 수동으로 VACUUM을 실행하는 것은 autovacuum 기본 설정값보다 더 적극적으로 vacuum을 실행한다. 반면, 시스템 자원을 더 많이 사용한다는 트레이드오프가 있다.

때문에 AWS에서는 두 가지를 권장하고 있다.

• 오래된 트랜잭션 나이를 인지할 수 있는 모니터링 시스템을 구축할 것
• 테이블 수정/삭제/추가 빈도가 많은 테이블인 경우, autovacuum에 의존하는 것 외에도 정기적으로 수동 vacuum freeze를 실행할 것

 

Determining which tables are currently eligible for autovacuum

autovacuum은 테이블의 relfrozenxid 값이 autovacuum_freeze_max_age 보다 커진 테이블들을 대상으로 vacuum을 실행한다.

그리고 마지막 vacuum이 실행된 이후에 발생한 데드 튜플이 테이블 vacuum의 임계치 ( vacuum threshold )를 넘었을 때 vacuum을 실행한다.

vacuum threshold을 구하는 계산식은 다음과 같다.

Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples

다음 쿼리를 실행하면, vacuum 임계치를 넘어선 테이블 리스트를 확인할 수 있다.

WITH vbt AS (
  SELECT
    setting AS autovacuum_vacuum_threshold
  FROM
    pg_settings
  WHERE
    name = 'autovacuum_vacuum_threshold'
),
vsf AS (
  SELECT
    setting AS autovacuum_vacuum_scale_factor
  FROM
    pg_settings
  WHERE
    name = 'autovacuum_vacuum_scale_factor'
),
fma AS (
  SELECT
    setting AS autovacuum_freeze_max_age
  FROM
    pg_settings
  WHERE
    name = 'autovacuum_freeze_max_age'
),
sto AS (
  select
    opt_oid,
    split_part(setting, '=', 1) as param,
    split_part(setting, '=', 2) as value
  from
    (
      select
        oid opt_oid,
        unnest(reloptions) setting
      from
        pg_class
    ) opt
)
SELECT
  '"' || ns.nspname || '"."' || c.relname || '"' as relation,
  pg_size_pretty(
    pg_table_size(c.oid)
  ) as table_size,
  age(relfrozenxid) as xid_age,
  coalesce(
    cfma.value :: float, autovacuum_freeze_max_age :: float
  ) autovacuum_freeze_max_age,
  (
    coalesce(
      cvbt.value :: float, autovacuum_vacuum_threshold :: float
    ) + coalesce(
      cvsf.value :: float, autovacuum_vacuum_scale_factor :: float
    ) * c.reltuples
  ) as autovacuum_vacuum_tuples,
  n_dead_tup as dead_tuples
FROM
  pg_class c
  join pg_namespace ns on ns.oid = c.relnamespace
  join pg_stat_all_tables stat on stat.relid = c.oid
  join vbt on (1 = 1)
  join vsf on (1 = 1)
  join fma on (1 = 1)
  left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold'
  and c.oid = cvbt.opt_oid
  left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor'
  and c.oid = cvsf.opt_oid
  left join sto cfma on cfma.param = 'autovacuum_freeze_max_age'
  and c.oid = cfma.opt_oid
WHERE
  c.relkind = 'r'
  and nspname <> 'pg_catalog'
  and (
    age(relfrozenxid) >= coalesce(
      cfma.value :: float, autovacuum_freeze_max_age :: float
    )
    or coalesce(
      cvbt.value :: float, autovacuum_vacuum_threshold :: float
    ) + coalesce(
      cvsf.value :: float, autovacuum_vacuum_scale_factor :: float
    ) * c.reltuples <= n_dead_tup -- or 1 = 1 ) ORDER BY age(relfrozenxid) DESC LIMIT 50;

 

Determining if autovacuum is currently running and for how long

테이블을 수동으로 vacuum 해야 하는 경우, autovacuum이 현재 실행 중인지 확인해야 한다.

이 결과를 기반으로 autovacuum 설정값을 변경하여 vacuum을 더 효율적으로 만들 수도 있고, 혹은 수동 vacuum을 실행하기 위해 autovacuum을 종료할 수도 있다.

다음 쿼리를 사용하여 vacuum이 얼마나 지연되고 있는지 확인할 수 있다. (PostgreSQL 9.6+ 기준)

SELECT
  datname,
  usename,
  pid,
  state,
  wait_event,
  current_timestamp - xact_start AS xact_runtime,
  query
FROM
  pg_stat_activity
WHERE
  upper(query) LIKE '%VACUUM%'
ORDER BY
  xact_start;

 

 datname | usename  |  pid  | state  | wait_event |      xact_runtime       | query
 --------+----------+-------+--------+------------+-------------------------+--------------------------------------------------------------------------------------------------------
 mydb    | rdsadmin | 16473 | active |            | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound)
 mydb    | rdsadmin | 22553 | active |            | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound)
 mydb    | rdsadmin | 41909 | active |            | 3 days 02:43:54.203349  | autovacuum: VACUUM ANALYZE public.mytable3
 mydb    | rdsadmin |   618 | active |            | 00:00:00                | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+
         |          |       |        |            |                         | FROM pg_stat_activity                                                                                 +
         |          |       |        |            |                         | WHERE query like '%VACUUM%'                                                                           +
         |          |       |        |            |                         | ORDER BY xact_start;                                                                                  +

autovacuum이 오랜 시간 동안 실행되는 이유는 몇 가지가 있다. 그중 가장 대표적인 원인은 maintenance_work_mem을 테이블의 크기 혹은 데이터 변경 비율에 비해 너무 적게 설정해서이다.

AWS에서는 maintenance_work_mem을 다음과 같은 공식으로 설정하기를 추천한다.

GREATEST({DBInstanceClassMemory/63963136*1024},65536)

autovacuum 실행 시간이 짧지만, vacuum이 정상 동작하지 않는 경우에는 두 가지 케이스를 고려해볼 수 있다.

• DB의 작업량 대비 autovacuum_max_workers이 너무 적게 설정되어있는 케이스이다. 이 경우 워커수를 늘리는 게 좋다.
• autovacuum 도중 크래시가 발생하고, 더 이상 vacuum이 진행되지 않는 케이스이다. 이때에는 인덱스 충돌 문제일 수 있다. 이때 더 자세한 정보를 얻으려면, vacuum freeze verbose {table_name}을 실행하여 세부 정보를 확인해야 한다.

 

Performing a manual vacuum freeze

autovacuum이 실행 중이지만 maintenance_work_mem이 너무 적게 설정되어있어서 vacuum 처리가 지연되는 케이스에 대응하는 방법에 대해서 소개하고자 한다.

이때에는 maintenance_work_mem을 높게 설정해줘야 하는 것과 동시에 vacuum이 동작하고 있는 테이블에 즉각적인 조치를 취해야 한다. PostgresSQL에서는 이미 vacuum이 돌고 있는 테이블에 수동 vacuum을 실행할 수 있다.

다음에서는 이 경우, 수동으로 vacuum을 실행하는 절차에 대해서 설명하고자 한다.

수동으로 vacuum freeze 실행하기

1. 데이터베이스 접근 세션 두 개를 실행한다.

2. 첫 번째 세션에서는 다음 명령문을 실행하여 autovaccum 중인 테이블의 PID를 구한다.

SELECT
  datname,
  usename,
  pid,
  current_timestamp - xact_start AS xact_runtime,
  query
FROM
  pg_stat_activity
WHERE
  upper(query) LIKE '%VACUUM%'
ORDER BY
  xact_start;

3. 두 번째 세션에서는 maintenance_work_mem의 설정값을 변경한다.

set maintenance_work_mem='2 GB'; SET

4. 그리고 두 번째 세션에서 vacuum이 실행 중인 테이블을 대상으로 vacuum freeze 명령문을 실행한다. verbose 옵션을 추가하면, vacuum 실행 과정이 상세하게 출력되기 때문에 실행 과정을 이해하는 데에 도움이 된다.

\timing on
Timing is on.
vacuum freeze verbose pgbench_branches;

INFO:  vacuuming "public.pgbench_branches"
INFO:  index "pgbench_branches_pkey" now contains 50 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pgbench_branches_test_index" now contains 50 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pgbench_branches": found 0 removable, 50 nonremovable row versions
     in 43 out of 43 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 9347 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 2.765 ms

 

5. 첫 번째 세션에서 확인한 autovacuum activity가 두 번째 세션에서 실행한 수동 vacuum으로 인하여 blocking 된다면, 다음 명령문을 실행하여 커넥션을 강제 종료할 수 있다.

SELECT pg_terminate_backend('the_pid');

 

Reindexing a table when autovacuum is running

테이블 인덱스가 손상된 경우, autovacuum이 실행되다가 중지되는 현상이 발생한다. 이때, 수동 vacuum을 실행하면 다음과 같은 에러 메시지가 발생한다.

mydb=# vacuum freeze pgbench_branches;
ERROR: index "pgbench_branches_test_index" contains unexpected 
   zero page at block 30521
HINT: Please REINDEX it. 

 

이때에는 테이블 reindex가 필요하다. 다음에서는 autovacuum이 실행 중인 테이블을 reindex 하는 방법에 대해서 설명하고자 한다.

 

1. 두 개의 DB 접근 세션을 실행한다.

2. 첫 번째 세션에서는 현재 vacuum이 실행 중인 테이블을 확인하고, pid를 구한다.

SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY xact_start;

3. 두 번째 세션에서는 reindex 명령문을 실행한다.

reindex 명령문 실행 도중에는 테이블에 대한 exclusive lock이 해제되고, write 명령문이 블락되고, 특정 인덱스 조회가 불가능해진다.

\timing on
Timing is on.
reindex index pgbench_branches_test_index;
REINDEX
Time: 9.966 ms

 

4. 첫 번째 세션에서 확인한 autovacuum이 블락되어 대기상태라면, 다음 명령문을 실행하여 세션을 강제 종료한다.

select pg_terminate_backend('the_pid');

 

Other parameters that affect autovacuum

다음 쿼리는 autovacuum 관련 파라미터 설정 값들의 정보를 보여준다.

SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name IN (
'autovacuum_max_workers',
'autovacuum_analyze_scale_factor',
'autovacuum_naptime',
'autovacuum_analyze_threshold',
'autovacuum_analyze_scale_factor',
'autovacuum_vacuum_threshold',
'autovacuum_vacuum_scale_factor',
'autovacuum_vacuum_threshold',
'autovacuum_vacuum_cost_delay',
'autovacuum_vacuum_cost_limit',
'vacuum_cost_limit',
'autovacuum_freeze_max_age',
'maintenance_work_mem',
'vacuum_freeze_min_age');

 

이 중, 가장 중요한 5가지 설정값은 다음과 같다.

• maintenance_work_mem
• autovacuum_freeze_max_age
• autovacuum_max_workers
• autovacuum_vacuum_cost_delay
• Autovacuum_vacuum_cost_limit

Setting table-level autovacuum parameters

Autovacuum과 관련된 storage parameters를 사용하면 테이블 별로 autovacuum 설정을 다르게 설정할 수 있다. 테이블 크기가 크거나, 수정 빈도가 많은 테이블을 대상으로 이러한 값을 설정하는 것이 전체 데이터베이스 설정을 변경하는 것보다 안전하고, 더 효율적이다.

다음 쿼리를 사용하면 테이블 별 옵션 설정을 확인할 수 있다.

SELECT relname, reloptions
FROM pg_class
WHERE reloptions IS NOT null;

 

다음 예시처럼 테이블 별로 설정 파라미터를 변경할 수 있다.

ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0); 

 

Autovacuum logging

기본적으로 autovacuum에 관한 정보는 postgresql.log에 남지 않는다. rds.force_autovacuum_logging_level 세팅 값을 설정해야만 autovacuum에서 발생한 에러 로그를 출력할 수 있다. 이 세팅 값은 disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic 중에서 선택할 수 있다. 기본 값은 disabled이다.

AWS 가이드 문서에서는 rds.force_autovacuum_logging_level 파라미터를 warning으로 설정하고, log_autovacuum_min_duration을 1000-5000ms으로 설정할 것을 권장한다. 만약 5000ms를 설정한 경우, 5초 이상 소요된 autovacuum activity 이력을 로그에 남긴다. 그리고 lock 때문에 autovacuum을 건너뛰게 된 히스토리도 로그로 남길 수 있다.

만약 트러블슈팅에 문제가 있어서 상세한 정보를 기록해야 하는 경우에는, 로그 레벨을 debug1, debug3으로 조정하면 된다. 그러나 이 경우, 에러 로그에 너무 많은 정보를 쌓게 되기 때문에 짧은 기간 동안만 설정하여 트러블 슈팅을 하는 것이 좋다.

PostgreSQL 로그 설정에 대한 상세 정보는 PostgreSQL 문서를 참고하면 된다.

 

참고

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html