[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_size
FROM 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,
 query
FROM
 pg_stat_activity
WHERE
 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

SQL Server MySQL PostgreSQL 비교

 

·         Version : SQL Server MySQL PostgreSQL

 

RDBMS 많이 사용하는 SQL Server MySQL 그리고 PostgreSQL 특징 차이점에 대해서 살펴본다. 데이터베이스 기능이 너무나 많기 때문에 모두 다루지는 못하며 대표적인 내용 몇가지만 다루도록 한다.

·         MySQL PostgreSQL, SQL Server 데이터베이스 모두 ACID 완벽하게 지원하며 많은 양의 데이터와 높은 수준의 쿼리 동시성을 처리할 있다.

·         PostgreSQL 기능이 풍부하고 확장성이 뛰어나다

·         MySQL 많은 곳에서 사용하고 있으며 애플리케이션과 전자상거래 프로젝트에 적합하며, PostgreSQL 비해 블로그, 지원, 문서가 훨씬 많다

·         PostgreSQL 경우 연결마다 자체 메모리가 있기 때문에 연결수가 많은 환경의 경우 많은 메모리가 필요할수 있다. 그러나 PgBouncers 외부 연결 풀을 사용하여 이러한 문제를 해결할 있는 솔루션이 있다.

·         PostgreSQL 수동 파티션 관리는 너무 많은 오버헤드와 파티션에서 다른 파티션으로 행을 이동시키는 업데이트가 필요하다.

·         MySQL에는 쿼리당 1개의 CPU 사용하는 중첩루프 조인 알고리즘만 있어 MySQL 데이터웨어우스 시스템에는 적합하지 않다.

·         Check 제약 기능이 중요한 서비스라면 MySQL 적합하지 않다.

 

[MySQL, PostgreSQL, SQL Server일반 정보]

 

MySQL

PostgreSQL

SQL Server

Maturity

1995 릴리즈

1989 릴리즈

1989 MSMS OS/2 SQL Server릴리즈(Sybase 함께)

1995 SQL Server 6.0릴리즈

Language

C (일부 C++)

C

C++

Cost

오픈소스 / 오라클 소유의 유료버전

완전 무료 / 오픈소스

SQL Server Express 무료버전과 외의 유료버전

 

[MySQL, PostgreSQL, SQL Server 데이터 변경]

 

MySQL

PostgreSQL

SQL Server

Row Update

업데이트가 수행되고 변경된 데이터가 롤백 세그먼트로 복사. Vacuum 인덱스 압축이 매우 효율적. MySQL 읽기에는 속도가 느리지만 쓰기는 원자적이며 보조 인덱스의 열이 변경되어도 모든 인덱스를 변경할 필요가 없음

업데이트는 인서트 + 삭제 표시로 구현된다. 모든 색인에는 행의 실제 ID 대한 링크가 있다. 열이 업데이트되면 새로운 물리적ID 있는 행이 만들어지고 모든 행이 새로운 행의 실제ID 대한 포인터를 얻기 위해 변경된 열을 참조하지 않는 경우에도 모든 인덱스가  업데이트 되어야 하기 때문에 업데이트 오버헤드가 발생

Row-Store 데이터베이스 엔진 :

 

인메모리 데이터베이스 엔진 : 업데이트는 인서트 + 삭제 표시로 구현. 가비지 컬렉터는 블럭킹 병렬로 작업

 

Columnstore 데이터베이스 엔진 : in-place 업데이트

Vacuum / Defragmentation

Vacuum 인덱스 압축은 매우 효율적

Vacuum 전체 테이블 스캔을 수행하여 삭제 행을 찾는다. 프로세스/사용자의 작업에 오버헤드를 있다.

메모리 가비지 컬렉턴는 최대 15% 오버헤드가 발생할 있다.

 

[MySQL, PostgreSQL, SQL Server 데이터 쿼리]

 

MySQL

PostgreSQL

SQL Server

쿼리 요청에 대한 Buffer Pool / Cache

MySQL 캐시는사용자 쿼리를 버퍼풀이라고 한다. 캐시는 필요에 따라 크기로 설정할 있으므로 서버의 다른 프로세스에 충분한 메모리만 남겨둔다. 버퍼 풀을 여러 부분으로 분할하여 메모리 구조에 대한 경합을 최소화하고 테이블을 버퍼 풀에 고정할 있다. 테이블 스캔 또는 mysqldump 이전 데이터를 제거한다.

PostgreSQL 데이터 페이지를 위한 공유 메모리를 유지한다. 프로세스 기반 시스템이기 때문에 연결은 고유한 고유  OS 프로세스를 가지며 자체 메모리를 가지고 있다. 프로세스는 실행이 끝난 메모리를 해제 한다. 따라서 많은 연결을 확장하는데 문제가 발생할 있다.

SQL Server 메모리는 버퍼풀이라고 하며 크기는 필요에 따라 크게 설정할   있으며 여러 버퍼 풀을 설정하는 옵션은 없다.

제약 조건 지원

기본키, 외래키, not null 제약 조건, 고유 제약조건, 기본 제약조건을 지원. CHECK 제약 조건을 지원하지 않는다.

기본 , 외래키, not null 제약 조건, 체크 제약 조건, 유니크 제약 조건, 기본 제약 조건을 지원한다.

기본 , 외래키, not null 제약 조건, check 제약 조건, 고유 제약 조건, 기본 제약 조건을 지원한다.

임시 테이블

CTE 지원하고 전역 임시 테이블 테이블 변수를 지원하지 않음.

동일한 쿼리에서 TEMPORARY 테이블을 번이상 참조할 없음. ( : select * from tbl_temp t1 join tbl_temp as t2)

CTE, 전역 로컬 임시 테이블 테이블 변수를 지원.

동일한 이름을 가진 개의 테이블을 만들면 다른 하나는 일반 테이블이다.

Creatae temp table x (..)

Create table x (…)

Select * from X 항상 임시테이블에서 데이터를 가져옴

CTE, 전역 로컬 임시테이블 테이블 변수 지원

Windows /Analytical 함수

CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENT_RANK, ROW_NUMBER, RANK, DENSE_RANK, NTILE, NTH_VALUE PERCENTILE_CONT, PERCENTILE_DISC

CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, ROW_NUMBER, RANK, DENSE_RANK, NTILE, NTH_VALUE

CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, ROW_NUMBER, RANK, DENSE_RANK, NTILE

병렬 쿼리 실행

MySQL 일반적으로 쿼리당 1개의 CPU 사용

쿼리 계획은 여러 CPU 활용할 있음

쿼리 계획은 여러 CPU 활용할 있음

인덱스

인덱스 구성 테이블을 지원 클러스터 인덱스.

지속된 인덱스/구체화된 뷰를 지원하지 않음

인덱스 구성 테이블 미지원.

지속된 인덱스 / 구체화된 지원

인덱스 구성 테이블을 지원 클러스터 인덱스

단일 쿼리에서 다중 인덱스 사용

단일 쿼리에 다중 인덱스 사용 가능

단일 쿼리에 다중 인덱스 사용 가능. Xy 별도 인덱스가 있는 경우 WHERE x=5 and y=6 같은 쿼리를 구현할 있는 방법 하나는 적절한 쿼리 절과 함께 인덱스를 사용한 다음 인덱스 결과를 AND 결합하여 결과 행을 식별하는 것이다.

단일 쿼리에 다중 인덱스 사용 불가능

다중 컬럼 인덱스

다중 인덱스에는 최대 16개의 열을 포함할 있음

다중 인덱스에는 최대 32개의 열을 포함할 있음

다중 인덱스에는 최대 16개의 열을 포함할 있음

부분 인덱스

부분 인덱스를 지원하지 않음

부분 인덱스 지원

부분 인덱스 지원

JOIN 알고리즘

MySQL 중첩 루프 알고리즘 또는 변형을 사용하여 테이블간의 조인을 실행

중첩 루프 조인, 해시 조인 병합 조인 알고리즘 지원

중첩 루푸 조인, 해시 조인 병합 조인 알고리즘 지원

쿼리 실행 계획 재사용

준비된 명령문 저장된 프로그램에 대한 캐시를 세션별로 유지. 세션에 대해 캐시된 명령문은 다른 세션에서 액세스할 없음

준비된 문이 열려있는 동안에만 쿼리 계획을 캐시함. 쿼리 계획은 준비된 문이 클로즈때 해제됨.

쿼리가 실행계획을 다시 사용할 있도록 공유 실행계획 캐시가 있음

통계

지속성 지속성 통계 유지 (서버 재시작시 지워짐)

 

 

메모리 최적화 테이블

MySQL 테이블을 메모리에 저장할 있다. 메모리에 작성된 테이블은 트랜잭션을 지원하지 않으므로 데이터가 손상될수 있다. 이러한 테이블은 임시 영역 또는 읽기 전용 캐시로 사용해야한다.

메모리 엔진을 제공하지 않음

메모리 OLTP SQL Server 데이터베이스 엔진에 통합되어 있음

Columnstore 또는 저장소

MariaDB 최근에 여러 서버가 있는 환경에서 대규모 병렬 데이터베이스로 설계된 MySQL 저장소 엔진을 추시. InnoDB 스토리지 엔진 대신 사용 가능

저장소, 컬럼형 스토리지 엔진을 제공하지 않음

SQL Server 테이블을 쿼리하기 위해 저장소 인덱스를 제공

 

[MySQL, PostgreSQL, SQL Server JSON 데이터 유형]

 

MySQL

PostgreSQL

SQL Server

JSON 데이터 유형

MySQL JSON 데이터 형식을 지원하며 전체 문서를 바꾸는 대신 JSON 통한 부분 업데이트를 지원. 그러나 많은 제한이 있다. JSON 대한 인덱스 생성은 지원하지 않음

PostgreSQL JSON 데이터 유형을 지원하며 부분 업데이트를 지원한다.

SQL Server JSON 데이터 형식을 지원하며 부분 업데이트를 지원

고급 데이터 형식

지형 공간 데이터 유형을 지원한다. 사용자 정의 유형이 없다.

지형 공간 다차원 배열, 사용자 정의 형식 등과 같은 많은 고급 데이터 형식을 지원

지형 공간 데이터 유형, 계층 데이터 지원

 

[MySQL, PostgreSQL, SQL Server 샤딩/파티셔닝/복제]

 

MySQL

PostgreSQL

SQL Server

파이셔닝 지원

HASH 파이셔닝(모든 컬럼에서 HASH 함수를 사용하여 테이블을 N개의 파티션으로 분할), 여러 컬럼을 기반으로 하는 RANGE 또는 LIST 파티셔닝, HASH 유사한 KEY 파티셔닝(자동생성된 숫자 기반) 지원

RANGE LIST 파티셔닝을 지원하지만 파티션 인덱스는 수동으로 생성해야하며 테이블 상속을 통한 구식 파티셔닝이 필요(부모 테이블을 쿼리할때 모든 하위 테이블도 쿼리가 될때 하위 테이블은 파티셔닝 컬럼에 제약이 있음)

하위 테이블에는 하위 테이블에 부모 테이블과 인덱스를 별도로 적용해야하는 열이 많을 있음

RANGE 파이셔닝을 지원

샤딩 지원

공유를 구현하지 못함(MySQL Cluster 많은 제한 사항 때문에 거의 배포되지 않음)

공유를 구현하는 Postgres 포크는 수십가지가 있지만 아직 커뮤니티 출시에 추가된 것은 없음

표준 공유 구현이 없음

복제

명령문 또는 변경된 행을 기반으로 하는 마스터-슬레이브 복제.

그룹 복제는 마스터 서버에서 자동으로 복제

변경된 로그 전달을 기반으로하는 마스터-슬레이브 복제

데이터베이스 수준 : 가용성 그룹의 마스터-여러 슬레이브

로그전달

On Data level : 마스터-슬레이브 /양방향 마스터-슬레이브 / 마스터-마스터(병합) 복제

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5745/compare-sql-server-mysql-and-postgresql-features/

 

 

2018-10-30 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, MySQL, PostgreSQL


+ Recent posts