Amazon RDS for PostgreSQL에서 PostgreSQL Autovacuum 사용
자동 정리 기능을 사용하여 PostgreSQL DB 인스턴스의 상태를 유지 관리하는 것이 좋습니다. Autovacuum은 VACUUM 및 ANALYZE 명령의 시작을 자동화합니다. Autovacuum은 삽입되고 업데이트되거나 삭제된 튜플 수가 많은 테이블이 있는지 확인합니다. 확인이 끝나면 Autovacuum은 PostgreSQL 데이터베이스에서 폐기된 데이터 또는 튜플을 제거하여 스토리지를 회수합니다.
기본적으로 자동 정리는 기본 PostgreSQL DB 파라미터 그룹을 사용하여 생성한 Amazon RDS for PostgreSQL DB 인스턴스에서 사용 설정됩니다. 여기에는 default.postgres10, default.postgres11 등이 포함됩니다. 모든 기본 PostgreSQL DB 파라미터 그룹의 rds.adaptive_autovacuum 파라미터가 1로 설정되어 자동 정리 기능이 사용됩니다. 자동 정리 기능과 관련된 다른 구성 파라미터도 기본적으로 설정됩니다. 이러한 기본값은 다소 일반적이기 때문에 특정 워크로드에 대해 자동 정리 기능과 관련된 일부 파라미터를 조정하면 도움이 될 수 있습니다.
다음에서 자동 정리에 대한 자세한 정보와 RDS for PostgreSQL DB 인스턴스에 대한 파라미터를 조정하는 방법을 확인할 수 있습니다. 높은 수준의 정보는 PostgreSQL로 작업하기 위한 모범 사례 섹션을 참조하세요.
주제
Autovacuum에 메모리 할당
autovacuum 성능에 영향을 미치는 가장 중요한 파라미터 중 하나는 maintenance_work_memmaintenance_work_mem 파라미터 값을 너무 낮게 설정하면 vacuum 프로세스가 테이블을 여러 번 스캔해야 작업이 완료될 수 있습니다. 이러한 다중 스캔은 성능에 부정적인 영향을 줄 수 있습니다.
maintenance_work_mem 파라미터 값을 결정하는 계산을 할 때 다음 두 가지 사항에 유의하세요.
이 파라미터의 기본 단위는 킬로바이트(KB)입니다.
-
maintenance_work_mem파라미터는autovacuum_max_workers파라미터와 함께 작동합니다. 작은 테이블이 많이 있는 경우에는 autovacuum_max_workers를 더 많이 할당하고maintenance_work_mem을 더 적게 할당합니다. 큰 테이블이 많이 있는 경우(100GB 이상)에는 메모리를 더 많이 할당하고 작업자 프로세스를 더 적게 할당합니다. 가장 큰 테이블에서 성공적으로 작업을 수행하려면 충분한 메모리를 할당해 두어야 합니다. 각각의autovacuum_max_workers는 할당된 메모리를 사용할 수 있습니다. 따라서 작업자 프로세스와 메모리를 합한 양이 할당하려는 전체 메모리 양과 같아야 합니다.
일반적으로 큰 호스트의 경우 maintenance_work_mem 파라미터를 1~2기가바이트 사이(1,048,576 ~ 2,097,152KB) 값으로 설정합니다. 매우 큰 호스트의 경우 파라미터를 2~4기가바이트 사이(2,097,152 ~ 4,194,304KB) 값으로 설정합니다. 이 파라미터에 설정하는 값은 워크로드에 따라 달라져야 합니다. Amazon RDS는 이 파라미터의 기본값을 다음과 같이 계산된 킬로바이트로 업데이트했습니다.
GREATEST({DBInstanceClassMemory/63963136*1024},65536).
트랜잭션 ID 랩어라운드의 가능성 감소
경우에 따라 Autovacuum과 관련된 파라미터 그룹 설정이 트랜잭션 ID 랩어라운드를 방지하기에 충분히 공격적이지 않을 수 있습니다. 이를 해결하기 위해 RDS for PostgreSQL은 자동 정리 파라미터 값을 자동으로 조정하는 메커니즘을 제공합니다. 적응형 autovacuum 파라미터 튜닝은 RDS for PostgreSQL의 기능입니다. TransactionID wraparound
동적 파라미터 rds.adaptive_autovacuum이 ON으로 설정된 RDS for PostgreSQL 인스턴스의 경우 적응형 autovacuum 파라미터 튜닝이 기본적으로 활성화됩니다. 이 설정을 항상 활성화해 놓는 것이 좋습니다. 그러나 적응형 Autovacuum 파라미터 튜닝을 끄려면 rds.adaptive_autovacuum 파라미터를 0 또는 OFF로 설정합니다.
Amazon RDS가 자동 정리 파라미터를 조정하더라도 트랜잭션 ID 랩어라운드는 계속 가능합니다. 트랜잭션 ID 랩어라운드에 대한 Amazon CloudWatch 경보를 구현하는 것이 좋습니다. 자세한 내용은 AWS 데이터베이스 블로그의 RDS for PostgreSQL에서 트랜잭션 ID 랩어라운드에 대한 조기 경고 시스템 구축
적응형 자동 정리 파라미터 튜닝을 사용 설정한 경우 Amazon RDS는 CloudWatch 지표 MaximumUsedTransactionIDs가 autovacuum_freeze_max_age 파라미터 값 또는 500,000,000 중 큰 값에 도달하면 자동 정리 파라미터를 조정하기 시작합니다.
테이블이 계속 트랜잭션 ID 랩어라운드 방향으로 향하면 Amazon RDS는 Autovacuum의 파라미터를 계속 조정합니다. 이러한 각각의 조정은 랩어라운드를 피하기 위해 Autovacuum에 더 많은 리소스를 할애합니다. Amazon RDS는 다음 자동 정리 관련 파라미터를 업데이트합니다.
RDS는 새 값이 Autovacuum을 보다 공격적으로 만드는 경우에만 이러한 파라미터를 수정합니다. 파라미터는 DB 인스턴스의 메모리에서 수정됩니다. 파라미터 그룹의 값은 변경되지 않습니다. 현재 인 메모리 설정을 보려면 PostgreSQL SHOW
Amazon RDS가 이러한 Autovacuum 파라미터를 수정하면 영향받은 DB 인스턴스에 대한 이벤트를 생성합니다. 이 이벤트는 AWS Management Console 및 Amazon RDS API에서 볼 수 있습니다. MaximumUsedTransactionIDs CloudWatch 지표가 임계값 미만의 값을 반환하면 Amazon RDS는 메모리의 자동 정리 관련 파라미터를 파라미터 그룹에 지정된 값으로 다시 설정합니다. 그런 다음 이 변경에 해당하는 다른 이벤트를 생성합니다.
데이터베이스의 테이블을 vacuum해야 하는지 여부를 결정
다음 쿼리를 사용하여 데이터베이스의 vacuum되지 않은 트랜잭션 수를 표시할 수 있습니다. 데이터베이스 datfrozenxid 행의 pg_database 열은 해당 데이터베이스에 나타나는 정상 트랜잭션 ID의 하한값입니다. 이 열은 데이터베이스 내 테이블 단위 relfrozenxid 값 중 최소값입니다.
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;
예를 들어 앞의 쿼리를 실행하면 다음과 같은 결과가 나올 수 있습니다.
datname | age mydb | 1771757888 template0 | 1721757888 template1 | 1721757888 rdsadmin | 1694008527 postgres | 1693881061 (5 rows)
데이터베이스의 수명이 20억 트랜잭션 ID에 도달하면 트랜잭션 ID(XID) 랩어라운드가 발생하고 데이터베이스는 읽기 전용이 됩니다. 이 쿼리를 사용하면 지표를 생성하여 하루에 몇 번 실행되도록 할 수 있습니다. 기본적으로 autovacuum은 트랜잭션 수명을 200,000,000(autovacuum_freeze_max_age
샘플 모니터링 전략은 다음과 같습니다.
autovacuum_freeze_max_age값을 2억 개 트랜잭션으로 설정하십시오.테이블이 5억 개의 vacuum되지 않은 트랜잭션에 도달하면 낮은 심각도 경보가 트리거됩니다. 이 값은 타당한 값이지만 autovacuum이 계속 수행되고 있지 않음을 나타낼 수 있습니다.
테이블 수명이 10억이 되면 조치를 취해야 할 경보로 처리되어야 합니다. 성능상의 이유로 수명을
autovacuum_freeze_max_age에 더 가깝게 유지하려는 경우가 대부분입니다. 다음 권장 사항을 사용하여 조사하는 것이 좋습니다.테이블이 15억 개의 vacuum되지 않은 트랜잭션에 도달하면 높은 심각도 경보가 트리거됩니다. 데이터베이스가 트랜잭션 ID를 사용하는 속도에 따라 이 경보는 시스템에서 autovacuum을 실행할 시간이 부족함을 나타낼 수 있습니다. 이 경우 즉시 이를 해결하는 것이 좋습니다.
테이블이 지속적으로 이 임계값을 위반하면 autovacuum 파라미터를 추가로 수정합니다. 기본적으로 수동 VACUUM을 사용하면(비용에 따른 지연이 비활성화됨)은 기본 autovacuum을 사용할 때보다 더 적극적이지만 시스템 전체에 더 많이 침입할 수 있는 상태이기도 합니다.
다음과 같이 하는 것이 좋습니다.
모니터링 메커니즘을 숙지하고 활성화하여 가장 오래된 트랜잭션의 수명을 확인합니다.
트랜잭션 ID 랩어라운드에 대해 경고하는 프로세스 생성에 대한 자세한 내용은 AWS 데이터베이스 블로그 게시물 Amazon RDS for PostgreSQL의 트랜잭션 ID 랩어라운드용 조기 경고 시스템 구현
을 참조하세요. 더 많이 사용되는 테이블의 경우 autovacuum을 사용하는 것 이외에 유지 관리 기간 동안 수동 vacuum freeze를 정기적으로 수행합니다. 수동 vacuum freeze 수행에 대한 자세한 내용은 수동 vacuum freeze 수행 단원을 참조하십시오.
현재 Autovacuum을 수행할 수 있는 테이블 결정
vacuum을 수행해야 하는 테이블이 하나이거나 두 개인 경우가 많습니다. relfrozenxid 값이 autovacuum_freeze_max_age의 트랜잭션 수보다 큰 테이블은 항상 Autovacuum의 대상이 됩니다. 그렇지 않은 경우 VACUUM이 "vacuum 임계값"을 초과하여 튜플 수가 더 이상 사용되지 않는 경우 테이블이 vacuum됩니다.
autovacuum 임계값
Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples
여기서 vacuum base threshold는 autovacuum_vacuum_threshold이고, vacuum scale factor는 autovacuum_vacuum_scale_factor이며, number of tuples는 pg_class.reltuples입니다.
데이터베이스에 연결되어 있는 상태에서 다음 쿼리를 실행하여 autovacuum이 vacuum 가능한 대상으로 분류하는 테이블 목록을 확인합니다.
WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold'), vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor'), fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'), sto AS (select opt_oid, split_part(setting, '=', 1) as param, split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt) SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation, pg_size_pretty(pg_table_size(c.oid)) as table_size, age(relfrozenxid) as xid_age, coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age, (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) AS autovacuum_vacuum_tuples, n_dead_tup as dead_tuples FROM pg_class c join pg_namespace ns on ns.oid = c.relnamespace join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1) left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid WHERE c.relkind = 'r' and nspname <> 'pg_catalog' AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float) OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup) ORDER BY age(relfrozenxid) DESC LIMIT 50;
현재 Autovacuum이 실행 중인지 여부 및 실행 기간 확인
테이블을 수동으로 vacuum해야 하는 경우 autovacuum이 현재 실행 중인지 확인합니다. 실행 중이면 더 효율적으로 실행되도록 파라미터를 수정하거나 VACUUM을 수동으로 실행할 수 있도록 일시적으로 autovacuum을 종료해야 합니다.
다음 쿼리를 사용하여 autovacuum이 실행 중인지 여부와 얼마 동안 실행되고 있는지, 다른 세션에 대해 대기하고 있는지 확인합니다.
SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;
쿼리를 실행하면 다음과 유사한 출력이 표시됩니다.
datname | usename | pid | state | wait_event | xact_runtime | query --------+----------+-------+--------+------------+-------------------------+-------------------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | active | | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | active | | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | active | | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | active | | 00:00:00 | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+ | | | | | | FROM pg_stat_activity + | | | | | | WHERE query like '%VACUUM%' + | | | | | | ORDER BY xact_start; +
몇 가지 문제로 인해 autovacuum 세션이 오래(며칠간) 실행될 수 있습니다. 이 문제는 대부분 maintenance_work_mem
다음 공식을 사용하여 maintenance_work_mem 파라미터 값을 설정하는 것이 좋습니다.
GREATEST({DBInstanceClassMemory/63963136*1024},65536)
짧은 기간 동안 실행되는 autovacuum 세션에서도 문제를 표시할 수 있습니다.
워크로드에
autovacuum_max_workers가 충분하지 않다고 표시될 수 있습니다. 이 경우 작업자 수를 명시해야 합니다.인덱스 손상(autovacuum에 충돌이 발생하여 동일한 관계에서 다시 시작되지만 진행되지 않음)이 있다고 표시될 수 있습니다. 이 경우에는
vacuum freeze verbose매뉴얼을 실행하여 정확한 원인을 확인합니다.table
수동 vacuum freeze 수행
vacuum 프로세스가 실행되고 있는 테이블에서 수동 vacuum을 수행하려는 경우가 있습니다. 이 작업은 수명이 20억 개 트랜잭션에 도달하거나 모니터링 중인 임계값을 초과한 테이블을 파악해 둔 경우 유용합니다.
다음 단계는 지침으로 이 프로세스를 여러 가지로 변형할 수 있습니다. 예를 들어 테스트 중에 maintenance_work_memmaintenance_work_mem 파라미터 설정도 변경해야 하지만 즉시 조치를 취해 문제가 되는 테이블을 vacuum해야 하기도 합니다. 이 경우 어떤 작업을 수행해야 하는지가 다음 절차에 나와 있습니다.
vacuum freeze를 수동으로 수행하려면
vacuum할 테이블이 포함되어 있는 데이터베이스에 세션 두 개를 엽니다. 두 번째 세션의 경우 "screen"을 사용하거나 연결이 끊긴 경우 세션을 유지하는 다른 유틸리티를 사용합니다.
첫 번째 세션에서는 테이블에서 실행 중인 autovacuum 세션의 프로세스 ID(PID)를 가져옵니다.
다음 쿼리를 실행하여 autovacuum 세션의 PID를 가져옵니다.
SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;세션 2에서 이 작업에 필요한 메모리 양을 계산합니다. 이 예제에서는 이 작업에 메모리를 최대 2GB까지 사용할 수 있는 것으로 보고 현재 세션의
maintenance_work_mem을 2GB로 설정합니다. SET maintenance_work_mem='2 GB';SET세션 2에서 테이블에 대한
vacuum freeze verbose명령을 실행하십시오. 현재 PostgreSQL에서 이 작업에 대한 진행률 보고가 없음에도 작업을 확인할 수 있기 때문에 상세 정보 표시 설정이 유용하게 사용됩니다.\timing onTiming is on.vacuum freeze verbose pgbench_branches;INFO: vacuuming "public.pgbench_branches" INFO: index "pgbench_branches_pkey" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pgbench_branches_test_index" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pgbench_branches": found 0 removable, 50 nonremovable row versions in 43 out of 43 pages DETAIL: 0 dead row versions cannot be removed yet. There were 9347 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Time: 2.765 ms-
세션 1에서 autovacuum이 vacuum 세션을 차단한 경우
pg_stat_activity에서 vacuum 세션에 대한 대기를 나타내는 "T"를 확인할 수 있습니다. 이 경우 다음과 같이 autovacuum 프로세스를 종료해야 합니다.SELECT pg_terminate_backend('the_pid');이때 세션이 시작됩니다. 이 테이블이 작업 목록에서 가장 상위에 있을 것이므로 autovacuum이 즉시 다시 시작된다는 점을 알아 두어야 합니다.
-
세션 2에서
vacuum freeze verbose명령을 시작한 다음 세션 1에서 autovacuum 프로세스를 종료합니다.
Autovacuum이 실행 중인 경우 테이블 인덱스 다시 지정
인덱스가 손상되면 autovacuum은 계속해서 테이블을 처리하려 하고 실패합니다. 이 경우 수동 vacuum을 시도하면 다음과 비슷한 오류 메시지가 표시됩니다.
postgres=>vacuum freeze pgbench_branches;ERROR: index "pgbench_branches_test_index" contains unexpected zero page at block 30521 HINT: Please REINDEX it.
인덱스가 손상된 상태에서 테이블에 대해 autovacuum을 실행하려고 하면 이미 실행 중인 autovacuum 세션이 있음을 확인하게 됩니다. REINDEX
테이블에서 autovacuum을 실행할 때 테이블 인덱스를 다시 지정하려면
vacuum할 테이블이 포함되어 있는 데이터베이스에 세션 두 개를 엽니다. 두 번째 세션의 경우 "screen"을 사용하거나 연결이 끊긴 경우 세션을 유지하는 다른 유틸리티를 사용합니다.
첫 번째 세션에서는 테이블에서 실행 중인 autovacuum 세션의 PID를 가져옵니다.
다음 쿼리를 실행하여 autovacuum 세션의 PID를 가져옵니다.
SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY xact_start;세션 2에서 reindex 명령을 실행합니다.
\timing onTiming is on.reindex index pgbench_branches_test_index;REINDEX Time: 9.966 ms세션 1에서 autovacuum이 프로세스를 차단한 경우
pg_stat_activity에서 vacuum 세션에 대한 대기를 나타내는 "T"를 확인할 수 있습니다. 이 경우에는 autovacuum 프로세스를 종료합니다.SELECT pg_terminate_backend('the_pid');이때 세션이 시작됩니다. 이 테이블이 작업 목록에서 가장 상위에 있을 것이므로 autovacuum이 즉시 다시 시작된다는 점을 알아 두어야 합니다.
세션 2에서 명령을 시작한 다음 세션 1에서 autovacuum 프로세스를 종료합니다.
대용량 인덱스를 사용하여 autovacuum 관리
작업 중에 autovacuum은 테이블에서 실행되는 동안 여러 vacuum 단계를
이 프로세스에서는 먼저 전체 인덱스 크기를 확인합니다. 그런 다음, 다음 예제에 나온 것처럼 제거할 수 있는 사용하지 않는 인덱스가 있는지 확인합니다.
테이블 및 해당 인덱스의 크기를 확인하려면
postgres=> select pg_size_pretty(pg_relation_size('pgbench_accounts'));pg_size_pretty 6404 MB (1 row)
postgres=> select pg_size_pretty(pg_indexes_size('pgbench_accounts'));pg_size_pretty 11 GB (1 row)
이 예제에서는 인덱스 크기가 테이블보다 큽니다. 이러한 차이로 인해 인덱스가 팽창하거나 사용되지 않아 성능 문제가 발생하여 autovacuum 및 삽입 작업에 영향을 미칠 수 있습니다.
사용하지 않는 인덱스를 확인하려면
pg_stat_user_indexesidx_scan 열에 사용되는 빈도를 확인할 수 있습니다. 다음 예제를 보면 사용하지 않은 인덱스는 idx_scan 값이 0입니다.
postgres=> select * from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+------------------+-----------------------+----------+--------------+--------------- 16433 | 16454 | public | pgbench_accounts | index_f | 6 | 6 | 0 16433 | 16450 | public | pgbench_accounts | index_b | 3 | 199999 | 0 16433 | 16447 | public | pgbench_accounts | pgbench_accounts_pkey | 0 | 0 | 0 16433 | 16452 | public | pgbench_accounts | index_d | 0 | 0 | 0 16433 | 16453 | public | pgbench_accounts | index_e | 0 | 0 | 0 16433 | 16451 | public | pgbench_accounts | index_c | 0 | 0 | 0 16433 | 16449 | public | pgbench_accounts | index_a | 0 | 0 | 0 (7 rows)
postgres=> select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;schemaname | relname | indexrelname | idx_scan ------------+------------------+-----------------------+---------- public | pgbench_accounts | index_f | 6 public | pgbench_accounts | index_b | 3 public | pgbench_accounts | pgbench_accounts_pkey | 0 public | pgbench_accounts | index_d | 0 public | pgbench_accounts | index_e | 0 public | pgbench_accounts | index_c | 0 public | pgbench_accounts | index_a | 0 (7 rows)
참고
이러한 통계는 통계가 재설정된 시점부터 증분됩니다. 사업 분기 말에만 사용되거나 특정 보고서에만 사용되는 인덱스가 있는 경우를 가정해 보겠습니다. 통계가 재설정된 이후로 이 인덱스가 사용되지 않았을 수 있습니다. 자세한 내용은 Statistics Functions
데이터베이스의 통계가 마지막으로 재설정된 시간을 확인하려면 pg_stat_database
postgres=> select datname, stats_reset from pg_stat_database where datname = 'postgres';datname | stats_reset ----------+------------------------------- postgres | 2022-11-17 08:58:11.427224+00 (1 row)
테이블에 최대한 신속하게 Vacuum을 실행하는 방법
RDS for PostgreSQL 12 이상
대형 테이블에 인덱스가 너무 많으면 DB 인스턴스가 트랜잭션 ID 랩어라운드(XID)에 가까워질 수 있는데, 이때 XID 카운터가 0으로 래핑됩니다. 이 옵션을 선택하지 않으면 데이터가 손실될 수 있습니다. 그러나 인덱스를 정리하지 않고도 테이블에 신속하게 vacuum을 실행할 수 있습니다. RDS for PostgreSQL 12에서는 INDEX_CLEANUP
postgres=> VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) pgbench_accounts;INFO: vacuuming "public.pgbench_accounts" INFO: table "pgbench_accounts": found 0 removable, 8 nonremovable row versions in 1 out of 819673 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7517 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
autovacuum 세션이 이미 실행 중인 경우, 해당 세션을 종료하여 수동 VACUUM을 시작해야 합니다. 수동 vacuum freeze 수행에 대한 자세한 내용은 수동 vacuum freeze 수행 섹션을 참조하세요.
참고
주기적인 인덱스 정리를 건너뛰면 인덱스 팽창이 발생하여 전체 스캔 성능에 영향을 미칠 수 있습니다. 가장 좋은 방법은 이전 프로시저만 사용하여 트랜잭션 ID 랩어라운드를 방지하는 것입니다.
RDS for PostgreSQL 11 이상
그러나 RDS for PostgreSQL 11 이하 버전에서 vacuum을 더 신속하게 완료할 수 있는 유일한 방법은 테이블의 인덱스 수를 줄이는 것입니다. 인덱스를 삭제하면 쿼리 계획에 영향을 미칠 수 있습니다. 사용하지 않는 인덱스를 먼저 삭제한 다음, XID 랩어라운드가 매우 가까워졌을 때 인덱스를 삭제하는 것이 좋습니다. vacuum 프로세스가 완료되면 이러한 인덱스를 다시 생성할 수 있습니다.
Autovacuum에 영향을 주는 기타 파라미터
이 쿼리를 사용하면 autovacuum 및 해당 동작에 직접 영향을 주는 일부 파라미터 값이 표시됩니다. autovacuum 파라미터
SELECT name, setting, unit, short_desc FROM pg_settings WHERE name IN ( 'autovacuum_max_workers', 'autovacuum_analyze_scale_factor', 'autovacuum_naptime', 'autovacuum_analyze_threshold', 'autovacuum_analyze_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_cost_delay', 'autovacuum_vacuum_cost_limit', 'vacuum_cost_limit', 'autovacuum_freeze_max_age', 'maintenance_work_mem', 'vacuum_freeze_min_age');
모두 autovacuum에 영향을 주지만 가장 중요한 사항 몇 가지는 다음과 같습니다.
테이블 수준 Autovacuum 파라미터 설정
Autovacuum이 관련된 스토리지 파라미터
이 쿼리를 사용하면 현재 어떤 테이블에 테이블 수준 옵션을 사용 중인지가 표시됩니다.
SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT null;
이 쿼리가 유용한 경우는 테이블 하나가 나머지 테이블보다 훨씬 더 큰 경우입니다. 300GB 테이블 하나와 1GB 미만의 테이블 30개가 있다고 가정하십시오. 이 경우 더 큰 테이블에 특정 파라미터를 설정하여 전체 시스템의 동작이 변경되지 않도록 할 수 있습니다.
ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0);
이렇게 하면 시스템의 리소스를 더 많이 사용하는 대신 이 테이블의 비용에 따른 autovacuum 지연이 비활성화됩니다. 일반적으로 매시간 autovacuum_cost_limit에 도달한 autovacuum_vacuum_cost_delay의 autovacuum이 일시 중지됩니다. 자세한 내용은 비용에 따른 vacuum 수행
autovacuum 및 vacuum 활동 로그
autovacuum 활동에 대한 정보는 rds.force_autovacuum_logging_level 파라미터에 지정된 레벨을 기반으로 postgresql.log에 전송됩니다. 다음은 이 파라미터와 PostgreSQL 버전에 허용되는 해당 값이 기본 설정인 값입니다.
disabled(PostgreSQL 10, PostgreSQL 9.6)debug5,debug4,debug3,debug2,debug1info(PostgreSQL 12, PostgreSQL 11)noticewarning(PostgreSQL 13 이상)error, 로그,fatal,panic
rds.force_autovacuum_logging_level은 log_autovacuum_min_duration 파라미터와 함께 작동합니다. log_autovacuum_min_duration 파라미터 값은 autovacuum 작업이 기록되는 임계값(밀리초) 이상입니다. -1로 설정하면 아무것도 기록하지 않지만 0으로 설정하면 모든 작업이 기록됩니다. rds.force_autovacuum_logging_level과 마찬가지로 log_autovacuum_min_duration의 기본값은 다음과 같이 버전에 따라 다릅니다.
10000 ms- PostgreSQL 14, PostgreSQL 13, PostgreSQL 12, PostgreSQL 11(empty)- PostgreSQL 10 및 PostgreSQL 9.6 기본값 없음
rds.force_autovacuum_logging_level을 WARNING로 설정하는 것이 좋습니다. 또한 log_autovacuum_min_duration을 1000에서 5000까지의 값으로 설정하는 것이 좋습니다. 5,000밀리초 이상 걸리는 5000개의 기록 활동 설정. -1을 제외한 모든 설정은 충돌하는 잠금 또는 동시에 삭제된 관계로 인해 autovacuum 작업을 건너뛴 경우에도 메시지를 기록합니다. 자세한 내용은 PostgreSQL 설명서의 자동 Vacuuming
문제를 해결하려면 rds.force_autovacuum_logging_level 매개 변수를 상세 표시 정보에 대해 debug1부터 debug5까지의 디버그 레벨 중 하나로 변경할 수 있습니다. 디버그 설정은 단기 문제 해결 목적으로만 사용하는 것이 좋습니다. 자세한 내용은 PostgreSQL의 로그 시기
참고
PostgreSQL을 사용하면 rds_superuser 계정에서 pg_stat_activity의 autovacuum 세션을 볼 수 있습니다. 예: 명령의 실행을 차단하거나, 수동으로 실행한 vacuum 명령보다 느리게 실행되는 autovacuum 세션을 식별 및 종료 가능
