[PostgreSQL] 기본 모니터링 방법(1)

 

Postgresql 기본 모니터링을 위해선 다음과 같은 view를 조회한다.

 

pg_catalog : tables, views, functions, indexes, fdw, trigger, constraints, rules, users, groups 등등 정보 저장

pg_attribute : 테이블 및 다른 pg_class object columns에 대한 정보 저장

pg_index : 인덱스에 대한 정보 저장

pg_depend : object dependent에 대한 정보

pg_rewrite : 테이블 및 view 에 대한 rules 정보

pg_constraint : 테이블에 constraint 정보

pg_stat_statements : db에서 수행된 쿼리에 대한 통계 정보

pg_buffercache : cache에 대한 deeper 한 정보

information_schema : 메타 정보를 담고 있는 view

 

--1. 접속된 사용자 확인

SELECT datname, usename, client_addr, client_port, application_name 
 FROM pg_stat_activity;

5초 간격으로 위에 SQL를 수행 => \watch 5

 

--2. Active 세션 확인

SELECT datname, usename, state, query FROM pg_stat_activity WHERE state = 'active';

* 세션에 대한 detail한 분석은 pg_stat_statement 설치 (real-time performance for query)

 

--3. long 실행 쿼리 확인

SELECT
    current_timestamp - query_start AS runtime,
    datname, usename, query
FROM pg_stat_activity 
WHERE state = 'active' ORDER BY 1 DESC;

 

--1분 이상 실행되는 쿼리 확인

SELECT
    current_timestamp - query_start AS runtime,
    datname, usename, query
FROM pg_stat_activity
WHERE state = 'active'
      AND current_timestamp - query_start > '1 min'
ORDER BY 1 DESC;

--4. query를 process title에 보이도록 설정 (postgresql.conf)

update_process_title = on

 

--5. wait 또는 blocking 되는 세션 확인

SELECT datname, usename, query FROM pg_stat_activity WHERE waiting = true;

 

6. query block user 찾기

SELECT
    w.query AS waiting_query,
    w.pid AS waiting_pid,
    w.usename AS waiting_user,
    l.query AS locking_query,
    l.pid AS locking_pid,
    l.usename AS locking_user,
    t.schemaname || '.' || t.relname AS tablename
 FROM pg_stat_activity w
       JOIN pg_locks l1 ON w.pid = l1.pid AND NOT l1.granted
       JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted
       JOIN pg_stat_activity l ON l2.pid = l.pid
       JOIN pg_stat_user_tables t ON l1.relation = t.relid
 WHERE w.waiting;

 

7. kill session

  postgresql에 세션 kill 아래 3가지 순서로 차례로 진행하면 됨 (1이 안되면 2, 2 안되면 3)

  1) pg_cancel_backend(pid)          -- current query kill and not disconnect

  2) pg_terminate_backend(pid)        -- connection disconnect. 

  3) kill -9 process

 

  10분동안 유휴 상태인 세션 kill

  SELECT pg_terminate_backend(pid) FROM pg_stat_activity

  WHERE state = 'idle in transaction' AND current_timestamp - query_start > '10 min';

 

 현재 세션을 제외한 모든 세션 kill.

  SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();

 

 

작업을 위해 해당 데이터베이스 접속을 막음.

UPDATE pg_database set datallowconn = 'false' WHERE datname = 'database to drop';

 

8. long 쿼리 수행시 timeout 설정

set statement_timeout to '10 s'

select ~~ from ~ : long query..

 

 

9.  2pc 사용시 lock 확인 

SELECT t.schemaname || '.' || t.relname AS tablename,
       l.pid, l.granted
       FROM pg_locks l JOIN pg_stat_user_tables t
       ON l.relation = t.relid;

아래 결과중 pid가 표시 되지 않는 부분

tablename |  pid    | granted

-----------+-------+---------

    test1    |           | t

    test1    | 11111    | f

 

10. 테이블 사용량 확인

초기 사용량 저장

CREATE TEMPORARY TABLE tmp1_201512 AS  SELECT * FROM pg_stat_user_tables;

현시점 사용량이 비교하여 수치가 달라진 테이블 출력 

SELECT * FROM pg_stat_user_tables n
  JOIN tmp1_201512 t
    ON n.relid=t.relid
   AND (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del)
    <> (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);

 

기존 통계 데이터 초기화

SELECT pg_stat_reset();

 

테이블 정보를 snapshot 생성 (cron 또는 pg_agent, bgworker 활용)

INSERT INTO stat1_201512
SELECT current_timestamp AS snaptime, * FROM pg_stat_user_tables;

 

11. 테이블 마지막 사용 일자 확인

-- 아래 function 수행

do $$
         PERFORM table_file_access_info('test1', 'test');
$$  -- output이 없음

 

-- 일자 확인 function 생성

CREATE OR REPLACE FUNCTION table_file_access_info(
   IN schemaname text, IN tablename text,
   OUT last_access timestamp with time zone,
   OUT last_change timestamp with time zone
   ) LANGUAGE plpgsql AS $func$
DECLARE
    tabledir text;
    filenode text;
BEGIN
    SELECT regexp_replace(
        current_setting('data_directory') || '/' || pg_relation_filepath(c.oid),
          pg_relation_filenode(c.oid) || '$', ''),
        pg_relation_filenode(c.oid)
      INTO tabledir, filenode
      FROM pg_class c
      JOIN pg_namespace ns
        ON c.relnamespace = ns.oid
       AND c.relname = tablename
       AND ns.nspname = schemaname;
       
    RAISE NOTICE 'tabledir: % - filenode: %', tabledir, filenode;
    -- find latest access and modification times over all segments

    SELECT max((pg_stat_file(tabledir || filename)).access),
           max((pg_stat_file(tabledir || filename)).modification)
      INTO last_access, last_change
      FROM pg_ls_dir(tabledir) AS filename
      -- only use files matching <basefilename>[.segmentnumber]
     WHERE filename ~ ('^' || filenode || '([.]?[0-9]+)?$');

END;

$func$;

 

12. 템프 데이터 사용률 확인

1) 별도 temporary 테이블스페이스 사용 확인

 SELECT current_setting('temp_tablespaces');  -- 결과가 없으면 default tablespace 사용



2) 1의 결과가 나오면 다음 SQL를 사용하여 사용량 확인

WITH temporary_tablespaces AS (
  SELECT unnest(string_to_array(
    current_setting('temp_tablespaces'), ',')
  ) AS temp_tablespace
)   
SELECT tt.temp_tablespace,
  pg_tablespace_location(t.oid) AS location,
  -- t.spclocation AS location, -- for 9.0 and 9.1 users
  pg_tablespace_size(t.oid) AS size
FROM temporary_tablespaces tt
JOIN pg_tablespace t ON t.spcname = tt.temp_tablespace
  ORDER BY 1;

3) 1의 결과가 안나오면 다음과 같은 방법으로 사용량 확인

  SELECT current_setting('data_directory') || '$PG_HOME/base/pgsql_tmp'
  SELECT datname, temp_files, temp_bytes, stats_reset
  FROM pg_stat_database;


4) 기타 방법

- 실제 파일이 있는 디렉토리를 du -sk 로 확인

- pg_tablespace_location(oid), pg_tablespace_size(oid), pg_tablespace_size(name)함수로 확인