Aurora PostgreSQL을 처음 운영하면서 겪은 VACUUM과 관련된 이슈 케이스와 그에 대한 검토 및 적용 사례를 정리해보았습니다.
1. Long Transaction 으로 인한 VACUUM 지연과 이로 인한 쿼리 지연 케이스
이슈 내용
postgreSQL의 MVCC 특징으로 인한 롱 트랜잭션 발생이 Read 성능에 영향을 주는 케이스에 대한 내용입니다.
특정 인스턴스의 여러 쿼리들의 AVG Duration이 우상향으로 증가하는 것을 확인하였습니다.
(약 16:00 쯤 부터 전체적인 우상향)
이에 따라 CPU가 증가하였으며 특이사항으로 TPS 등의 지표는 증가하지 않고 tuple_returned 지표가 급격하게 상승하였습니다.
 |
 |
 |
 |
CommitThroughput : TPS 지표
Tuple_Fetched : 클라이언트에게 반환 된 튜플의 수
(공식 문서 : Number of live rows fetched by index scans in this database)
Tuple_Returned : DB에서 조회하는데 사용된 튜플의 수
(공식 문서 : Number of live rows fetched by sequential scans and index entries returned by index scans in this database)
관련 문서
https://www.datadoghq.com/blog/aws-rds-postgresql-monitoring/#metrics-to-watch (Rows fetched vs. rows returned by queries to the database 항목)
불필요하게 조회된 튜플이 많다고 생각이 되었고 지연 시간이 점차 증가하는 쿼리들이 공통적으로 테이블A를 조회하고 있어서
seq_scan 또는 dead_tuple의 영향이 있을 수 있다고 추측 후 확인을 진행하였습니다.
확인 과정
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
seq_scan,idx_scan,
pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
pg_stat_get_live_tuples(c.oid) AS live_tuple,
pg_stat_get_dead_tuples(c.oid) AS dead_tuple,
s.n_mod_since_analyze,
s.n_ins_since_vacuum ,
s.last_autovacuum AS Autovacuum,
s.last_autoanalyze AS Autoanalyze,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
pg_size_pretty(pg_relation_size(c.oid)) as relation_size
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
join pg_stat_user_tables as S on C.relname = S.relname
WHERE c.relname NOT LIKE 'pg_%';
(TABLEA 테이블 시간 순 결과)
schema_name | table_name | seq_scan | idx_scan | total_tuple | live_tuple | dead_tuple | n_mod_since_analyze | n_ins_since_vacuum | autovacuum | autoanalyze | total_relation_size | relation_size
public | TABLEA | 27577 | 58924906 | 153505 | 44214 | 109291 | 402 | 4 | 2025-06-13 09:40:42.694594+00 | 2025-06-13 09:40:18.944225+00 | 602 MB | 122 MB
public | TABLEA | 27577 | 58937416 | 156068 | 44244 | 111824 | 821 | 0 | 2025-06-13 09:44:49.668139+00 | 2025-06-13 09:43:35.56026+00 | 605 MB | 124 MB
public | TABLEA | 27577 | 58945416 | 157676 | 44256 | 113420 | 311 | 3 | 2025-06-13 09:47:24.859644+00 | 2025-06-13 09:47:01.028134+00 | 606 MBDEAD_TUPLE이 AUTO_VACUUM 이후 제거되지 않고 있는 것 확인.
수동 VACUUM 진행
VACUUM (verbose) TABLEA;
INFO: vacuuming "TABLEA"
INFO: table "TABLEA": index scan bypassed: 283 pages from table (1.79% of total) have 336 dead item identifiers
INFO: launched 2 parallel vacuum workers for index cleanup (planned: 2)
INFO: table "TABLEA": found 0 removable, 155068 nonremovable row versions in 15534 out of 15830 pages
DETAIL: 111667 dead row versions cannot be removed yet, oldest xmin: 192985791
Skipped 1 page due to buffer pins, 0 frozen pages.
CPU: user: 0.04 s, system: 0.01 s, elapsed: 0.06 s.
INFO: vacuuming "pg_toast.pg_toast_65554"
INFO: table "pg_toast_65554": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 192985791
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.xmin 트랜잭션 192985791 으로 인하여 dead row가 제거되지 못하고 있는 것 확인
--튜플 정보 확인
select ctid,xmin,* from TABLEA where xmin =192985791 limit 10;
ctid | xmin | order_id | order_hash | order_type | product_key | is_seller | item_id | token_id | product_hash | wallet_address | owner_wallet_address | price | quantity | origin_quantity | locked_quantity | order_json | order_sign | create_at | update_at | expire_at
------+------+----------+------------+------------+-------------+-----------+---------+----------+--------------+----------------+----------------------+-------+----------+-----------------+-----------------+------------+------------+-----------+-----------+-----------
(0 rows)
--RW, RO 에서 롱 트랜잭션 확인
SELECT pid, datname, usename, state, backend_xmin, backend_xid
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
ORDER BY greatest(age(backend_xmin), age(backend_xid)) DESC;RW에서는 미확인, RO에서는 롱 트랜잭션 확인. 해당 세션 KILL 이후 정상화.
추가 조치
이러한 롱 트랜잭션을 모니터링하는 건 PostgreSQL의 MVCC 구조로 인하여 중요 포인트입니다.
다음 방안들로 롱트랜잭션을 확인할 수 있습니다.
--DB 엔진의 세션 상태를 조회하여 롱 트랜잭션 확인
SELECT age(backend_xmin)
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
ORDER BY greatest(age(backend_xmin));
--Aurora의 경우 성능 개선 도우미 카운터 지표 API를 통한 수집
--RO 에서의 롱 트랜잭션 : oldest_reader_feedback_xid_age
--RW 에서의 롱 트랜잭션 : oldest_running_transaction_xid_age
aws pi get-resource-metrics \
--service-type RDS \
--identifier \
--start-time 2025-06-17T00:00:00Z \
--end-time 2025-06-17T01:00:00Z \
--period-in-seconds 60 \
--metric-queries '[{ "Metric":"db.Transactions.oldest_running_transaction_xid_age.avg" }
]'
2. Aggressive AutoVacuum 의 CPU 점유를 막기 위한 수동 VACUUM 추가 작업
이슈 내용
https://kominjae.tistory.com/277
[PostgreSQL] Auto Vacuum 정리
개요라이브 운영 중 Vacuum 으로 인한 DB CPU 상승을 확인하여 Vacuum에 대해 세부 내용을 파악하고 대책을 마련하고자함 문제 상황서비스에서 CPU가 증가하며 비교적 증가 시간이 유지되는 상황 확인
kominjae.tistory.com
위 글에서 확인한 VACUUM 에서 CPU를 평소보다 오래 점유하는 Aggressive VACUUM 을 운영 중에 영향도를 낮추기 위한 방안을 마련하고자 하였습니다.
확인 과정
Aggressive Vacuum의 정의와 동작 원리는 위 글에 정리되어있습니다.
정리하자면 Insert 위주의 테이블에서는 별도 옵션 조정이 없을 경우 데이터가 커짐에 따라 VM의 1번비트가 0인 경우가 많아지고 이로 인하여 Aggressive Vacuum이 동작할 때 CPU 점유 시간이 길어지게 됩니다.
이는 autovacuum_vacuum_insert_scale_factor, autovacuum_vacuum_insert_threshold 를 테이블마다 적당한 값으로 조치하여도 결국 Aggressive Vacuum 은 동작하게 되므로 영향도를 낮출 수 없다고 파악하였습니다.
이러한 케이스에서 2가지 방안으로 조치가 가능한데
1. 파티셔닝 테이블로 나누어 VACUUM 이 처리해야하는 페이지의 양을 줄입니다.
2. VACUUM FREEZE를 배치로 실행하여 큰 테이블에서 VM의 1번 비트가 0인 페이지의 양을 주기적으로 관리합니다.
이번 케이스는 2번으로 작업을 진행하였고 이를 위해 추가적으로 검토한 내용은 다음과 같습니다
1. 수동 VACUUM 파라미터 조정 필요
수동 VACUUM 을 수행할 때 CPU 리소스를 점유하는 양을 관리해야 합니
vacuum_cost_delay : 기본값 = 0, autovacuum_vacuum_cost_delay 과 동일한 5ms로 설정
vacuum_cost_limit : 기본값 = 200, autovacuum_vacuum_cost_limit 과 동일한 600으로 설정
(autovacuum_vacuum_cost_limit은 다음과 같은 수식으로 계산됩니다. GREATEST({log(DBInstanceClassMemory/21474836480)*600},200))
max_parallel_maintenance_workers : 하나의 VACUUM 명령어에 사용 가능한 병렬 프로세스 수로 0으로 설정
2. VACUUM 권한 확인
vacuum minjae_test;
WARNING: skipping "minjae_test" --- only table or database owner can vacuum i
VACUUM은 SUPER USER 권한 필요
3. AUTO VACUUM과 수동 VACUUM 사이의 충돌 상황
AUTO AGGRESSIVE VACUUM 의 경우 종료되지 않는다고 한다
일반 AUTOVACUUM의 경우 1초 이내 중단이 되고 수동 VACUUM이 처리가 됨
트랜잭션이 테이블에 대한 잠금 하나를 유지하라는 요청과 함께 제공되고 Autovacuum 데몬이 이미 해당 테이블 중 하나에서 AutoVacuum 작업을 실행 중인 경우, 다른 트랜잭션이 잠금을 취할 수 있도록 Vacuum 작업을 즉시 취소합니다.
유사하게, 트랜잭션이 이미 테이블에 대한 ACCESS EXCLUSIVE 잠금을 보유하고 있는 경우 Autovacuum은 해당 테이블을 Vacuuming에서 건너뜁니다.
Autovacuum 프로세스는 다음 반복에서 Vacuum 작업을 실행하기 위해 건너뛴 테이블을 유지합니다.
테스트를 통한 확인
--autovacuum을 off한 대량 테이블 생성
schema_name | table_name | table_pages | seq_scan | idx_scan | total_tuple | live_tuple | dead_tupple | n_mod_since_analyze | n_ins_since_vacuum | autovacuum | autoanalyze | total_relation_size | relation_size
-------------+----------------------------------+-------------+----------+----------+-------------+------------+-------------+---------------------+--------------------+-------------------------------+-------------+---------------------+---------------
public | minjae_test | 48451796 | 1 | | 605766964 | 605766964 | 0 | 0 | 605477513 | | | 370 GB | 370 GB
--autovacuum on 처리
alter table minjae_test set (autovacuum_enabled = true);
--1번 세션에서 auto vacuum 실행 확인
SELECT datname, usename, pid, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY
xact_start;
pid | xact_runtime | query
----------------+----------+-------+-----------------+--------------------------------------------------------------------
19538 | 00:00:01.061305 | autovacuum: VACUUM public.minjae_test
16576 | 00:00:00 | SELECT datname, usename, pid, current_timestamp - xact_start +
| | AS xact_runtime, query +
| | FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY +
| | xact_start;
--2번 세션에서 수동 vacuum 진행
\timing on
Timing is on.
vacuum freeze verbose minjae_test;
INFO: aggressively vacuuming "public.minjae_test"
SELECT pid, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY
xact_start;
pid | xact_runtime | query
----------------+----------+-------+-----------------+--------------------------------------------------------------------
19538 | 00:00:04.781856 | autovacuum: VACUUM public.minjae_test
18753 | 00:00:00.888804 | vacuum freeze verbose minjae_test;
16576 | 00:00:00 | SELECT datname, usename, pid, current_timestamp - xact_start +
| | AS xact_runtime, query +
| | FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY +
| | xact_start;
SELECT pid, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY
xact_start;
pid | xact_runtime | query
----------------+----------+-------+-----------------+--------------------------------------------------------------------
18753 | 00:00:04.268573 | vacuum freeze verbose minjae_test;
19538 | 00:00:03.247073 | autovacuum: VACUUM pg_toast.pg_toast_225290
16576 | 00:00:00 | SELECT datname, usename, pid, current_timestamp - xact_start +
| | AS xact_runtime, query +
| | FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY +
| | xact_start;
SELECT pid, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY
xact_start;
pid | xact_runtime | query
----------------+----------+-------+-----------------+--------------------------------------------------------------------
18753 | 00:00:07.256983 | vacuum freeze verbose minjae_test;
16576 | 00:00:00 | SELECT datname, usename, pid, current_timestamp - xact_start +
| | AS xact_runtime, query +
| | FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY +
| | xact_start;
--5초 주기로 autovacuum lock 지연 로그 확인
2025-07-03 00:14:42 UTC::@:[19543]:LOG: skipping vacuum of "minjae_test" --- lock not available
2025-07-03 00:14:47 UTC::@:[19548]:LOG: skipping vacuum of "minjae_test" --- lock not available
2025-07-03 00:14:52 UTC::@:[19553]:LOG: skipping vacuum of "minjae_test" --- lock not available
...
2025-07-03 00:18:57 UTC::@:[19797]:LOG: skipping vacuum of "minjae_test" --- lock not available
2025-07-03 00:19:02 UTC::@:[19802]:LOG: skipping vacuum of "minjae_test" --- lock not available
--테이블 락 수준 확인
select l.locktype, t.relname, pid, mode, granted
from pg_locks l
left join pg_class t on l.relation = t.oid
;
locktype | relname | pid | mode | granted
------------+-----------------------------------+-------+--------------------------+---------
virtualxid | | 18753 | ExclusiveLock | t
relation | pg_class_tblspc_relfilenode_index | 16576 | AccessShareLock | t
relation | pg_class_relname_nsp_index | 16576 | AccessShareLock | t
relation | pg_class_oid_index | 16576 | AccessShareLock | t
relation | pg_class | 16576 | AccessShareLock | t
relation | pg_locks | 16576 | AccessShareLock | t
virtualxid | | 16576 | ExclusiveLock | t
relation | minjae_test | 18753 | ShareUpdateExclusiveLock | t
--VACUUM 로그
vacuum freeze verbose minjae_test;
INFO: aggressively vacuuming "public.minjae_test"
INFO: "minjae_test": found 0 removable, 605477513 nonremovable row versions in 48451796 out of 48451796 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 282320414
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 642.63 s, system: 12.91 s, elapsed: 2141.77 s.
INFO: aggressively vacuuming "pg_toast.pg_toast_225290"
INFO: "pg_toast_225290": found 0 removable, 103886 nonremovable row versions in 25961 out of 25961 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 282323357
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.30 s, system: 0.00 s, elapsed: 0.98 s.
VACUUM
Time: 2145108.910 ms (35:45.109)
--수동 vacuum 종료 이후 lock 관련 로그 미 발생, AUTOVACUUM 취소로 인하여 STATS에서 AUTOVACUUM COUNT가 0으로 확인됨
schema_name | table_name | table_pages | seq_scan | idx_scan | total_tuple | live_tuple | dead_tupple | n_mod_since_analyze | n_ins_since_vacuum | autovacuum | autoanalyze | total_relation_size | relation_size
-------------+----------------------------------+-------------+----------+----------+-------------+------------+-------------+---------------------+--------------------+-------------------------------+-------------+---------------------+---------------
public | minjae_test | 48451796 | 7 | | 605477513 | 605477513 | 0 | 20 | 0 | | | 370 GB | 370 GB
'공부 > DATABASE' 카테고리의 다른 글
| [Aurora PostgreSQL] 파티션 테이블 전환 (0) | 2025.10.08 |
|---|---|
| [AWS] DocumentDB Garbage Collection (0) | 2025.07.05 |
| [PostgreSQL] 로그 설정 (0) | 2025.06.06 |
| [PostgreSQL] Auto Vacuum 정리 (0) | 2025.04.19 |
| [PostgreSQL] 유니크 인덱스 NULL 처리 (0) | 2025.03.29 |