PostgreSQL | 모니터링 SQL 정리

유용한 모니터링 SQL 쿼리들을 정리하려고 합니다. 

1. pg_stat_activity - 현재 실행중인 쿼리와 세션 정보

SELECT * FROM pg_stat_activity;

 

PostgreSQL 서버에서 현재 실행중인 활동 및 세션 정보를 제공합니다. 

 

datid, datname, pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state, waiting, state_change, query 등이 포함될 수 있습니다.

 

2. pg_stat_user_indexes - 인덱스 사용률 확인

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes;

 

특정 테이블의 인덱스 스캔 횟수 및 읽은, 가져온 행 수 등을 통해 인덱스의 활용 정도를 확인할 수 있습니다. 

 

3. 테이블 로우 개수 및 크기

SELECT relname, n_live_tup, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables;

 

특정 테이블의 현재 로우 수와 테이블의 총 크기를 확인하여 테이블의 크기 및 데이터 분포를 파악할 수 있습니다. 

 

4. 쿼리 실행 계획 분석 

EXPLAIN ANALYZE <your_query>;

 

쿼리 실행 계획을 자세히 분석하여 쿼리의 실행 속도와 최적화 가능성을 평가할 수 있습니다. 

 

5. 버퍼 캐시 히트율 확인 

SELECT (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit + heap_blks_read) AS buffer_cache_hit_ratio
FROM pg_statio_user_tables;

 

히트된 힙 블록 수와 읽힌 힙 블록 수를 통해 데이터베이스의 버퍼 캐시 히트율을 평가할 수 있습니다.

 

6. 인덱스 히트율 확인 

SELECT idx_scan, idx_tup_read, idx_tup_fetch,
       (idx_tup_read + idx_tup_fetch) / NULLIF(idx_scan, 0) AS index_hit_ratio
FROM pg_stat_user_indexes;

 

인덱스 스캔 횟수, 읽은 행 수, 가져온 행 수를 통해 인덱스의 효율성 및 히트율을 확인할 수 있습니다. 

• idx_scan : 인덱스 스캔 횟수
• idx_tup_read : 읽은 행 수 
• idx_tup_fetch : 가져온 행 수 
• index_hit_ratio : 인덱스 히트율(인덱스 스캔 횟수 대비 인덱스에서 가져온 행의 비율)

7. 연결된 클라이언트 정보 및 연결 수 

SELECT usename, client_addr, client_port, count(*) AS connection_count
FROM pg_stat_activity
GROUP BY usename, client_addr, client_port;

 

현재 연결된 클라이언트들의 정보와 해당 클라이언트와의 연결 수를 확인할 수 있습니다. 

 

 

8. 테이블별 행 추가 및 갱신 정보 

SELECT relname, n_tup_ins, n_tup_upd
FROM pg_stat_user_tables;

 

특정 테이블에 추가된 행 수 와 갱신된 행 수를 통해 데이터베이스의 활동과 업데이트 패턴을 파악할 수 있습니다.