누가 Vacuum을 방해하는가?

Postgresql 입문자라면 누구나 Vacuum이 중요하다는 것을 알고 있을 것이다.

Vacuum은 dead tuples에 의해 차지하고 있는 disk space를 정리하여 table size가 쓸데없이 커지는 것을

방지하며, 불필요한 block read를 줄이고 최신의 통계정보 갱신으로 보다 나은 실행계획을 세울수 있도록

query optimizer에 도움을 주며 wraparound가 발생하는 것을 방지하도록 해 준다.

“autovacuum=on으로 설정하면 모든게 자동으로 알아서 잘 처리해 주는거 아냐?” 라고 할 수도 있지만

실상은 그렇지 않다.

이 글에서는 autovacuum=on 운영하에서 dead tuples을 제대로 정리하지 못하는 3가지 상황에 대해

예제를 통해 살펴보도록 하겠다.

 

1) Long Transaction 그냥 두지 말자 !!

간단한 예제의 모니터링을 위해 vac01 table의 autovacuum 관련 parameter를 아래와 같이 수정한다.

=> alter table vac01 set (autovacuum_vacuum_scale_factor = 0.0);

=> alter table vac01 set (autovacuum_vacuum_threshold = 5);

=> alter system set log_autovacuum_min_duration to 0;

=> select pg_reload_conf();

 

Session #01은 vac01 table에 10건의 data를 생성하고 6건의 데이터를 갱신하여 auto vacuuming이

실행되도록 한다. (pg_stat_user_tables.n_dead_tup > autovacuum_vacuum_threshold +

autovacuum_vacuum_scale_factor * pg_class.reltuples, 6 > 5 + 0*10)

auto vacuuming이 실행되기 전에 Session #02는 Long Transaction으로 되도록 하고.

Session #03은 Long running Query가 되도록 한다.

-----------------------------------------------------------------------------------------------------------------------

-. Session #01 -. Session #02 -. Session #03

=> insert into vac01

select id,

substr(md5(random()::text),1,10)

from generate_series(1, 10) id;

=> begin; => select pg_sleep(600);

insert into t01 values (1, 'long trans');

 

=> update vac01

set c2 = 'update' where c1 < 7;

~

commit;

-----------------------------------------------------------------------------------------------------------------------

-. Active Session Monitoring

=> select pid, state, substr(query,1,40), backend_xid, backend_xmin from pg_stat_activity;

pid | state | substr | backend_xid | backend_xmin

-------+--------------------+------------------------------------------+------+------

23782 | idle in transaction | insert into t01 values (1, 'long trans') | 7452 |

23783 |         active         |          select pg_sleep(600);             |        | 7452

 

위와 같은 상황에서 vac01 table은 auto vacuum이 실행되어도 6개의 rows들의 xmax 값은 7454로

removable cutoff: 7452 보다 크기 때문에 auto vacuum에 의해 정리되지 않고 남아 있게 된다.

 

agensdb=# select lp, t_xmin, t_xmax, t_ctid from heap_page_items(get_raw_page('vac01', 0));

lp | t_xmin | t_xmax | t_ctid                                           lp | t_xmin | t_xmax | t_ctid

-----------------------------------------------------------------------------------------------------------------------

1 | 7451 | 7454 | (0,11)                                                7 | 7451 | 0 | (0,7)

2 | 7451 | 7454 | (0,12)                                                8 | 7451 | 0 | (0,8)

3 | 7451 | 7454 | (0,13)                                                9 | 7451 | 0 | (0,9)

4 | 7451 | 7454 | (0,14)                                               10 | 7451 | 0 | (0,10)

5 | 7451 | 7454 | (0,15)                                               11 | 7454 | 0 | (0,11)

6 | 7451 | 7454 | (0,16)                                               12 | 7454 | 0 | (0,12)

                                                                              13 | 7454 | 0 | (0,13)

                                                                              14 | 7454 | 0 | (0,14)

                                                                              15 | 7454 | 0 | (0,15)

                                                                              16 | 7454 | 0 | (0,16)

 

LOG: automatic vacuum of table "agensdb.public.vac01": index scans: 0

pages: 0 removed, 1 remain, 1 scanned (100.00% of total)

tuples: 0 removed, 16 remain, 6 are dead but not yet removable

removable cutoff: 7452, which was 3 XIDs old when operation ended

 

#. removable cutoff란 간단히 말해서 현재 시점의 Database 내에서 해당 txid를 참조하는 Session은 없는

것을 보장한다는 의미이다. 즉, 7452까지는 vacuum 대상이 된다는 것이다.

 

transaction 정리 후, auto vacuum에 의해 6개의 dead tuples이 정리되는 것을 볼 수 있다.

LOG: automatic vacuum of table "agensdb.public.vac01": index scans: 0

pages: 0 removed, 1 remain, 1 scanned (100.00% of total)

tuples: 6 removed, 10 remain, 0 are dead but not yet removable

removable cutoff: 7458, which was 0 XIDs old when operation ended

 

이와 같이 오래된 ‘idle in transaction’ 이나 active한 long query는 auto vacuum의 대상 table에 관계없이

dead tuples을 정리하지 못하게 하는 방해물인 것이다.

또한 autovacuum_naptime(default 60초) 마다 성공하지도 못하는 auto vacuum 작업을 long query가

완료될때까지 쓸데없이 반복하며 시스템 리소스만 낭비하게 되는 요인으로 작용하게 된다.

PostgreSQL은 이러한 상황을 예방할 수 있도록 ‘idle_in_transaction_session_timeout’ 과

‘statement_timeout’을 설정할 수 있다.(default는 0)

 

#. idle_in_transaction_session_timeout은 트랜잭선이 시작한 뒤 작업을 실행하지 않고 대기 중인 상태의

트랜잭션을 일정 시간이 지나면 자동으로 종료 해주는 parameter로 postgresql.conf 파일을 수정하는

방법과 아래와 같이 Command를 이용하는 방법이 있다. (단위는 ms)

=> alter system set idle_in_transaction_session_timeout to 300000;

 

#. statement_timeout parameter는 OLTP 업무의 특성을 고려하여 적정한 값을 적용하고 배치 프로그램

단위로 => set statement_timeout to 0 or 배치최대시간; 구분하여 적용할 수 있다.

 

2) Replication Slot을 사용할때 Standby Server의 DB 상태를 체크 하자 !!

Replication Conflict를 방지하기 위해 standby server에 hot_standby_feedback = on으로 설정하고

사용할때 standby server의 DB를 stop한 뒤에 replication slot의 상태를 조회해 보면,

=> select slot_name, slot_type, active, active_pid, xmin from pg_replication_slots;

slot_name | slot_type | active | active_pid | xmin

-------------+----------+-------+-----------+------

repl_slot01  | physical |     f     |              | 7532

 

xmin 값이 7532 인 것을 알수 있고, 이 값이 auto vacuum 수행 시, removable cutoff 값이 될 것이다.

=> update vac01 set c2 = 'update06' where c1 < 7; 와 같이 6건을 update 한 다음의 xmax 값을

조회해 보면, 아래와 같이 7533 인것을 확인할 수 있다.

 

agensdb=# select lp, t_xmin, t_xmax, t_ctid from heap_page_items(get_raw_page('vac01', 0));

lp | t_xmin | t_xmax | t_ctid                                          lp | t_xmin | t_xmax | t_ctid

-----------------------------------------------------------------------------------------------------------------

1 | 7531 | 7533 | (0,11)                                               7 | 7531 | 0 | (0,7)

2 | 7531 | 7533 | (0,12)                                               8 | 7531 | 0 | (0,8)

3 | 7531 | 7533 | (0,13)                                               9 | 7531 | 0 | (0,9)

4 | 7531 | 7533 | (0,14)                                               10 | 7531 | 0 | (0,10)

5 | 7531 | 7533 | (0,15)                                               11 | 7533 | 0 | (0,11)

6 | 7531 | 7533 | (0,16)                                               12 | 7533 | 0 | (0,12)

                                                                              13 | 7533 | 0 | (0,13)

                                                                              14 | 7533 | 0 | (0,14)

                                                                              15 | 7533 | 0 | (0,15)

                                                                              16 | 7533 | 0 | (0,16)

 

auto vacuum의 수행 log를 보면, dead tuples을 정리하지 못한 것을 알 수 있다.

LOG: automatic vacuum of table "agensdb.public.vac01": index scans: 0

pages: 0 removed, 1 remain, 1 scanned (100.00% of total)

tuples: 0 removed, 16 remain, 6 are dead but not yet removable

removable cutoff: 7532, which was 42 XIDs old when operation ended

 

standby server의 DB를 start 한 후에,

=> select slot_name, slot_type, active, active_pid, xmin from pg_replication_slots;

slot_name | slot_type | active | active_pid | xmin

repl_slot01 | physical |    t     |    60061   | 7538

xmin 값은 7538로 바뀌고 auto vacuum의 수행 log를 보면, dead tuples이 정리 되었다.

LOG: automatic vacuum of table "agensdb.public.vac01": index scans: 0

pages: 0 removed, 1 remain, 1 scanned (100.00% of total)

tuples: 6 removed, 10 remain, 0 are dead but not yet removable

removable cutoff: 7538, which was 0 XIDs old when operation ended

standby server의 DB를 한동안 start 할 수 없는 상황이라면, slot 삭제도 고려해 볼 수 있다.

=> select pg_drop_replication_slot('repl_slot01');

pg_drop_replication_slot

--------------------------

(1 row)

 

3) Distributed Transaction을 사용한다면 Transaction Hang 상황도 살펴 보자 !!

-------------------------------------------------------------------------------------------------------------

-. Session #01 -. Session #02

=> begin;

prepare transaction 'tran01';

=> update vac01 set c2 = 'update' where c1 < 7;

=> agensdb=# select gid, prepared, owner, database, transaction AS xmin from pg_prepared_xacts;

  gid    |                    prepared                    | owner | database | xmin

--------+-------------------------------------------+--------+----------+------

tran01  |     2024-06-11 12:40:39.095001+00     | agens | agensdb  | 7560

(1 row)

 

auto vacuum의 수행 log를 보면, dead tuples을 정리하지 못한 것을 알 수 있다.

LOG: automatic vacuum of table "agensdb.public.vac01": index scans: 0

pages: 0 removed, 1 remain, 1 scanned (100.00% of total)

tuples: 0 removed, 16 remain, 6 are dead but not yet removable

removable cutoff: 7560, which was 5 XIDs old when operation ended

 

Hang 상황을 처리하려면, 아래와 같이 rollback을 수행한다.

agensdb=# rollback prepared 'tran01';

ROLLBACK PREPARED

agensdb=# select gid, prepared, owner, database, transaction AS xmin from pg_prepared_xacts;

gid | prepared | owner | database | xmin

-----+----------+-------+-----------+------

(0 rows)

 

① Long Transaction, ② Replication, ③ Distributed Transaction 사용자에 따라 PostgreSQL 운영환경은

다양할 수 있지만, 먼저 나의 운영환경을 이해하고 나에게 맞는 상황들을 고려하여 모니터링을 잘

수행하고 예방한다면 auto vacuum은 정상적으로 수행될 것이므로 vacuum에 대해 너무 걱정할 필요는

없을 것이라 생각됩니다.

 

#. Replication Conflict는 아래를 참조하세요.

Replication conflicts in PostgreSQL and how to deal with them | CYBERTEC PostgreSQL | Services & Support (cybertec-postgresql.com)

 

#. Script 참고

-. pageinspect는 debugging 목적으로 database pages의 내용을 살펴볼 수 있는 기능을 제공

CREATE EXTENSION pageinspect;