Overview

This document provides monitoring SQL for responding to delayed queries when using TmaxOpenSQL.

Test Environment

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

Method

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;

- state: The status is displayed in three forms (active / idle / idle in transaction).

- client_addr: Through this query result, you can trace the services connected to the DB.

Active Session Long SQL

1. Querying LONG SQL for Active Sessions via pg_stat_activity

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;

- Adjust the time using the 'n' value in current_timestamp - query_start > interval 'n seconds'.

2. Querying LONG SQL using accumulated statistics from pg_stat_statements

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

- pg_stat_statements provides cumulative statistics of executed SQL and is useful for analysis based on average execution time of accumulated data rather than a specific period.

- SQL with a high number of calls and large mean_exec_time can be a candidate for tuning.

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;   

- Sessions where pg_locks.granted = false are in lock waiting state, and sessions holding the same lock resource with granted = true can be identified as Blocking Sessions.

Column Description