AWS re:Post을(를) 사용하면 다음에 동의하게 됩니다. 이용 약관

Amazon Aurora PostgreSQL Auto Vacuum 이해하기 (2/2)

8분 분량
고급
1

AWS Aurora PostgreSQL에서 발생하는 Vacuum에 대한 원리 및 목적을 이해하고, 성능 향상을 위한 최적화 전략에 대해서 알아봅니다.

Amazon Aurora PostgreSQL Auto Vacuum 이해하기 (2/2)

Author : Sungwook Kang(강성욱), AWS Solutions Architect

PostgreSQL은 오픈소스 관계형 데이터베이스로 Amazon Aurora (https://aws.amazon.com/ko/rds/aurora/) PostgreSQL은 오픈소스 PostgreSQL과 완전히 호환된 완전 관리형 데이터베이스 서비스입니다. 많은 사용자들이 PostgreSQL(이하 PG)을 사용할 때 Vacuum 동작으로 인해 예상하지 못한 성능 저하 문제를 겪고 있는데 Vacuum이 수행되었을 때 발생하는 문제는 무엇이 있는지, 그리고 이러한 문제를 최소화하기 위한 전략이 무엇이 있는지에 대해서 알아보겠습니다.

Autovacuum 모니터링은 어떻게 할까요?

Autovacuum이 효과적으로 작동하는지 확인하려면 데드 튜플, 디스크 사용량, autovacuum 또는 ANALYZE가 마지막으로 실행된 시간을 정기적으로 모니터링해야 합니다.

Dead Tuple 모니터링

PostgreSQL은 pg_stat_user_tables 뷰를 제공하는데, 이 뷰는 각 테이블(relname)과 테이블에 있는 데드 튜플(n_dead_tup)에 대한 정보를 제공합니다. 각 테이블, 특히 자주 업데이트되는 테이블의 데드 튜플 수를 모니터링하면 Autovacuum 프로세스가 주기적으로 제거하여 디스크 공간을 더 나은 성능을 위해 재사용할 수 있는지 확인하는 데 도움이 됩니다. 아래 쿼리를 사용하여 데드 튜플의 수와 테이블에서 마지막 Autovacuum이 실행된 시간을 확인할 수 있습니다.

SELECT
relname AS TableName
,n_live_tup AS LiveTuples
,n_dead_tup AS DeadTuples
,last_autovacuum AS Autovacuum
,last_autoanalyze AS Autoanalyze
FROM pg_stat_user_tables;

Table Disk Usage 모니터링

각 테이블이 사용하는 디스크 공간의 양을 추적하면 시간 경과에 따른 쿼리 성능의 변화를 분석할 수 있기 때문에 중요한 부분입니다. 또한 Vacuum과 관련된 문제를 감지하는 데 도움이 될 수 있습니다. 예를 들어, 최근에 많은 새 데이터를 테이블에 추가할 때, 테이블의 디스크 사용량이 예기치 않게 증가한 경우 해당 테이블에 Vacuuming 문제가 있을 수 있습니다. Vacuuming은 오래된 행을 재사용 가능한 것으로 표시하는 데 도움이 되므로 VACUUM이 정기적으로 실행되지 않으면 새로 추가된 데이터는 데드 튜플이 차지하는 디스크 공간을 재사용하는 대신 추가 디스크 공간을 사용하게 됩니다.

Last autovacuum and autoanalyzer 확인

pg_stat_user_tables은 autovacuum 데몬이 테이블에서 마지막으로 실행된 시간에 대한 정보를 제공합니다. autovacuum 및 autoanalyze를 사용하여 autovacuum 데몬이 효율적으로 작동하는지 추적할 수 있습니다. 아래 쿼리는 테이블에서 실행되는 last_autovacuum 및 last_autoanalyze에 대한 세부 정보를 제공합니다.

SELECT relname, last_autovacuum,last_autoanalyze FROM  pg_stat_user_tables;

Enabling log_autovacuum_min_duration

log_autovacuum_min_duration 매개변수는 Autovacuum 프로세스가 실행한 모든 작업을 기록하는 데 도움이 됩니다. Autovacuum이 지정된 시간(밀리초) 동안 실행하거나 임계값 테이블 저장 매개변수를 초과하면 작업이 기록됩니다. 예를 들어, 이 매개변수를 150밀리초로 설정하면 150밀리초 이상 실행되는 모든 Autovacuum 프로세스가 기록됩니다. 또한 이 매개변수가 -1 이외의 값으로 설정되면 충돌하는 잠금으로 인해 Autovacuum 작업을 건너뛸 경우 메시지가 기록됩니다. 또한 Autovacuum 프로세스의 느린 속도에 대한 자세한 정보를 제공할 수 있습니다.

Enabling an Amazon CloudWatch alarm

트랜잭션 warparound에 대한 Amazon CloudWatch 경보를 설정할 수 있습니다. 자세한 내용은 아래 링크를 참고할 수 있습니다.

또한 CloudWatch 지표를 사용하여 전체 시스템 리소스 사용량을 모니터링하고 Autovacuum 세션이 동시에 실행될 때 허용 가능한 범위 내에 있는지 확인할 수 있습니다.

일반적으로 자주 겪는 Autovacuum 문제

Autovacuum parameter tuning

Autovacuum이 정기적으로 테이블의 Vacuum 프로세스를 트리거하지 않거나 효율적으로 수행되지 않는 경우 Autovacuum 매개변수 조정을 고려해야 합니다. Autovacuum 프로세스는 테이블에서 VACUUM 및 ANALYZE 명령을 자동으로 실행해야 하는 시기를 결정하기 위해 여러 구성 설정을 참조합니다. 아래 쿼리는 조정할 수 있는 Autovacuum 매개변수 목록을 제공합니다.

select category, name,setting,unit,source,min_val,max_val from  pg_settings where category = 'Autovacuum' ;

여기에 이미지 설명 입력

Settings열에는 현재 구성된 값이 표시됩니다. boot_val열에는 기본 매개변수를 변경하지 않을 때 사용하는 PostgreSQL에서 설정한 Autovacuum 매개변수의 기본값이 표시됩니다. 이러한 Autovacuum 매개변수를 조정하면 Autovacuum 프로세스가 테이블에서 자주 효율적으로 작동합니다. Autovacuum 조정에 대한 자세한 내용은 아래 링크를 참고할 수 있습니다.

Autovacuum skipped due to lock conflicts

테이블에서 Vacuum을 실행하려면 Autovacuum 프로세스가 SHARE UPDATE EXCLUSIVE 잠금을 획득해야 하는데, 이는 두 트랜잭션이 동시에 SHARE UPDATE EXCLUSIVE 잠금을 보유할 수 없기 때문에 다른 잠금과 충돌하는 것입니다. 이는 SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE 및 ACCESS EXCLUSIVE와 같은 다른 잠금 모드에서도 동일합니다. SHARE UPDATE EXCLUSIVE 잠금은 SELECT, UPDATE, INSERT 또는 DELETE를 차단하지 않으며 아래 잠금이 있는 트랜잭션만 차단합니다.

  • SHARE UPDATE EXCLUSIVE – Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, CREATE STATISTICS, and certain ALTER INDEX and ALTER TABLE variants.
  • SHARE – Acquired by CREATE INDEX (without CONCURRENTLY).
  • SHARE ROW EXCLUSIVE – Acquired by CREATE TRIGGER and some forms of ALTER TABLE.
  • EXCLUSIVE – Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY.
  • ACCESS EXCLUSIVE – Acquired by DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. Many forms of ALTER INDEX and ALTER TABLE also acquire a lock at this level.

따라서 트랜잭션이 테이블에 대한 이러한 잠금 중 하나를 유지하라는 요청과 함께 제공되고 Autovacuum 데몬이 이미 해당 테이블 중 하나에서 Vacuum 작업을 실행 중인 경우, 다른 트랜잭션이 잠금을 취할 수 있도록 Vacuum 작업을 즉시 취소합니다. 유사하게, 트랜잭션이 이미 테이블에 대한 ACCESS EXCLUSIVE 잠금을 보유하고 있는 경우 Autovacuum은 해당 테이블을 Vacuuming에서 건너뜁니다. Autovacuum 프로세스는 다음 반복에서 Vacuum 작업을 실행하기 위해 건너뛴 테이블을 유지합니다.

Autovacuum action skipped due long-running transactions

PostgreSQL은 MVCC 개념을 기반으로 하기 때문에 하나 이상의 트랜잭션이 오래된 버전의 데이터에 액세스하는 경우 Autovacuum 프로세스는 데드 튜플을 정리하지 않습니다. 데이터가 삭제되거나 업데이트되기 전에 생성된 데이터의 스냅샷에서 트랜잭션이 작업 중인 경우 Autovacuum은 해당 데드 튜플을 건너뛰고 해당 데드 튜플은 다음 반복에서 Vacuum 됩니다. 이런 케이스는 일반적으로 데이터베이스의 장기 실행 트랜잭션에서 발생합니다. 데이터베이스에서 장기 실행 트랜잭션을 찾으려면 아래 쿼리를 실행해 볼 수 있습니다. 예제 쿼리는 5분이상 실행되고 있는 쿼리를 나타냅니다.

SELECT now()-query_start as Running_Since, pid, datname, usename,  application_name, client_addr , left(query,60) FROM pg_stat_activity WHERE  state in ('active','idle in transaction') AND (now() - query_start) >  interval '5 minutes';

Autovacuum은 데드 튜플을 건너뛰게 할 수 있으므로 모니터링의 일부로 트랜잭션 세션의 유휴 상태(idle in transaction)를 포함하는 것이 좋습니다.

Autovacuum 모범 사례

Allocating memory for autovacuum

maintenance_work_mem 파라미터는 Autovacuum의 성능에 영향을 미치는 중요한 파라미터입니다. Autovacuum 프로세스가 데이터베이스의 테이블을 스캔하는 데 사용할 메모리 양을 결정하고 Vacuum이 필요한 행 ID를 보유합니다. 매개변수를 낮게 설정하면 Vacuum 프로세스가 테이블을 여러 번 스캔하여 Vacuum 작업을 완료하므로 데이터베이스 성능에 부정적인 영향을 미칠 수 있습니다. 작은 테이블이 많은 경우 autovacuum_max_workers를 더 많이 할당하고 maintenance_work_mem을 더 적게 할당합니다. 큰 테이블(100GB 이상)이 있는 경우, 더 많은 메모리와 더 적은 수의 작업자 프로세스를 할당합니다. 가장 큰 테이블에서 성공하려면 충분한 메모리가 할당되어야 합니다. 각 autovacuum_max_workers는 할당한 메모리를 사용할 수 있습니다. 따라서 작업자 프로세스와 메모리의 조합이 할당하려는 총 메모리와 동일한지 확인해야 합니다. 더 큰 인스턴스의 경우 maintenance_work_mem을 1GB 이상으로 설정하면 많은 수의 데드 튜플이 있는 테이블을 Vacuuming하는 성능이 크게 향상됩니다. 하지만, Vacuum 메모리 사용을 1GB로 제한하는 것이 좋습니다. 한 패스에서 약 1억 7,900만 개의 데드 튜플을 처리하기에 충분합니다. 그 보다 더 많은 데드 튜플이 있는 테이블을 Vacuuming하려면 테이블 인덱스를 여러 번 통과해야 하므로 Vacuum이 훨씬 더 오래 걸릴 수 있습니다. maintenance_work_mem 바이트를 6으로 나누어 단일 패스에서 Vacuum이 처리할 수 있는 데드 튜플 수를 계산할 수 있습니다.

GREATEST({DBInstanceClassMemory/63963136*1024},65536)

더 자세한 내용은 아래 링크를 참고할 수 있습니다.

Reducing the chances of transaction ID wraparound

일부 사용 사례에서는 조정된 Autovacuum 설정도 트랜잭션 ID warparound를 방지할 만큼 만족하지 않습니다. 이 문제를 해결하기 위해 Amazon RDS에는 autovacuum 파라미터 값을 자동으로 조정하는 메커니즘이 있습니다. 적응형 Autovacuum 파라미터 조정이 활성화된 경우 Amazon RDS는 CloudWatch 지표 MaximumUsedTransactionIDs가 750,000,000 또는 autovacuum_freeze_max_age 중 더 큰 값에 도달할 때 Autovacuum 파라미터 조정을 시작합니다. Amazon RDS는 테이블이 계속해서 트랜잭션 ID warparound로 향하는 경향이 있을 때 Autovacuum에 대한 매개변수를 계속 조정합니다. 각 조정은 warparound를 피하기 위해 Autovacuum에 더 많은 리소스를 할당합니다. Amazon RDS는 다음과 같은 Autovacuum 관련 파라미터를 업데이트합니다.

  • autovacuum_vacuum_cost_delay – autovacuum 프로세스가 제한을 초과할 때 대기하는 지정된 시간(밀리초) 입니다. 기본값은 20밀리초 입니다.
  • autovacuum_vacuum_cost_limit – Autovacuum 프로세스를 휴면 상태로 만드는 누적 비용으로 기본값은 200입니다.
  • autovacuum_work_mem – 각 Autovacuum 작업자 프로세스에서 사용하는 최대 메모리 양 입니다. 기본값은 -1로 maintenance_work_mem 값을 사용해야 함을 나타냅니다.
  • autovacuum_naptime – 주어진 데이터베이스에서 Autovacuum 실행 사이의 최소 지연을 지정합니다. 각 라운드에서 데몬은 데이터베이스를 검사하고 해당 데이터베이스의 테이블에 대해 필요에 따라 VACUUM 및 ANALYZE 명령을 실행 합니다. 지연은 초 단위로 측정되며 기본값은 1분 입니다. 이 매개변수는 postgresql.conf 파일이나 서버 명령줄에서만 설정할 수 있습니다.

Setting autovacuum at table level

글로벌 Autovacuum 설정을 기반으로 증가하는 PostgreSQL 환경에서 큰 테이블은 효과적으로 Vacuum되지 않고 작은 테이블은 자주 Vacuum되는 것을 볼 수 있습니다. 이러한 시나리오를 피하기 위해 다음 단계에 따라 테이블 수준에서 Autovacuum 매개변수를 설정할 수 있습니다.

  1. 데이터베이스에서 큰 테이블을 나열한다.
  2. 많은 수의 변경 사항이 발생한 테이블을 나열한다.
  3. 어떤 테이블에 'n_dead_tup' 수가 많은지 확인한다.
  4. 테이블이 마지막으로 자동 분석 및 자동 Vacuum 처리된 시간을 확인한다.
  5. 테이블 수준에서 Autovacuum 및 Autoanalyze 매개변수를 변경한다.

Reference