활성 문 트랜잭션의 유휴 상태 준비된 트랜잭션 논리적 복제 슬롯 리더 인스턴스 임시 테이블

Aurora PostgreSQL에서 식별 가능한 vacuum 블로커 해결

Autovacuum은 공격적인 vacuum을 수행하고 트랜잭션 ID의 연령을 RDS 인스턴스의 autovacuum_freeze_max_age 파라미터에 의해 지정된 임곗값 미만으로 낮춥니다. 이 연령은 Amazon CloudWatch 지표 MaximumUsedTransactionIDs를 사용하여 추적할 수 있습니다.

Amazon RDS 인스턴스에 대한 autovacuum_freeze_max_age의 설정(기본값은 2억 개의 트랜잭션 ID)을 찾으려면 다음 쿼리를 사용할 수 있습니다.


SELECT
    TO_CHAR(setting::bigint, 'FM9,999,999,999') autovacuum_freeze_max_age
FROM
    pg_settings
WHERE
    name = 'autovacuum_freeze_max_age';

postgres_get_av_diag()는 연령이 Amazon RDS의 적응형 autovacuum 임곗값인 트랜잭션 ID 5억 개를 초과할 때만 공격적 vacuum 블로커를 확인합니다. postgres_get_av_diag()가 블로커를 감지하려면 블로커의 연령이 최소 5억 개의 트랜잭션이어야 합니다.

postgres_get_av_diag() 함수는 다음과 같은 유형의 블로커를 식별합니다.

활성 문

PostgreSQL에서 활성 문은 현재 데이터베이스에서 실행 중인 SQL 문입니다. 여기에는 쿼리, 트랜잭션 또는 진행 중인 작업이 포함됩니다. pg_stat_activity를 통해 모니터링할 때 상태 열은 해당 PID가 있는 프로세스가 활성 상태임을 나타냅니다.

postgres_get_av_diag() 함수는 활성 문인 문을 식별할 때 다음과 유사한 출력을 표시합니다.


blocker               | Active statement
database              | my_database
blocker_identifier    | SELECT pg_sleep(20000);
wait_event            | Timeout:PgSleep
autovacuum_lagging_by | 568,600,871
suggestion            | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_terminate_backend (29621);"}

권장 조치

suggestion 열의 지침에 따라 사용자는 활성 문이 있는 데이터베이스에 연결할 수 있으며 suggested_action 열에 지정된 대로 세션을 종료하는 옵션을 주의 깊게 검토하는 것이 좋습니다. 종료가 안전한 경우 pg_terminate_backend() 함수를 사용하여 세션을 종료할 수 있습니다. 이 작업은 관리자(예: RDS 마스터 계정) 또는 필요한 pg_terminate_backend() 권한이 있는 사용자가 수행할 수 있습니다.

주의

종료된 세션은 해당 세션이 만든 (ROLLBACK) 변경 사항을 취소합니다. 요구 사항에 따라 문을 다시 실행할 수 있습니다. 그러나 autovacuum 프로세스가 공격적인 vacuum 작업을 완료한 후에만 다시 실행하는 것이 좋습니다.

트랜잭션의 유휴 상태

트랜잭션 문의 유휴는 명시적 트랜잭션을 열고(예: BEGIN 명령문을 발급하여), 일부 작업을 수행했으며, 이제 클라이언트가 더 많은 작업을 전달하거나 COMMIT, ROLLBACK또는 END(암시적 COMMIT을 유발함)를 발급하여 트랜잭션 종료 신호를 보내기를 기다리는 세션을 나타냅니다.

postgres_get_av_diag() 함수는 idle in transaction 문을 블로커로 식별할 때 다음과 유사한 출력을 표시합니다.


blocker               | idle in transaction
database              | my_database
blocker_identifier    | INSERT INTO tt SELECT * FROM tt;
wait_event            | Client:ClientRead
autovacuum_lagging_by | 1,237,201,759
suggestion            | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_terminate_backend (28438);"}

권장 조치

suggestion 열에 표시된 대로 트랜잭션 세션에 유휴가 있는 데이터베이스에 연결하고 pg_terminate_backend() 함수를 사용하여 세션을 종료할 수 있습니다. 사용자는 관리자(RDS 마스터 계정) 사용자이거나 pg_terminate_backend() 권한이 있는 사용자일 수 있습니다.

주의

준비된 트랜잭션

PostgreSQL은 준비된 트랜잭션이라고 하는 두 단계 커밋 전략의 일부인 트랜잭션을 허용합니다. max_prepared_transactions 파라미터를 0이 아닌 값으로 설정하면 활성화됩니다. 준비된 트랜잭션은 데이터베이스 충돌, 재시작 또는 클라이언트 연결 해제 후에도 트랜잭션이 내구성을 유지하고 계속 가능하도록 하기 위해 설계되었습니다. 일반 트랜잭션과 마찬가지로 트랜잭션 ID가 할당되며 autovacuum에 영향을 미칠 수 있습니다. 준비된 상태로 두면 autovacuum이 고정을 수행할 수 없으며 트랜잭션 ID 랩어라운드로 이어질 수 있습니다.

트랜잭션 관리자가 확인하지 않고 트랜잭션을 무기한으로 준비된 상태로 두면 고립된 준비 트랜잭션이 됩니다. 이 문제를 해결하는 유일한 방법은 COMMIT PREPARED 또는 ROLLBACK PREPARED 명령을 각각 사용하여 트랜잭션을 커밋하거나 롤백하는 것입니다.

참고

준비된 트랜잭션 중에 가져온 백업에는 복원 후에도 해당 트랜잭션이 계속 포함되어 있습니다. 이러한 트랜잭션을 찾고 닫는 방법은 다음 정보를 참조하세요.

준비된 트랜잭션인 블로커를 식별하면 postgres_get_av_diag() 함수에 다음 출력이 표시됩니다.


blocker               | Prepared transaction
database              | my_database
blocker_identifier    | myptx
wait_event            | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion            | Connect to database "my_database" and consider either COMMIT or ROLLBACK the prepared transaction using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"COMMIT PREPARED 'myptx';",[OR],"ROLLBACK PREPARED 'myptx';"}

권장 조치

제안 열에 언급된 대로 준비된 트랜잭션이 있는 데이터베이스에 연결합니다. suggested_action 열을 기반으로 COMMIT과 ROLLBACK 중 무엇을 수행할지 주의 깊게 검토하고 작업을 적절히 적용합니다.

준비된 트랜잭션을 일반적으로 모니터링하기 위해 PostgreSQL은 pg_prepared_xacts라는 카탈로그 보기를 제공합니다. 다음 쿼리를 사용하여 준비된 트랜잭션을 찾을 수 있습니다.


SELECT
    gid,
    prepared,
    owner,
    database,
    transaction AS oldest_xmin
FROM
    pg_prepared_xacts
ORDER BY
    age(transaction) DESC;

논리적 복제 슬롯

복제 슬롯의 목적은 사용되지 않은 변경 사항이 대상 서버에 복제될 때까지 이를 유지하는 것입니다. 자세한 내용은 PostgreSQL의 Logical replication을 참조하세요.

논리적 복제 슬롯에는 두 가지 유형이 있습니다.

비활성 논리적 복제 슬롯

복제가 종료되면 소비되지 않은 트랜잭션 로그를 제거할 수 없으며 복제 슬롯이 비활성화됩니다. 비활성 논리적 복제 슬롯은 현재 구독자가 사용하지 않지만 서버에 남아 있어 WAL 파일이 보존되고 이전 트랜잭션 로그를 제거할 수 없습니다. 이렇게 되면 LSN 정보를 덮어쓰지 않도록 시스템이 보존해야 하므로 디스크 사용량이 증가하고 특히 autovacuum이 내부 카탈로그 테이블을 정리하지 못하게 됩니다. 해결하지 않으면 카탈로그 팽창, 성능 저하 및 랩어라운드 vacuum 위험 증가로 이어질 수 있으며 이로 인해 트랜잭션 가동 중지 시간이 발생할 수도 있습니다.

활성이지만 느린 논리적 복제 슬롯

논리적 복제의 성능 저하로 인해 카탈로그의 데드 튜플 제거가 지연되는 경우가 있습니다. 이러한 복제 지연으로 인해 catalog_xmin의 업데이트 속도가 느려지고 카탈로그 팽창 및 랩어라운드 vacuum이 발생할 수 있습니다.

postgres_get_av_diag() 함수는 논리적 복제 슬롯을 블로커로 찾았을 때 다음과 유사한 출력을 표시합니다.


blocker               | Logical replication slot
database              | my_database
blocker_identifier    | slot1
wait_event            | Not applicable
autovacuum_lagging_by | 1,940,103,068
suggestion            | Ensure replication is active and resolve any lag for the slot if active. If inactive, consider dropping it using the command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_drop_replication_slot('slot1') FROM pg_replication_slots WHERE active = 'f';"}

권장 조치

이 문제를 해결하려면 대상 스키마 또는 적용 프로세스를 종료할 수 있는 데이터에 문제가 있는지 복제 구성을 확인합니다. 가장 일반적인 이유는 다음과 같습니다.

열 누락
호환되지 않는 데이터 유형
데이터 불일치
누락된 테이블

문제가 인프라 문제와 관련된 경우:

네트워크 문제 - 호환되지 않는 네트워크 상태의 Amazon RDS DB 관련 문제를 해결하려면 어떻게 해야 하나요?
데이터베이스 또는 DB 인스턴스를 사용할 수 없는 이유는 다음과 같습니다.
- 복제본 인스턴스의 스토리지가 부족함 - 스토리지 추가에 대한 자세한 내용은 Amazon RDS DB instances run out of storage를 검토하세요.
- 호환되지 않는 파라미터 - 문제를 해결하는 방법에 대한 자세한 내용은 호환되지 않는 파라미터 상태에서 멈춘 Amazon RDS DB 인스턴스를 해결하려면 어떻게 해야 하나요?를 검토하세요.

인스턴스가 AWS 네트워크 외부 또는 AWS EC2에 있는 경우 관리자에게 가용성 또는 인프라 관련 문제를 해결하는 방법을 문의하세요.

비활성 슬롯 삭제

주의

주의: 복제 슬롯을 삭제하기 전에 복제가 진행 중이지 않고 비활성 상태이며 복구할 수 없는 상태인지 주의 깊게 확인합니다. 슬롯을 성급히 삭제하면 복제가 중단되거나 데이터가 손실될 수 있습니다.

복제 슬롯이 더 이상 필요하지 않음을 확인한 후 autovacuum이 계속되도록 슬롯을 삭제합니다. active = 'f' 조건은 비활성 슬롯만 삭제되도록 합니다.


SELECT pg_drop_replication_slot('slot1') WHERE active ='f'

리더 인스턴스

hot_standby_feedback 설정이 활성화되면 라이터 인스턴스의 자동 정리가 리더 인스턴스에서 실행 중인 쿼리에 여전히 필요할 수 있는 데드 행을 제거하는 것을 방지합니다. 리더 인스턴스(Aurora 글로벌 데이터베이스의 리더 인스턴스에도 해당)에서 실행되는 쿼리는 쿼리 충돌 및 취소를 방지하는 라이터 인스턴스에서 해당 행을 계속 사용할 수 있어야 하기 때문에 이 동작이 필요합니다.

참고

hot_standby_feedback은 Aurora PostgreSQL에서 기본적으로 활성화되며 수정할 수 없습니다.

postgres_get_av_diag() 함수는 물리적 복제 슬롯을 사용하는 읽기 전용 복제본을 블로커로 찾았을 때 다음과 유사한 출력을 표시합니다.


blocker               | Oldest query running on aurora reader
database              | Not applicable
blocker_identifier    | my-aurora-reader-2
wait_event            | Not applicable
autovacuum_lagging_by | 540,122,859
suggestion            | Run the following query on the reader "my-aurora-reader-2" to find the long running query:                                                                                                 
                      | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 523476310;                                                                                                    
                      | Review carefully and you may consider terminating the query on reader using suggested_action.                                                      
suggested_action      | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 523476310;","                                                                       
                      | [OR]                                                                                                                                                                                    
                      | ","Delete the reader if not needed"}

suggested_action 열에서 권장하는 대로 이러한 옵션을 주의 깊게 검토하여 autovacuum 차단을 해제합니다.

쿼리 종료 - 제안 열의 지침에 따라 suggested_action 열에 지정된 대로 읽기 전용 복제본에 연결할 수 있습니다. 세션을 종료하는 옵션을 주의 깊게 검토하는 것이 좋습니다. 종료가 안전한 것으로 간주되는 경우 pg_terminate_backend() 함수를 사용하여 세션을 종료할 수 있습니다. 이 작업은 관리자(예: RDS 마스터 계정) 또는 필요한 pg_terminate_backend() 권한이 있는 사용자가 수행할 수 있습니다.

읽기 전용 복제본에서 다음 SQL 명령을 실행하여 기본의 vacuum이 오래된 행을 정리하지 못하게 하는 쿼리를 종료할 수 있습니다. backend_xmin의 값은 함수의 출력에 보고됩니다.
```
SELECT
    pg_terminate_backend(pid)
FROM
    pg_catalog.pg_stat_activity
WHERE
    backend_xmin::text::bigint = backend_xmin;
```
필요하지 않은 경우 리더 인스턴스 삭제 - 리더 인스턴스가 더 이상 필요하지 않은 경우 삭제할 수 있습니다. 이렇게 하면 연결된 복제 오버헤드가 제거되고 프라이머리 인스턴스가 인스턴스에 의해 지연되지 않고 트랜잭션 로그를 재활용할 수 있습니다.

임시 테이블

TEMPORARY 키워드를 사용하여 만든 임시 테이블은 pg_temp_xxx와 같은 임시 스키마에 상주하며 해당 테이블을 만든 세션에서만 액세스할 수 있습니다. 세션이 종료되면 임시 테이블이 삭제됩니다. 그러나 이러한 테이블은 PostgreSQL의 autovacuum 프로세스에는 보이지 않으며 테이블을 만든 세션에서 수동으로 vacuum 처리해야 합니다. 다른 세션에서 임시 테이블을 정리하려고 해도 아무런 효과가 없습니다.

비정상적인 상황에서는 임시 테이블이 활성 세션 없이 존재합니다. 치명적인 충돌, 네트워크 문제 또는 유사한 이벤트로 인해 소유 세션이 예기치 않게 종료되는 경우 임시 테이블이 정리되지 않아 '고립된' 테이블로 남겨질 수 있습니다. PostgreSQL autovacuum 프로세스가 고립된 임시 테이블을 감지하면 다음 메시지를 로깅합니다.


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

postgres_get_av_diag() 함수는 임시 테이블을 블로커로 식별할 때 다음과 유사한 출력을 표시합니다. 함수가 임시 테이블과 관련된 출력을 올바르게 표시하려면 해당 테이블이 있는 것과 동일한 데이터베이스 내에서 실행해야 합니다.


blocker               | Temporary table
database              | my_database
blocker_identifier    | pg_temp_14.ttemp
wait_event            | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion            | Connect to database "my_database". Review carefully, you may consider dropping temporary table using command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"DROP TABLE ttemp;"}

권장 조치

출력의 suggestion 열에 제공된 지침에 따라 autovacuum 실행을 막는 임시 테이블을 식별하고 제거합니다. 다음 명령을 사용하여 postgres_get_av_diag()에서 보고한 임시 테이블을 삭제합니다. postgres_get_av_diag() 함수에서 제공하는 출력을 기반으로 테이블 이름을 바꿉니다.


DROP TABLE my_temp_schema.my_temp_table;

다음 쿼리를 사용하여 임시 테이블을 식별할 수 있습니다.


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

javascript가 브라우저에서 비활성화되거나 사용이 불가합니다.

AWS 설명서를 사용하려면 Javascript가 활성화되어야 합니다. 지침을 보려면 브라우저의 도움말 페이지를 참조하십시오.

문서 규칙

postgres_get_av_diag()의 기능

식별 불가능한 vacuum 블로커 해결