Azure Database for PostgreSQL의 자동 진공 튜닝

이 문서에서는 Azure Database for PostgreSQL 의 자동 진공 기능과 데이터베이스 bloat 및 자동 진공 차단기를 모니터링하는 데 사용할 수 있는 기능 문제 해결 가이드에 대해 간략하게 설명합니다. 또한 데이터베이스가 긴급 상황 또는 랩어라운드 상황에서 얼마나 멀리 떨어져 있는지에 대한 정보를 제공합니다.

자동 진공이란?

자동 진공은 데드 튜플을 자동으로 정리하고 통계를 업데이트하는 PostgreSQL 백그라운드 프로세스입니다. 두 가지 주요 유지 관리 작업을 자동으로 실행하여 데이터베이스 성능을 유지하는 데 도움이 됩니다.

• VACUUM - 데드 튜플을 제거하고 PostgreSQL에서 해당 공간을 재사용 가능으로 표시하여 데이터베이스 파일 내의 공간을 회수합니다. 디스크에 있는 데이터베이스 파일의 물리적 크기를 반드시 줄이는 것은 아닙니다. 운영 체제에 공간을 반환하려면 단독 잠금 또는 유지 관리 기간과 같은 추가 고려 사항이 있는 테이블을 다시 작성하는 작업(예: VACUUM FULL 또는 pg_repack)을 사용합니다.
• ANALYZE - PostgreSQL 쿼리 플래너가 효율적인 실행 계획을 선택하는 데 사용하는 테이블 및 인덱스 통계를 수집합니다.

자동 진공이 제대로 작동하는지 확인하려면 자동 진공 서버 매개 변수 ON를 .로 설정합니다. 사용하도록 설정하면 PostgreSQL은 테이블에서 진공 또는 분석을 실행할 시기를 자동으로 결정하여 데이터베이스가 효율적이고 최적화된 상태를 유지하도록 합니다.

자동 진공 내부

자동 진공은 페이지를 읽어서 데드 튜플을 찾습니다. 데드 튜플을 찾지 못하면 자동 진공은 해당 페이지를 취소합니다. 자동 진공이 데드 튜플을 찾으면 제거합니다. 비용은 다음 매개 변수를 기반으로 합니다.

자동 진공이 수행하는 작업량은 다음 두 매개 변수에 따라 달라집니다.

현재 지원되는 모든 PostgreSQL 버전에서 기본값 autovacuum_vacuum_cost_limit 은 200입니다(실제로는 -1로 설정되므로 기본적으로 200인 일반 vacuum_cost_limit값과 같음).

기본값 autovacuum_vacuum_cost_delay 은 PostgreSQL 버전 12 이상에서 2밀리초입니다(버전 11에서는 20밀리초).

버퍼 사용 제한(PostgreSQL 16 이상)

PostgreSQL 버전 16부터 이 매개 변수를 사용하여 vacuum_buffer_usage_limit VACUUM, ANALYZE 및 자동 진공 작업 중에 메모리 사용량을 제어할 수 있습니다.

이 매개 변수는 VACUUM 및 자동 진공이 공유 버퍼에서 너무 많은 유용한 페이지를 제거하지 못하도록 방지하여 유지 관리 작업 중 전반적인 데이터베이스 성능을 향상시킬 수 있습니다. 기본값은 일반적으로 shared_buffers을 기준으로 설정되며, 일반 데이터베이스 작업의 요구 사항과 vacuum performance의 균형을 맞추도록 구성할 수 있습니다.

자동 진공의 최대 임계값(PostgreSQL 18 이상)

PostgreSQL 버전 18부터 매개 변수를 사용하여 autovacuum_vacuum_max_threshold 자동 진공을 트리거하는 튜플 업데이트 또는 삭제 수에 대한 상한을 설정할 수 있습니다.

이 매개 변수는 기본 크기 조정 인자 기반 트리거로 인해 자동 진공이 실행되기 전에 너무 오랫동안 기다려야 하는 대형 테이블에 특히 유용합니다.

자동 진공은 1초에 50번(50*20ms=1000ms) 활성화됩니다. 깨어날 때마다 자동 진공은 200페이지를 읽습니다.

즉, 오토백큠은 1초 안에 다음을 수행할 수 있습니다.

• 데드 튜플이 있는 모든 페이지가 공유 버퍼에서 발견되는 경우 ~80MB/초 [(200페이지/vacuum_cost_page_hit) * 50 * 8KB/페이지].
• 데드 튜플이 있는 모든 페이지를 디스크에서 읽는 경우 ~8MB/초 [(200페이지/vacuum_cost_page_miss) * 50 * 8KB/페이지].
• ~4MB/초 [(200페이지/vacuum_cost_page_dirty) * 50 * 8KB/페이지] 자동 진공은 최대 4MB/초를 쓸 수 있습니다.

자동 진공 모니터링

Azure Database for PostgreSQL은 자동 진공을 모니터링하기 위한 다음 메트릭을 제공합니다.

자동 진공 메트릭을 사용하여 Azure Database for PostgreSQL 유연한 서버에 대한 자동 진공 성능을 모니터링하고 조정할 수 있습니다. 각 메트릭은 30분 간격으로 내보내며 최대 93일 동안 보관됩니다. 특정 메트릭에 대한 경고를 만들 수 있으며 DatabaseName 차원을 사용하여 메트릭 데이터를 분할하고 필터링할 수 있습니다.

자동 진공 메트릭을 사용하도록 설정하는 방법

• 자동 진공 메트릭은 기본적으로 사용하지 않도록 설정됩니다.
• 이러한 메트릭을 사용하도록 설정하려면 서버 매개 변수 metrics.autovacuum_diagnostics를 ON으로 설정합니다.
• 이 매개 변수는 동적이므로 인스턴스를 다시 시작할 필요가 없습니다.

자동 진공 메트릭 목록

자동 진공 메트릭 사용 시 고려 사항

• DatabaseName 차원을 사용하는 자동 진공 메트릭에는 30개 데이터베이스 제한이 있습니다.
• Burstable SKU에서 DatabaseName 차원을 사용하는 메트릭의 경우, 데이터베이스의 최대 허용 수는 10개입니다.
• DatabaseName 차원 제한은 데이터베이스 만들기 순서를 반영하는 OID 열에 적용됩니다.

자세한 내용은 자동 진공 메트릭을 참조하세요.

다음 쿼리를 사용하여 자동 진공을 모니터링합니다.

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

다음 열은 자동 청소 기능이 테이블 활동을 따라잡고 있는지 확인하는 데 도움이 됩니다.

자동 진공 트리거

자동 진공 작업(ANALYZE 또는 VACUUM)은 데드 튜플 수가 특정 수를 초과하면 트리거됩니다. 이 숫자는 테이블의 총 행 수와 고정 임계값의 두 가지 요인에 따라 달라집니다. ANALYZE 트리거는 기본적으로 테이블의 10개% 50개 행 변경이 발생하는 반면 VACUUM 트리거는 테이블 20개% 50개 행 변경이 발생할 때 발생합니다. VACUUM 임계값은 ANALYZE 임계값보다 두 배 높기 때문에 ANALYZE 트리거는 VACUUM보다 일찍 트리거됩니다.

PostgreSQL 버전 13 이상에서는 테이블 20개% 및 행 삽입 1,000개 이상이 발생하면 기본적으로 ANALYZE 트리거가 트리거됩니다.

각 작업에 대한 정확한 방정식은 다음과 같습니다.

• 자동 분석 = autovacuum_analyze_scale_factor * 튜플 + autovacuum_analyze_threshold 또는 autovacuum_vacuum_insert_scale_factor * 튜플 + autovacuum_vacuum_insert_threshold(PostgreSQL 버전 13 이상)
• 자동 진공 = autovacuum_vacuum_scale_factor * 튜플 + autovacuum_vacuum_threshold

예를 들어 행이 100개인 테이블이 있는 경우 다음 수식은 분석 및 진공 작업이 트리거되는 경우를 보여 줍니다.

업데이트 및 삭제의 경우: Autoanalyze = 0.1 * 100 + 50 = 60Autovacuum = 0.2 * 100 + 50 = 70

테이블에서 60개 행이 변경된 후 ANALYZE 트리거가 트리거되고, 테이블에서 70개의 행이 변경되면 VACUUM 이 트리거됩니다.

삽입의 경우: Autoanalyze = 0.2 * 100 + 1000 = 1020

테이블에 1,020개의 행이 삽입되면 ANALYZE 트리거가 발생합니다.

수식에 사용되는 매개 변수에 대한 설명은 다음과 같습니다.

다음 쿼리를 사용하여 데이터베이스의 테이블을 나열하고 자동 진공 프로세스에 적합한 테이블을 식별합니다.

 SELECT *
      ,n_dead_tup > av_threshold AS av_needed
      ,CASE
        WHEN reltuples > 0
          THEN round(100.0 * n_dead_tup / (reltuples))
        ELSE 0
        END AS pct_dead
    FROM (
      SELECT N.nspname
        ,C.relname
        ,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
        ,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
        ,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
        ,pg_stat_get_live_tuples(C.oid) AS n_live_tup
        ,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
        ,C.reltuples AS reltuples
        ,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
        ,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
        ,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_autoanalyze_time(C.oid))) AS last_analyze
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN (
          'r'
          ,'t'
          )
        AND N.nspname NOT IN (
          'pg_catalog'
          ,'information_schema'
          )
        AND N.nspname !~ '^pg_toast'
      ) AS av
    ORDER BY av_needed DESC ,n_dead_tup DESC;

일반적인 자동 진공 문제

자동 진공 프로세스에 대한 일반적인 문제의 다음 목록을 검토합니다.

사용량이 많은 서버를 따라가지 못함

자동 진공 프로세스는 모든 I/O 작업의 비용을 예측하고, 수행하는 각 작업에 대한 합계를 누적하고, 비용의 상한에 도달하면 일시 중지합니다. 이 프로세스는 두 개의 서버 매개 변수를 autovacuum_vacuum_cost_delayautovacuum_vacuum_cost_limit사용합니다.

기본적으로 autovacuum_vacuum_cost_limit -1로 설정됩니다. 즉, 자동 진공 비용 제한이 매개 변수와 vacuum_cost_limit 동일한 값을 사용합니다. 기본값 vacuum_cost_limit 은 200입니다. vacuum_cost_limit 는 수동 진공의 비용을 나타냅니다.

autovacuum_vacuum_cost_limit를 -1로 설정하면 자동 진공은 vacuum_cost_limit 매개 변수를 사용합니다. autovacuum_vacuum_cost_limit를 -1보다 큰 값으로 설정하면 자동 진공은 autovacuum_vacuum_cost_limit 매개 변수를 사용합니다.

자동 진공이 유지되지 않는 경우 다음 매개 변수를 변경하는 것이 좋습니다.

자동 진공 지속적으로 실행

자동 진공이 지속적으로 실행되는 경우 서버의 CPU 및 I/O 사용률에 영향을 줄 수 있습니다. 가능한 이유는 다음과 같습니다.

maintenance_work_mem

자동 진공 데몬은 기본적으로 -1로 설정된 autovacuum_work_mem을(를) 사용합니다. 이 기본 설정은 autovacuum_work_mem이 maintenance_work_mem 매개 변수와 동일한 값을 사용한다는 것을 의미합니다. 이 문서에서는 autovacuum_work_mem을 -1으로 설정하는 것으로 가정하며, 자동 진공 데몬이 maintenance_work_mem을 사용합니다.

maintenance_work_mem가 낮은 경우 Azure Database for PostgreSQL 유연한 서버 인스턴스에서 최대 2GB까지 늘릴 수 있습니다. 일반적으로 RAM 1GB당 50MB를 maintenance_work_mem에 할당합니다.

많은 수의 데이터베이스

자동 진공은 autovacuum_naptime초마다 각 데이터베이스에서 작업자를 시작하려고 시도합니다.

예를 들어 서버에 60개의 데이터베이스가 있고 autovacuum_naptime이 60초로 설정된 경우 자동 진공 작업자는 1초마다 [autovacuum_naptime/데이터베이스 수]를 시작합니다.

클러스터에 더 많은 데이터베이스가 있는 경우 autovacuum_naptime를 증가시키십시오. 동시에 autovacuum_cost_limit 매개변수를 늘리고 autovacuum_cost_delay 매개변수를 줄여 자동 진공 프로세스를 더욱 공격적으로 만듭니다. 기본값인 3에서 autovacuum_max_workers을(를) 4 또는 5로 늘릴 수도 있습니다.

메모리 부족 오류

지나치게 공격적인 maintenance_work_mem 값은 시스템에서 주기적으로 메모리 부족 오류를 일으킬 수 있습니다. 매개 변수를 변경하기 전에 서버에서 사용 가능한 RAM을 이해합니다 maintenance_work_mem .

자동 진공이 너무 중단됨

자동 진공이 너무 많은 리소스를 사용하는 경우 다음 작업을 시도합니다.

자동 진공 매개 변수

매개 변수 autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit, 및 autovacuum_max_workers을 평가하십시오. 자동 진공 매개 변수를 부적절하게 설정하면 자동 진공이 너무 중단되는 시나리오가 발생할 수 있습니다.

자동 진공이 너무 중단되면 다음 작업을 고려하세요.

• 기본값인 200보다 높게 설정하면 autovacuum_vacuum_cost_delay를 늘리고 autovacuum_vacuum_cost_limit를 줄입니다.
• autovacuum_max_workers을 기본값인 3보다 높게 설정한 경우 그 수를 줄이십시오.

너무 많은 자동 진공 작업자

자동 진공 작업자 수를 늘려도 진공 속도는 증가하지 않습니다. 많은 수의 자동 진공 작업자를 사용하지 마세요.

자동 진공 작업자 수를 늘리면 메모리 사용량이 늘어나게 됩니다. 값 maintenance_work_mem에 따라 성능이 저하될 수 있습니다.

각 자동 진공 작업자 프로세스는 총 autovacuum_cost_limit개 중 (1/autovacuum_max_workers)만 가져오므로 작업자 수가 많으면 각 작업자가 느려집니다.

작업자 수를 늘리면, autovacuum_vacuum_cost_limit를 늘리거나 autovacuum_vacuum_cost_delay를 줄여 진공 프로세스를 더 빠르게 할 수 있습니다.

그러나 테이블 수준 autovacuum_vacuum_cost_delay 또는 autovacuum_vacuum_cost_limit 매개 변수에서 매개 변수를 설정하는 경우 해당 테이블에서 실행되는 작업자는 분산 알고리즘 [autovacuum_cost_limit/autovacuum_max_workers]에서 고려되지 않습니다.

자동 진공 TXID(트랜잭션 ID) 랩어라운드 보호

데이터베이스가 트랜잭션 ID 래핑 방지로 실행되면 다음 오류와 같은 오류 메시지가 표시됩니다.

Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.

랩어라운드 문제는 데이터베이스가 진공 처리되지 않았거나 자동 진공 처리가 너무 많은 데드 튜플을 제거하지 못할 때 발생합니다.

이 문제의 가능한 원인은 다음과 같습니다.

많은 워크로드

워크로드가 많으면 짧은 기간 내에 너무 많은 데드 튜플이 생겨 자동 진공이 따라잡기 어렵습니다. 시스템의 데드 튜플이 일정 기간 동안 누적되어 쿼리 성능이 저하되고 랩어라운드 상황이 발생합니다. 이러한 상황이 발생하는 한 가지 이유는 자동 진공 매개 변수가 적절하게 설정되지 않고 사용량이 많은 서버를 따라가지 못하기 때문일 수 있습니다.

장기 실행 트랜잭션

시스템에서 장기 실행 트랜잭션이 발생하면 자동 진공이 유효하지 않은 튜플을 제거할 수 없습니다. 그들은 진공 과정을 방해합니다. 장기 실행 트랜잭션을 제거하면 자동 진공이 실행될 때 삭제를 위해 데드 튜플이 해제됩니다.

장기 실행 트랜잭션은 다음 쿼리를 사용하여 쿼리할 수 있습니다.

    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;

준비된 문

커밋되지 않은 준비된 문이 있으면 자동 진공이 데드 튜플을 제거하는 것을 방해합니다. 다음 쿼리는 커밋되지 않은 준비된 문을 찾는 데 도움이 됩니다.

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

COMMIT PREPARED 또는 ROLLBACK PREPARED를 사용하여 이러한 문을 커밋하거나 롤백하세요.

미사용 복제 슬롯

사용되지 않는 복제 슬롯은 자동 진공이 데드 튜플을 요구하는 것을 방지합니다. 다음 쿼리는 사용되지 않는 복제 슬롯을 식별하는 데 도움이 됩니다.

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

사용하지 않는 복제 슬롯을 삭제하려면 pg_drop_replication_slot()을 사용합니다.

데이터베이스가 트랜잭션 ID 랩어라운드 보호로 실행되면 앞에서 언급한 대로 방해 요소를 확인하고 자동 진공이 계속되고 완료될 수 있도록 방해 요소를 수동으로 제거합니다. autovacuum_cost_delay를 0으로 설정하고 autovacuum_cost_limit를 200보다 큰 값으로 증가시켜 자동 진공 속도를 높일 수도 있습니다. 하지만 이러한 매개 변수의 변경 내용은 기존 자동 진공 작업자에게는 적용되지 않습니다. 매개 변수 변경 내용을 적용하려면 데이터베이스를 다시 시작하거나 기존 작업자를 수동으로 종료합니다.

테이블별 요구 사항

개별 테이블에 대한 자동 진공 매개 변수를 설정할 수 있습니다. 이러한 설정은 크고 작은 테이블에 특히 중요합니다. 예를 들어 행이 100개만 포함된 작은 테이블의 경우 자동 진공은 이전에 계산된 대로 70개 행이 변경되면 VACUUM 작업을 트리거합니다. 이 테이블을 자주 업데이트하는 경우 하루에 수백 개의 자동 진공 작업이 표시될 수 있습니다. 이러한 작업은 변경 비율이 크게 중요하지 않은 다른 테이블을 자동 진공이 유지 관리하는 것을 방해합니다. 또는 10억 개의 행이 포함된 테이블에서 자동 진공 작업을 트리거하려면 2억 개의 행을 변경해야 합니다. 자동 진공 매개 변수를 적절하게 설정하면 이러한 시나리오를 방지할 수 있습니다.

각 테이블에 대한 자동 진공 설정을 설정하려면 다음 예제와 같이 서버 매개 변수를 변경합니다.

    ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);
    -- For PostgreSQL 16 and later:
    ALTER TABLE <table name> SET (vacuum_buffer_usage_limit = 'xx MB');

삽입 전용 워크로드

PostgreSQL 버전 13 이하에서는 데드 튜플이 없고 회수해야 하는 여유 공간이 없으므로 삽입 전용 워크로드가 있는 테이블에서 자동 진공이 실행되지 않습니다. 그러나 새 데이터가 있으므로 자동 분석은 삽입 전용 워크로드에 대해 실행됩니다. 이 동작의 단점은 다음과 같습니다.

• 테이블의 표시 유형 맵은 업데이트되지 않으므로 특히 인덱스 전용 검사가 있는 쿼리 성능은 시간이 지남에 따라 저하되기 시작합니다.
• 데이터베이스는 트랜잭션 ID 랩어라운드 보호로 실행될 수 있습니다.
• 힌트 비트가 설정되지 않았습니다.

솔루션

PostgreSQL 버전 13 및 이전 버전

pg_cron 확장을 사용하면 테이블에서 주기적인 진공 분석을 예약하도록 cron 작업을 설정할 수 있습니다. cron 작업의 빈도는 워크로드에 따라 다릅니다.

지침은 Azure Database for PostgreSQL에서 pg_cron 사용하는 방법에 대한 특별한 고려 사항을 참조하세요.

PostgreSQL 13 이상 버전

자동 진공은 삽입 전용 워크로드가 있는 테이블에서 실행됩니다. 두 개의 서버 매개 변수 autovacuum_vacuum_insert_threshold 와 autovacuum_vacuum_insert_scale_factor삽입 전용 테이블에서 자동 진공을 트리거할 수 있는 시기를 제어하는 데 도움이 됩니다.

문제 해결 가이드

Azure Database for PostgreSQL 유연한 서버는 포털에서 데이터베이스 또는 개별 스키마 수준에서 bloat을 모니터링하고 자동 진공 프로세스에 대한 잠재적인 차단기를 식별하는 데 도움이 되는 문제 해결 가이드를 제공합니다.

다음 두 가지 문제 해결 가이드를 사용할 수 있습니다.

• 자동 진공 모니터링 - 이 가이드를 사용하여 데이터베이스 또는 개별 스키마 수준에서 블로트를 모니터링합니다.
• 자동 진공 차단 및 랩어라운드 - 이 가이드는 잠재적인 자동 진공 차단기를 식별하고 서버의 데이터베이스가 랩어라운드 또는 긴급 상황에서 얼마나 멀리 떨어져 있는지에 대한 정보를 제공합니다.

문제 해결 가이드는 잠재적인 문제를 완화하기 위한 권장 사항도 공유합니다. 문제 해결 가이드를 설정하고 사용하는 방법에 대한 자세한 내용은 설치 문제 해결 가이드를 참조하세요.

자동 진공 프로세스 종료: pg_signal_autovacuum_worker 역할

자동 진공은 데이터베이스의 효율적인 스토리지 및 성능 유지 관리에 도움이 되므로 중요한 백그라운드 프로세스입니다. 일반 자동 진공 프로세스는 deadlock_timeout 후에 스스로 취소됩니다. 사용자가 테이블에 DDL 문을 실행하는 경우 사용자는 deadlock_timeout 간격까지 기다려야 할 수 있습니다. 자동 진공은 다른 연결 요청에서 해당 테이블에 대한 읽기 또는 쓰기 실행을 허용하지 않으며, 이로 인해 트랜잭션의 대기 시간이 증가할 수 있습니다.

PostgreSQL에서 새로운 역할을 pg_signal_autovacuum_worker 도입하여 비수용자 멤버가 진행 중인 자동 진공 작업을 종료할 수 있도록 했습니다. 새 역할은 사용자가 자동 진공 프로세스에 대한 안전하고 제어된 액세스를 얻는 데 도움이 됩니다. 슈퍼유저가 아닌 사용자는 pg_signal_autovacuum_worker 역할이 부여되면 pg_terminate_backend 명령을 사용하여 오토백업 프로세스를 취소할 수 있습니다. 이 역할은 pg_signal_autovacuum_worker PostgreSQL 버전 15 이상에서 Azure Database for PostgreSQL에서 사용할 수 있습니다.

반복적인 자동 진공 작업자를 위한 권장 방법

래핑 방지 자동 진공과 같은 드문 시나리오에서는 작업자가 트랜잭션 ID 소진을 방지하는 데 중요하기 때문에 종료 직후에 다시 시작될 수 있습니다. 반복되는 충돌을 최소화하려면 다음 단계를 수행합니다.

• 종료 전에 DDL 작업을 목록에 등록합니다.

  • 세션 1: DDL 문을 준비하고 실행합니다.

  • 세션 2: 자동 진공 프로세스를 종료합니다.

    중요합니다

    이러한 두 단계는 연속으로 실행되어야 합니다. DDL 문이 너무 오랫동안 차단된 상태로 유지되면 잠금이 유지되고 서버에서 다른 DML 작업이 차단될 수 있습니다.

• 자동 진공을 종료하고 DDL을 실행합니다. DDL을 즉시 실행해야 하는 경우:

  • pg_terminate_backend()를 사용하여 자동 진공 프로세스를 종료합니다.
  • 종료 직후 DDL 문을 실행합니다.

반복되는 충돌을 방지하는 단계:

1. 사용자에게 역할 부여

   GRANT pg_signal_autovacuum_worker TO app_user;
   

   1. 자동 진공 프로세스 ID 식별

   SELECT pid, query FROM pg_stat_activity WHERE query LIKE '%autovacuum%' and pid!=pg_backend_pid();
   

2. 자동청소 종료

   SELECT pg_terminate_backend(<pid>);
   

3. DDL 문 즉시 실행

   ALTER TABLE my_table ADD COLUMN new_col TEXT;
   

Azure Advisor 권장 사항

Azure Advisor 권장 사항은 서버의 bloat 비율이 높거나 서버가 트랜잭션 랩어라운드 시나리오에 접근하는지 여부를 사전에 식별합니다. 또한 권장 사항에 대한 Azure Advisor 경고를 만들 수도 있습니다.

권장 사항은 다음과 같습니다.

• 높은 bloat 비율: 높은 bloat 비율은 여러 가지 방법으로 서버 성능에 영향을 줄 수 있습니다. 한 가지 중요한 문제는 PostgreSQL 엔진 최적화 프로그램이 최상의 실행 계획을 선택하는 데 어려움을 겪어 쿼리 성능이 저하될 수 있다는 것입니다. 따라서 이러한 성능 문제를 방지하기 위해 서버의 팽창 백분율이 특정 임곗값에 도달하면 권장 사항이 트리거됩니다.

• 트랜잭션 랩어라운드: 이 시나리오는 서버에서 발생할 수 있는 가장 심각한 문제 중 하나입니다. 서버가 이 상태에 있으면 더 이상 트랜잭션 수락이 중지되어 서버가 읽기 전용이 될 수 있습니다. 따라서 서버가 10억 트랜잭션 임계값을 초과하면 권장 사항이 트리거됩니다.

관련 콘텐츠

• Azure Database for PostgreSQL에서 pg_repack을 사용하여 전체 백업 수행
• Azure Database for PostgreSQL의 높은 CPU 사용률 문제 해결
• Azure Database for PostgreSQL의 높은 메모리 사용률 문제 해결
• Azure Database for PostgreSQL에서 높은 IOPS 사용률 문제 해결
• Azure Database for PostgreSQL에서 느리게 실행되는 쿼리 문제 해결 및 식별
• Azure Database for PostgreSQL의 서버 매개 변수