[PostgreSQL] Autovacuum 최적화

참고


AUtovacuum, Vacuum(Fuul) 에 대해

  • PostgreSQL에는 Autovacuum, 혹은 Vacuum 이라는 개념이 존재합니다.
  • PostgreSQL의 Autovacuum은 크게 두 가지 상황에서 동작합니다.
  • 그 중 하나는 XID Wraparound를 방지하기 위해 XID를 고정할 때 입니다.
  • 두 번째 경우는 바로 임계점 이상으로 늘어난 dead tuple 들을 제거하여 FSM(Free Space Map) 으로 반환하고자 할 때 입니다.
  • 즉, Autovacuum을 올바르게 이해하기 위해선 dead tuple 이 무엇인지를 알아야 할 필요가 있습니다.

Dead Tuple 이란

  • PostgreSQL에서 모든 데이터는 tuple 이라 불리는 형태로 저장이 됩니다.
  • 그리고 모든 tuple은 live tuple, dead tuple 로 나뉘며, 더 이상 사용 되지 않는 tuple을 dead tuple 이라 부릅니다.
  • 그리고 dead tuple 은 PostgreSQL이 MVCC를 구현한 방법으로 인해 발생합니다.
  • 만일 특정 column 혹은 row 를 업데이트하는 트랜잭션이 수행될 경우 PostgreSQL은 MVCC 지원을 위해 다음과 같이 동작합니다.
    • FSM에 여유가 있는지 확인합니다. 없으면 FSM을 추가적으로 확보합니다.
    • FSM의 빈 공간에 업데이트 될 데이터를 기록합니다. 이 때 새로운 tuple이 추가됩니다.
    • 기록이 완료되면, 기존 Column 혹은 row를 가리키는 포인터를 새로 기록된 tuple로 변경합니다.
    • 업데이트 이전 정보가 기록된 공간은 더 이상 참조가 되지 않게 됩니다. 이 참조가 되지 않는 tuple을 dead tuple 이라 부릅니다.
  • 일련의 과정에서 생성된 dead tuple은 참조가 되지 않을 뿐 아니라 무의미하게 저장공간만 낭비하고 있는 상태가 됩니다.
  • 그리고 이런 dead tuple 이 점유하고 있는 공간을 정리하여 FSM으로 반환하여 재사용 가능하도록 하는 작업을 바로 Vacuum 이라 합니다.
  • 정리하면 다음과 같습니다.
    • PostgreSQL의 MVCC 구현체는 update/delete 트랜잭션이 일어날 때 dead tuple 을 남기게 됩니다.
    • dead tuple 을 정리하기 위해 Full Vacuum 이라는 task 가 만들어지게 됩니다.
    • Full Vacuum 은 수동으로 구동됩니다. 그리고 Full Vacuum 이 수행중일 때 해당 테이블은 lock 이 걸리며 모든 트랜잭션이 거부됩니다.
    • 위와 같은 이유로 테이블에 lock 을 걸지 않으면서 dead tuple 을 정리해주는 Vacuum 명령어가 나오게 되었습니다. (단, 이 경우 dead tuple 의 정리 결과로 과다 사용된 저장 공간이 줄어들지는 않습니다.)
    • 또한, Vacuum 을 정기적으로, 그리고 자동으로 Vacuuming을 수행하는 Autovacuum이 만들어 지게 됩니다.

PostgreSQL 배포 철학과 Autovacuum

  • Autovacuum 이라는게 기본적으로 활성화 되어 있다면, 데이터베이스가 dead tuple 을 알아서 잘 관리하지 않을까? 라는 생각을 가질 수 있습니다.
  • 그런데 여기에 PostgreSQL 의 배포 철학이 또 다른 문제를 만들어 냅니다.
  • 바로 PostgreSQL의 기본 설정은 최고의 성능을 내기 보다는 가능한 다양한 기기에서 잘 동작할 수 있도록 매우 보수적으로 잡혀 있다는 점 입니다.
  • 따라서 우리는 PostgreSQL의 Autovacuum 과 관련된 설정들을 알아보고, 필요에 따라 이를 최적화 할 필요가 있습니다.
  • dead tuple 을 제거하기 위한 vacuuming 동작에 관여하는 설정은 postgresql.conf 파일의 다음 설정들입니다.
    • autovacuum_vacuum_threshold : vacuum 이 일어나기 위한 dead tuple 의 최소 갯수입니다. 기본 값은 50 입니다.
    • autovacuum_vacuum_scale_factor : vacuum 이 일어나기 위한 live tuple 대비 dead tuple 의 최소 비율입니다. 기본 값은 0.2 입니다.
  • 위 두 인자를 통해 구한 dead tuple 의 합을 토대로 Autovacuum 동작 여부가 결정도비니다.
  • 예를 들어 A 라는 테이블에 100,000건의 레코드가 있을 경우, (100,000 * 0.2) + 50 = 20,050 개의 dead tuple 이 발생할 경우 Autovacuum 이 동작하는 식입니다.
  • 위 상황들을 종합적으로 정리해 보면 다음과 같습니다.
    • Autovacuum 의 동작 여부를 결정하는 인자 중 autovacuum_vacuum_scale_factor 는 특정 테이블의 live tuple 대비 dead tuple 의 비율을 계산합니다.
    • 비율 기반으로 Autovacuum 이 동작하기 때문에 테이블의 레코드가 커질수록 처리해야 할 dead tuple 의 수가 비율적으로 급증하게 됩니다. 만일 10만건의 레코드를 가진 테이블이라면 2만 개의 dead tuple 이 생성될 때 Autovacuum 이 동작하지만, 1억건의 레코드를 가진 테이블이라면 2천만건의 dead tuple 이 생성 되고서야 Autovacuum 이 동작하게 됩니다. 즉 레코드가 늘어날 수록 Autovacuum 의 동작 주기는 점점 길어짐과 동시에 한 번에 처리해야 할 dead tuple 도 많아집니다.
    • 한 번에 처리해야 할 dead tuple 이 증가하게 될 경우, 그에 맞추어 다양한 추가적인 설정의 최적화를 필요로 합니다. 그러지 못할 경우 Autovacuum 이 진행되다가 dead tuple 을 모두 처리하지 못하고 중단이 될 수 있습니다. 결과적으로 Autovacuum 이 동작하는데도 불구하고 dead tuple 이 줄어들지 않거나 오히려 증가하게 될 수 있습니다.
    • 이는 저장소의 불필요한 증가 문제와 더불어 데이터베이스 성능의 전반적인 하락으로 이어집니다.
  • 요약하면 기본으로 설정되어 있는 autovacuum 관련 설정들은 매우 보수적으로 잡혀 있으며, 많은 트랜잭션 + 많은 레코드를 담는 테이블에는 적합하지 않습니다. 이제부터 하나씩 최적화를 해 보도록 하겠습니다.

모니터링 하기

  • 먼저 모니터링 쿼리를 통해 현재 PostgreSQL 의 테이블에 있는 dead tuple / live tuple 의 비율을 구해볼 수 있습니다.
  • 참고로 update, delete 가 일어나지 않고 insert 만 수행되는 테이블의 경우 dead tuple 은 사실 상 생기지 않는다고 봐도 됩니다.
-- dead tuple 이 1000 개 이상인 테이블의 dead tuple / live tuple 의 비율을 계산하여 출력합니다.
SELECT relname, n_live_tup, n_dead_tup, n_dead_tup / (n_live_tup::float) as ratio
FROM pg_stat_user_tables
WHERE n_live_tup > 0 AND n_dead_tup > 1000
ORDER BY ratio DESC;
  • 그리고 다음 쿼리를 통해 각 테이블 별로 Autovacuum 이 마지막에 실행된 시각을 알아볼 수 있습니다.
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY relname asc;
  • 정기적으로 해당 통계 정보를 모니터링 함으로써 트랜잭션이 잦은 테이블의 경우 평균적으로 발생하는 dead tuple 수를 알아낼 수 있으며, 이는 다음에 설명할 최적화 작업을 진행할 때 참고할 중요한 정보가 될 수 있습니다.

Autovacuum 최적화

  • 그럼 이제부터 Autovacuum 의 설정을 조금씩 살펴보며 어떤 식으로 dead tuple 증가를 최대한 억제할 수 있는지 알아보도록 하겠습니다.

postgresql.conf 파일 안에 Autovacuum 내용

  • postgresql.conf 파일 안에 Autovacuum 내용은 다음과 같습니다.
#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------

#autovacuum = on         # Enable autovacuum subprocess?  'on'
               # requires track_counts to also be on.
#autovacuum_max_workers = 3      # max number of autovacuum subprocesses
               # (change requires restart)
#autovacuum_naptime = 1min      # time between autovacuum runs
#autovacuum_vacuum_threshold = 50   # min number of row updates before
               # vacuum
#autovacuum_vacuum_insert_threshold = 1000   # min number of row inserts
               # before vacuum; -1 disables insert
               # vacuums
#autovacuum_analyze_threshold = 50   # min number of row updates before
               # analyze
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum
#autovacuum_vacuum_insert_scale_factor = 0.2   # fraction of inserts over table
               # size before insert vacuum
#autovacuum_analyze_scale_factor = 0.1   # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000   # maximum XID age before forced vacuum
               # (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000   # maximum multixact age
               # before forced vacuum
               # (change requires restart)
#autovacuum_vacuum_cost_delay = 2ms   # default vacuum cost delay for
               # autovacuum, in milliseconds;
               # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1   # default vacuum cost limit for
               # autovacuum, -1 means use
               # vacuum_cost_limit
  • 위와 같은 옵션으로 Default 값들이 설정되어 있습니다.

1. autovacuum_vacuum_scale_factor 를 0 으로 설정하기

  • Autovacuum 을 최적화 하는 가장 간단한 방법은 autovacuum_vacuum_scale_facotr 를 0 으로 설정하는 것입니다.
  • 이렇게 설정하게 되면 autovacuum_vacuum_threshold 에 지정된 숫자만큼의 dead tuple 에 따라 Autovacuum 이 동작하게 되므로 훨씬 일관성 있는 성능을 확보할 수 있습니다.
  • 하지만 이 설정을 postgresql.conf 에 적용할 경우 모든 테이블에 영향을 미치게 되는 문제가 있습니다.
  • 다행히 PostgreSQL 은 해당 옵션을 테이블 별로 설정할 수 있는 기능을 제공하므로 각 테이블에 맞춰 최적화된 설정을 할 수 있습니다.
  • 다음 쿼리는 huge_table 이라는 이름의 테이블의 Autovacuum 설정을 별도로 변경하는 예제입니다.
  • 아래와 같이 테이블에 autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 100000 을 설정하게 되면 해당 테이블에 한하여 postgresql.conf 의 동일한 설정을 override 하게 되고 해당 테이블은 dead tuple 이 100,000 개가 생성될 때 마다 Autovacuum 이 동작하게 됩니다. 해당 설정은 운용 중인 데이터베이스에서 진행해도 문제가 없습니다.
ALTER TABLE huge_table SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE huge_table SET (autovacuum_vacuum_threshold = 100000);
  • 만일 잦은 upldate 와 delete 가 발생하면서, 레코드가 많은 테이블의 경우 위와 같은 설정은 Autovacuum 을 조금 더 자주 실행되게 만들며, 결과적으로 dead tuple 이 지나치게 많이 생기는 것을 예방함으로써 디스크 사용 용량 증가를 최대한 억제하고 성능을 조금이라도 개선하는데 도움이 됩니다.
  • 참고로 테이블에 override 된 설정 정보는 \d+ 명령어를 통해 확인할 수 있습니다.
db=> \d+ huge_table
...
Options: autovacuum_vacuum_scale_factor=0.0, autovacuum_vacuum_threshold=100000

2. autovacuum_vacuum_cost_limit 을 증가시키기

  • postgresql.conf 에는 autovacuum_vacuum_cost_limit 이라는 설정이 있습니다.
  • 이는 다른 설정들과 함께 복합적으로 동작하며, Autovacuum 이 한 번 동작할 때의 동작 시간을 결정하게 됩니다.
  • vacuum_cost 와 관련된 기본 설정은 다음과 같습니다.
vacuum_cost_delay = 0
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 200

autovacuum_vacuum_cost_limit = -1
  • 이는 다음과 같은 의미를 가집니다.
    • autovacuum_vacuum_cost_limit 이 -1 일 경우, 해당 값은 vacuum_cost_limit 을 참조합니다.
    • Autovacuum 이 한 번 실행될 때, 해당 프로세스는 200 의 credit 을 가집니다.
    • page_hit 영역(shared buffer 영역)에 있는 데이터를 vacuuming 할 때 마다 1 의 credit 을 소모합니다.
    • page_miss 영역(디스크 영역)에 있는 데이터를 vacuuming 할 때 마다 10 의 credit 을 소모합니다.
    • page_dirty 영역에 있는 데이터를 vacuuming 할 때 마다 20 의 credit 을 소모합니다.
    • 200 의 credit 이 모두 소진되면 해당 Autovacuum 프로세스는 종료됩니다.
  • 만일 테이블에 dead tuple 이 빈번하게 높은 수준으로 생성될 경우 우리는 Autovacuum 이 한 번 수행될 때 조금 더 오랫동안 동작하도록 해야 할 필요가 있습니다.
  • 여기서 vacuum_cost_page_ 로 시작하는 설정들의 값을 낮추는 방법도 있겠습니다만, 해당 설정들은 전역적으로 적용되는 값이기 때문에 테이블 별로 별도 설정이 가능한 autovacuum_vacuum_cost_limit 값을 변경하는 것을 추천합니다.
  • 다음 예제 쿼리는 특정 테이블의 vacuum credit 을 1,000 으로 상향 조정하는 예제 입니다.
  • 이렇게 설정할 경우 기본 설정보다 약 5배 많은 vacuuming 을 한 번에 처리하게 됩니다.
  • 해당 설정 역시 운용 중인 데이터베이스에서 진행해도 문제가 없습니다.
ALTER TABLE huge_table SET (autovacuum_vacuum_cost_limit = 1000);

3. autovacuum_analyze_scale_factor 을 테이블 별로 별도 설정하기

  • PostgreSQL 은 Autovacuum 데몬을 통해 주기적으로 분석 데이터를 수집합니다.
  • 이 분석 데이터를 기반으로 해당 테이블에 select 쿼리를 수행할 떄의 최적의 실행 계획을 수립합니다.
  • 이 역시 dead tuple 의 존재가 분석 데이터에 좋지 않은 영향을 끼칠 수 있으므로 가능하면 autovacuum_vacuum_scale_factor 및 autovacuum_vacuum_threshold 와 동일한 값으로 설정해 주도록 합니다.
  • 이 값 역시 테이블 별로 설정이 가능하며, 운용 중인 데이터베이스에서 진행해도 문제가 없습니다.
ALTER TABLE huge_table SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE huge_table SET (autovacuum_analyze_threshold = 100000);

4. autovacuum_work_mem, autovacuum_max_workers 의 최적화

  • Autovacuum 이 동작할 떄, autovacuum_work_mem 에 설정된 메모리를 이용하게 됩니다. (해당 값이 -1 일 경우, maintenance_work_mem 을 공유합니다.)
  • 따라서 적절한 maintenance_work_mem 을 설정할 필요가 있습니다.
  • 해당 서버에서 오로지 PostgreSQL 만 동작한다고 가정할 경우 적절한 maintenance_work_mem 의 값은 서버 메모리 1GB 당 50MB 를 할당하는 것이 일반적이지만 절대적이지는 않습니다.
  • autovacuum_max_workers 는 동시에 동작 가능한 Autovacuum 의 프로세스 갯수를 정의합니다.
  • Autovacuum 이 관리해야 할 테이블이 많다면 해당 값을 늘려야 합니다.
  • 늘리지 않을 경우 XID Freeze 가 제때 실행이 되지 않을 수 있으며 이는 치명적인 결과로 이어질 수 있습니다.
  • autovacuum_max_workers 는 변경 시 PostgreSQL 서버의 재시작을 필요로 하므로 Autovacuum 의 동작을 꾸준히 모니터링하며 신중히 변경하는 것이 좋습니다.

Full Vacuum 과 AutoVacuum 의 차이

  • 수동 Full Vacuum 과 AutoVacuum 의 가장 큰 차이점은 해당 테이블에 lock 을 유발하는 지 여부입니다.
  • 그리고 Autovacuum 의 경우 vacuuming 이 꾸준히 잘 수행되고 있다 하더라도 디스크 저장 용량이 줄어들거나 하는 일은 일어나지 않습니다.
  • 그에 반해 수동으로 Full Vacuum 을 수행할 경우 FSM 을 재설정하기 때문에 확실한 디스크 저장 용량 감소 효과를 볼 수 있습니다.
  • 물론 Autovacuum 이 주기적으로 잘 동작한다면 비록 저장 용량이 줄어들지는 않더라도 불필요하게 용량이 증가되는 것을 확실히 막을 수 있습니다.
  • dead tuple 이 FSM 으로 반환되므로 해당 공간은 다른 트랜잭션을 통해 재사용이 되기 떄문입니다.
728x90

이 글을 공유하기

댓글

Designed by JB FACTORY