PostgreSQL 에는 다른 DBMS 에서는 볼 수 없는 Vacuum 이라는 개념이 존재합니다.
이는 MVCC 구현 방법이 ORACLE이나 MySQL 같은 다른 DBMS와 다르다는 차이점에서 오는 문제점을 해결하기 위한 PostgreSQL 만의 특별한 동작인데요.
PostgreSQL은 Vacuum 동작을 자동으로 수행하는 AutoVacuum을 통해 아래 두가지 작업을 수행합니다.
- transaction id wraparound 방지
- 임계치 이상으로 발생한 dead tuple을 정리하여 FSM (Free Space Map) 으로 반환
위 두가지를 수행하지 않고 방치하면 어떤 문제점이 생기는지에 대해서는 PostgreSQL을 소개하며 간단히 다룬 적이 있으니 참고하시기 바랍니다.
https://kimdubi.github.io/postgresql/pg_intro2/
PostgreSQL을 운영하면서 꼭 알아둬야하는 동작인 AutoVacuum에 대해서 살펴보겠습니다.
dead tuple ?
tuple이란 다른 RDBMS의 row, record와 동일한 개념입니다. PostgreSQL 에서 모든 데이터는 tuple 이라 불리는 형태로 저장이 되고 tuple 은 live tuple, dead tuple 로 나뉩니다.
이중에서 더 이상 참조되지 않는 tuple 을 dead tuple 이라 부르는데 이런 dead tuple은 PostgreSQL의 MVCC의 구현방식으로 인해 생겨납니다.
MVCC 구현을 위해 PostgreSQL의 update 동작은 아래와 같이 동작하게 됩니다.
- FSM (FreeSpaceMap)에 사용가능한 공간이 있는지 확인, 없으면 FSM을 추가 확보
- FSM의 사용가능한 공간에 update 된 데이터를 기록함 (insert 처럼 동작)
- update 된 데이터의 저장이 완료되면 기존 tuple을 가리키는 포인터를 새로 기록된 tuple 로 변경함
위 과정에서 update 가 완료되면 기존 원본 data를 저장한 tuple 은 어디에도 참조되지 않는 tuple이 되는데 이를 dead tuple 이라고 합니다.
문제는 이 dead tuple이 자동으로 FSM으로 반환되지 않기 때문에 쓸데없는 공간을 차지하게 되고 select 성능에도 영향을 줍니다.
다른 DBMS에서도 데이터를 읽을 때 block 단위로 가져오듯이 PostgreSQL 은 chunk 단위로 가져오는데 chunk 에 live tuple 뿐만 아니라 dead tuple도 포함되어 있습니다.
필요한 live tuple 을 읽기 위해 더 많은 chunk를 읽게 되고 이는 곧 더 많은 디스크 I/O 발생으로 이어지게 됩니다.
이러한 이슈를 해결하는 동작이 바로 Vacuum 이고 Vacuum을 자동으로 수행하는 것이 AutoVacuum 입니다.
AutoVacuum
AutoVacuum은 PostgreSQL configure default로 ON 이고 관련된 설정은 크게 아래 세가지 입니다.
더 많은 설정은 아래 글에서 확인할 수 있습니다.
https://kimdubi.github.io/postgresql/postgresql_bg_process/
autovacuum_max_workers : autovacuum 을 병렬로 수행할 최대 서브 프로세스 수 설정
autovacuum_vacuum_threshold / autovacuum_analyze_threshold : 테이블에서 업데이트 된 튜플 수의 임계 값 설정 , 튜플 업데이트 수가 이 값을 초과하면 vacuum / analyze 수행 필요
autovacuum_vacuum_scale_factor / autovacuum_analyze_scale_factor : 설정한 배율만큼 테이블 내 데이터 변경 시, autovacuum이 수행될 테이블로 추가됨
AutoVacuum은 dead tuple의 개수가
테이블의 총 tuple 개수 * autovacuum_vacuum_scale_factor (deafult = 0.2) + autovacuum_vacuum_threshold ( deafult = 50) 를 초과할 때 수행됩니다.
예를 들어 tb_test 라는 테이블에 100,000 건의 tuple이 있을 때 (100,000 * 0.2) + 50 = 20,050 개의 dead tuple이 생길 경우 Autovacuum 이 동작하게 됩니다.
- 테이블 별 dead 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;
relname | n_live_tup | n_dead_tup | ratio
-----------------+------------+------------+-------------------
commands_detail | 11950 | 2139 | 0.178995815899582
commands | 11950 | 2105 | 0.176150627615063
(2 rows)
=> 위 쿼리는 n_dead_tup > 1000 조건을 추가하여 dead tuple 이 1000개 이상인 테이블들만 조회했습니다.
그 결과로 두개 테이블들이 나왔고 dead tuple ratio 가 0.17 인 것으로 보아 곧 autovacuum_vacuum_scale_factor (deafult = 0.2) 에 도달하여 AutoVacuum이 수행될 것으로 보입니다.
- 테이블 별 AutoVacuum 마지막 수행일자 확인하는 쿼리
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY relname asc;
relname | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
--------------------------------+-------------+-------------------------------+--------------+-------------------------------
audits | | | | 2020-09-03 15:06:09.252059+09
client_configs | | 2020-10-06 15:11:16.850492+09 | | 2020-10-06 15:11:16.886793+09
client_configs_to_hosts | | | | 2020-09-03 15:06:11.632468+09
cm_version | | 2020-11-08 03:01:13.820883+09 | | 2020-11-14 15:11:52.012185+09
command_schedules | | | | 2020-11-13 23:00:24.825538+09
commands | | 2020-10-06 15:09:19.283071+09 | | 2020-11-10 02:10:12.876896+09
commands_detail | | 2020-10-03 20:47:58.749046+09 | | 2020-10-17 15:29:20.062466+09
users | | | |
.
.
AutoVacuum 최적화
위에서 본 것처럼 AutoVacuum 이 제대로 돌아가고 있는 것 같지만 AutoVacuum의 default 설정을 그대로 운영환경에서 사용하기엔 무리가 있습니다.
AutoVacuum은 dead tuple의 개수가 테이블의 총 tuple 개수 * autovacuum_vacuum_scale_factor (deafult = 0.2) + autovacuum_vacuum_threshold ( deafult = 50) 를 초과할 때 수행되는데
만약 테이블이 1억건이라면 dead tuple 이 20,000,050 2천만 건이나 되어야 AutoVacuum 이 수행됩니다.
이렇게 많은 tuple을 한번에 Vacuum 하는 것은 너무 비효율적이기 때문에 운영 환경에서는 AutoVacuum 관련 설정을 default로 사용하는 것은 추천드리지 않습니다.
autovacuum_vacuum_scale_factor 를 0 으로 설정하기
postgres=# alter system set autovacuum_vacuum_scale_factor = 0;
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# select context,name,setting from pg_settings where name ='autovacuum_vacuum_scale_factor';
context | name | setting
---------+--------------------------------+---------
sighup | autovacuum_vacuum_scale_factor | 0
sighup | autovacuum_vacuum_threshold | 50
=> 가장 간단한 방법입니다.
autovacuum_vacuum_scale_factor = 0 으로 설정하면 AutoVacuum이 autovacuum_vacuum_threshold 값만 보고 동작하게 되어 모든 테이블에 대해 일관성 있게 동작할 수 있습니다.
그러나 postgresql.conf 설정이나 위 처럼 전역으로 설정하는 건 모든 테이블에 일괄 적용 되기 때문에 세심한 방법은 아닐 수 있는데요.
그럴 땐 아래처럼 테이블 각각에 설정해줄 수 있습니다.
testdb2=# alter table test set (autovacuum_vacuum_scale_factor=0);
ALTER TABLE
testdb2=# alter table test set (autovacuum_vacuum_threshold=100);
ALTER TABLE
testdb2=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------------------------------+---------+--------------+-------------
a | integer | | not null | nextval('test_a_seq'::regclass) | plain | |
Options: autovacuum_vacuum_scale_factor=0, autovacuum_vacuum_threshold=100
autovacuum cost 설정
autovacuum_vacuum_cost_limit = -1
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에 대한 cost 설정은 vacuum_cost_limit 을 참조
- vacuum_cost_limit : Autovacuum 이 한 번 실행될 때, 해당 프로세스는 200 의 cost를 가짐
- vacuum_cost_page_hit : Shared_buffer에 있는 데이터를 vacuuming 할 때 마다 1 의 cost 소모
- vacuum_cost_page_miss : Shared_buffer 가 아닌 disk 에 존재하는 데이터를 vacuuming 할 때 마다 10 의 cost 소모
- vacuum_cost_page_dirty : page_dirty 영역에 있는 데이터를 vacuuming 할 때 마다 20 의 cost 소모
- vacuum_cost_delay : vacuum_cost_limit 를 모두 소모하면 vacuum_cost_delay 만큼 sleep 후 다시 동작, 0이면 sleep 하지 않고 계속 동작
운영하는 서비스에서 Vacuum이 Disk I/O 에 부담을 많이 주게 된다면 vacuum_cost_delay를 10ms 등으로 주어 autovacuum 수행 중 term을 두게 하는 것이 좋습니다.
너무 많은 term은 autovacuum 완료시간에 영향을 주기 때문에 vacuum_cost_limit = 1000 등으로 늘릴 수 있습니다.
마찬가지로 table 단위 설정이 가능합니다.
autovacuum_work_mem, autovacuum_max_workers
postgres=# select name,setting from pg_settings where name='autovacuum_work_mem';
name | setting
---------------------+---------
autovacuum_work_mem | -1
(1 row)
=> autovacuum_work_mem 메모리는 autovacuum 수행 중 사용하는 공간으로 -1 이면 maintenance_work_mem 공간을 사용합니다.
maintenance_work_mem는 보통 서버 메모리의 1/16 정도로 설정합니다.
autovacuum_max_workers 는 병렬로 수행할 AutoVacuum 프로세스의 개수로 default는 3개 입니다. 동적으로 설정되지 않고 변경 시 서버 재수행이 필요합니다.
Vacuum 과 AutoVacuum 의 차이점
지금까지 AutoVacuum 에 대해서 알아보았는데 Vacuum 과의 차이점은 무엇일까요?
Vacuum은 실제 사용자에 의해 수동으로 수행되는데 이 때 해당 테이블에 LOCK을 걸게 됩니다. 또한 dead tuple에 의해 늘어난 공간을 실제로 디스크 회수 까지 수행합니다.
autovacuum 에 의한 정리는 디스크 회수는 하지 않고 dead tuple 를 FSM으로 반환하여 해당 공간을 재사용될 수 있게끔만 동작합니다.
MySQL이나 ORACLE에서 delete 처럼 동작한다고 생각하면 됩니다.
이러한 AutoVacuum의 한계 때문에 pg_repack 이라는 extension 도 있습니다. 테이블의 복제본을 새로 생성하고 데이터 copy 후 테이블을 교체하는 방식으로 동작하는데요
써보지는 않았지만 MySQL의 pt-osc 처럼 원본테이블에 PK나 unique key 가 존재해야 하며 원본테이블의 DML은 trigger 로 복제 테이블에 전달하는 원리로 보입니다.
정리
정리하자면 PostgreSQL만의 MVCC 구현 방식으로 인해 traction id wraparound , dead tuple 증가 같은 이슈가 발생하고
이를 해결하기 위해 Vacuum을 자동으로 수행하는 AutoVacuum 이라는 동작이 있습니다.
AutoVacuum의 default 발동 조건은 운영 환경에서 사용하기엔 무리가 있어 autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold를 적당한 값으로 설정하는 것을 추천하고
이 외에도 autovacuum cost나 autovacuum_max_workers 같은 설정을 튜닝하는 방법도 있습니다.