기본 모니터링 쿼리

 

카탈로그 정보

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) shared buffers (shared_buffers, default 32MB) : 전체 메모리에 25% 설정
  2) working memory (work_mem, default 1MB) : work_mem * 전체 커넥션수
  3) checkpoint_segments : 값을 높이면 쓰기 성능이 좋아지고, 낮추면 복구 시간이 빨라짐.
  4) effective_cache_size : disk caching, 전체에 50 ~ 70%
  5) random_page_cost (default 4.0) : ssd, san 3, 1.5 ~ 2.5 

 

세션관련

접속된 사용자 확인

SELECT datname, usename, client_addr, client_port, application_name FROM pg_stat_activity;
5초 간격으로 위에 SQL를 수행 => \watch 5

 

Active 세션 확인
SELECT datname, usename, state, query FROM pg_stat_activity WHERE state = 'active';
* 세션에 대한 detail한 분석은 pg_stat_statement 설치 (real-time performance for query)

 

wait 또는 blocking 되는 세션 확인
SELECT datname, usename, query FROM pg_stat_activity WHERE waiting = true;

 

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();
 

Query 관련

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;

 

query를 process title에 보이도록 설정 (postgresql.conf)
  update_process_title = on

Temp 관련

별도 temporary 테이블스페이스 사용 확인
 SELECT current_setting('temp_tablespaces');  -- 결과가 없으면 default tablespace 사용
 

위 쿼리의 결과가 나오면 다음 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;

 

위 쿼리의 결과가 안나오면 다음과 같은 방법으로 사용량 확인
  SELECT current_setting('data_directory') || '$PG_HOME/base/pgsql_tmp'
  SELECT datname, temp_files, temp_bytes, stats_reset
  FROM pg_stat_database;

 

데이터 사용량 관련

데이터베이스 사용량 확인
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
 
select oid from pg_database;
du -h /data_dir/base/oid
 
테이블 사용량 확인
SELECT tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) FROM pg_tables WHERE schemaname = 'car_portal_app' LIMIT 2;
    
인덱스 사용량 확인
SELECT indexrelid::regclass,  pg_size_pretty(pg_relation_size(indexrelid::regclass))  FROM pg_index WHERE indexrelid::regclass::text like 'car_portal_app.%' limit 2;
 

오브젝트 사용여부 관련

사용하지 않은 테이블 2개 출력
SELECT relname FROM pg_stat_user_tables WHERE n_live_tup= 0 limit 2;
  
SELECT schemaname, tablename, attname FROM pg_stats WHERE null_frac= 1 and schemaname NOT IN ('pg_catalog', 'information_schema') limit 1;
 
인덱스가 사용되는지 확인
SELECT schemaname, relname, indexrelname FROM pg_stat_user_indexes s JOIN pg_index i ON s.indexrelid = i.indexrelid WHERE idx_scan=0 AND NOT indisunique AND NOT indisprimary;
 
foreign key 확인
SELECT * FROM pg_constraint WHERE contype = 'f';

 

 

 

 

출처

https://semode.tistory.com/6