[AWS Aurora] Aurora PostgreSQL Auto Vacuum 이해하기
l Version : AWS Aurora PostgreSQL
PostgreSQL 은 오픈소스 관계형 데이터베이스로 AWS PostgreSQL 은 오픈소스 데이터베이스를 완전 관리형 데이터베이스 서비스로 제공한다 .
l Amazon Aurora : https://aws.amazon.com/ko/rds/aurora/
많은 사용자들이 PostgreSQL( 이하 PG) 을 사용할 때 Vacuum 동작으로 인해 예상하지 못한 성능 하락 문제를 겪고 있는데 Vacuum 이 수행되었을 때 발생하는 문제는 무엇이 있는지 , 그리고 이러한 문제를 최소화하기 위한 전략이 무엇이 있는지에 대해서 알아본다 .
[Vacuum 은 왜 하는 것일까 ]
Vacuum 은 일반 적으로 진공 청소기라는 뜻으로 , 의미와 동일하게 PG 에서 더 이상 사용되지 않는 데이터를 정리해주는 역할을 한다 . 쉽게 예를 들면 디스크 조각모음과 같다 .
PG는 MVCC (Multi Version Concurrency Control, 다중 버전 동시성 제어 ) 를 지원하기 때문에 데이터의 삭제 , 수정이 발생하면 더 이상 사용하지 않는 여러 버전의 데이터가 존재한다 . 만약 Vacuum 을 진행하지 않으면 이러한 데이터가 지속적으로 쌓여 실제 테이블 데이터 자체는 적은데 테이블의 공간을 차지하여 테이블이 지속적으로 커지는 문제가 발생한다 . 그러면 당연히 불필요하거나 부적절한 인덱스가 증가하여 조회속도가 느려지고 , I/O 오버헤드가 증가한다 . 또한 트랜잭션 ID 겹침이나 , 다중 트랜잭션 ID 겹침 상황으로 오래된 자료가 손실될 수도 있으며 이러한 현상이 지속되면 트랜잭션 ID 를 재활용하지 못해서 최악의 상황에는 데이터베이스가 멈추는 상황까지 발생할 수 있다 . 이러한 여러 이유로 Vacuum 작업은 그 이유에 맞게 다양한 주기로 , 다양한 대상으로 진행된다 .
MVCC : 동시접근을 허용하는 데이터베이스에서 동시성을 제어하기 위해 사용하는 방법 . 즉 , MVCC 모델에서 데이터에 접근하는 사용자는 접근한 시점에서의 데이터베이스의 Snapshot 을 읽는데 , 이 snapshot 데이터에 대한 변경이 완료될 때 ( 트랜잭션이 commit 될 때 ) 까지 만들어진 변경사항은 다른 데이터베이스 사용자가 볼 수 없다 . 이러한 개념에 의해 사용자가 데이터를 업데이트하면 이전의 데이터를 덮어 씌우는게 아니라 새로운 버전의 데이터를 생성한다 . 대신 이전 버전의 데이터와 비교해서 변경된 내용을 기록한다 . 이렇게해서 하나의 데이터에 대해 여러 버전의 데이터가 존재하게 되고 , 사용자는 마지막 버전의 데이터를 읽게 된다 .
[Vacuum 이 하는 일 ]
Vacuum 이 실행되면 사용되지 않는 Dead Tuple ( 이하 데드 튜플 ) 을 FSM(Free Space Map) 에 반환한다 . 데드 튜플은 Vacuum 작업을 통해 FSM 에 반환되기 전까지는 그 자리에 새로운 데이터를 저장할 수 없다 . 예를 들어 10 만 row 를 가지고 있는 테이블에 update 를 10 만개를 했다면 10 만개의 데드 튜플이 생기고 , 또 다시 10 만개의 업데이트를 했다면 Vacuum 이 실행되지 않은 상태에서는 또 다시 10 만개의 데드 튜플이 발생한다 . 즉 . 해당 테이블은 실제 데이터 10 만개와 , 20 만개의 데드 튜플이 존재하게 된다 . 이때 Vacuum 을 실행하면 20 만개의 데드 튜플 공간을 FSM 에 반환하게 되며 다음 업데이트부터는 해당 공간을 재활용할 수 있다 . 하지만 Vacuum 을 실행한다고 해서 이미 늘어난 테이블의 크기는 줄어들지는 않으며 해당 공간이 재활용되어 사용되므로 테이블 크기가 더 이상 늘어나지는 않는다 . Vacuum 이 실행되므로써 FSM 에 공간반환 뿐만 아니라 , 인덱스 전용 검색 성능을 향상하는데 참고하는 실 자료 지도 (VM, Visivility Map) 정보를 갱신한다 . 또한 삭제된 데이터뿐만 아니라 남아 있는 데이터에 대해서도 Frozen XID(XID 2) 로 변경해 주어 앞으로 XID wrap around 가 발생하더라도 트랜잭션 ID 겹침을 방지할 수 있다 .
PG 에서는 트랜잭션 ID 의 크기가 32bit 정수형 크기이며 하나의 서버에서 해당 크기를 넘기면 트랜잭션 ID 가 겹치는 현상이 발생한다 .
[Vacuum 실행 ]
Vacuum 작업은 기본적으로 디스크 I/O 오버헤드를 유발한다 . 이 때문에 동시에 작업하고 있는 다른 세션의 성능을 떨어뜨릴 수 있다 . Vacuum 작업에 대한 비용은 아래 링크를 참고한다 .
l Cost-based Vacuum Delay : https://www.postgresql.kr/docs/9.4/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST
Vacuum 은 수동 또는 자동으로 실행될 수 있다 . 그리고 Vacuum 실행 시 옵션에 따른 특징이 있다 . 수동으로 실행할 경우 아래와 같은 명령으로 실행할 수 있다 .
-- DB 전체 full vacuum vacuum full analyze; -- DB 전체 간단하게 실행 vacuum verbose analyze; -- 특정 테이블만 간단하게 실행 vacuum analyze [ 테이블 명 ]; -- 특정 테이블만 full vacuum vacuum full [ 테이블 명 ];
l Vacuum : 데드 튜플을 FSM 에 반환하는 작업을 하며 , 운영 환경에서도 DML (SELECT, INSERT, UPDATE, DELETE) 이 실행되고 있어도 동시에 사용할 수 있다 . 하지만 DDL (ALTER TABLE) 명령은 Vacuum 작업이 실행되는 동안에는 사용할 수 없다 .
l Vacuum FULL : VACUUM FULL 작업은 해당 테이블의 사용할 수 있는 자료들만을 모아서 새 파일에 저장하는 방식을 이용하기 때문에 운영체제 입장에서 디스크 여유 공간을 확보할 수 있다 . 작업 결과로 해당 테이블에 대해서 최적의 물리적 크기로 테이블이 만들어진다 . 하지만 작업 시 테이블에 대한 베타적 잠금 (Exclusive Lock) 을 지정하여 실행되기 때문에 어떠한 작업도 할 수 없다 . ( 운영중인 데이터베이스에서는 사용 금지 ) 그리고 일반 VACUUM 작업에 비해 시간이 오래 걸린다 . 또한 이 작업이 완료되기 전까지 이 작업을 할 수 있는 여유 공간이 있어야 작업을 할 수 있다 .
l Vacuum Analyze : 통계 메타데이터를 업데이트하므로 쿼리 옵티마이저가 더 정확한 쿼리 계획을 생성할 수 있어 Vacuum 명령어 실행 시 같이 실행하는 것이 좋다 .
Autovacuum( 자동 ) 은 내부 알고리즘으로 필요에 따라 Vacuum 을 자동으로 처리해 주는 것으로 수동처럼 명령어로 테이블을 정리하는 것이 아닌 테이블 혹은 DB 단위의 설정을 통해서 vacuum 이 진행된다 . 이때 설정된 값에 따라서 데드 튜플의 증가를 얼마나 제어할지가 정해지기 때문에 Autovacuum 을 사용할 때에는 현재 운영중인 서버의 최적화 값을 파악하고 있어야 한다 .
일반적인 Vacuum 전략은 주기적인 표준 Vacuum 작업을 진행하여 지속적으로 빈공간을 확보하여 디스크가 어느정도 커지지만 더 이상 커지지 않게 하여 최대한 Vacuum FULL 작업을 방지하는 것이다 . Autovacuum 데몬이 이러한 전략으로 작업을 한다 . 즉 , autovacuum 기능을 사용하되 Vacuum FULL 작업을 하지 않는 것을 기본 정책으로 설정하면 된다 . 기본적으로 실시간 ( 주기적 ) Vacuum(FULL Vacuum 아님 ) 실시하며 , autovacuum_freeze_max_age 에 도달하면 강제로 Vacuum 작업을 실시하게 된다 .
정확한 데이터베이스 사용량을 파악하지 않은 상태에서 autovacuum 기능을 끄는 것은 현명하지 않은 방법일 수 있다 .
아래 쿼리는 튜플에 대한 정보를 확인한다 .
SELECT n.nspname AS schema_name, c.relname AS table_name, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple, pg_stat_get_live_tuples(c.oid) AS live_tuple, pg_stat_get_dead_tuples(c.oid) AS dead_tupple, round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate, round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate, pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size, pg_size_pretty(pg_relation_size(c.oid)) as relation_sizeFROM pg_class AS c JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE pg_stat_get_live_tuples(c.oid) > 0 AND c.relname NOT LIKE 'pg_%' ORDER BY dead_tupple DESC;
아래 쿼리는 Vacuum 통계 정보를 확인한다 .
SELECT * FROM pg_stat_all_tables ORDER BY schemaname, relname;
Vacuum FULL 실행시 pg_class 의 relfilenode 값이 변경된다 . 아래 쿼리는 relfilenode 의 물리적인 파일 위치를 확인한다 .
SELECT oid, pg_relation_filepath(oid), relname, relfilenode FROM pg_class LIMIT 10;
아래 쿼리는 현재 실행중인 Vacuum 세션 정보를 확인할 수 있다 .
SELECT datname, usename, pid, CURRENT_TIMESTAMP - xact_start AS xact_runtime, queryFROM pg_stat_activityWHERE upper(query) LIKE '%VACUUM%'ORDER BY xact_start;
[Autovacuum 데몬 워크플로우 ]
Autovacuum 데몬은 Autovacuum 실행기와 Autovacuum 작업자의 두 가지 다른 종류의 프로세스로 설계되어있다 .
Autovacuum 실행기는 Autovacuum 매개변수가 on 으로 설정될 때 postmaster 가 시작하는 기본 실행 프로세스이다 . postmaster 는 PostgreSQL 시스템에 대한 요청에 대한 처리 메커니즘 역할을 한다 . 모든 프론트 엔드 프로그램은 시작 메시지를 postmaster 에게 보내고 postmaster 는 메시지의 정보를 사용하여 백엔드 프로세스를 시작한다 . Autovacuum 실행기 프로세스는 테이블에서 Vacuum 작업을 실행하기 위해 Autovacuum 작업자 프로세스를 시작할 적절한 시간을 결정한다 .
Autovacuum 작업자는 테이블에서 vacuum 작업을 실행하는 실제 작업자 프로세스이다 . 실행 프로그램에서 예약한 대로 데이터베이스에 연결하고 카탈로그 테이블을 읽고 Vacuum 작업을 실행할 테이블을 선택한다 .
Autovacuum 시작 프로그램 프로세스는 데이터베이스의 테이블을 계속 모니터링하고 테이블이 Autovacuum 임계값에 도달한 후 Vacuum 작업에 적합한 테이블을 선택합니다 . 이 임계값은 아래와 같은 매개변수를 기반으로 한다 .
l autovacuum_vacuum_threshold, autovacuum_analyze_threshold : 이 매개변수는 각각 autovacuum 및 autoanalyzer 에 대해 예약할 테이블의 최소 업데이트 또는 삭제 수를 결정한다 . 둘 다 기본값은 50 이다 .
l autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor : 이 매개변수는 각각 autovacuum 및 autoanalyzer 에 대해 예약할 테이블에 대해 변경이 필요한 테이블의 백분율을 결정한다 . autovacuum_vacuum_scale_factor 의 기본값은 0.2(20%) 이고 autovacuum_analyze_scale_factor 는 0.1(10%) 이다 . 테이블의 행 수가 너무 많지 않은 경우 기본 값을 사용해도 되지만 , 많은 수의 행이 있는 테이블의 경우에는 빈번한 Vacuum 이 발생할 수 있어 적절한 값으로 조절하는 것이 좋다 . 데이터베이스 내에서 큰 테이블이 일부 존재하는 경우 구성 파일보다 테이블 수준에서 이러한 매개변수를 설정하는 것이 좋다 .
임계값 계산은 아래 공식을 사용할 수 있다 .
vacuum threshold = vacuum base threshold + vacuum scale factor * number of live tuples
l Vacuum base threshold – autovacuum_vacuum_threshold
l Vacuum scale factor – autovacuum_vacuum_scale_factor
l Number of live tuples – The value of n_live_tup from pg_stat_all_tables view
Autovacuum 실행기는 자체적으로 Autovacuum 작업자 프로세스를 시작할 수 없으며 postmaster 프로세스에 의해 수행된다 . 런처는 Autovacuum 공유 메모리 영역에 데이터베이스에 대한 정보를 저장하고 공유 메모리에 플래그를 설정하고 postmaster 에게 신호를 보낸다 . postmaster 가 Autovacuum 작업자 프로세스를 시작한다 . 이 새로운 작업자 프로세스는 공유 메모리에서 정보를 읽고 필요한 데이터베이스에 연결하고 Vacuum 작업을 완료한다 .
postmaster 가 작업자 프로세스 시작에 실패하면 공유 메모리에 플래그를 설정하고 런처 프로세스에 신호를 보낸다 . postmaster 의 신호를 읽고 실행기는 postmaster 에게 신호를 전송하여 작업자 프로세스 시작을 다시 시도한다 . (postmaster 가 작업자 프로세스를 시작하지 못하는 것은 로드 및 메모리 압력이 높거나 이미 실행 중인 프로세스가 너무 많기 때문일 수 있다 ).
Autovacuum 작업자 프로세스가 Vacuum 작업으로 완료되면 런처에 신호를 보낸다 . 런처가 작업자로부터 신호를 받으면 런처가 깨어나 Vacuum 테이블 목록이 공유 메모리에 너무 많으면 다른 작업자를 시작하려고 시도한다 . 이는 다른 작업자가 해당 테이블에 대한 Vacuum 잠금을 기다리는데 차단되는 것을 방지하기 위한 것이다 . 또한 다른 작업자가 방금 정리를 완료하여 공유 메모리에 더 이상 기록되지 않은 테이블을 Vacuum 하지 않도록 각 테이블을 Vacuum 하기 직전에 pgstats 테이블의 데이터를 다시 로드한다 .
PostgreSQL 의 일반적인 오해는 Autovacuum 프로세스가 I/O 를 증가시킨다는 것이다 . 따라서 많은 사람들이 Autovacuum 프로세스를 완전히 끄도록 선택한다 . 이러한 행동은 데이터베이스 운영 초기 단계에서는 효과적인 솔루션처럼 보일 수 있지만 데이터베이스 크기가 증가하기 시작하면 데드 튜플이 차지하는 공간이 빠르게 증가하고 , 테이블 및 디스크 공간 증가와 함께 데이터베이스 속도가 느려지기 때문에 권장하지 않는다 .
[Autovacuum 장점 ]
통계 업데이트
PostgreSQL ANALYZE 데몬은 테이블의 통계를 수집하고 계산한다 . 쿼리 플래너는 이러한 통계를 사용하여 쿼리 계획을 실행한다 . 이 정보는 ANALYZE 데몬에 의해 계산 및 수집되며 이러한 통계를 사용하여 카탈로그 테이블에 저장된다 . 그런 다음 쿼리 플래너는 데이터를 가져오기 위한 쿼리 계획을 만든다 . 비슷한 시나리오에서 Autovacuum 이 off 로 설정되어 있으면 ANALYZE 데몬이 통계를 수집하고 계산하지 않는다 . 쿼리 플래너에는 테이블에 대한 정보가 없으므로 잘못된 쿼리 계획을 작성하게 되어 비용 효율적이지 않다 .
트랜잭션 warparound 방지
앞서 설명한 것처럼 PostgreSQL 은 트랜잭션 ID 로 트랜잭션에 숫자를 할당한다 . 트랜잭션 ID 는 숫자이기 때문에 허용되는 최대값 및 최소값과 같은 제한이 있다 . PostgreSQL 은 트랜잭션 ID 에 대한 명확한 숫자로 4 바이트 정수를 사용한다 . 즉 , 4 바이트로 생성할 수 있는 최대 트랜잭션 ID 는 2^32 으로 (~ 4294967296) 약 40 억 개의 트랜잭션 ID 를 사용할 수 있다 . 그러나 PostgreSQL 은 트랜잭션 ID 를 1 에서 2^31( ~ 2147483648) 에서 회전시켜 4 바이트 정수로 트랜잭션을 무제한으로 처리할 수 있다 . PostgreSQL 은 트랜잭션 ID 가 2147483648 에 도달하면 트랜잭션 ID 를 1 에서 2 로 변경하여 2^31 까지 할당 트랜잭션을 관리하고 , 이후 추가 할당 트랜잭션을 트랜잭션 ID 를 1 로 할당하여 사용하는데 이렇게 트랜잭션 ID 를 교체하는 작업을 warparound 라고 한다 .
Autovacuum 은 각 페이지의 각 행을 방문하여 트랜잭션 ID 를 고정한다 . 데이터베이스 트랜잭션 ID 수명이 autovacuum_freeze_max_age 에 도달할 때마다 PostgreSQL 은 Autovacuum 프로세스를 즉시 시작하여 전체 데이터베이스에서 freeze 작업을 수행한다 .
[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 경보를 설정할 수 있습니다 . 자세한 내용은 아래 링크를 참고한다 .
l Implement an Early Warning System for Transaction ID Wraparound in Amazon RDS for PostgreSQL : https://aws.amazon.com/ko/blogs/database/implement-an-early-warning-system-for-transaction-id-wraparound-in-amazon-rds-for-postgresql/
또한 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 조정에 대한 자세한 내용은 아래 링크를 참고한다 .
l A Case Study of Tuning Autovacuum in Amazon RDS for PostgreSQL : https://aws.amazon.com/ko/blogs/database/a-case-study-of-tuning-autovacuum-in-amazon-rds-for-postgresql/
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 를 차단하지 않으며 아래 잠금이 있는 트랜잭션만 차단한다 .
l SHARE UPDATE EXCLUSIVE – Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, CREATE STATISTICS, and certain ALTER INDEX and ALTER TABLE variants.
l SHARE – Acquired by CREATE INDEX (without CONCURRENTLY).
l SHARE ROW EXCLUSIVE – Acquired by CREATE TRIGGER and some forms of ALTER TABLE.
l EXCLUSIVE – Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY.
l 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 이 처리할 수 있는 데드 튜플 수를 계산할 수 있다 .
autovacuum_work_mem 또는 maintenance_work_mem 매개변수를 설정하면 각 Autovacuum 작업자 프로세스가 사용해야 하는 최대 메모리 크기가 설정된다 . 기본적으로 autovacuum_work_mem 은 -1 로 설정되며 이는 Autovacuum 작업자 프로세스에 대한 메모리 할당이 maintenance_work_mem 설정을 사용해야 함을 나타낸다 .
Amazon RDS 는 이 파라미터의 기본값은 아래와 같이 계산된 KB 로 적용되어 있다 .
GREATEST({DBInstanceClassMemory/63963136*1024},65536).
더 자세한 내용은 아래 링크를 참고한다 .
l Common DBA tasks for Amazon RDS for PostgreSQL : https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.WorkMemory
l A Case Study of Tuning Autovacuum in Amazon RDS for PostgreSQL : https://aws.amazon.com/ko/blogs/database/a-case-study-of-tuning-autovacuum-in-amazon-rds-for-postgresql/
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 관련 파라미터를 업데이트한다 .
l autovacuum_vacuum_cost_delay – autovacuum 프로세스가 제한을 초과할 때 대기하는 지정된 시간 ( 밀리초 ) 이다 . 기본값은 20 밀리초이다 .
l autovacuum_vacuum_cost_limit – Autovacuum 프로세스를 휴면 상태로 만드는 누적 비용으로 기본값은 200 이다 .
l autovacuum_work_mem – 각 Autovacuum 작업자 프로세스에서 사용하는 최대 메모리 양이다 . 기본값은 -1 로 maintenance_work_mem 값을 사용해야 함을 나타낸다 .
l autovacuum_naptime – 주어진 데이터베이스에서 Autovacuum 실행 사이의 최소 지연을 지정한다 . 각 라운드에서 데몬은 데이터베이스를 검사하고 해당 데이터베이스의 테이블에 대해 필요에 따라 VACUUM 및 ANALYZE 명령을 실행한다 . 지연은 초 단위로 측정되며 기본값은 1 분이다 . 이 매개변수는 postgresql.conf 파일이나 서버 명령줄에서만 설정할 수 있다 .
Amazon RDS 는 기존 값이 충분히 공격적이지 않은 경우에만 이러한 파라미터를 수정한다 . 이러한 파라미터는 DB 인스턴스의 메모리에서 수정되며 파라미터 그룹에서는 변경되지 않는다 . Amazon RDS 가 이러한 Autovacuum 파라미터를 수정할 때마다 Amazon RDS API 를 통해 AWS Management 콘솔에서 볼 수 있는 영향을 받는 DB 인스턴스에 대한 이벤트를 생성한다 . MaximumUsedTransactionIDs CloudWatch 지표가 임계값 아래로 반환되면 Amazon RDS 는 메모리의 Autovacuum 관련 파라미터를 파라미터 그룹에 지정된 값으로 재설정한다 .
Setting autovacuum at table level
글로벌 Autovacuum 설정을 기반으로 증가하는 PostgreSQL 환경에서 큰 테이블은 효과적으로 Vacuum 되지 않고 작은 테이블은 자주 Vacuum 되는 것을 볼 수 있다 . 이러한 시나리오를 피하기 위해 다음 단계에 따라 테이블 수준에서 Autovacuum 매개변수를 설정할 수 있다 .
1. 데이터베이스에서 큰 테이블을 나열한다 .
2. 많은 수의 변경 사항이 발생한 테이블을 나열한다 .
3. 어떤 테이블에 'n_dead_tup' 수가 많은지 확인한다 .
4. 테이블이 마지막으로 자동 분석 및 자동 진공 처리된 시간을 확인한다 .
5. 테이블 수준에서 Autovacuum 및 Autoanalyze 매개변수를 변경한다 .
[ 참고자료 ]
l Amazon Aurora : https://aws.amazon.com/ko/rds/aurora/
l 정기적인 Vacuum 작업 : https://www.postgresql.kr/docs/9.4/routine-vacuuming.html
l MVCC : https://en.wikipedia.org/wiki/Multiversion_concurrency_control
l Free Space Map(FSM) : https://www.postgresql.org/docs/current/storage-fsm.html
l Visibility Map (VM) : https://www.postgresql.org/docs/current/storage-vm.html
l Understanding autovacuum in Amazon RDS for PostgreSQL environments : https://aws.amazon.com/ko/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/
l AWS RDS for PostgreSQL Vacuum Tuning : https://catalog.us-east-1.prod.workshops.aws/workshops/2a5fc82d-2b5f-4105-83c2-91a1b4d7abfe/en-US/3-intermediate/vacuum-tuning
l Visibility Map Problems : https://wiki.postgresql.org/wiki/Visibility_Map_Problems
l Cost-based Vacuum Delay : https://www.postgresql.kr/docs/9.4/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST
l Automatic Vacuuming : https://www.postgresql.org/docs/current/runtime-config-autovacuum.html
l Implement an Early Warning System for Transaction ID Wraparound in Amazon RDS for PostgreSQL : https://aws.amazon.com/ko/blogs/database/implement-an-early-warning-system-for-transaction-id-wraparound-in-amazon-rds-for-postgresql/
l A Case Study of Tuning Autovacuum in Amazon RDS for PostgreSQL : https://aws.amazon.com/ko/blogs/database/a-case-study-of-tuning-autovacuum-in-amazon-rds-for-postgresql/
l Common DBA tasks for Amazon RDS for PostgreSQL : https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.WorkMemory
2022-10-26 / Sungwook Kang / http://sungwookkang.com
AWS, Aurora, PostgreSQL, Autovacuunm, Vacuum
