[PostgreSQL] Vacuum 튜닝

https://blog.naver.com/sssang97/222939603415
어느 정도 써본 사람은 알겠지만, PostgreSQL은 Auto Vacuum이라는 프로세스를 통해 과거의 데이터를 정리하는 작업을 백그라운드에서 주기적으로 수행한다.
Vacuum이 제대로 돌아야지 디스크 공간이 절약되고, 인덱스 정확도나 쿼리 튜닝을 위한 통계 정보가 컴팩트하게 맞춰질 수 있다.

작은 사용 규모에서는 그냥 auto vacuum 도는대로만 둬도 딱히 성능상의 문제가 되지는 않지만, 사용량이 임계점을 넘어가게 되면 vacuum으로 인한 문제가 조금씩 발생하고 auto vacuum만 믿을 수가 없게 된다.

Auto Vacuum이 실행되지 않았을 경우

PostgreSQL은 MVCC 기반으로 데이터를 버전 단위로 관리한다.

그래서 update/delete가 일어나서 사라진 데이터에 대해서도 일단 보관을 하는데, 이런 쓰레기 데이터를 dead tuple이라고 부른다.
이걸 지우는게 auto vacuum의 주된 역할이라 할 수 있겠다.

그리고 auto vacuum이 제때 돌지 않아서 dead tuple이 가득 쌓여있다면, 쿼리 성능이나 디스크 공간이 미쳐날뛰는 문제가 발생할 수 있다.
극단적인 예지만, 예를 들어 테이블에 실제 존재하는 데이터는 1개인데 dead tuple이 100만개가 있으면

select * from foo;

이런 단순한 쿼리도 몇초씩 걸릴 수가 있는 것이다.

그래서 auto vacuum이 작동하는 방식을 어느정도 알아두는 것이 중요하다.

Auto Vacuum 트리거 조건: dead tuple

Auto Vacuum은 항상 마법처럼 완벽한 타이밍에 수행되지는 않는다.

autovacuum 제어용 파라미터는 대표적으로 다음과 같이 2가지가 있다.

autovacuum_vacuum_threshold = 50 # 기본값
autovacuum_vacuum_scale_factor = 0.2 # 기본값

autovacuum_vacuum_scale_factor는 dead tuple이 쌓이는 임계치고, autovacuum_vacuum_threshold는 임계치 기준으로 몇개를 넘었을때 autovacuum을 실행할지를 결정하는 옵션이다.

그러니까 저 기본 옵션대로라면 dead tuple의 비율이 테이블의 모든 row 중에서 20%+50개를 초과하는 경우에 auto vacuum이 실행되는 것이다.

이 비율에 대해서는 딱히 정답이 없다.
vacuum이 너무 드물게 돌면 쓰레기가 한번에 쌓였다가 대규모로 처리되기 때문에 한번 돌때마다 막대한 부하를 감내해야할 수 있다.
그렇다고 너무 빈번하게 돌면 autovacuum이 계속해서 뱅뱅 돌면서 쿼리를 방해할 수도 있다.

Auto Vacuum 트리거 조건: age

이건 dead tuple에 비해서 조금 복잡하다. 그렇지만 꼭 알아야 하는 내용이기도 하다.
이게 잘못되면 과거 데이터가 전부 날라가는 끔찍한 일이 일어나거나, write가 전부 블락될 수도 있기 때문이다.

알다시피, postgresql에서의 update는 사실 update가 아닌 새 버전의 insert다.

https://techblog.woowahan.com/9478/
각 tuple에는 age라는 필드가 있는데, 1부터 시작해서 업데이트될때마다 +1씩 증가되는 값이다.
그리고 xmin, xmax가 transaction ID라고 부르는 필드다.

select를 날릴 때에는 transaction ID가 제일 큰 것을 기준으로 현재 데이터를 가져오기 때문에, 옛날 버전 데이터가 잔뜩 쌓여있어도 최신 데이터를 가져올 수 있는 것이다.

근데 문제는 transaction ID의 할당량이 무한하지 않다는 것이다. xmin와 xmax는 4바이트 정수라서 약 40억개를 사용할 수 있고, 20억은 과거, 20억은 미래를 위해 사용한다.
여기서 할당량을 다 쓰게 된다면, transaction ID는 다시 1부터 시작한다. 그러면 1 이후의 값들은 실제로는 과거 데이터인데 미래의 데이터처럼 보이는 참사가 일어난다. 이 문제를 Transaction ID Wraparound라고 한다.

PostgreSQL은 이 문제를 해결하기 위해 transaction ID 소진시에 Anti Wraparound Vacuum라는 것을 돌린다.

https://techblog.woowahan.com/9478/
그냥 기존의 transaction ID를 다 날려버리고 하드코딩된 frozen 값(2)로 덮어씌워버리는 것이다.

이건 GC의 stop the world처럼 해당 테이블을 거의 통째로 freezing시킨다.
성능적으로 장애에 준하는 결과를 낼 수 있기 때문에, 당연히 가급적 트리거되지 않는 것이 좋다.

Anti Wraparound Vacuum은 다음과 같은 옵션을 통해 제어할 수 있다.

autovacuum_freeze_max_age = 200000000 # 2억
vacuum_freeze_min_age = 50000000 # 5000만
vacuum_freeze_table_age = 150000000 # 1억 5000만

transaction id를 기준으로 트리거되는게 아니라 age를 기반으로 트리거됨을 유의한다.

1. autovacuum_freeze_max_age
   이걸 설정하면 해당 값을 초과하는 age을 가진 테이블에 대해 Anti Wraparound AutoVacuum을 수행한다. 그러니까 기본값에서는 2억을 넘어야 트리거되는 것이다.
   이건 autovacuum을 꺼도 강제로 실행된다.

2. vacuum_freeze_min_age
   vacuum 돌때, 이 값을 초과하는 age의 Tuple을 Anti Wraparound Vacuum 작업의 대상으로 한다.
   autovacuum_freeze_max_age로 인한 전체 프리징이 발생하기 전에 웬만해서는 처리되도록 유도하는 것이다.

3. vacuum_freeze_table_age
   이 값을 초과하는 age의 테이블에 대해 vacuum이 호출될 때 Anti Wraparound Vacuum 작업도 같이 수행한다.
   다수의 테이블들이 autovacuum_freeze_max_age에 걸려서 동시에 Anti Wraparound AutoVacuum이 돌아 전방위적으로 뻗는 것을 방지하기 위해, vacuum이 호출된 테이블들에 대해서만이라도 부분적으로 처리하려는 방어적 옵션.

디스크 반환

vacuum이나 autovacuum이 dead tuple을 정리해서 공간을 확보하는 개념이긴 한데, 여기에도 약간의 함정이 있다.
일반 vacuum은 PostgreSQL의 저장 공간에서 dead tuple을 정리하는거지, 정리한 공간을 OS에도 반환해주는 것은 아니다.

그래서 vacuum으로 dead tuple 10gb어치를 정리하더라도, 실제로 DB가 차지하고 있는 디스크 용량이 10gb 줄어드는게 아니다.
미래에 postgresql이 사용할 것을 대비해서 그냥 둔다.

정말 디스크 용량을 컴팩트하게 줄이고 싶다면 다운타임을 감안해서라도 full vacuum을 돌려야 한다.

응급조치

autovacuum과 쿼리가 서로 심각하게 맞물리게 되면 vacuum도 처리가 지연되고 쿼리도 블락걸린 것처럼 느리게 처리되는 끔찍한 장애상황에 맞닥뜨릴 수 있다.

그럴때는 우선 autovacuum을 끄는 것도 하나의 방법일 수 있다.

ALTER TABLE public.테이블명 SET (autovacuum_enabled = false);

이러면 autovacuum을 특정 테이블에 대해서 비활성화할 수 있다.

그리고 필요에 따라서 수동으로 vacuum을 돌린다.

vacuum VERBOSE public.테이블;

verbose 옵션을 주면 vacuum으로 인한 실행 로그를 자세히 볼 수 있으니, 이걸 기반으로 현재 상황에 대한 인사이틀 얻을 수 있다.

그리고 나서는 autovacuum 옵션을 조절해서 최적화하거나, 쿼리를 최적화하는 방법 등이 있을 것이다.

기타 스크립트

실행중인 vacuum 정보 확인

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;

테이블별 옵션 정보 확인

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

참조
https://medium.com/29cm/postgresql-autovacuum-%EC%9E%A5%EC%95%A0-%EB%8C%80%EC%9D%91%EA%B8%B0-1-8284955c0193
https://postgresql.kr/blog/postgresql_table_bloating.html
https://techblog.woowahan.com/9478/
https://aws.amazon.com/ko/blogs/tech/postgresql-architecture-considerations-for-application-developers-part-1/