Aurora PostgreSQL 호환에서 트랜잭션 ID 랩어라운드 문제를 해결하려면 어떻게 해야 합니까?

해결 방법

문제 예방을 위해 지표 및 CloudWatch 경보 모니터링

정리되지 않은 가장 오래된 트랜잭션 수명을 모니터링하려면 Amazon CloudWatch에서 MaximumUsedTransactionIDs 지표를 확인하십시오. 트랜잭션 ID 랩어라운드 문제를 방지하려면 CloudWatch 경보를 생성하십시오. 자세한 내용은 Amazon Relational Database Service(Amazon RDS) for PostgreSQL에서 트랜잭션 ID 랩어라운드에 대한 조기 경고 시스템 구현을 참조하십시오.

진단 쿼리 실행

psql 또는 pgAdmin을 사용하여 PostgreSQL DB 인스턴스에 연결합니다.

데이터베이스 수명을 확인하려면 다음 명령을 실행합니다.

SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY 2 DESC
LIMIT 20;

참고: 출력에 rdsadmin 또는 template0 데이터베이스의 트랜잭션 ID 수명이 가장 오래된 것으로 표시되면 AWS Support에 문의하십시오.

수명이 가장 오래된 데이터베이스에 연결하여 테이블의 잠재적 문제를 파악하려면 다음 명령을 실행합니다.

SELECT
c.relnamespace::regnamespace as schema_name,
c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
pg_size_pretty(pg_table_size(c.oid)) as table_size,
t.relkind, t.relpersistence
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY 3 DESC
LIMIT 20;

수명 값이 높은 테이블의 자동 정리 기록을 검토하려면 다음 명령을 실행합니다.

SELECT
  relname,
  n_live_tup, n_tup_upd, n_tup_del, n_dead_tup,
  last_vacuum, last_autovacuum,
  last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname='table_name';

참고: table_name을 테이블 이름으로 바꾸십시오.

활성 정리 작업을 모니터링하려면 다음 명령을 실행합니다.

SELECT datname, usename, pid, state, wait_event,
current_timestamp - xact_start AS xact_runtime, query
FROM pg_stat_activity
WHERE upper(query) LIKE '%VACUUM%'
AND pid <> pg_backend_pid()
ORDER BY xact_start;

자동 정리 프로세스 블로커 해결

다음과 같은 Aurora PostgreSQL 호환 버전 중 하나를 사용하는 경우 Aurora PostgreSQL의 강력한 정리 블로커 파악 및 해결을 참조하십시오.

• 13.19 이상
• 14.16 이상
• 15.11 이상
• 16.7 이상
• 17.2 이상

장기 실행 트랜잭션 해결

유휴 상태인 장기 실행 트랜잭션 또는 전환 중인 세션이 있으면 자동 정리가 차단될 수 있습니다. 

PostgreSQL 오류 로그에 다음과 같은 경고 메시지가 표시됩니다.

"WARNING: oldest xmin is far in the past HINT: Close open transactions soon to avoid wraparound problems."

자동 정리를 차단하는 트랜잭션을 파악하려면 다음 명령을 실행합니다.

SELECT pid,
    age(backend_xid),
    substr(query,1,20),
    extract(epoch from now()) - extract(epoch from xact_start) as duration,
    state
FROM pg_stat_activity
WHERE xact_start is not null
AND age(backend_xid) is not null
ORDER BY 2 DESC
LIMIT 10;

이 문제를 해결하려면 활성 쿼리를 취소하거나 연결을 종료하면 됩니다.

활성 쿼리를 취소하려면 다음 명령을 실행합니다.

SELECT pg_cancel_backend(pid);

참고: pid를 쿼리 결과의 프로세스 ID로 바꾸십시오.

연결을 종료하려면 다음 명령을 실행하십시오.

SELECT pg_terminate_backend(pid);

참고: pid를 쿼리 결과의 프로세스 ID로 바꾸십시오.

장기 실행 트랜잭션이 자동 정리를 다시 차단하지 않도록 하려면 statement_timeout, idle_in_transaction_session_timeout 및 log_min_duration_statement 파라미터를 설정하십시오.

비활성 논리적 복제 슬롯 해결

비활성 슬롯을 파악하려면 다음 명령을 실행합니다.

SELECT slot_name, slot_type, database, xmin, catalog_xmin,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag,
    active
FROM pg_replication_slots
ORDER BY age(xmin), age(catalog_xmin) DESC;

경고: 복제 슬롯을 삭제하기 전에 진행 중인 복제가 없고, 비활성 상태이며, 복구할 수 없는 상태인지 확인하십시오. 아직 복제 중이고 활성 상태이며 복구 가능한 슬롯을 삭제하면 복제가 중단되거나 데이터 손실이 발생할 수 있습니다.

사용하지 않는 슬롯을 삭제하려면 다음 명령을 실행합니다.

SELECT pg_drop_replication_slot('slot_name');

참고: slot_name을 복제 슬롯 이름으로 바꾸십시오.

리더 인스턴스 문제 해결

핫 스탠바이 피드백을 방지하고 자동 정리를 차단할 수 있는 Aurora 클러스터의 리더를 파악하려면 다음 쿼리를 실행합니다.

select server_id, feedback_epoch, feedback_xmin
from aurora_replica_status()
WHERE (feedback_xmin IS NOT NULL AND feedback_xmin > 0)

Global Database 클러스터에서 핫 스탠바이 피드백을 방지하는 리더에 대해 다음 쿼리를 실행합니다.

select server_id,aws_region,feedback_epoch,feedback_xmin
from aurora_global_db_instance_status ()
WHERE (feedback_xmin IS NOT NULL AND feedback_xmin > 0);

feedback_epoch 및 feedback_xmin의 결합 값이 가장 낮은 리더 인스턴스에서 다음 쿼리를 실행합니다.

SELECT pid,
    age(backend_xid),
    substr(query,1,20),
    extract(epoch from now()) - extract(epoch from xact_start) as duration,
    state
FROM pg_stat_activity WHERE backend_xmin::text::bigint =feedback_xmin

참고: feedback_xmin을 이전 쿼리 결과의 feedback_xmin 값으로 바꾸십시오.

안전하게 종료할 수 있다면 **pg_terminate_backend(pid)**를 사용하여 장기 실행 트랜잭션을 종료합니다.

커밋되지 않은 준비된 트랜잭션 해결

준비된 트랜잭션을 파악하려면 다음 명령을 실행합니다.

SELECT database, gid, prepared, owner, database, transaction::text::bigint as xid,
now() - prepared AS time_since_prepared
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

준비된 트랜잭션을 해결하려면 준비된 트랜잭션을 커밋하거나 롤백하면 됩니다.

준비된 트랜잭션을 커밋하려면 다음 명령을 실행합니다.

COMMIT PREPARED 'gid'

참고: 쿼리 결과에서 gid를 Global Transaction ID로 바꾸십시오.

경고: 준비된 트랜잭션을 롤백하기 전에 트랜잭션이 분산 트랜잭션 시퀀스에 속하지 않는지 확인하십시오.

준비된 트랜잭션을 롤백하려면 다음 명령을 실행합니다.

ROLLBACK PREPARED 'gid'

참고: 쿼리 결과에서 gid를 Global Transaction ID로 바꾸십시오.

고아 임시 테이블 해결

PostgreSQL 자동 정리 프로세스에서 고아 임시 테이블을 발견하면 다음 이벤트를 로깅합니다.

LOG: autovacuum: found orphan temp table "%s"."%s" in database "%s"

고아 임시 테이블을 파악하려면 다음 명령을 실행합니다.

SELECT
  oid,
  relname,
  relnamespace::regnamespace,
  age(relfrozenxid)
FROM pg_class
WHERE relpersistence = 't'
ORDER BY age(relfrozenxid) DESC;

임시 테이블을 삭제하려면 다음 명령을 실행합니다.

DROP TABLE temporary_table

참고: temporary_table을 임시 테이블 이름으로 바꾸십시오.

정리 성능 문제 해결

자동 정리가 느리게 수행되면 고정 작업이 지연될 수 있습니다.

이 문제를 해결하려면 먼저 더 많은 동시 정리 작업을 수행할 수 있도록 autovacuum_max_workers 파라미터를 늘리십시오. 그리고 다음 공식을 사용하여 autovacuum_vacuum_cost_limit 파라미터를 동일하게 늘리십시오.

Individual worker's cost_limit = autovacuum_vacuum_cost_limit / autovacuum_max_workers

파라미터를 늘린 후에도 정리 성능이 부족하면 Amazon RDS 인스턴스를 vCPU가 더 많은 클래스로 업그레이드하십시오. 테이블 수준에서 autovacuum_vacuum_cost_delay 파라미터를 수정하여 자동 정리 작업의 속도를 높일 수 있습니다.

테이블 수준에서 autovacuum_vacuum_cost_delay 파라미터를 수정하려면 다음 명령을 실행합니다.

ALTER TABLE mytable SET ( autovacuum_vacuum_cost_delay = value, autovacuum_vacuum_cost_limit = value );

참고: mytable을 테이블 이름으로, value를 원하는 파라미터 값으로 바꾸십시오.

대형 인덱스 정리 성능

대형 인덱스를 정리할 때 메모리 제약으로 인해 동일한 인덱스를 여러 번 통과해야 해서 총 VACUUM 기간이 크게 늘어날 수 있습니다.

PostgreSQL 16 이하 버전에서 대형 인덱스에 대한 VACUUM 작업이 느리다면 다음과 같은 잠재적 원인을 확인하십시오.

• 인덱스 크기가 1GB를 초과합니다.
• VACUUM 통과가 여러 번 발생하고 있습니다. 통과 수를 보려면 pg_stat_progress_vacuum 뷰에서 index_vacuum_count를 쿼리하십시오.
• maintenance_work_mem 또는 autovacuum_work_mem을 잘못 구성했습니다.

이 문제를 해결하려면 다음 작업을 수행하십시오.

• 사용하지 않거나 중복된 인덱스를 삭제합니다.
• maintenance_work_mem 또는 autovacuum_work_mem을 늘립니다. 새 메모리 설정을 적용하려면 현재 자동 정리를 실행 중인 프로세스를 종료하십시오.
• VACUUM FREEZE 작업을 수동으로 수행하고 세션 내에서 maintenance_work_mem을 늘리십시오.

PostgreSQL 버전 12 이상을 사용하는 경우 다음 명령을 실행하여 인덱스 제외 항목이 포함된 잠재적 수동 정리를 검토하십시오.

VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;

참고: table_name을 테이블 이름으로 바꾸십시오.

논리적 불일치

논리적으로 일치하지 않는 인덱스로 인해 자동 정리 프로세스가 차단되는 경우 다음 오류 메시지 중 하나가 표시될 수 있습니다.

• ERROR: right sibling's left-link doesn't match:block 5 links to 10 instead of expected 2 in index ix_name
• ERROR: failed to re-find parent key in index "XXXXXXXXXX" for deletion target page XXX CONTEXT: while vacuuming index index_name of relation schema.table

이 문제를 해결하려면 인덱스를 다시 작성하십시오. PostgreSQL 버전 12 이상의 경우에는 인덱스 정리를 건너뛰면 됩니다.

index를 다시 빌드하려면 다음 명령을 실행합니다.

REINDEX INDEX ix_name CONCURRENTLY;

참고: ix_name을 인덱스 이름으로 바꾸십시오.

-또는-

PostgreSQL 12 이상에서 인덱스 정리를 건너뛰려면 다음 명령을 실행합니다.

VACUUM FREEZE INDEX_CLEANUP FALSE table_name;

참고: table_name을 테이블 이름으로 바꾸십시오.

관련 정보

PostgreSQL 웹 사이트에서 트랜잭션 ID 랩어라운드 실패 방지

Amazon 오로라 PostgreSQL에서 PostgreSQL 자동 정리 사용

Amazon RDS for PostgreSQL 환경의 autovacuum 이해

Amazon RDS for PostgreSQL 또는 Aurora PostgreSQL 호환 DB 인스턴스에서 장기 실행 쿼리를 종료하려면 어떻게 해야 합니까?