DB 인사이드 | PostgreSQL Vacuum - Monitoring : XMIN’s Horizon

 

 

Vacuum Series를 통한 Vacuum 동작원리에 이어 이번에는 Monitoring 시 주의해야 할 사항에 대해 이야기해 보도록 하겠습니다.

PostgreSQL에 익숙하지 않은 사용자의 경우 Vacuum의 중요성만 인지할 뿐, 단순히 Autovacuum을 Enable 시키거나 Job(Cron)을 통한 Manual Vacuum을 수행하는 것으로 필요한 처방을 다 했다고 생각하곤 합니다.

하지만, MVCC 모델에서 파생된 복잡/다양한 메커니즘은 비단 [Auto]vacuum Operation뿐만 아니라 일반적인 Database 운영 과정까지 지대한 영향을 끼칩니다. 본 문서에서는 그중 idle in transaction상태의 Session이 Vacuum Operation과 맞물렸을 때 발생하는 상황에 대해 알아보도록 하겠습니다.

 

PG_STAT_ACTIVITY

PG_STAT_ACTIVITY는 PostgreSQL을 모니터링할 때 가장 많이 사용하는 View 중 하나로, Session Level 모니터링에서 빼놓을 수 없습니다. 해당 View에는 PID, Query, Status 및 Wait 정보를 포함하기 때문에 그 자체, 혹은 다른 성능 View와의 Join을 통해 다양한 정보를 추출할 수 있습니다.

우리는 PG_STAT_ACTIVITY의 다양한 컬럼 중 state, backend_xid, backend_xmin 에 대해 알아보도록 하겠습니다.

state

Current overall state of this backend. Possible values are:
• active: The backend is executing a query.
• idle: The backend is waiting for a new client command.
• idle in transaction: The backend is in a transaction, but is not currently executing a query.
• idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error.
• fastpath function call: The backend is executing a fast-path function.
• disabled: This state is reported if track_activities is disabled in this backend.

state 컬럼은 Session의 현재 상태를 나타냅니다. 모니터링 시 active 값을 사용하여 활성 Session 목록을 추출하거나, 유휴상태의 Session을 필터링하기 위해 idle 값을 사용하기도 합니다. 이 중 우리가 다룰 idle in transaction 상태란, Transaction이 진행 중이지만 Query를 수행하지는 않는 상태를 의미합니다.

backend_xid

Top-level transaction identifier of this backend, if any.

backend_xid는 Transaction의 XID를 표시합니다. 명시적으로 Transaction을 시작하거나, 혹은 Auto-Commit 기능을 Disable 한 상태에서 DML을 수행하면 비로소 해당 시점의 XID가 backend_xid 값으로 설정됩니다.

아래와 같이 BEGIN TRANSACTION을 통한 Transaction 선언 시 해당 Session의 state 값이 idle in transaction으로 변경되며, 이후 첫 DML을 수행하는 시점의 XID가 backend_xid에 할당됩니다.

BEGIN TRANSACTION; [or BEGIN;]
 DML;
END TRANSACTION; [or COMMIT;]

backend_xmin

The current backend's xmin horizon.

공식 Document에서는 “xmin의 지평선”이라는 추상적인 표현으로 backend_xmin을 설명하고 있습니다. 그 숨은 뜻을 이해하기 위해서 본 문서는 backend_xmin이 갖는 다양한 특징을 먼저 알아보도록 하겠습니다. backend_xmin 이 갖는 특징은 다음과 같습니다.

• Session이 Query를 수행하는 상태(Active) 중에는 항상 backend_xmin값을 가지며, 그 값은 다음 두 규칙을 따른다.
• 접속해 있는 모든 Session들의 backend_xid 중 가장 낮은(오래된) 값을 backend_xmin값으로 사용한다.
• 현재 Transaction을 진행 중인 Session이 없는 경우 Current XID값을 backend_xmin값으로 사용한다.

이러한 특징들을 종합하면, backend_xmin이란 Session이 Query를 수행할 때 참조하는 현존하는 가장 오래된 Transaction ID 정보임을 알 수 있습니다.

📌 backend_xmin은 Replication 환경에서도 중요한 의미를 갖습니다. 해당 내용에 대해서는 Replication Series를 통해 다룰 예정입니다.

 

backend_xmin과 Vacuum의 상관관계

앞서 설명한 컬럼 중, backend_xmin컬럼을 통해 현존하는 가장 오래된 Transaction ID정보를 관리하는 이유는 무엇일까요?

예정된 Vacuum 작업이 있다고 가정해 보겠습니다. 처리해야 할 Dead Tuple 중에는 이미 (다른 Session에 의해) 시작된 Transaction이 참조할 “가능성”이 있는 Dead Tuple도 존재할 수 있습니다. 이처럼 Transaction의 읽기 일관성을 유지하기 위해서는 참조 “가능성”이 있는 Dead Tuple을 Vacuum대상에서 제외시킬 방법과 기준점이 필요합니다.

즉, PostgreSQL은 Vacuum대상을 구분 짓기 위한 Cut-Off 지점을 정의하고 해당 시점 이후의 Tuple들에 대해서는 Vacuum작업을 “다음으로 미루는” 방식으로 동작을 합니다. 그리고 이러한 XID의 Cut-Off지점으로 backend_xmin컬럼을 사용합니다.

이러한 상황을 간단한 예제를 통해 확인해보도록 하겠습니다. 시나리오는 다음과 같이 두 개의 Session을 통해 Step 1. ~ 6. 의 순서로 진행됩니다.

	Session S1	Session S2
Step 1.	테이블 A 생성
Step 2.		테이블 B 생성 후 Auto-commit 기능을 Disable 한 상태로 데이터 입력 (Transaction Start)
Step 3.	A 테이블에 데이터 입력 후, Update 수행
Step 4.	테이블 A에 대한 Aggressive Vacuum 수행 (vacuum freeze) 및 결과 관찰
Step 5.		Auto-commit 종료 (Transaction End)
Step 6.	Step 4. 의 과정을 반복 수행 및 결과 관찰

Step 1. ~ Step 3.

-- Session S1 (PID : 2028436)
-- Step 1.

drop table A;
create table A (C1 INTEGER);
								

								-- Session S2 (PID : 2028435)
								-- Step 2.

								create table B (C1 INTEGER);
								--Auto-commit Disable (Transaction Start)
								insert into B values (1) returning TXID_CURRENT();
								txid_current|
								------------+
								        1914|


-- Session S1 (PID : 2028436)
-- Step 3.

insert into A values(1);
update A set c1=2;

select * from HEAP_PAGE_ITEMS(GET_RAW_PAGE('A', 0))
=>
lp|lp_off|lp_flags|lp_len|t_xmin|t_xmax|t_field3|t_ctid|t_infomask2|t_infomask|t_hoff|t_bits|t_oid|t_data|
--+------+--------+------+------+------+--------+------+-----------+----------+------+------+-----+------+
 1|  8160|       1|    28|1915  |1916  |       0|(0,2) |      16385|       256|    24|      |     |      |
 2|  8128|       1|    28|1916  |0     |       0|(0,2) |      32769|     10240|    24|      |     |      |

• Step 2. 에서 Auto-Commit 기능을 Disable 시킨 후 데이터 입력 (XID=1914)
• Step 3. 에서 Insert 시점의 XID=1915이며, Update 시점의 XID=1916 (pageinspect Extension을 통한 확인)
• Step 3. 이후, Session S1에서 PG_STAT_ACTIVITY 조회 시, 아래와 같이 Session S2의 state가 idle in transaction이며, backend_xid는 1914로 설정되어 있는 것을 확인
• 이를 조회하는 Session S1의 backend_xmin은 1914 임을 확인

-- Session S1 (PID : 2028436)

select * from pg_Stat_activity;
=>
pid    |xact_start                   |query_start                  |state_change                 |wait_event_type|wait_event         |state              |backend_xid|backend_xmin|query_id|query                            |backend_type                |
-------+-----------------------------+-----------------------------+-----------------------------+---------------+-------------------+-------------------+-----------+------------+--------+---------------------------------+----------------------------+
...
2028435|2022-10-28 12:15:42.268 +0900|2022-10-28 12:15:42.291 +0900|2022-10-28 12:15:42.291 +0900|Client         |ClientRead         |idle in transaction|1914       |            |        |SHOW search_path                 |client backend              | << Session S2
2028436|2022-10-28 12:15:52.764 +0900|2022-10-28 12:15:52.765 +0900|2022-10-28 12:15:52.765 +0900|               |                   |active             |           |1914        |        |select * from pg_Stat_activity   |client backend              | << Session S1
...

📌 HEAP_PAGE_ITEMS을 사용하기 위해서는 pageinspect Extension 설치가 필요합니다.

Step 4.

-- Session S1 (PID : 2028436)
-- Step 4.

vacuum freeze verbose A;
=>
aggressively vacuuming "public.A"
table "A": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages

select * from HEAP_PAGE_ITEMS(GET_RAW_PAGE('A', 0))
=>
lp|lp_off|lp_flags|lp_len|t_xmin|t_xmax|t_field3|t_ctid|t_infomask2|t_infomask|t_hoff|t_bits|t_oid|t_data|
--+------+--------+------+------+------+--------+------+-----------+----------+------+------+-----+------+
 1|  8160|       1|    28|1915  |1916  |       0|(0,2) |      16385|      1280|    24|      |     |      |
 2|  8128|       1|    28|1916  |0     |       0|(0,2) |      32769|     10496|    24|      |     |      |
 
select substring( 1280::bit(16), 7,1) as "10th",
		substring( 10496::bit(16), 7,1) as "10th"
=>
10th|10th|
----+----+
   0|   0|

• vacuum freeze verbose 명령어로 강제 Data Freezing 수행 후 Vacuum 로그 확인
• “0 removable, 2 nonremovable” 로그를 통해 “Update 이전 이미지” (Dead Tuple)와 최신 이미지 모두 Vacuum 대상에서 제외되었음을 확인
• t_infomask의 10번째 bit값 확인을 통해, 2개 Tuple 모두 Data Freezing 대상에서 제외되었음을 확인

💡Vacuum을 수행한 Session S1의 backend_xmin은 1914이므로, 해당 시점 이후에 입력 및 변경된 A 테이블의 Tuple들은 Vacuum 효과 없음 (대상 제외)

Step 5. ~ Step 6.

								-- Session S2 (PID : 2028435)							
								-- Step 5.

								-- Auto-commit Enable (Transaction End)


-- Session S1 (PID : 2028436)
-- Step 6.

vacuum freeze verbose A;
=>
aggressively vacuuming "public.A"
table "A": found 1 removable, 1 nonremovable row versions in 1 out of 1 pages

select * from HEAP_PAGE_ITEMS(GET_RAW_PAGE('A', 0))
 =>
lp|lp_off|lp_flags|lp_len|t_xmin|t_xmax|t_field3|t_ctid|t_infomask2|t_infomask|t_hoff|t_bits|t_oid|t_data|
--+------+--------+------+------+------+--------+------+-----------+----------+------+------+-----+------+
 1|     2|       2|     0|      |      |        |      |           |          |      |      |     |      |
 2|  8160|       1|    28|1916  |0     |       0|(0,2) |      32769|     11008|    24|      |     |      |

select substring( 11008::bit(16), 7,1) as "10th"
=>
10th|
----+
   1|

• Step 5. 에서 Transaction을 종료한 후 Session S1에서 다시 vacuum freeze verbose 명령어 수행
• “1 removable, 1 nonremovable” 로그를 통해, “Update 이전 이미지”(Dead Tuple)가 삭제가능한 상태임을 확인
• 남아있는 “Update 이후 이미지”는 t_infomask의 10번째 bit값이 1로 변경되어 Data Freezing까지 완료되었음을 확인

💡 Session S2의 Transaction 정상 종료로 인해 Session S1의 backend_xmin이 current XID와 동일해졌으며 (Current XID = backend_xmin = 1917) 이로 인해 Step 4. 와 달리 XID=1917 이전에 입력 및 변경된 Tuple들이 정상적으로 Vacuum 처리되었음을 확인

테스트 결과

위 테스트를 통해 전하고자 하는 이야기는 비단 Vacuum과 backend_xmin의 상관관계뿐만이 아닙니다.

사실 테스트과정에서 생성한 Table A는 Session S2가 시작한 Transaction 및 Table B와 어떤 관계도 없습니다. 하지만 Vacuum 작업만큼은 직접적인 영향을 주고받았음을 확인할 수 있었습니다.

이를 보다 범용적인 표현으로 정리하면, 오래된 backend_xid를 갖는 Session이 Database 내에 존재하는 것만으로도 모든 Table에 대한 Vacuum 작업에 영향을 준다는 무서운 결론에 도달합니다.

혹자에게 이러한 상황은 Vacuum의 완료속도가 빠르며 특별한 Error 로그도 남기지 않으므로 정상적인 상태로 오인될 수 있습니다. 하지만 해당 상태가 오래 지속되는 경우, 처리되지 못한 Dead Tuple에 의한 Table Bloating(부풀림) 현상이 심화될 것이며, 차일피일 미뤄둔 Vacuum 대상들은 결국은 풀어야 할 골칫거리가 될 것입니다.

 

“idle in transaction”의 발생

테스트 결과를 바탕으로, 우리는 backend_xid를 갖는 Session. 즉, idle in transaction 상태의 Session을 면밀히 파악해야 할 필요성을 공감했으리라 생각합니다.

해당 상태는 정상적인 Transaction과정에서도 충분히 발생 가능하기 때문에 비정상 상태와 비교해 낼 수 있는 분별력을 필요로 합니다.

일반적으로 idle in transaction 이 가장 많이 발생하는 패턴으로는 다음과 같은 3가지가 있습니다.

1. SQL Editing Tool을 통해 임의의 Transaction 작업 후 종료하지 않은 경우
2. Autocommit을 Disable 한 상태로 개발된 Application - Transaction 종료 생략
3. 외에 기타 Client의 비정상 종료로 인해 idle in transaction 상태로 남은 경우

이 중, 새로운 업무가 추가되거나 신규로 오픈한 시스템이 아닌, 그동안 문제없이 운영해 온 환경이라면 특히 1번의 경우를 주의해야 합니다.

적절한 DML 권한부여와 개발/테스트/QA 환경의 분리 등, 문제가 될 만한 요소들에 대한 적절한 통제가 필요합니다.

 

Monitoring & Prevention

이해를 위한 설명은 복잡했을지 모르나, 해당 상황을 모니터링하는 방법은 매우 간단합니다.

아래 Script와 같이 idle in transaction 상태로 변경된 후, 동일 상태를 오래 유지한 Session을 출력하면 되기 때문입니다. 물론 이 중 backend_xid 값을 갖는 Session을 실제 눈여겨봐야 하며, 함께 추출한 Application, User Name, IP 정보 등은 인적오류 여부를 판단하는데 참고할 수 있습니다.

select
    application_name ,
    client_addr ,
    usename,
    backend_xmin ,
    backend_xid ,
    state ,
    (NOW() - state_change) as timed,
    query
from
    PG_STAT_ACTIVITY
where backend_type ='client backend'
and state <> 'idle'
order by
    7 desc nulls last;

application_name                          |client_addr |usename |backend_xmin|backend_xid|state              |timed           |query                 |
------------------------------------------+------------+--------+------------+-----------+-------------------+----------------+----------------------+
DBeaver 21.3.3 - SQLEditor <Script-65.sql>|10.10.200.25|postgres|            |1937       |idle in transaction| 00:34:02.930011|SHOW search_path      |
DBeaver 21.3.3 - SQLEditor <Script-75.sql>|10.10.200.25|postgres|            |           |idle in transaction| 00:25:48.675633|SHOW search_path      |

 

반면, 모니터링 및 주기적인 관리가 어려운 경우라면 Timeout 설정을 통한 사전 예방도 가능합니다.

IDLE_IN_TRANSACTION_SESSION_TIMEOUT Parameter는 idle in transaction 상태가 유지되는 시간을 체크하여 조건 만족 시 Session을 강제로 종료시키는 기능입니다.

기본값은 0ms로, Timeout 기능이 Disable 되어 있으며 필요하다면 발생 가능한 최대 Transaction 시간을 참고하여 값을 설정하면 됩니다.

 

Conclusion

본 문서에서는 idle in transaction 상태와 backend_xid, backend_xmin의 의미, 그리고 Vacuum과의 상관관계에 대해 알아보았습니다.

마치 나비효과처럼, 특정 Session이 수행한 Transaction이 Database 전반에 영향을 끼친다는 사실은 Vacuum Series부터 내내 토로해온 불편함과 불합리함의 연장선상에 있습니다.

하지만 원리를 이해하고 정확한 정보만 추출/해석할 수 있다면 모니터링과 예방작업은 매우 쉬운 편이므로, 부디 본 문서를 읽은 독자분들은 이를 십분 활용할 수 있길 바라봅니다.

 

기획 및 글 | 기술기획팀