PostgreSQL 튜닝 - Autovacuum 최적화에 대하여

엔라이즈 개발팀은 2014년부터 PostgreSQL 을 꾸준히 사용해 왔습니다. 그동안 PostgreSQL 을 사용하면서 알게 된 다양한 최적화 방법들을 앞으로 작성할 여러 글들을 통해 공유 하고자 합니다. 이번 글에서는 PostgreSQL 의 Autovacuum 과 이를 최적화 하는 방법에 대해 이야기 해 보도록 하겠습니다.

Autovacuum, Vacuum(Full) 에 대해

PostgreSQL 에는 Autovacuum, 혹은 Vacuum 이라는 개념이 존재합니다. 이는 Oracle, MariaDB(MySQL), SQLSERVER 등에는 존재하지 않는 개념이기 때문에 처음 PostgreSQL 을 접하시는 분들은 Vacuum 과 관련된 설정들을 소홀히 하다가 데이터베이스의 트랜잭션이 증가하였을 때 예상밖의 느린 성능을 겪게 될 수 있습니다. 따라서 PostgreSQL 을 안정적으로 운용하기 위해서는 반드시 Autovacuum(Vacuum) 에 대해 이해할 필요가 있습니다.

PostgreSQL 의 Autovacuum 은 크게 두 가지 상황에서 동작합니다.¹ 그 중 하나는 XID wraparound 를 방지하기 위해 XID 를 고정(Freeze) 할 때 입니다. 다만 이 경우는 본 글에서 다루고자 하는 주제와 크게 관련이 없으며, XID 가 임계점에 도달할 경우 강제로 동작하므로 이번 글에서는 다루지 않겠습니다. Autovacuum 이 동작하는 두 번째 경우는 바로 임계점 이상으로 늘어난 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 지원을 위해 다음과 같이 동작합니다.

1. FSM 에 여유가 있는지 확인합니다. 없으면 FSM 을 추가적으로 확보합니다.
2. FSM 의 빈 공간에 업데이트 될 데이터를 기록합니다. 이 떄 새로운 tuple 이 추가 됩니다.
3. 기록이 완료되면, 기존 column(혹은 row) 를 가리키는 포인터를 새로 기록된 tuple 로 변경합니다.
4. 업데이트 이전 정보가 기록된 공간은 더 이상 참조가 되지 않게 됩니다. 이 참조가 되지 않는 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 이 만들어 지게 됩니다.

Dead Tuple 이 만들어내는 부수효과에 대하여

위 내용을 조금 거칠게 요약하면 PostgreSQL 에서 update 는 사실 상 insert 와 동일한 동작이라 볼 수 있으며, delete 역시 수행하더라도 해당 데이터가 저장된 tuple 은 Vacuum 없이는 FSM 으로 반환되지도 않으며, 저장소에서 삭제되지도 않습니다. 이는 PostgreSQL 에서 Data Bloat 이라고 불리우는, 쉽게 말해 데이터베이스의 저장 공간이 불어나는 효과를 가져오게 됩니다. 이는 여러 문제점을 야기합니다.

가장 큰 문제는 저장 공간이 무한정 늘어나는 점입니다. update/delete 트랜잭션이 자주 일어나면 일어날 수록, 저장공간 사용량은 급속도로 불어나며 이로 인해 추가적인 문제점이 발생하게 됩니다. PostgreSQL 은 select 트랜잭션을 수행할 경우 live tuple 을 디스크에서 읽어들일 때 일정한 용량의 chunk 단위로 파일을 읽어 들이게 됩니다. 만일 이 때 읽어들인 chunk 에 정리되지 않은 dead tuple 이 포함 되어 있을 경우 원하는 live tuple 을 읽기 위해 더 많은 디스크 I/O 가 발생하게 됩니다. 그리고 증가된 디스크 I/O 는 결과적으로 select 트랜잭션의 성능 저하를 가져오게 됩니다. 문제는 여기서 끝나지 않습니다. PostgreSQL 은 주기적으로 테이블의 통계정보를 갱신하는 작업을 수행하여 이를 통해 최적의 쿼리 계획을 수립합니다. 그런데 dead tuple 로 인해 쿼리 성능이 저하되는 경우가 자주 발생할 경우 통계 수집기는 인덱스가 멀쩡히 있음에도 인덱스를 사용하지 말라는 황당한 판단을 내리는 경우도 발생하며, 결과적으로 select 성능을 더욱 떨어트릴 수 있습니다.

물론 PostgreSQL 에는 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 증가를 최대한 억제할 수 있는지 알아보도록 하겠습니다.

1. autovacuum_vacuum_scale_factor 를 0 으로 설정하기

Autovacuum 을 최적화 하는 가장 간단한 방법은 autovacuum_vacuum_scale_factor 를 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);

만일 잦은 update 와 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배 많은 vacumming 을 한 번에 처리하게 됩니다. 해당 설정 역시 운용 중인 데이터베이스에서 진행해도 문제가 없습니다.

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 으로 반환되므로 해당 공간은 다른 트랜잭션을 통해 재사용이 되기 때문입니다.

Data Bloat 문제를 Autovacuum 이 근본적으로 해결할 수 없는 것에 대한 대안으로 pg_repack 이라는 확장 모듈이 있긴 합니다. 이 모듈을 이용해서 특정 테이블을 repack 을 하게 되면 해당 테이블의 복제본을 새로 생성한 후 테이블을 교체하는 작업을 진행하기 때문에 운용 중인 데이터베이스에서도 사용할 수는 있습니다. 하지만 repack 을 수행할 때 repack 시작 때와 종료 때 순단현상이 발생하며, repack 진행 중에는 데이터베이스의 리소스가 심각하게 소비됩니다. 말 그대로 운용 중인 데이터베이스에서 “쓸 수 있다” 정도이지, pg_repack 을 통해 완전한 무중단으로 테이블을 Reorganize 할 수는 없습니다. AWS RDS 문서 에도 pg_repack 에 대한 소개가 나와 있는데 이런 경고가 없는 점은 분명 아쉬운 점입니다.

마치며

일반적으로 PostgreSQL 을 최초 설치 후에 서버의 CPU, 메모리 사양에 맞추어 캐시나 버퍼 사이즈, 사용할 코어 수 등의 설정을 진행하게 됩니다. 하지만 Autovacuum 과 관련된 설정들은 데이터베이스를 운용하며 발생하는 다양한 환경에 맞추어 설정을 진행해야 하며, 이 와중에 많은 시행착오를 겪게 됩니다.

엔라이즈 개발팀은 PostgreSQL 의 Autovacuum 을 최적화 하며 많은 성능적 이득을 얻을 수 있었습니다. 또한 PostgreSQL 을 운용함에 있어 Autovacuum 에 대한 개념을 이해하는 것은 매우 중요하다라는 것 역시 깨달을 수 있었습니다. 이는 완전 관리형 데이터베이스(AWS Aurora, AWS RDS 등) 를 사용한다고 하더라도 동일하게 중요하기 때문에 PostgreSQL 을 운용하며 Autovacuum 에 대한 이해도를 높이는 것은 선택이 아니라 필수라고 생각합니다.⁵

제공하는 서비스마다 데이터베이스가 운용되는 환경이 천차만별이므로 이렇게 하면 좋다! 라는 정답도 없기에 Autovacuum 을 꾸준히 모니터링하고 최적화 하는 것은 매우 중요한 것 같습니다. 이번 글이 PostgreSQL 을 운용하시는 분들에게 작게나마 도움이 되었으면 합니다.

우리 팀에 흥미를 가진 개발자를 찾고 있습니다! 관심 있는 분들의 지원을 기다립니다. :)

감사합니다.