RDS for 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() 권한이 있는 사용자가 수행할 수 있습니다.

트랜잭션의 유휴 상태

트랜잭션 문의 유휴는 명시적 트랜잭션을 열고(예: 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';"}

권장 조치

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

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

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

비활성 슬롯 삭제

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

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

읽기 전용 복제본

Amazon RDS 읽기 전용 복제본에 대해 hot_standby_feedback 설정이 활성화된 경우 기본 데이터베이스의 autovacuum이 읽기 전용 복제본에서 실행되는 쿼리에 여전히 필요할 수 있는 데드 행을 제거하는 것을 방지합니다. 이는 복제 슬롯을 사용하거나 사용하지 않고 관리되는 복제본을 포함하여 모든 유형의 물리적 읽기 전용 복제본에 영향을 미칩니다. 대기 복제본에서 실행되는 쿼리가 쿼리 충돌 및 취소를 방지하면서 기본에서 해당 행을 계속 사용 가능하도록 요구하기 때문에 이 동작이 필요합니다.

물리적 복제 슬롯을 사용하는 읽기 전용 복제본

물리적 복제 슬롯을 사용하는 읽기 전용 복제본은 RDS for PostgreSQL에서 복제의 신뢰성과 안정성을 크게 향상시킵니다. 이러한 슬롯은 복제본이 처리할 때까지 기본 데이터베이스가 필수 Write-Ahead Log 파일을 유지하여 네트워크 중단 중에도 데이터 일관성을 유지하도록 합니다.

RDS for PostgreSQL 버전 14부터 모든 복제본은 복제 슬롯을 사용합니다. 이전 버전에서는 교차 리전 복제본만 복제 슬롯을 사용했습니다.

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

blocker               | Read replica with physical replication slot
database              |
blocker_identifier    | rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxxx
wait_event            | Not applicable
autovacuum_lagging_by | 554,080,689
suggestion            | Run the following query on the replica "rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxx" to find the long running query:                           
                      | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;                                                       
                      | Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.                                 +                      |
suggested_action      | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;","                                                                                 +
                      | [OR]                                                                                                                                                                                                 +
                      | ","Disable hot_standby_feedback","                                                                                                                                                                   +
                      | [OR]                                                                                                                                                                                                 +
                      | ","Delete the read replica if not needed"}

스트리밍 복제를 사용하는 읽기 전용 복제본

Amazon RDS를 사용하면 버전 13까지 이전 버전에서 물리적 복제 슬롯 없이 읽기 전용 복제본을 설정할 수 있습니다. 이 접근 방식은 기본이 WAL 파일을 더 공격적으로 재활용할 수 있도록 하여 오버헤드를 줄입니다. 이는 디스크 공간이 제한되고 가끔 ReplicaLag를 허용할 수 있는 환경에서 유용합니다. 그러나 슬롯이 없으면 WAL 파일이 누락되지 않도록 대기가 동기화된 상태로 유지되어야 합니다. Amazon RDS는 아카이브된 WAL 파일을 사용하여 지연될 경우 복제본이 따라잡는 데 도움이 되지만, 이 프로세스는 주의 깊은 모니터링이 필요하며 느릴 수 있습니다.

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

blocker               | Read replica with streaming replication slot
database              | Not applicable
blocker_identifier    | xx.x.x.xxx/xx
wait_event            | Not applicable
autovacuum_lagging_by | 610,146,760
suggestion            | Run the following query on the replica "xx.x.x.xxx" to find the long running query:                                                                                                                                                         +
                      | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343;                                                                                                                                                     +
                      | Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.                                       +
                      |
suggested_action      | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343;","                                                                                                                        +
                      | [OR]                                                                                                                                                                                                                                        +
                      | ","Disable hot_standby_feedback","                                                                                                                                                                                                          +
                      | [OR]                                                                                                                                                                                                                                        +
                      | ","Delete the read replica if not needed"}

권장 조치

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

임시 테이블

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;