이 페이지에서는 데이터베이스가 PostgreSQL에서 트랜잭션 ID 랩어라운드 보호로 전환되는 경우 취할 수 있는 조치를 설명합니다. 다음과 같은 ERROR 메시지가 표시됩니다.
database is not accepting commands to avoid wraparound data loss in database dbname. Stop the postmaster and vacuum that database in single-user mode. You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
또는 다음과 같은 WARNING 메시지가 나타날 수 있습니다.
database dbname must be vacuumed within 10985967 transactions. To avoid a database shutdown, execute a database-wide VACUUM in that database.
단계 개요
- 랩어라운드를 일으키는 데이터베이스와 테이블이 무엇인지 알아봅니다.
- (AUTO)VACUUM을 방해하는 요소가 있는지 확인합니다(예: 중단된 트랜잭션 ID).
- AUTOVACUUM 속도를 측정합니다. 원하는 경우 속도가 느리면 속도를 높일 수 있습니다.
- 필요한 경우 VACUUM 명령을 몇 번 더 수동으로 실행합니다.
- 베큠의 속도를 높이는 다른 방법이 있는지 조사합니다. 경우에 따라 가장 빠른 방법은 테이블 또는 일부 색인을 삭제하는 것입니다.
플래그 값과 관련된 권장사항은 여러 데이터베이스 매개변수에 따라 달라지기 때문에 특정 목적에 부합하지 않을 수 있습니다. 이 주제에 대해 더 자세한 분석이 필요하면 이 페이지 아래에 링크된 문서를 읽어보세요.
랩어라운드를 유발하는 데이터베이스 및 테이블 찾기
데이터베이스 찾기
랩어라운드를 일으키는 테이블이 포함된 데이터베이스를 찾으려면 다음 쿼리를 실행합니다.
SELECT datname,
age(datfrozenxid),
2^31-1000000-age(datfrozenxid) as remaining
FROM pg_database
ORDER BY 3
remaining 값이 0에 가까운 데이터베이스는 문제를 일으키는 데이터베이스입니다.
테이블 찾기
데이터베이스에 연결하고 다음 쿼리를 실행합니다.
SELECT c.relnamespace::regnamespace as schema_name,
c.relname as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
2^31-1000000-greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as remaining
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY 4;
이 쿼리는 문제를 일으키는 테이블을 반환합니다.
TEMPORARY 테이블의 경우
schema_name이 pg_temp_로 시작하는 경우 문제를 해결하는 유일한 방법은 테이블을 삭제하는 것입니다. PostgreSQL이 다른 세션에서 만든 VACUUM 임시 테이블을 사용하지 않기 때문입니다. 해당 세션이 열려 있고 액세스할 수 있는 경우 테이블을 정리할 수 있지만 그렇지 않은 경우도 많습니다.
다음 SQL 문을 사용하여 임시 테이블을 삭제합니다.
SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
DROP TABLE pg_temp_<N>.<tablename>;
이것이 유일한 차단 요소인 경우 약 1분 안에 autovacuum에서 이 변경사항을 선택하고 datfrozenxid를 pg_database 앞으로 이동합니다. 이렇게 하면 랩어라운드 보호 읽기 전용 상태가 해결됩니다.
일반 테이블
일반 테이블(비임시 테이블)의 경우, 아래의 다음 단계를 진행하여 정리를 방해하는 항목이 있는지, VACUUM이 충분히 빠르게 실행되는지, 가장 중요한 테이블이 베큠 상태인지 확인합니다.
중단된 트랜잭션 ID가 있는지 확인
시스템에 트랜잭션 ID가 바닥나는 한 가지 가능한 이유는 PostgreSQL이 현재 실행 중인 가장 오래된 트랜잭션이 시작된 후 생성된 트랜잭션 ID를 프리징(모든 트랜잭션에 보이도록 표시)할 수 없는 것입니다. 이는 다중 버전 동시 실행 제어(MVCC) 규칙 때문입니다. 극단적인 경우 이러한 트랜잭션이 너무 오래되어 VACUUM으로 전체 20억 트랜잭션 ID 랩어라운드 한도에 대한 기존 트랜잭션을 삭제하는 것이 불가능하기 때문에 전체 시스템이 새로운 DML 받아들이는 것을 중단하게 됩니다. 또한 일반적으로 로그 파일에 WARNING: oldest
xmin is far in the past라는 경고가 표시됩니다.
이럴 때는 중단된 트랜잭션 ID를 구제 조치한 후에만 최적화로 이동해야 합니다.
다음은 트랜잭션 ID가 중단될 수 있는 4가지 이유와 이러한 각 이유를 완화하는 방법에 대한 설명입니다.
- 장기 실행 트랜잭션: 이들을 식별하여 취소하거나 백엔드를 종료하여 VACUUM을 차단 해제합니다.
- 분리된 준비 트랜잭션: 이러한 트랜잭션은 롤백합니다.
- 폐기된 복제 슬롯: 폐기된 슬롯을 삭제합니다.
hot_standby_feedback = on으로 복제본에서 장기 실행 중인 트랜잭션: 해당하는 트랜잭션을 식별하여 취소하거나 백엔드를 종료하여 VACUUM을 차단 해제합니다.
다음 쿼리는 위의 각 시나리오에서 가장 오래된 트랜잭션의 나이와 랩어라운드까지 남은 트랜잭션 수를 반환합니다.
WITH q AS (
SELECT
(SELECT max(age(backend_xmin))
FROM pg_stat_activity WHERE state != 'idle' ) AS oldest_running_xact_age,
(SELECT max(age(transaction)) FROM pg_prepared_xacts) AS oldest_prepared_xact_age,
(SELECT max(greatest(age(catalog_xmin),age(xmin))) FROM pg_replication_slots) AS oldest_replication_slot_age,
(SELECT max(age(backend_xmin)) FROM pg_stat_replication) AS oldest_replica_xact_age
)
SELECT *,
2^31 - oldest_running_xact_age AS oldest_running_xact_left,
2^31 - oldest_prepared_xact_age AS oldest_prepared_xact_left,
2^31 - oldest_replication_slot_age AS oldest_replication_slot_left,
2^31 - oldest_replica_xact_age AS oldest_replica_xact_left
FROM q;
이 쿼리는 랩어라운드까지 1백만에 가깝거나 그 미만으로 보고된 모든 *_left 값을 반환할 수 있습니다. PostgreSQL에서 새로운 쓰기 명령 허용을 중단하면 이 값이 랩어라운드 보호 한도입니다. 이 경우 VACUUM 차단 요소 제거 방법 또는 VACUUM 튜닝을 참조하세요.
예를 들어 앞의 쿼리는 다음을 반환할 수 있습니다.
┌─[ RECORD 1 ]─────────────────┬────────────┐ │ oldest_running_xact_age │ 2146483655 │ │ oldest_prepared_xact_age │ 2146483655 │ │ oldest_replication_slot_age │ ¤ │ │ oldest_replica_xact_age │ ¤ │ │ oldest_running_xact_left │ 999993 │ │ oldest_prepared_xact_left │ 999993 │ │ oldest_replication_slot_left │ ¤ │ │ oldest_replica_xact_left │ ¤ │ └──────────────────────────────┴────────────┘
여기서 oldest_running_xact_left 및 oldest_prepared_xact_left는 1백만 랩어라운드 보호 한도 미만이므로 먼저 VACUUM의 차단 요소를 제거해야 다음을 계속 진행할 수 있습니다.
VACUUM 차단 요소 제거 방법
장기 실행 트랜잭션
위 쿼리에서 oldest_running_xact가 oldest_prepared_xact와 동일한 경우 분리된 준비 트랜잭션 섹션으로 이동하세요. 최신 실행 값에 준비된 트랜잭션도 포함되기 때문입니다.
이를 위해서는 postgres 사용자로서 먼저 다음 명령어를 실행해야 할 수 있습니다.
GRANT pg_signal_backend TO postgres;
잘못된 트랜잭션이 시스템 사용자(cloudsql...로 시작)에 속하는 경우 이를 직접 취소할 수 없습니다. 취소하려면 데이터베이스를 다시 시작해야 합니다.
장기 실행 쿼리를 식별하고 취소 또는 종료하여 vacuum을 차단 해제하려면 먼저 가장 오래된 쿼리 중 몇 개를 선택합니다. LIMIT 10 행은 화면에 결과를 맞추는 데 도움이 됩니다. 가장 오래 실행 중인 쿼리를 해결한 후 이 작업을 반복해야 할 수도 있습니다.
SELECT pid,
age(backend_xid) AS age_in_xids,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
state,
query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY 2 DESC
LIMIT 10;
age_in_xids가 NULL을 반환하면 트랜잭션에 영구 트랜잭션 ID가 할당되지 않았으며 안전하게 무시할 수 있다는 의미입니다.
xids_left_to_wraparound가 1M에 가까워지면 쿼리를 취소하세요.
state가 active이면 SELECT pg_cancel_backend(pid);를 사용하여 쿼리를 취소할 수 있습니다. 그렇지 않으면 SELECT pg_terminate_backend(pid);를 사용하여 전체 연결을 종료해야 합니다. pid는 이전 쿼리의 pid입니다.
분리된 준비 트랜잭션
모든 준비된 트랜잭션을 나열합니다.
DB_NAME=> SELECT age(transaction),* FROM pg_prepared_xacts ; ┌─[ RECORD 1 ]┬───────────────────────────────┐ │ age │ 2146483656 │ │ transaction │ 2455493932 │ │ gid │ trx_id_pin │ │ prepared │ 2021-03-03 16:54:07.923158+00 │ │ owner │ postgres │ │ database │ DB_NAME │ └─────────────┴───────────────────────────────┘
마지막 쿼리(이 경우 trx_id_pin)에서 gid를 트랜잭션 ID로 사용하여 가장 오래된 분리된 준비 트랜잭션을 롤백하거나
ROLLBACK PREPARED trx_id_pin;
커밋하세요.
COMMIT PREPARED trx_id_pin;
전체 설명은 준비된 SQL 롤백 문서를 참조하세요.
폐기된 복제 슬롯
기존 복제 슬롯이 중지, 일시중지, 또는 지속적인 문제가 발생하기 때문에 복제 슬롯이 폐기되는 경우 gcloud 또는 Google Cloud Console에서 복제본을 삭제할 수 있습니다.
먼저 읽기 복제본 관리에 설명된 대로 복제본이 사용 중지되지 않은 것을 확인하세요. 복제본이 사용 중지되었으면 다시 사용 설정합니다. 지연이 여전히 높으면 복제본을 삭제합니다.
복제 슬롯은 pg_replication_slots 시스템 뷰에 표시됩니다.
다음 쿼리는 관련 정보를 가져옵니다.
SELECT *, age(xmin) AS age FROM pg_replication_slots; ┌─[ RECORD 1 ]────────┬─────────────────────────────────────────────────┐ │ slot_name │ cloudsql_1_355b114b_9ff4_4bc3_ac88_6a80199bd738 │ │ plugin │ ¤ │ │ slot_type │ physical │ │ datoid │ ¤ │ │ database │ ¤ │ │ active │ t │ │ active_pid │ 1126 │ │ xmin │ 2453745071 │ │ catalog_xmin │ ¤ │ │ restart_lsn │ C0/BEF7C2D0 │ │ confirmed_flush_lsn │ ¤ │ │ age │ 59 │ └─────────────────────┴─────────────────────────────────────────────────┘
이 예시에서 pg_replication_slots 값은 정상입니다(age == 59).
나이가 20억에 가깝다면 슬롯을 삭제하는 것이 좋습니다. 쿼리가 여러 레코드를 반환하는 경우, 어느 복제본이 어느 것인지 확인할 수 있는 쉬운 방법은 없습니다. 따라서 어떤 복제본이든 장기 실행 트랜잭션이 있는지 전부 확인하세요.
복제본에서 장기 실행 중인 트랜잭션
hot_standby_feedback이 on으로 설정된 가장 오래된 실행 트랜잭션의 복제본을 확인하고 복제본에서 사용 중지합니다.
pg_stat_replication 뷰의 backend_xmin 열에 복제본에 필요한 가장 오래된 TXID가 있습니다.
계속 진행하려면 복제본에서 보류시키고 있는 쿼리를 중지합니다. 어느 쿼리가 보류시키고 있는지 찾아내려면 장기 실행 트랜잭션 섹션의 쿼리를 사용하세요. 단, 이번에는 복제본에서 적용합니다.
또 다른 옵션은 복제본을 다시 시작하는 것입니다.
VACUUM 구성
다음 두 플래그를 설정합니다.
- autovacuum_vacuum_cost_delay = 0
- autovacuum_work_mem = 1048576
첫 번째 플래그는 PostgreSQL에서 VACUUM을 위한 모든 디스크 제한을 사용 중지하므로 VACUUM이 전체 속도로 실행될 수 있습니다(기본적으로 autovacuum은 제한적이므로 가장 느린 서버의 모든 디스크 IO를 사용하지는 않습니다).
두 번째 플래그 autovacuum_work_mem은 색인 삭제 패스 수를 줄입니다. 가능하다면 VACUUM이 청소할 테이블에서 데드 행의 모든 ID를 저장할 만큼 커야 합니다. 이 값을 설정할 때는 이것이 각 실행 VACUUM에서 할당할 수 있는 최대 로컬 메모리 양이라는 점을 고려하세요. 가용 메모리보다 많은 양을 허용하지 않아야 하며 만일의 경우에 대비해 여분을 남겨두는 것이 좋습니다. 데이터베이스를 읽기 전용 모드로 두는 경우 읽기 전용 쿼리에 사용되는 로컬 메모리도 고려해야 합니다.
하지만 대부분의 시스템에서는 최댓값을 사용합니다(샘플에 표시된 대로 1GB 또는 1048576KB). 이는 최대 약 1.78억 개의 데드 튜플에 해당합니다. 이보다 많으면 여전히 복수의 색인 스캔 패스를 야기합니다.
이러한 플래그와 다른 플래그에 대한 상세 설명은 PostgreSQL의 VACUUM 작업 최적화, 모니터링, 문제 해결에 자세히 설명되어 있습니다.
이러한 플래그를 설정한 후 데이터베이스를 재시작하면 새로운 값으로 autovaccum이 시작됩니다.
pg_stat_progress_vacuum 뷰를 사용하여 autovacuum으로 시작한 VACUUM의 진행 상황을 모니터링할 수 있습니다. 이 뷰는 모든 데이터베이스를 비롯하여 뷰 열 relid을 사용하여 테이블 이름을 찾을 수 없는 다른 데이터베이스의 테이블(관계)에서 실행되는 VACUUM을 보여줍니다.
다음으로 VACUUM 작업이 필요한 데이터베이스와 테이블을 식별하려면 PostgreSQL에서 VACUUM 작업 최적화, 모니터링, 문제 해결에 설명된 쿼리를 사용하세요. 서버 VM이 충분히 강력하여 autovacuum으로 시작하는 것보다 더 많은 VACUUM 프로세스를 병행 처리할 대역폭을 보유하고 있다면 수동 VACUUM 작업을 시작할 수도 있습니다.
VACUUM 속도 확인
이 섹션에서는 VACUUM 속도를 확인하고 필요에 따라 가속하는 방법을 설명합니다.
autovacuum 실행 확인
VACUUM을 실행하는 모든 백엔드는 시스템 뷰 pg_stat_progress_vacuum에 표시됩니다.
현재 단계가 scanning heap이면 heap_blks_scanned 열에의 변경사항을 감시하여 진행 상태를 모니터링할 수 있습니다.
아쉽게도 다른 단계의 스캔 속도를 쉽게 확인할 수 있는 방법은 없습니다.
VACUUM 스캔 속도 예측
스캔 속도를 예측하기 위해서는 먼저 기본값을 저장한 다음 시간 경과에 따른 변경 상태를 계산하여 완료 시간을 추정해야 합니다. 먼저 다음 스냅샷 쿼리를 사용해서 타임스탬프와 함께 heap_blks_scanned 스냅샷을 저장해야 합니다.
SELECT set_config('save.ts', clock_timestamp()::text, false),
set_config('save.heap_blks_scanned', heap_blks_scanned::text, false)
FROM pg_stat_progress_vacuum
WHERE datname = 'DB_NAME';
이미 랩어라운드된 테이블에는 저장할 수 없으므로 set_config(flag, value)를 사용하여 다음 두 가지 사용자 정의 플래그 save.ts 및save.heap_blks_scanned를 pg_stat_progress_vacuum의 현재 값으로 설정합니다.
다음 쿼리에서는 이 두 가지를 비교 기준으로 사용하여 속도와 예상 완료 시간을 결정합니다.
참고: WHERE datname = DB_NAME은 한 번에 하나의 데이터베이스로 조사를 제한합니다. 데이터베이스당 행이 1개 이상이면 이 데이터베이스에서 autovacuum이 1개만 실행 중인 경우 이 숫자로 충분합니다. 단일 autovacuum 행을 나타내려면 추가 필터 조건 ('AND relid= …'')를 WHERE에 추가해야 합니다. 이는 다음 쿼리에 대해서도 마찬가지입니다.
기본값을 저장한 후에 다음 쿼리를 실행할 수 있습니다.
with q as (
SELECT datname,
phase,
heap_blks_total,
heap_blks_scanned,
clock_timestamp() - current_setting('save.ts')::timestamp AS ts_delta,
heap_blks_scanned - current_setting('save.heap_blks_scanned')::bigint AS scanned_delta
FROM pg_stat_progress_vacuum
WHERE datname = DB_NAME
), q2 AS (
SELECT *,
scanned_delta / extract('epoch' FROM ts_delta) AS pages_per_second
FROM q
)
SELECT *,
(heap_blks_total - heap_blks_scanned) / pages_per_second AS remaining_time
FROM q2
;
┌─[ RECORD 1 ]──────┬──────────────────┐ │ datname │ DB_NAME │ │ phase │ scanning heap │ │ heap_blks_total │ 9497174 │ │ heap_blks_scanned │ 18016 │ │ ts_delta │ 00:00:40.30126 │ │ as_scanned_delta │ 11642 │ │ pages_per_second │ 288.87434288655 │ │ remaining_time │ 32814.1222418038 │ └───────────────────┴──────────────────┘
이 쿼리는 현재 값을 저장 기본값과 비교하여 pages_per_second 및 remaining_time을 계산하며, 이를 통해 VACUUM이 충분히 빠르게 실행되는지 또는 속도를 높여야 할지 결정할 수 있습니다. remaining_time 값은 scanning heap 단계에만 해당합니다.
다른 단계는 시간이 더 걸리며, 경우에 따라서는 아주 오래 걸리기도 합니다. VACUUM 작업에 대해 더 자세히 읽어보세요. 인터넷에는 VACUUM의 다양한 측면을 자세히 다룬 블로그 게시물로 많이 있습니다.
VACUUM 속도 향상
VACUUM 스캔을 더 빠르게 할 수 있는 가장 쉽고 빠른 방법은 autovacuum_vacuum_cost_delay=0을 설정하는 것입니다. 이는 Google Cloud Console을 통해 수행할 수 있습니다.
안타깝게도 이 값은 이미 실행 중인 VACUUM에서 가져오지 않으므로 데이터베이스를 다시 시작하는 것이 좋습니다.
다시 시작하면 아래와 비슷한 결과가 표시됩니다.
┌─[ RECORD 1 ]──────┬──────────────────┐ │ datname │ DB_NAME │ │ phase │ scanning heap │ │ heap_blks_total │ 9497174 │ │ heap_blks_scanned │ 222382 │ │ ts_delta │ 00:00:21.422615 │ │ as_scanned_delta │ 138235 │ │ pages_per_second │ 6452.76031894332 │ │ remaining_time │ 1437.33713040171 │ └───────────────────┴──────────────────┘
이 샘플에서는 300페이지/초 미만에서 ~6500페이지/초로 속도가 증가했고 힙 스캔 단계의 예상되는 남은 시간은 9시간에서 23분으로 줄었습니다.
다른 단계의 스캔 속도는 측정하기 쉽지 않지만, 비슷한 속도 상승을 보일 것입니다.
또한 색인의 여러 패스를 방지하기 위해 autovacuum_work_mem을 최대한 크게 만드는 것이 좋습니다. 색인 전달은 메모리가 데드 튜플 포인터로 채워질 때마다 발생합니다.
데이터베이스를 사용하는 다른 항목이 없으면 autovacuum_work_mem을 shared_buffers에 필요한 양을 허용한 후 남은 여유 메모리의 약 80%로 설정하세요.
이는 autovacuum으로 시작한 각 VACUUM 프로세스의 상한값입니다. 읽기 전용 워크로드를 계속 실행하려면 메모리를 적게 사용하는 것이 좋습니다.
속도를 향상시키는 다른 방법
색인 베큠 방지
대규모 테이블의 경우 VACUUM에서는 대부분의 시간을 색인을 정리하는 데 사용합니다.
PostgreSQL 14는 시스템이 랩어라운드 위험에 처한 경우 색인 정리를 방지하기 위한 특별 최적화를 제공합니다.
PostgreSQL 12 및 13에서는 다음 문을 수동으로 실행할 수 있습니다.
VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) <tablename>;
11 이하 버전에서는 베큠을 실행하기 전에 색인을 DROP하여 나중에 다시 만들 수 있습니다.
해당 테이블에서 autovacuum이 이미 실행 중일 때 색인을 삭제하려면 실행 중인 베큠을 취소한 후 autovacuum이 해당 테이블에서 베큠을 다시 시작하기 전에 즉시 색인 삭제 명령어를 실행해야 합니다.
먼저 다음 문을 실행하여 종료해야 하는 autovacuum 프로세스의 PID를 찾습니다.
SELECT pid, query
FROM pg_stat_activity
WHERE state != 'idle'
AND query ilike '%vacuum%';
그런 후 다음 문을 실행하여 실행 중인 베큠을 종료하고 하나 이상의 색인을 삭제합니다.
SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
SELECT pg_terminate_backend(<pid>);DROP INDEX <index1>;DROP INDEX <index2>; ...
문제가 있는 테이블 삭제
드물지만 테이블을 삭제할 수 있습니다. 예를 들면 백업 또는 다른 데이터베이스와 같은 다른 소스에서 테이블을 복원하기 쉬운 경우입니다.
여전히 cloudsql.enable_maintenance_mode = 'on'을 사용해야 하며 이전 섹션에 표시된 것처럼 해당 테이블에서 VACUUM을 종료할 수도 있습니다.
VACUUM FULL
드물지만 VACUUM FULL FREEZE가 더 빠르게 실행되는데, 이는 일반적으로 테이블에 라이브 튜플의 비율이 낮기 때문입니다.
이는 pg_stat_user_tables 뷰에서 확인할 수 있습니다(통계가 삭제된 비정상 종료가 발생하지 않은 경우).
VACUUM FULL 명령어는 라이브 튜플을 새 파일로 복사하므로 새 파일과 해당 색인에 사용할 수 있는 충분한 공간이 확보됩니다.
다음 단계
- 랩어라운드에 대한 VACUUM 자세히 알아보기
- 루틴 VACUUM 작업에 대해 자세히 알아보기
- 자동 VACUUM 작업 자세히 알아보기
- PostgreSQL의 VACUUM 작업 최적화, 모니터링, 문제해결 자세히 알아보기