시스템 리소스와 데이터베이스 상태를 확인하기 위해 과거 고수 엔지니어가 작성한 유용한 스크립트들을 정리해두었다. 필요 시 참고할 수 있도록 기록한다.

CPU 사용률 확인

시스템의 CPU 사용 현황을 확인하려면 sar 명령어를 사용한다:

sar -u -f /var/log/sa/sa27

여기서 sa27은 해당 날짜(예: 27일)에 수집된 로그 파일이다. 실행 결과는 다음과 같다:

15시52분01초 CPU %user %nice %system %iowait %steal %idle
15시53분01초 all 0.32 0.00 0.69 0.00 0.00 98.99
15시54분01초 all 0.30 0.00 0.68 0.00 0.00 99.02
...
평균시간: all 0.33 0.00 0.61 0.03 0.00 99.02

데이터베이스 연결 상태 조회

현재 데이터베이스 세션의 상태별 개수를 확인하려면 다음 SQL 쿼리를 사용한다:

SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state;

느린 쿼리(Slow Query) 분석

전체 느린 쿼리 통계

특정 데이터베이스에서 발생한 쿼리 유형별 호출 횟수를 확인할 수 있다:

SELECT db_name, unique_query_id, COUNT(*)
FROM statement_history
GROUP BY db_name, unique_query_id;

예시 출력:

db_name | unique_query_id | count
--------+-----------------+-------
gzdz    | 2533841608      | 1
gzdz    | 2066747755      | 3
gzdz    | 1116594456      | 1

구체적인 느린 쿼리 내용 확인

실제 실행된 쿼리 문장을 확인하려면 다음을 수행한다:

SELECT unique_query_id, query
FROM statement_history;

결과 예시:

unique_query_id | query
----------------+---------------------------------------------------
2066747755      | INSERT INTO sys_stu.log_stu (...) VALUES (...) RETURNING id
2533841608      | INSERT INTO sys.log_change (...) VALUES (...) RETURNING id
...

실행 시간 기준 정렬

쿼리 호출 빈도순으로 정렬하여 자주 실행되는 쿼리를 파악할 수 있다:

SELECT db_name, unique_query_id, COUNT(*) 
FROM statement_history 
GROUP BY db_name, unique_query_id 
ORDER BY 3 DESC;

예시 출력:

db_name | unique_query_id | count
--------+-----------------+--------
gzdz    | 4233328942      | 1441
gzdz    | 2533841608      | 11
gzdz    | 2533841608      | 10
...

개별 쿼리 실행 소요 시간 확인

특정 쿼리의 실제 실행 시간을 확인하려면 다음처럼 한다:

SELECT query, finish_time - start_time AS exec_time
FROM statement_history
WHERE unique_query_id = '4233328942'
LIMIT 1;

출력 예시:

query                                                                 | exec_time
----------------------------------------------------------------------+------------------
UPDATE exam.exam_place AS e SET applied_capacity = ...               | 00:00:01.144499
WHERE ...

쿼리 플랜 분석

실행 계획을 살펴보려면 아래와 같이 한다:

SELECT query_plan
FROM statement_history
WHERE unique_query_id = '4233328942'
LIMIT 1;

예시 출력:

Datanode Name: dn_6001_6002_6003
Update on exam_place e (cost=0.00..2.48 rows=1 width=180)
-> Index Scan using idx_exam_place_exam_place_code on exam_place e
   Index Cond: ((exam_place_code)::text = '***'::text)
   Filter: (is_valid AND ((capacity - applied_capacity) > '***'))

락(Lock) 대기 상황 확인

v_locks_monitor 뷰 생성

먼저 특정 데이터베이스(gzdz)로 접속한다:

\c gzdz

그 후 아래의 복잡한 CTE 기반 뷰를 생성하여 락 상태를 모니터링한다:

CREATE VIEW v_locks_monitor AS
WITH
t_wait AS (
    SELECT a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
           a.objid,a.objsubid,a.pid,a.transactionid,
           b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
    FROM pg_locks a JOIN pg_stat_activity b ON a.pid = b.pid
    WHERE NOT a.granted
),
t_run AS (
    SELECT a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
           a.objid,a.objsubid,a.pid,a.transactionid,
           b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
    FROM pg_locks a JOIN pg_stat_activity b ON a.pid = b.pid
    WHERE a.granted
),
t_overlap AS (
    SELECT r.*
    FROM t_wait w JOIN t_run r ON (
        r.locktype IS NOT DISTINCT FROM w.locktype AND
        r.database IS NOT DISTINCT FROM w.database AND
        r.relation IS NOT DISTINCT FROM w.relation AND
        r.page IS NOT DISTINCT FROM w.page AND
        r.tuple IS NOT DISTINCT FROM w.tuple AND
        r.transactionid IS NOT DISTINCT FROM w.transactionid AND
        r.classid IS NOT DISTINCT FROM w.classid AND
        r.objid IS NOT DISTINCT FROM w.objid AND
        r.objsubid IS NOT DISTINCT FROM w.objsubid AND
        r.pid <> w.pid
    )
),
t_unionall AS (
    SELECT * FROM t_overlap
    UNION ALL
    SELECT * FROM t_wait
)
SELECT locktype, datname, relation::regclass, page, tuple, transactionid::TEXT, classid::regclass, objid, objsubid,
       STRING_AGG(
           'Pid: ' || COALESCE(pid::TEXT, 'NULL') || E'\n' ||
           'Lock_Granted: ' || COALESCE(granted::TEXT, 'NULL') || ', Mode: ' || COALESCE(mode, 'NULL') ||
           ', Username: ' || COALESCE(usename, 'NULL') || ', Database: ' || COALESCE(datname, 'NULL') ||
           ', Client_Addr: ' || COALESCE(client_addr::TEXT, 'NULL') || ', Client_Port: ' || COALESCE(client_port::TEXT, 'NULL') ||
           ', Application_Name: ' || COALESCE(application_name, 'NULL') || E'\n' ||
           ', Xact_Start: ' || COALESCE(xact_start::TEXT, 'NULL') || ', Query_Start: ' || COALESCE(query_start::TEXT, 'NULL') ||
           ', Xact_Elapse: ' || COALESCE((NOW() - xact_start)::TEXT, 'NULL') || E'\n--------', 
           CASE WHEN granted THEN '0' ELSE '1' END ORDER BY (
               CASE mode
                   WHEN 'INVALID' THEN 0
                   WHEN 'AccessShareLock' THEN 1
                   WHEN 'RowShareLock' THEN 2
                   WHEN 'RowExclusiveLock' THEN 3
                   WHEN 'ShareUpdateExclusiveLock' THEN 4
                   WHEN 'ShareLock' THEN 5
                   WHEN 'ShareRowExclusiveLock' THEN 6
                   WHEN 'ExclusiveLock' THEN 7
                   WHEN 'AccessExclusiveLock' THEN 8
                   ELSE 0
               END DESC
           )
       ) AS lock_conflict
FROM t_unionall
GROUP BY locktype, datname, relation, page, tuple, transactionid, classid, objid, objsubid;

락 대기 상황 조회

생성된 뷰를 통해 현재 락 충돌 상황을 실시간으로 확인할 수 있다:

\c gzdz
SELECT * FROM v_locks_monitor;