개요

본 문서는 TmaxOpenSQL 사용 시 지연 쿼리 대응을 위한 모니터링 SQL 입니다.

테스트 환경

• DB : postgresql 14, postgresql 15
• OS : RedHat Linux

방법

Total Session Count

select 
   state,
   client_addr,
   application_name,
   count(*) 
from pg_stat_activity 
group by state, client_addr, application_name 
order by count(*) desc;

- stat : 상태는 3가지 형태로 조회됩니다. (active / idle / idle in transaction)

- client_addr : 해당 조회 결과를 통해 DB에 붙은 서비스를 추적할 수 있습니다.

Active Session Long SQL

1.pg_stat_activity를 통한 Active Session에 대한 LONG SQL 조회

select 
  pid,
  application_name
  current_timestamp - query_start AS runtime,
  datname, usename,
  substr(query,1,30) as query
from pg_stat_activity
where state = 'active'
and current_timestamp - query_start > interval '5 seconds'
order by 1 desc;

- current_timestamp - query_start > interval 'n seconds'  'n' 값을 통해 시간을 조절합니다.

2. pg_stat_statements를 통한 SQL 누적 통계를 활용한 LONG SQL 조회

select 
  query,
  calls,
  mean_exec_time,
  max_exec_time
from pg_stat_statements
where mean_exec_time > 5000 --5초
order by mean_exec_time desc;

- pg_stat_statements는 실행된 SQL의 누적 통계를 제공하고 특정 기간 기준이 아닌 누적 데이터의 평균 실행 시간 기준 분석 시 유용하게 사용합니다.

- calls 호출 건수가 많고 mean_exec_time이 큰 SQL은 튜닝 대상이 될 수 있습니다.

Blocking Session

select 
  lock1.pid as waiting_pid,
  stat1.usename as waiting_user,
  stat1.wait_event_type as waited_type,
  stat1.query as locked_statement,
  stat1.state as state,
  lock2.pid as blocking_pid,
  stat2.usename as blocking_user,
  stat2.query as blocking_statement,
  stat2.state as blocking_state,
  now() - stat1.query_start as blocking_duration
from pg_catalog.pg_locks lock1 
JOIN pg_catalog.pg_stat_activity stat1 
ON lock1.pid = stat1.pid
JOIN pg_catalog.pg_locks lock2 
ON
(
  lock1.locktype,
  lock1.database,
  lock1.relation,
  lock1.page,
  lock1.tuple,
  lock1.virtualxid,
  lock1.transactionid,
  lock1.classid,
  lock1.objid,
  lock1.objsubid
) IS NOT DISTINCT
from 
(
  lock2.locktype,
  lock2.database,
  lock2.relation,
  lock2.page,
  lock2.tuple,
  lock2.virtualxid,
  lock2.transactionid,
  lock2.classid,
  lock2.objid,
  lock2.objsubid
)
JOIN pg_catalog.pg_stat_activity stat2 ON lock2.pid = stat2.pid
where 
NOT lock1.granted and lock2.granted;   

- pg_locks.granted = false인 session은 lock 대기 상태이며, 동일 lock resource를 granted = true로 점유 중인 Session이 Blocking Session 파악이 가능합니다.

컬럼 설명