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

아티클
01/23/2024

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

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

자동 진공이란?

PostgreSQL의 내부 데이터 일관성은 MVCC(Multi-Version Concurrency Control) 메커니즘을 기반으로 하며, 이를 통해 데이터베이스 엔진이 여러 버전의 행을 유지 관리하고 서로 다른 프로세스 간의 차단을 최소화하여 더 큰 동시성을 제공합니다.

PostgreSQL 데이터베이스는 적절한 유지 관리가 필요합니다. 예를 들어 행이 삭제되더라도 실제로는 제거되지 않습니다. 대신 행은 "데드"로 표시됩니다. 마찬가지로 업데이트의 경우 행이 "데드"로 표시되고 새 버전의 행이 삽입됩니다. 이러한 작업은 해당 버전을 볼 수 있는 모든 트랜잭션이 완료된 후에도 데드 튜플이라고 하는 데드 레코드를 남깁니다. 정리하지 않으면 데드 튜플이 남아 디스크 공간을 사용하고 테이블과 인덱스를 부풀려 쿼리 성능을 저하시킵니다.

PostgreSQL은 자동 진공이라는 프로세스를 사용하여 데드 튜플을 자동으로 정리합니다.

자동 진공 내부

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

vacuum_cost_page_hit: 이미 공유 버퍼에 있고 디스크 읽기가 필요하지 않은 페이지를 읽는 비용입니다. 기본값은 1로 설정됩니다.
vacuum_cost_page_miss: 공유 버퍼에 없는 페이지를 가져오는 비용입니다. 기본값은 10으로 설정됩니다.
vacuum_cost_page_dirty: 데드 튜플이 발견되었을 때 페이지에 쓰는 비용. 기본값은 20으로 설정됩니다.

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

autovacuum_vacuum_cost_limit는 자동 진공이 한 번의 이동으로 수행하는 작업의 양입니다.
autovacuum_vacuum_cost_delay 자동 진공이 autovacuum_vacuum_cost_limit 매개 변수에 지정된 비용 한도에 도달한 후 절전 모드로 전환되는 시간(밀리초)입니다.

현재 지원되는 모든 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;

다음 열은 자동 진공이 테이블 작업을 따라잡고 있는지 확인하는 데 도움이 됩니다.

dead_pct: 라이브 튜플과 비교할 때 데드 튜플의 백분율입니다.
last_autovacuum: 테이블이 마지막으로 자동 진공된 날짜입니다.
last_autovacuum_autoanalyze: 테이블이 자동으로 분석된 마지막 날짜입니다.

PostgreSQL은 언제 자동 진공을 트리거하나요?

자동 진공 작업( ANALYZE 또는 VACUUM)은 데드 튜플 수가 테이블의 총 행 수와 고정 임계값의 두 가지 요소에 종속된 특정 수를 초과할 때 트리거됩니다. 기본적으로 ANALYZE는 테이블의 10%와 50개의 행이 변경될 때 트리거되는 반면 VACUUM은 테이블의 20%와 50개의 행이 변경될 때 트리거됩니다. VACUUM 임계값이 ANALYZE 임계값의 두 배이므로 ANALYZE가 VACUUM보다 일찍 트리거됩니다.

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

자동 분석 = autovacuum_analyze_scale_factor * 튜플 + autovacuum_analyze_threshold
자동 진공 = autovacuum_vacuum_scale_factor * 튜플 + autovacuum_vacuum_threshold

예를 들어 다음 방정식을 사용하여 100개 행이 포함된 테이블에서 60개 행이 변경된 후 트리거를 분석하고 테이블에서 70개 행이 변경되면 진공 트리거를 분석합니다.

Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

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

 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_analyze_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;

참고 항목

쿼리는 "alter table" DDL 명령을 사용하여 테이블별로 자동 진공을 구성할 수 있다는 점을 고려하지 않습니다.

일반적인 자동 진공 문제

자동 진공 프로세스에서 발생할 수 있는 일반적인 문제가 나열된 다음 목록을 검토합니다.

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

자동 진공 프로세스는 모든 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 매개 변수가 고려됩니다.

자동 진공이 유지되지 않는 경우 다음 매개 변수가 변경될 수 있습니다.

매개 변수	설명
`autovacuum_vacuum_scale_factor`	기본값: `0.2`, 범위: `0.05 - 0.1`. 배율은 워크로드에 따라 다르며 테이블의 데이터 양에 따라 설정해야 합니다. 값을 변경하기 전에 워크로드 및 개별 테이블 볼륨을 조사합니다.
`autovacuum_vacuum_cost_limit`	기본값: `200`. 비용 한도가 증가할 수 있습니다. 변경 전후에 데이터베이스의 CPU 및 I/O 사용률을 모니터링해야 합니다.
`autovacuum_vacuum_cost_delay`	Postgres 버전 11 - 기본값: `20 ms`. 매개 변수를 `2-10 ms`로 줄일 수 있습니다. Postgres 버전 12 이상 - 기본값: `2 ms`.

참고 항목

autovacuum_vacuum_cost_limit 값은 실행 중인 자동 진공 작업자 간에 비례적으로 분배되므로 둘 이상의 작업자가 있는 경우 각 작업자에 대한 제한의 합이 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/DB 수]를 시작합니다.

클러스터에 더 많은 데이터베이스가 있는 경우 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.

참고 항목

이 오류 메시지는 오랫동안 관리되었습니다. 일반적으로 단일 사용자 모드로 전환할 필요가 없습니다. 대신 필요한 VACUUM 명령을 실행하고 VACUUM이 빠르게 실행되도록 튜닝을 수행할 수 있습니다. DML(데이터 조작 언어)은 실행할 수 없지만 VACUUM은 실행할 수 있습니다.

랩어라운드 문제는 데이터베이스가 진공 상태가 아니거나 자동 진공으로 제거할 수 없는 데드 튜플이 너무 많은 경우에 발생합니다. 그 이유는 다음과 같습니다.

많은 워크로드

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

장기 실행 트랜잭션

시스템에서 장기 실행 트랜잭션은 자동 진공이 실행되는 동안 데드 튜플을 제거하는 것을 허용하지 않습니다. 그들은 진공 과정을 방해합니다. 장기 실행 트랜잭션을 제거하면 자동 진공이 실행될 때 삭제를 위해 데드 튜플이 해제됩니다.

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

    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 랩어라운드 보호로 실행될 수 있습니다.
힌트 비트는 설정되지 않습니다.

해결 방법

13 이전의 Postgres 버전

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

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

Postgres 13 이상 버전

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

문제 해결 가이드

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

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

자동 진공이란?

자동 진공 내부

자동 진공 모니터링

PostgreSQL은 언제 자동 진공을 트리거하나요?

일반적인 자동 진공 문제

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

자동 진공 지속적으로 실행

maintenance_work_mem

많은 수의 데이터베이스

메모리 부족 오류

자동 진공이 너무 중단됨

자동 진공 매개 변수

너무 많은 자동 진공 작업자

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

많은 워크로드

장기 실행 트랜잭션

준비된 문

미사용 복제 슬롯

테이블별 요구 사항

삽입 전용 워크로드

해결 방법

13 이전의 Postgres 버전

Postgres 13 이상 버전

문제 해결 가이드

관련 콘텐츠

추가 리소스

`maintenance_work_mem`