Azure Database for PostgreSQL - 유연한 서버의 자동 진공 튜닝

적용 대상: Azure Database for PostgreSQL - 유연한 서버

이 문서에서는 Azure Database for PostgreSQL 유연한 서버에 대한 자동 진공 기능의 개요와 데이터베이스 팽창, 자동 진공 방해 요소를 모니터링하는 데 사용할 수 있는 기능 문제 해결 가이드를 제공합니다. 또한 데이터베이스가 긴급 또는 랩어라운드 상황에서 얼마나 멀리 떨어져 있는지에 대한 정보를 제공합니다.

자동 진공이란?

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

• 진공 - 데드 튜플을 제거하여 디스크 공간을 확보합니다.
• 분석 - PostgreSQL 최적화 프로그램에서 쿼리에 가장 적합한 실행 경로를 선택하는 데 도움이 되는 통계를 수집합니다.

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

자동 진공 내부

자동 진공은 데드 튜플을 찾는 페이지를 읽고, 아무것도 발견되지 않으면 자동 진공은 페이지를 삭제합니다. 자동 진공이 데드 튜플을 찾으면 제거합니다. 비용은 다음을 기준으로 합니다.

자동 진공이 수행하는 작업의 양은 두 가지 매개 변수에 따라 다릅니다.

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

autovacuum_vacuum_cost_delay의 경우 Postgres 버전 11에서는 기본값이 20밀리초이고 Postgres 버전 12 이상에서는 기본값이 2밀리초입니다.

자동 진공은 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/초를 쓸 수 있습니다.

자동 진공 모니터링

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

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)은 데드 튜플 수가 테이블의 총 행 수와 고정 임계값의 두 가지 요소에 종속된 특정 수를 초과할 때 트리거됩니다. 기본적으로 분석은 테이블의 10%와 50개의 행이 변경될 때 트리거되는 반면 진공은 테이블의 20%와 50개의 행이 변경될 때 트리거됩니다. VACUUM 임계값이 ANALYZE 임계값의 두 배이므로 ANALYZE가 VACUUM보다 일찍 트리거됩니다. PG 버전 >=13의 경우 기본적으로 분석은 테이블의 20%와 1000개의 행이 삽입될 때 트리거됩니다.

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

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

예를 들어 행이 100개인 테이블이 있는 경우입니다. 다음 수식은 분석 및 진공이 트리거되는 시기에 대한 정보를 제공합니다.

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

테이블에서 60개의 행이 변경된 후 트리거를 분석하고, 테이블에서 70개의 행이 변경되면 진공이 트리거됩니다.

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

테이블에 1,020개의 행이 삽입된 후 트리거 분석

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

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

 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_delay 및 autovacuum_vacuum_cost_limit는 프로세스에서 사용되는 두 개의 서버 매개 변수입니다.

기본적으로 autovacuum_vacuum_cost_limit는 -1로 설정되며, 이는 자동 진공 비용 제한이 매개 변수 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 및 IO 사용률에 영향을 줄 수 있습니다. 다음과 같은 몇 가지 가능한 이유가 있습니다.

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 매개 변수를 줄이고 autovacuum_max_workers를 기본값인 3에서 4 또는 5로 늘리면 더 적극적으로 만들 수 있습니다.

메모리 부족 오류

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

자동 진공이 너무 중단됨

자동 진공이 더 많은 리소스를 사용하는 경우 다음 작업을 수행할 수 있습니다.

자동 진공 매개 변수

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

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

• 기본값인 200보다 높게 설정하면 autovacuum_vacuum_cost_delay를 늘리고 autovacuum_vacuum_cost_limit를 줄입니다.
• 기본값인 3보다 높게 설정된 경우 autovacuum_max_workers의 수를 줄입니다.

너무 많은 자동 진공 작업자

자동 진공 작업자 수를 늘려도 진공 속도는 증가하지 않습니다. 많은 수의 자동 진공 작업자를 사용하는 것은 권장되지 않습니다.

자동 진공 작업자의 수를 늘리면 더 많은 메모리를 사용하게 되며 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);

삽입 전용 워크로드

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

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

해결 방법

Postgres 버전 <= 13

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

pg_cron을 사용하는 단계별 지침은 확장을 검토합니다.

Postgres 13 이상 버전

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

문제 해결 가이드

Azure Database for PostgreSQL 유연한 서버 포털에서 사용할 수 있는 기능 문제 해결 가이드를 사용하여 자동 진공 프로세스에 대한 잠재적인 방해 요소를 식별하는 동시에 데이터베이스 또는 개별 스키마 수준에서 팽창을 모니터링할 수 있습니다. 두 가지 문제 해결 가이드가 제공되는데 첫 번째는 데이터베이스 또는 개별 스키마 수준에서 블로트를 모니터링하는 데 사용할 수 있는 자동 진공 모니터링입니다. 두 번째 문제 해결 가이드는 잠재적인 자동 진공 방해 요소를 식별하는 데 도움이 되는 자동 진공 방해 요소 및 랩어라운드입니다. 또한 서버의 데이터베이스가 랩어라운드 또는 긴급 상황에서 얼마나 멀리 떨어져 있는지에 대한 정보를 제공합니다. 문제 해결 가이드는 잠재적인 문제를 완화하기 위한 권장 사항도 공유합니다. 이를 사용하도록 문제 해결 가이드를 설정하는 방법은 설정 문제 해결 가이드를 따르세요.

Azure Advisor 권장 사항

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

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

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

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

Azure Database for PostgreSQL 제품 팀과 제안 및 버그를 공유합니다.

관련 콘텐츠

• 높은 CPU 사용률
• 높은 메모리 사용률
• 느린 쿼리 식별
• 서버 매개 변수