[Postgresql] 성능 정보 수집 관련 쿼리

1. PostgreSQL 기본(설정)정보

SELECT TO_CHAR(NOW(),'YYYY-MM-DD HH24:MI:SS') AS chk_date
, *
FROM  pg_catalog.pg_settings;

2. 데이터베이스 기본정보 테이블

SELECT TO_CHAR(NOW(),'YYYY-MM-DD HH24:MI:SS') AS chk_date
, 1 AS inst_no  -- idc.tbl_db_inst_list 테이블의 inst_no 사용
, A.oid, A.datname, A.encoding, A.datcollate, A.datctype, A.datistemplate, A.datallowconn, A.datconnlimit
, B.spcname
FROM  pg_catalog.pg_database AS A
INNER JOIN pg_catalog.pg_tablespace AS B ON A.dattablespace = B.oid;

3. 데이터베이스 사이즈 정보

SELECT TO_CHAR(NOW(),'YYYY-MM-DD HH24:MI:SS') AS chk_date
, 1 AS inst_no  -- idc.tbl_db_inst_list 테이블의 inst_no 사용
, oid, datname
, pg_database_size(datname) AS dat_size -- byte 단위
FROM  pg_catalog.pg_database;

5. DB별 트랜젝션 및 block read 통계

SELECT TO_CHAR(NOW(),'YYYY-MM-DD HH24:MI:SS') AS chk_date
, 1 AS inst_no  -- idc.tbl_db_inst_list 테이블의 inst_no 사용
, datid AS dbid
, datname AS dbname
, round(cast(tup_inserted/EXTRACT(EPOCH FROM now() - stats_reset)*3600 as NUMERIC),2) AS inserted_row_per_hour
, round(cast(tup_updated/EXTRACT(EPOCH FROM now() - stats_reset)*3600 as NUMERIC),2) AS updated_row_per_hour
, round(cast(tup_deleted/EXTRACT(EPOCH FROM now() - stats_reset)*3600 as NUMERIC),2) AS deleted_row_per_hour
, round(cast(blks_read/EXTRACT(EPOCH FROM now() - stats_reset) as NUMERIC),2) AS blks_read_per_hour
FROM  pg_catalog.pg_stat_database
WHERE  datname is not NULL;

6. WAL 크기

SELECT TO_CHAR(NOW(),'YYYY-MM-DD HH24:MI:SS') AS chk_date
, 1 AS inst_no  -- idc.tbl_db_inst_list 테이블의 inst_no 사용
, A.switch_time
, A.log_cnt
FROM  (
SELECT  substr(to_char(modification,'YYYY/MM/DD HH24:MI:SS'),1,13) as switch_time
, COUNT(*) as log_cnt
, (ROW_NUMBER() OVER()) AS ROWNUM
FROM  pg_ls_waldir()
WHERE  1 = 1
GROUP BY switch_time
ORDER BY switch_time DESC
) AS A;

7. 통계정보 현황

SELECT TO_CHAR(NOW(),'YYYY-MM-DD HH24:MI:SS') AS chk_date
, 1 AS inst_no  -- idc.tbl_db_inst_list 테이블의 inst_no 사용
, a.schemaname as schema_name
, TO_CHAR(max(a.last_analyze),'YYYY/MM/DD HH24:MI:SS') as last_analyzed
-- status : 통계수집완료=1, 통계수집필요=0, 기준: 마지막 통계수집 후 7일 경과
, CASE WHEN EXTRACT(EPOCH FROM now() - max(a.last_analyze))::int > 604800 THEN '0' 
 WHEN max(a.last_analyze) is null THEN '0'
 ELSE 1
END as status
FROM  (
SELECT schemaname, 
CASE WHEN last_analyze is not null and last_autoanalyze is null THEN last_analyze
 WHEN last_analyze is null and last_autoanalyze is not null THEN last_autoanalyze
 WHEN last_analyze is not null and last_autoanalyze is not null and last_analyze > last_autoanalyze THEN last_analyze
 WHEN last_analyze is not null and last_autoanalyze is not null and last_analyze < last_autoanalyze THEN last_autoanalyze
 WHEN last_analyze is null and last_autoanalyze is null THEN null
END as last_analyze
FROM  pg_stat_all_tables
WHERE 1=1
AND  schemaname not in ('pg_catalog','pg_toast')
) a
GROUP BY a.schemaname
;