Introduction
VACUUM의 옵션 중에는 VACUUM 수행 시 INDEX를 정리해주는 INDEX_CLEANUP 옵션이 존재합니다.
INDEX_CLEANUP에 대해 알아보고 이 옵션을 사용했을 때와, 직접 INDEX Drop -> VACUUM(or VACUUM FULL) -> INDEX 재생성을 하는 방식을 사용해보면서 성능 상 이점이 있을지 테스트를 통해 확인해보겠습니다.
INDEX_CLEANUP 옵션
VACUUM은 데이터베이스 테이블에서 데드 튜플(dead tuples)을 제거하는 명령어입니다.
일반적으로 VACUUM 수행 시 테이블에 데드 튜플이 매우 적다면 INDEX 정리를 건너뛰게 됩니다.
INDEX_CLEANUP 옵션은 이 VACUUM 명령에 대한 옵션으로, INDEX 정리 작업 수행 방법을 결정할 수 있습니다.
- INDEX_CLEANUP은 {auto, on, off} 중 하나를 설정할 수 있습니다.
- auto : 기본값으로, 적절한 경우(데드 튜플이 매우 적은 경우)에 INDEX 정리를 건너뜁니다.
- on : 테이블에 존재하는 데드 튜플이 1개 이상일 때, VACUUM이 모든 데드 튜플을 INDEX에서 제거하도록 강제합니다.
- on으로 설정한다면 불필요하게 INDEX 정리 작업을 수행할 수 있어 성능에 영향을 미칠 수 있습니다.
- off : 테이블에 데드 튜플이 많더라도 INDEX 정리를 항상 건너뜁니다.
- 하지만 INDEX 정리가 정기적으로 수행되지 않으면 성능이 저하될 수 있습니다. 왜냐하면 테이블이 수정됨에 따라 INDEX에 데드 튜플이 쌓이고, 테이블 자체는 INDEX 정리가 완료될 때까지 제거할 수 없는 데드 라인 포인터를 쌓기 때문입니다.
테스트 시나리오
- 서비스가 중단된 상태로 고정하고, 트랜잭션이 수행되지 않는 상태에서 수행합니다.
1. autovacuum = off 로 설정합니다. (on이라면, autovacuum 수행으로 인해 정확한 결과를 얻기 어렵습니다.)
2. 테스트 테이블과 INDEX를 생성합니다.
3. 테스트 대상 테이블에 Update를 수행하여 데드 튜플과 INDEX 데드 튜플을 생성합니다.
4. 위의 상태를 고정하기 위해 스냅샷을 남깁니다.
5. 각각의 시나리오 수행 전, 스냅샷 상태로 되돌려 동일한 환경에서 아래 시나리오를 수행하여 성능 차이를 비교합니다.
- 1번 시나리오 (VACUUM 수행 시 차이)
1-1. VACUUM 수행 시 INDEX_CLEANUP 옵션을 적용하고 수행합니다.
1-2. INDEX를 Drop -> VACUUM 수행 -> INDEX 생성
=> 1-1과 1-2를 비교하여 결과를 도출합니다.
VACUUM FULL 수행 시, 작업 완료 전 Rebuilding Index 수행 단계로 Reindex를 수행하는 과정이 포함되어 있습니다.
그러므로 2번 시나리오는 다음과 같습니다. - 2번 시나리오 (VACUUM FULL 수행 시 차이)
2-1. VACUUM FULL을 수행합니다.
2-2. INDEX를 Drop -> VACUUM FULL 수행 -> INDEX 생성
=> 2-1과 2-2를 비교하여 결과를 도출합니다.
환경 구축을 한 뒤 테스트를 진행하겠습니다.
PostgreSQL 설정은 아래와 같습니다.
테스트 대상 테이블과 INDEX 생성 코드, Update문은 아래와 같습니다.
시나리오 1-1
- INDEX Drop 없이 INDEX_CLEANUP 옵션을 사용하여 VACUUM 수행, 데드 튜플 정리와 INDEX 재정리를 함께 수행합니다.
pg_ctl start
date
psql -c "VACUUM (INDEX_CLEANUP on, VERBOSE) idx_test_table;"
date
pg_ctl stop시나리오 1-2
- INDEX Drop -> VACUUM 수행 -> INDEX를 재생성 합니다.
pg_ctl start
date
psql -c "drop index idx_random_num;"
psql -c "alter table idx_test_table drop CONSTRAINT idx_test_table_pkey;"
psql -c "VACUUM (VERBOSE) idx_test_table;"
psql -c "alter table idx_test_table add constraint idx_test_table_pkey primary key (series_num);"
psql -c "create index idx_random_num ON idx_test_table (random_num);"
date
pg_ctl stop
시나리오 2-1
- INDEX Drop 없이 VACUUM FULL을 수행하여 데드 튜플을 제거하고, INDEX REBUILD를 수행합니다.
pg_ctl start
date
psql -c "VACUUM (FULL, VERBOSE) idx_test_table;"
date
pg_ctl stop시나리오 2-2
- INDEX Drop -> VACUUM FULL 수행 -> INDEX를 재생성 합니다.
pg_ctl start
date
psql -c "drop index idx_random_num;"
psql -c "alter table idx_test_table drop CONSTRAINT idx_test_table_pkey;"
psql -c "VACUUM (FULL, VERBOSE) idx_test_table;"
psql -c "alter table idx_test_table add constraint idx_test_table_pkey primary key (series_num);"
psql -c "create index idx_random_num ON idx_test_table (random_num);"
date
pg_ctl stop테스트 결과
- 각각의 시나리오 수행 결과입니다. 두 가지 서버로 테스트를 진행해보았습니다.
- 서버에 따라 수행 시간 차이가 날 수 있으나, 각각의 시나리오에 대한 수행 시간 비교를 해주시면 됩니다.
- 테스트 서버 1
- Spec : 8Core 32GB Memory + SSD
| seq | 시나리오 1-1 | 시나리오 1-2 |
| 1 | 0:03:46 | 0:02:23 |
| 2 | 0:03:42 | 0:02:26 |
| 3 | 0:03:53 | 0:02:00 |
| 4 | 0:03:44 | 0:01:57 |
| 5 | 0:03:47 | 0:03:01 |
| AVG | 0:03:46 | 0:02:21 |
| seq | 시나리오 2-1 | 시나리오 2-2 |
| 1 | 0:03:52 | 0:05:31 |
| 2 | 0:04:32 | 0:03:40 |
| 3 | 0:05:28 | 0:04:37 |
| 4 | 0:04:19 | 0:06:38 |
| 5 | 0:06:29 | 0:03:55 |
| AVG | 0:04:56 | 0:04:52 |
- 테스트 서버 2
- Spec : 8Core 32GB Memory + HDD
| seq | 시나리오 1-1 | 시나리오 1-2 |
|---|---|---|
| 1 | 0:14:27 | 0:12:11 |
| 2 | 0:13:08 | 0:10:01 |
| 3 | 0:13:16 | 0:10:34 |
| 4 | 0:13:30 | 0:10:41 |
| 5 | 0:15:19 | 0:12:20 |
| AVG | 0:13:56 | 0:11:09 |
| seq | 시나리오 2-1 | 시나리오 2-2 |
|---|---|---|
| 1 | 0:05:41 | 0:06:22 |
| 2 | 0:05:39 | 0:04:51 |
| 3 | 0:06:13 | 0:05:16 |
| 4 | 0:06:51 | 0:05:30 |
| 5 | 0:05:30 | 0:06:42 |
| AVG | 0:05:59 | 0:05:44 |
- 테스트 서버 3
- Spec : 8Core 4GB Memory + HDD
- DATA 1/3
| seq | 시나리오 1-1 | 시나리오 1-2 |
|---|---|---|
| 1 | 00:10:02 | 00:08:11 |
| 2 | 00:10:19 | 00:09:36 |
| 3 | 00:09:41 | 00:08:42 |
| 4 | 00:11:16 | 00:08:20 |
| 5 | 00:09:29 | 00:09:49 |
| AVG | 00:10:09 | 00:08:55 |
| seq | 시나리오 2-1 | 시나리오 2-2 |
|---|---|---|
| 1 | 00:09:13 | 00:08:40 |
| 2 | 00:10:32 | 00:08:25 |
| 3 | 00:09:30 | 00:09:27 |
| 4 | 00:08:52 | 00:08:34 |
| 5 | 00:09:01 | 00:08:40 |
| AVG | 00:09:25 | 00:08:45 |
결론
시나리오 1-1과 시나리오 1-2는 VACUUM 수행 간에 INDEX를 정리하는 기능을 강제로 사용하는 방식(INDEX_CLEANUP 옵션)과 직접 INDEX를 재생성 하는 방식의 성능 비교를 위한 테스트를 진행했습니다.
그 결과 시나리오 1-2의 경우, 테스트 서버 1에서는 시나리오 1-1보다 약 37% 정도 더 높은 성능을 보여줬고
테스트 서버 2의 경우 약 20% 정도, 테스트 서버 3의 경우 약 12% 정도 더 높은 성능을 보여주었습니다.
(SSD, HDD, DISK I/O 속도 등에 따라 결과는 상이할 수 있습니다.)
INDEX_CLEANUP 옵션은 기존 INDEX 데드 튜플을 재정리하는 과정이 포함되어 있어 그만큼 DISK I/O가 다량 발생하게 되므로, INDEX_CLEANUP 옵션 사용보다는 INDEX를 삭제하고 재생성하는 것이 더 빠른 수행이 가능한 것을 확인할 수 있습니다.
하지만, 시나리오 1-2와 같은 방식은 테이블과 INDEX의 개수가 적어서 간단한 수행이 필요한 경우가 아니라면 권장할 수 없습니다.
왜냐하면 INDEX 삭제와 재생성 과정에서 관리자에게 더 많은 번거로움이 발생할 것이며, 실제 서비스 운영 중에 시나리오 1-2와 같이 INDEX를 삭제한다는 것은 사실상 불가능하기 때문입니다.
따라서, INDEX_CLEANUP 옵션을 사용하여 VACUUM을 수행하는 것이 합리적이라고 할 수 있습니다.
시나리오 2-1과 2-2의 경우 VACUUM FULL의 수행이므로, 서비스 운영 중에는 수행이 불가능합니다. 또한 성능 차이도 거의 없기 때문에,
INDEX를 삭제하고 생성하는 번거로움을 겪기보다는 단순히 VACUUM FULL을 수행하는 것이 효율적입니다.
✔ 정리하자면, VACUUM의 목적 자체를 확실히 하여 데드 튜플 정리가 필요하다면 VACUUM을 수행하는 것이 좋겠으며, INDEX 재정리가 필요하다면 REINDEX를 따로 수행할 수 있으므로 위와 같이 INDEX를 삭제하고 새롭게 생성하는 case의 수행은 권장하지 않습니다.
