수동으로 테이블 배큠 처리 및 분석 - AWS 권장 가이드
배큠 및 정리 작업을 병렬로 실행VACUUM FULL로 전체 테이블 다시 쓰기

수동으로 테이블 배큠 처리 및 분석

데이터베이스가 autovacuum 프로세스에 의해 배큠 처리되는 경우 전체 데이터베이스에서 수동 배큠을 너무 자주 실행하지 않는 것이 가장 좋습니다. 수동 배큠으로 인해 불필요한 I/O 로드 또는 CPU 스파이크가 발생할 수 있으며 데드 튜플을 제거하지 못할 수도 있습니다. 라이브 튜플과 데드 튜플의 비율이 낮거나 autovacuum 사이에 간격이 긴 경우와 같이 실제로 필요한 경우에만 테이블 단위로 수동 배큠을 실행합니다. 또한 최소한의 사용자 활동이 존재하는 경우 수동 배큠을 실행해야 합니다.

또한 autovacuum은 테이블의 통계를 최신 상태로 유지합니다. ANALYZE 명령을 수동으로 실행하면 이러한 통계를 업데이트하는 대신 다시 빌드합니다. 일반 autovacuum 프로세스에 의해 이미 업데이트된 통계를 다시 빌드하면 시스템 리소스 사용률이 발생할 수 있습니다.

다음 시나리오에서는 VACUUM 및 ANALYZE 명령을 수동으로 실행하는 것이 좋습니다.

  • 사용량이 많은 테이블의 피크 시간이 낮을 경우(autovacuum 처리로 충분하지 않을 수 있음).

  • 데이터를 대상 테이블에 대량 로드한 직후. 이 경우 ANALYZE를 수동으로 실행하면 통계가 완전히 다시 빌드되므로 자동 배큠이 시작될 때까지 기다리는 것보다 더 좋은 옵션입니다.

  • 임시 테이블을 배큠하려는 경우(autovacuum은 여기에 액세스할 수 없음).

동시 데이터베이스 활동에서 VACUUM 및 ANALYZE 명령을 실행할 때 I/O 영향을 줄이기 위해 vacuum_cost_delay 파라미터를 사용할 수 있습니다. 대부분의 경우 VACUUM 및 ANALYZE와 같은 유지 관리 명령은 빠르게 완료할 필요가 없습니다. 그러나 이러한 명령이 다른 데이터베이스 작업을 수행하는 시스템의 기능을 방해해서는 안 됩니다. 이를 방지하기 위해 vacuum_cost_delay 파라미터를 사용하여 비용 기반 배큠 지연을 활성화할 수 있습니다. 이 파라미터는 수동으로 실행된 VACUUM 명령에 대해 기본적으로 비활성화되어 있습니다. 활성화하려면 0이 아닌 값으로 설정합니다.

배큠 및 정리 작업을 병렬로 실행

VACUUM 명령 PARALLEL 옵션은 인덱스 배큠 처리 및 인덱스 정리 단계에 병렬 작업자를 사용하며, 기본적으로 비활성화되어 있습니다. 병렬 작업자 수(병렬화 수준)는 테이블의 인덱스 수에 따라 결정되며 사용자가 지정할 수 있습니다. 정수 인수 없이 병렬 VACUUM 작업을 실행하는 경우 병렬화 수준은 테이블의 인덱스 수를 기준으로 계산됩니다.

다음 파라미터는 Amazon RDS for PostgreSQL 및 Aurora PostgreSQL 호환 버전에서 병렬 배큠 처리를 구성하는 데 도움이 됩니다.

참고

PARALLEL 옵션은 배큠 용도로만 사용됩니다. ANALYZE 명령에는 영향을 주지 않습니다.

다음 예제에서는 데이터베이스에서 수동 VACUUM 및 ANALYZE를 사용할 때 데이터베이스 동작을 보여줍니다.

다음은 autovacuum이 비활성화된 테이블 샘플입니다(설명용으로 제공됨, autovacuum 비활성화는 권장되지 않음).

create table t1 ( a int, b int, c int );
alter table t1 set (autovacuum_enabled=false);
apgl=> \d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | integer | | | | plain | |
Access method: heap
Options: autovacuum_enabled=false

테이블 t1에 1백만 개의 행을 추가합니다.

apgl=> select count(*) from t1;
count
1000000
(1 row)

테이블 t1의 통계:

select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+--------
relid         | 914744
schemaname    | public
relname       | t1
seq_scan      | 0
seq_tup_read  | 0
idx_scan      |
idx_tup_fetch |
n_tup_ins     | 1000000
n_tup_upd     | 0
n_tup_del     | 0
n_tup_hot_upd | 0
n_live_tup    | 1000000
n_dead_tup    | 0
n_mod_since_analyze | 1000000
last_vacuum      |
last_autovacuum  |
last_analyze     |
last_autoanalyze |
vacuum_count     | 0
autovacuum_count | 0
analyze_count    | 0
autoanalyze_count | 0

인덱스 추가:

create index i2 on t1 (b,a);

EXPLAIN 명령을 실행합니다(계획 1).

Bitmap Heap Scan on t1 (cost=10521.17..14072.67 rows=5000 width=4)
Recheck Cond: (a = 5)
→ Bitmap Index Scan on i2 (cost=0.00..10519.92 rows=5000 width=0)
Index Cond: (a = 5)
(4 rows)

EXPLAIN ANALYZE 명령을 실행합니다(계획 2).

explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Bitmap Heap Scan on t1 (actual time=0.023..0.024 rows=1 loops=1)
Recheck Cond: (b = 5)
Heap Blocks: exact=1
Buffers: shared hit=4
→ Bitmap Index Scan on i2 (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: (b = 5)
Buffers: shared hit=3
Planning Time: 0.054 ms
Execution Time: 0.076 ms
(9 rows)

테이블에서 autovacuum이 비활성화되고 ANALYZE 명령이 수동으로 수행되지 않았으므로 EXPLAIN 및 EXPLAIN ANALYZE 명령은 서로 다른 계획을 표시합니다. 이제 테이블의 값을 업데이트하고 EXPLAIN ANALYZE 계획을 다시 생성해 보겠습니다.

update t1 set a=8 where b=5;
explain (analyze,buffers,costs off) select a from t1 where b = 5;

이제 EXPLAIN ANALYZE 명령(계획 3)에서 다음을 표시합니다.

apgl=> explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Bitmap Heap Scan on t1 (actual time=0.075..0.076 rows=1 loops=1)
Recheck Cond: (b = 5)
Heap Blocks: exact=1
Buffers: shared hit=5
→ Bitmap Index Scan on i2 (actual time=0.017..0.017 rows=2 loops=1)
Index Cond: (b = 5)
Buffers: shared hit=3
Planning Time: 0.053 ms
Execution Time: 0.125 ms

계획 2와 계획 3 간의 비용을 비교하면 아직 통계를 수집하지 않았기 때문에 계획 및 실행 시간에서 차이가 표시됩니다.

이제 테이블에서 수동 ANALYZE를 실행한 다음 통계를 확인하고 계획을 다시 생성해 보겠습니다.

apgl=> analyze t1
apgl→ ;
ANALYZE
Time: 212.223 ms

apgl=> select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+------------------------------
relid          | 914744
schemaname     | public
relname        | t1
seq_scan       | 3
seq_tup_read   | 1000000
idx_scan       | 3
idx_tup_fetch  | 3
n_tup_ins      | 1000000
n_tup_upd      | 1
n_tup_del      | 0
n_tup_hot_upd  | 0
n_live_tup     | 1000000
n_dead_tup     | 1
n_mod_since_analyze | 0
last_vacuum       |
last_autovacuum   |
last_analyze      | 2023-04-15 11:39:02.075089+00
last_autoanalyze  |
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 1
autoanalyze_count | 0

Time: 148.347 ms

EXPLAIN ANALYZE 명령을 실행합니다(계획 4).

apgl=> explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Index Only Scan using i2 on t1 (actual time=0.022..0.023 rows=1 loops=1)
Index Cond: (b = 5)
Heap Fetches: 1
Buffers: shared hit=4
Planning Time: 0.056 ms
Execution Time: 0.068 ms
(6 rows)

Time: 138.462 ms

테이블을 수동으로 분석하고 통계를 수집한 후 모든 계획 결과를 비교하면 옵티마이저의 계획 4가 다른 계획보다 더 효과적이고 쿼리 실행 시간도 단축된다는 점을 알 수 있습니다. 이 예제에서는 데이터베이스에서 유지 관리 활동을 실행하는 것이 얼마나 중요한지 보여줍니다.

VACUUM FULL로 전체 테이블 다시 쓰기

FULL 파라미터를 사용하여 VACUUM 명령을 실행하면 테이블의 전체 콘텐츠를 추가 공간 없이 새 디스크 파일에 다시 쓰고 미사용 공간을 운영 체제에 반환합니다. 이 작업은 속도가 훨씬 느리며 각 테이블에 ACCESS EXCLUSIVE 잠금이 필요합니다. 또한 테이블의 새 사본을 쓰고 작업이 완료될 때까지 이전 복사본을 릴리스하지 않기 때문에 추가 디스크 공간이 필요합니다.

VACUUM FULL은 다음과 같은 사례에 유용할 수 있습니다.

  • 테이블에서 상당한 양의 공간을 회수하려는 경우.

  • 프라이머리 키가 아닌 키 테이블에서 팽창 공간을 회수하려는 경우.

데이터베이스가 가동 중지 시간을 허용할 수 있는 경우 프라이머리 키가 아닌 키 테이블이 있는 경우 VACUUM FULL을 사용하는 것이 좋습니다.

VACUUM FULL에는 다른 작업보다 더 많은 잠금이 필요하기 때문에 중요한 데이터베이스에서 실행하는 데 더 많은 비용이 듭니다. 이 메서드를 교체하기 위해 다음 섹션에 설명된 pg_repack 확장을 사용할 수 있습니다. 이 옵션은 VACUUM FULL과 유사하지만 최소한의 잠금이 필요하며 Amazon RDS for PostgreSQL 및 Aurora PostgreSQL 호환 모두에서 지원됩니다.