테이블 수동 정리 및 분석 - AWS 권장 가이드
vacuum 및 정리 작업을 병렬로 실행VACUUM FULL로 전체 테이블 다시 작성

기계 번역으로 제공되는 번역입니다. 제공된 번역과 원본 영어의 내용이 상충하는 경우에는 영어 버전이 우선합니다.

테이블 수동 정리 및 분석

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

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

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

  • 사용량이 많은 테이블의 피크 시간이 낮을 때는 자동 정리가 충분하지 않을 수 있습니다.

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

  • 임시 테이블을 정리하려면(autovacuum은 여기에 액세스할 수 없음).

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

vacuum 및 정리 작업을 병렬로 실행

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

다음 파라미터는 Amazon RDS for PostgreSQL 및 Aurora PostgreSQL-Compatible에서 병렬 vacuum을 구성하는 데 도움이 됩니다.

참고

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

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

다음은 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이 비활성화되었고 EXPLAIN EXPLAIN ANALYZE 명령이 수동으로 수행되지 않았기 때문에 및 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 호환 모두에서 지원됩니다.