Elapsed Time = CPU Time + Wait Time + ...
Database의 성능을 평가하는 중요한 지표 중 하나인, Elapsed Time은 사용자가 쿼리를 요청하여 결과를 받기까지 소요되는 총시간입니다. CPU Time은 쿼리를 처리하는 데 걸린 실제 시간, Wait Time은 요청된 작업이 비활성 상태로 대기열에서 처리되기를 기다리는 시간을 의미합니다.
이 중, Wait Time은 다양한 원인으로 발생하는 Wait Event(대기 현상)로 인해 길어지며, 프로세스가 원하는 작업을 처리하는데 걸리는 시간을 길어지게 합니다. 이러한 Wait Time을 해결하기 위하여 다양한 DBMS에서는 여러 가지 Wait Event를 그들만의 방식으로 카테고리화하여 관리하고 있습니다.
우리는 Wait Event를 주제로 하여 PostgreSQL에서는 Wait Event에 대하여 어떻게 분류하고 관리하는지 알아보고, 이를 바탕으로 Database의 성능을 향상하기 위한 방법을 찾아보고자 합니다.
PostgreSQL Wait Event
PostgreSQL는 Wait Event와 관련된 성능 문제를 다룰 때, 도움이 될 수 있는 정보를 다양한 방식으로 제공하고 있습니다. 최초에 제공했던 정보는 비교적 단순했지만, 여러 버전들을 거쳐 현재는 Wait Event에 대한 상세한 수준의 정보를 제공합니다.
1. Wait Event의 시작
PostgreSQL ver.9.6부터 Wait Event를 다루는 방식이 정립되었습니다. 이전에는 별도로 명명하거나 분류하지 않던 Wait Event들을 새롭게 명명하였으며, 각 Wait Event들을 Wait Event Type별로 분류했습니다.
PostgreSQL ver.9.6에서 69 종류의 Wait Event가 새롭게 명명되었고, 이를 아래와 같이 분류하였습니다.
| Wait Event Type | 설명 |
LWLockNamed |
Backend Process가 특정 이름을 가진 Lightweight Lock을 대기하고 있을 때의 Wait 유형 이 Wait Event Type을 발생시키는 Lightweight Lock은 Shared Memory 내의 특정 데이터 구조를 보호함 |
LWLockTranche |
Backend Process가 Lightweight Lock과 관련된 Lock 그룹을 대기하고 있을 때의 Wait 유형 ( LWLockNamed 에 속하는 Lock과 달리 특정 이름으로 명명되지 않은 Lock 그룹을 의미함) |
Lock |
Backend Process가 Heavyweight Lock을 대기하고 있을 때의 Wait 유형 이 Wait Event Type을 발생시키는 Lock Manager Lock 또는 간단하게 Lock 이라고도 불리는, Heavyweight Lock은 테이블과 같은 SQL-Level에서 바로 확인할 수 있는 Object들을 주요 대상으로 하여 보호함 Relation Extension과 같은 내부 작업에 대한 상호 배제(Mutual Exclusion) 상황을 보장하기 위해 사용되기도 함 |
BufferPin |
서버 프로세스가 다른 어떠한 프로세스의 개입 없이 Data Buffer에 접근하고자 할 때 발생하는 Wait 유형 만약 다른 프로세스가 해당 Buffer로부터 데이터를 읽고 Open Cursor를 가지고 있다면, BufferPin Wait은 길어질 수 있음 |
PostgreSQL에서는 위와 같이 크게 3가지 종류의 Lock을 중요한 개념으로 다루어 Wait Event Type 분류 기준으로 사용했습니다. 이는 프로세스가 작업을 처리하기 위해 사용하는 리소스는 Lock을 통해 관리되므로, 대부분의 Wait Event에 대한 원인을 관련 Lock에서 찾을 수 있기 때문입니다.
📢 Wait Event Type 분류 기준으로 사용한 3가지 Lock은 Lightweight Lock(LWLockNamed, LWLockTranche), Heavyweight Lock(Lock), Buffer Pin입니다.
📢 Wait Event Type과 Wait Event가 새롭게 정립됨에 따라 PostgreSQL에서 제공하는 시스템 뷰 중 하나인
pg_stat_activity는 다음과 같이 관련 컬럼이 변경되었습니다.
[~ver.9.5]
컬럼명 설명 waiting Backend Process가 현재 Lock을 대기하고 있는 경우, True [ver.9.6~]
컬럼명 설명 wait_event_type Backend Process가 대기하고 있는 Wait 유형 존재하지 않는다면 NULL 값을 가짐 wait_event Backend Process가 현재 대기 중이라면, 해당하는 Wait Event Name 표시
존재하지 않는다면 NULL 값을 가짐
2. Wait Event (~ver.16)
Wait Event 개념이 처음 도입 된 ver.9.6 이후, Wait Event들을 점진적으로 추가 명명하였고 Wait Event Type 역시 세분화되어 ver.16에서는 아래와 같이 규정되었습니다.
| Wait Event Type | 설명 |
Activity |
서버 프로세스가 유휴(Idle) 상태에 있으며, 이 Wait Event Type은 하나의 프로세스가 그와 관련된 Processing Loop 내부 활동을 기다리고 있음을 지칭함 |
BufferPin |
서버 프로세스가 어떠한 Data Buffer에 대해 Exclusive 모드로 접근하고자 할 때, 해당 프로세스에 생길 수 있는 Wait 유형 만약 다른 프로세스가 해당 Buffer에서 데이터를 읽고 Open Cursor를 가지고 있다면, BufferPin Wait은 길어질 수 있음 |
Client |
서버 프로세스가 사용자 애플리케이션에 연결된 소켓의 활동을 기다릴 때 생길 수 있는 Wait 유형 이 Wait Type에서 서버 프로세스는 내부 프로세스와는 무관한 작업이 수행되기를 기다림. |
Extension |
서버 프로세스가 Extension 모듈 실행에 따른 필요 조건이 설정되는 것을 기다릴 때 발생하는 Wait 유형 |
IO |
서버 프로세스가 I/O 작업이 완료되기를 기다릴 때 생길 수 있는 Wait 유형 |
IPC |
서버 프로세스가 다른 서버 프로세스들과의 상호 작용을 기다릴 때 생길 수 있는 Wait 유형 |
Lock |
서버 프로세스가 Heavyweight Lock을 요청하고 기다릴 때 발생하는 Wait 유형 Lock Manager Lock 또는 간단하게 Lock 이라고도 불리는 Heavyweight Lock은 테이블과 같은 SQL-Level에서 바로 확인할 수 있는 Object들을 주요 대상으로 하여 보호함 Relation Extension과 같은 내부 작업에 대한 상호 배제(Mutual Exclusion) 상황을 보장하기 위해 사용되기도 함 |
LWLock |
서버 프로세스가 Lightweight Lock을 기다릴 때 발생하는 Wait 유형 대부분의 Lightweight Lock은 Shared Memory 내부의 특정 데이터 구조를 보호함 일부는 특정 이름을 가지고 있는 Lightweight Lock, 그 외에는 비슷한 목적을 가진 Lock 그룹의 일부분 |
Timeout |
서버 프로세스가 Timeout이 만료되기를 기다릴 때 발생하는 Wait 유형 |
📢 Wait Event 분류가 처음 생긴 ver.9.6과 비교하여 주요 변경 사항은 아래와 같습니다.
wait_event 가 69 → 230으로 증가, 더 세분화됨
- 기존의 LWLock 관련 wait_event_type 명칭 변경 (
LWLockNamed,LWLockTranche→LWLock)- Client, IO, Background Process 등 내부 동작과 관련된 wait_event_type 추가
- 일부 wait_event 명칭이 변경되거나 해당 wait_event가 속한 wait_event_type이 변경됨
PostgreSQL에서는 이처럼 Wait Event에 대해서 새롭게 정립한 후, 관련 정보를 확인할 수 있도록 시스템 뷰도 개선하였습니다.
특히 pg_stat_activity는 PostgreSQL ver.9.6 이전에는 Lock을 대기하고 있는 상태인지 확인할 수 있는 waiting 컬럼 만을 제공하였다면, 이후 버전에서는 wait_event_type과 wait_event 컬럼을 추가하여 어떠한 유형의 Wait 인지 명확하게 확인할 수 있게 하였습니다.
이어서 PostgreSQL에서 제공하는 시스템 뷰를 활용하여 Wait Event를 추적 및 모니터링하는 방법을 알아보겠습니다.
3. Wait Event Monitoring
Wait Event에 대한 정보를 확인할 수 있는 시스템 뷰로는 대표적으로 현재 Wait Event 상태와 함께 프로세스 정보를 확인할 수 있는 pg_stat_activity, 그리고 wait_event_type이 Lock인 경우에 해당 Lock에 대한 정보를 확인할 수 있어서 특히 유용한 pg_locks가 있습니다.
📢 PostgreSQL의 각 Database에는 시스템 테이블과 모든 내장 데이터 유형, 함수 및 연산자가 포함된 pg_catalog 스키마가 포함되어 있습니다. pg_stat_activity와 pg_locks도 pg_catalog 스키마에 존재합니다.
1. pg_stat_activity
pg_stat_activity는 프로세스 수준의 다양한 정보를 제공하는 시스템 뷰입니다. 따라서 pg_locks 대비 프로세스에 관한 보다 다양한 정보를 포함하고 있으며, 조회 결과는 쿼리 한 시점의 상태(Snapshot)를 나타냅니다.
이러한 특징을 갖는 pg_stat_activity는 반복적이고 주기적인 조회를 통해 프로세스 수준의 상태 변화를 모니터링 가능한데, 특히, Wait Event와 관련하여 유용하게 활용될 수 있는 컬럼은 PostgreSQL ver.9.6부터 추가된 wait_event_type과 wait_event 컬럼입니다. 이 컬럼들을 pg_stat_activity의 다른 컬럼들과 조합하여 Wait Event가 발생한 원인에 대해 추적해볼 수 있습니다.
📢 pg_stat_activity 컬럼 상세 정보 ([PostgreSQL: Documentation: 16: 28.2. The Cumulative Statistics System](https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW))
pg_stat_activity에서 프로세스의 현재 상태를 식별하는 state와 wait 상태를 식별하는 wait_event 컬럼은 서로 독립적인 관계입니다. 따라서 어느 하나의 값으로 나머지를 추정할 수 없으며, 다만 wait_event 컬럼 값의 존재 유무에 따라 대기 여부를 알 수 있습니다.
📢 state 컬럼에 표시되는 값
표시 의미 activeBackend Process가 쿼리를 수행 중인 상태 idleBackend Process가 Client의 새로운 명령을 기다리고 있는 상태 idle in transactionBackend Process가 트랜잭션 내에 있으나, 현재 쿼리를 수행하고 있지 않은 상태 idle in transaction(aborted)idle in transaction과 유사한 상태이나, 트랜잭션 내 쿼리 구문 중 하나가 오류를 발생시켰을 때의 상태fastpath function callBackend Process가 fast-path Function을 실행 중인 상태 disabled해당 Backend Process가 ‘track_activities’*가 불가능하게 설정된 상태 (*track_activities : 수행된 명령에 대한 정보 수집 여부를 결정하는 파라미터. Default : ON)
2. pg_locks
pg_locks는 데이터베이스 서버 내 현재 활동 중인 프로세스가 소유 및 대기 중인 Lock 정보를 제공하는 시스템 뷰입니다.
pg_locks는 Lock의 대상이 되는 Object와 요청한 모드, 관련된 프로세스에 대해 하나의 Row로 정보를 표시합니다. Lock의 대상이 되는 Object의 type(locktype)은 relation, page, tuple, transactionid 등 다양합니다. 또한 하나의 Object에 대해서 여러 프로세스가 Lock을 보유하거나 기다릴 수 있으므로, 동일한 Object가 여러 개의 Row로 나타날 수 있습니다.
pg_locks를 이용해서 Lock이 설정된 Object를 확인할 수 있으며, Lock이 설정된 Object에 대해서 어떤 프로세스가 어떠한 모드로 Lock을 보유하고 있는지 그리고 해당 Lock을 다른 프로세스가 기다리고 있는지도 확인할 수 있습니다.
📢 pg_locks 컬럼 상세 정보 ([PostgreSQL: Documentation: 16: 54.12. pg_locks](https://www.postgresql.org/docs/16/view-pg-locks.html))
특히 pg_locks는 조회하고자 하는 프로세스의 wait_event_type이 Lock일 때, 아래와 같은 조건을 사용하면 다른 시스템 뷰와 함께 조회하여 추가 정보를 얻을 수 있습니다.
- pg_locks.pid=pg_stat_activity.pid : Lock을 보유하거나 대기하고 있는 프로세스에 대한 세부 정보
- pg_locks.relation=pg_class.oid : Lock이 설정된 Object에 대한 세부 정보
3. pg_locks & pg_stat_activity 사용 예
pg_locks와 pg_stat_activity를 통해 현재 Lock을 소유한 프로세스(Holder)와 대기하고 있는 프로세스(Waiter) 정보를 관련 쿼리와 함께 조회해 볼 수 있습니다.
SELECT current_timestamp AS db_time,
waiter_pid,
w_info.usename AS waiter_user ,
w_info.query AS waiter_query ,
w_info.query_start AS waiter_query_start ,
case
when EXTRACT(EPOCH from current_timestamp - w_info.query_start ) < 0 then 0
else EXTRACT(EPOCH from current_timestamp - w_info.query_start )
end as waiter_elapsed_time,
holder_pid ,
h_info.usename AS holder_user ,
h_info.query AS holder_query ,
h_info.query_start AS holder_query_start,
case
when EXTRACT(EPOCH from current_timestamp - h_info.query_start ) < 0 then 0
else EXTRACT(EPOCH from current_timestamp - h_info.query_start )
end as holder_elapsed_time
FROM (
SELECT snaptime,
locktype,
waiter_pid,
w_cnt,
h_cnt ,
CASE
WHEN h_cnt=Max(h_cnt) OVER(partition BY waiter_pid) THEN holder_pid
END AS holder_pid
FROM (
SELECT current_timestamp AS snaptime,
blocked_locks.locktype AS locktype,
blocked_locks.pid AS waiter_pid,
count(*) over(partition BY blocked_locks.pid) AS w_cnt,
count(*) over(partition BY blocking_locks.pid) h_cnt,
blocking_locks.pid AS holder_pid
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT
FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT
FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT
FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT
FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT
FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT
FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT
FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT
FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT
FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
WHERE NOT blocked_locks.granted ) t ) t2
JOIN pg_catalog.pg_stat_activity w_info
ON w_info.pid = t2.waiter_pid
JOIN pg_catalog.pg_stat_activity h_info
ON h_info.pid = t2.holder_pid
WHERE holder_pid IS NOT null;
db_time |waiter_pid|waiter_user|waiter_query |waiter_query_start |waiter_elapsed_time|holder_pid|holder_user|holder_query |holder_query_start |holder_elapsed_time|
-----------------------------+----------+-----------+----------------------------------------------------+-----------------------------+-------------------+----------+-----------+----------------------------------------------------+-----------------------------+-------------------+
2024-04-30 12:08:49.078 +0900| 2914359|postgresdt |update lock_test set c2 = 'row_update' where c1 = 1;|2024-04-30 12:07:55.777 +0900| 53.301024| 2914369|postgresdt |update lock_test set c2 = 'row_update' where c1 = 1;|2024-04-30 12:07:50.784 +0900| 58.294596|
2024-04-30 12:08:49.078 +0900| 2914369|postgresdt |update lock_test set c2 = 'row_update' where c1 = 1;|2024-04-30 12:07:50.784 +0900| 58.294596| 2914377|postgresdt |update lock_test set c2 = 'row_update' where c1 = 1;|2024-04-30 12:07:45.711 +0900| 63.366759|
더 나아가, 아래와 같이 pg_locks와 pg_class에 대한 조회를 통해 프로세스가 소유 및 대기하고 있는 Lock에 대한 상세 정보를 확인할 수 있습니다.
select l.locktype, t.relname, pid, mode, granted, waitstart
from pg_locks l
left join pg_class t on l.relation = t.oid
where l.pid in (2914359,2914369,2914377)
order by pid, locktype, relname;
locktype |relname |pid |mode |granted|waitstart |
-------------+--------------+-------+----------------+-------+-----------------------------+
relation |lock_test |2914359|RowExclusiveLock|true | |
relation |lock_test_pkey|2914359|RowExclusiveLock|true | |
transactionid| |2914359|ExclusiveLock |true | |
tuple |lock_test |2914359|ExclusiveLock |false |2024-04-30 12:07:55.783 +0900|
virtualxid | |2914359|ExclusiveLock |true | |
relation |lock_test |2914369|RowExclusiveLock|true | |
relation |lock_test_pkey|2914369|RowExclusiveLock|true | |
transactionid| |2914369|ShareLock |false |2024-04-30 12:07:50.790 +0900|
transactionid| |2914369|ExclusiveLock |true | |
tuple |lock_test |2914369|ExclusiveLock |true | |
virtualxid | |2914369|ExclusiveLock |true | |
relation |lock_test |2914377|RowExclusiveLock|true | |
relation |lock_test_pkey|2914377|RowExclusiveLock|true | |
transactionid| |2914377|ExclusiveLock |true | |
virtualxid | |2914377|ExclusiveLock |true | |
위의 예시들처럼, PostgreSQL에서 제공하는 pg_locks와 pg_stat_activity 같은 시스템 뷰를 활용하면 사용자는 Wait Event에 대한 상세 정보는 물론, 문제 유발 SQL과 대상 정보까지 확인 가능합니다.
이처럼 PostgreSQL에서는 ver.9.6 이후부터 Wait Event에 대해 새롭게 개념을 정립하고, 관련된 모니터링 및 원인 추적을 위한 기능을 제공하고 있다는 것을 확인하였습니다. 그리고 버전이 증가함에 따라 종류가 증가하고 범주가 추가되는 등 계속해서 발전하고 있습니다.
이번 글은 Wait Event Monitoring을 마지막으로 마무리하며, 다음으로 Wait Event의 주요한 원인이 되는 Lock에 대해서, PostgreSQL에서의 Lock을 주제로 하여 자세히 다루도록 하겠습니다.
글 | 플랫폼기술연구팀
'엑셈 경쟁력 > DB 인사이드' 카테고리의 다른 글
| DB 인사이드 | PWI - LOCK > Row-level Lock(1) (0) | 2024.08.05 |
|---|---|
| DB 인사이드 | PWI - LOCK > Relation-level Lock (0) | 2024.06.11 |
| DB 인사이드 | PostgreSQL Extension - PG_HINT_PLAN (0) | 2024.04.18 |
| DB 인사이드 | PostgreSQL Extension - Introduction (0) | 2024.04.18 |
| DB 인사이드 | PostgreSQL 16 Release - New Feature (3) (2) | 2023.12.27 |
댓글