PostgreSQL 성능 모니터링 확장 프로그램 소개
PostgreSQL은 강력한 데이터베이스 관리 시스템으로, 성능 모니터링을 위한 다양한 도구와 확장 기능을 제공합니다. 이번 블로그에서는 pg_stat_monitor, pg_stat_kcache, 그리고 pg_stat_statements 세 가지 확장 프로그램에 대해 살펴보겠습니다. (PostgreSQL 16버전, Rocky 8버전입니다.)
1. pg_stat_monitor
pg_stat_monitor는 PostgreSQL 쿼리 성능을 분석하고, 쿼리 실행 통계 및 성능 데이터를 수집하는 데 중점을 둔 확장 프로그램입니다. 이를 통해 데이터베이스의 성능 병목 현상을 식별하고 최적화할 수 있습니다.
특징
- 쿼리 호출 수, 평균 실행 시간, 최대 실행 시간 등의 다양한 통계 제공
- 쿼리의 실행 패턴을 시각화하여 성능 문제를 분석할 수 있는 도구로 활용
- 사용자와 데이터베이스별로 통계를 수집하여 보다 구체적인 분석 가능
설치 방법
sudo dnf install -y pg_stat_monitor_16
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_monitor';
sudo systemctl restart postgresql-16
CREATE EXTENSION pg_stat_monitor;사용 예시
SELECT application_name, userid AS user_name, datname AS database_name,
substr(query, 0, 50) AS query, calls, client_ip
FROM pg_stat_monitor;
application_name | user_name | database_name | query | calls | client_ip
psql | vagrant | postgres | SELECT application_name, userid AS user_name, dat | 1 | 127.0.0.1 파라미터
| 이름 | 타입 | 설명 |
|---|---|---|
| bucket | bigint | 데이터 수집 단위. 레코드가 체인의 어느 버킷에 속하는지 나타냅니다. |
| bucket_start_time | timestamp with time zone | 버킷의 시작 시간 |
| userid | oid | 쿼리를 실행한 사용자의 ID |
| username | text | 쿼리를 실행한 사용자의 이름 |
| dbid | oid | 쿼리가 실행된 데이터베이스의 ID |
| datname | text | 쿼리가 실행된 데이터베이스의 이름 |
| client_ip | inet | 쿼리를 실행한 클라이언트의 IP 주소 |
| pgsm_query_id | bigint | 쿼리를 고유하게 식별하는 해시 코드. PostgreSQL 서버 버전, 쿼리 내 상수, 데이터베이스, 사용자 또는 스키마에 독립적입니다. 쿼리 텍스트를 정규화한 후 계산됩니다. |
| toplevel | bool | 쿼리가 최상위 쿼리로 실행되었는지 여부 (참이면 최상위 쿼리) |
| top_queryid | bigint | 내부적으로 생성된 해시 코드로, 쿼리 문장에서 최상위 쿼리를 식별합니다. |
| query | text | 쿼리의 실제 텍스트 |
| comments | text | 쿼리에 대한 주석 |
| planid | text | 쿼리 계획의 내부적으로 생성된 ID |
| query_plan | text | 쿼리를 실행하는 데 사용된 단계의 순서. pgsm_enable_query_plan이 활성화된 경우에만 사용 가능합니다. |
| top_query | text | 문장에서 사용된 최상위 쿼리 |
| application_name | text | 데이터베이스에 연결된 애플리케이션의 이름 |
| relations | text[] | 쿼리에 관련된 테이블의 목록 |
| cmd_type | integer | 쿼리 유형의 ID. 지원되는 값: 1 - SELECT; 2 - UPDATE; 3 - INSERT; 4 - DELETE |
| cmd_type_text | text | 실행된 쿼리의 유형 |
| elevel | integer | 쿼리의 오류 수준 (WARNING, ERROR, LOG) |
| sqlcode | text | SQL 오류 코드 |
| message | text | 오류 메시지 |
| bucket_done | boolean | 버킷이 아직 활성 상태인지 (false) 또는 완료 상태인지 (true) 여부를 나타냅니다. 활성 버킷이면 더 많은 쿼리와 통계가 추가될 수 있습니다. 완료된 버킷에는 더 이상 추가할 수 없습니다. |
| plans | bigint | 문장이 계획된 횟수 |
| total_plan_time | double precision | 문장을 계획하는 데 소요된 총 시간 (ms) |
| min_plan_time | double precision | 문장을 계획하는 데 소요된 최소 시간 (ms) |
| max_plan_time | double precision | 문장을 계획하는 데 소요된 최대 시간 (ms) |
| mean_plan_time | double precision | 문장을 계획하는 데 소요된 평균 시간 (ms) |
| stddev_plan_time | double precision | 문장을 계획하는 데 소요된 시간의 표준 편차 (ms) |
| calls | bigint | 특정 쿼리가 실행된 횟수 |
| total_exec_time | double precision | 쿼리 실행에 소요된 총 시간 (ms) |
| min_exec_time | double precision | 쿼리 실행에 소요된 최소 시간 (ms) |
| max_exec_time | double precision | 쿼리 실행에 소요된 최대 시간 (ms) |
| mean_exec_time | double precision | 쿼리 실행에 소요된 평균 시간 (ms) |
| stddev_exec_time | double precision | 쿼리 실행에 소요된 시간의 표준 편차 (ms) |
| rows | bigint | 쿼리 실행 시 검색된 행의 수 |
| shared_blks_hit | bigint | 캐시에서 반환된 공유 메모리 블록의 총 수 |
| shared_blks_read | bigint | 캐시에서 반환되지 않은 공유 블록의 총 수 |
| shared_blks_dirtied | bigint | 쿼리 실행에 의해 "더럽혀진" 공유 메모리 블록의 수 |
| shared_blks_written | bigint | 쿼리 실행 중 드라이브에 동시에 기록된 공유 메모리 블록의 수 |
| local_blks_hit | bigint | 백엔드가 로컬로 간주하고 임시 테이블에 사용된 블록의 수 |
| local_blks_read | bigint | 쿼리 실행 중 읽힌 로컬 블록의 총 수 |
| local_blks_dirtied | bigint | 쿼리 실행 중 "더럽혀진" 로컬 블록의 총 수 |
| local_blks_written | bigint | 쿼리 실행 중 드라이브에 동시에 기록된 로컬 블록의 총 수 |
| temp_blks_read | bigint | 드라이브에서 읽은 임시 파일 블록의 총 수. 임시 파일은 메모리 부족 시 쿼리를 실행하기 위해 사용됩니다. |
| temp_blks_written | bigint | 드라이브에 기록된 임시 파일 블록의 총 수 |
| blk_read_time | double precision | 블록을 읽는 데 소요된 총 대기 시간 (ms) |
| blk_write_time | double precision | 블록을 드라이브에 쓰는 데 소요된 총 대기 시간 (ms) |
| temp_blk_read_time | double precision | 문장에 의해 읽힌 임시 블록의 총 수 |
| temp_blk_write_time | double precision | 문장이 임시 블록을 기록하는 데 소요된 총 시간 (ms) |
| resp_calls | text[] | 호출 히스토그램 |
| cpu_user_time | double precision | 쿼리를 실행하는 데 소요된 CPU 시간 (ms) |
| cpu_sys_time | double precision | 커널 코드 실행에 소요된 CPU 시간 (ms) |
| wal_records | bigint | 쿼리에 의해 생성된 WAL(Write Ahead Logs)의 총 수 |
| wal_fpi | bigint | 쿼리에 의해 생성된 WAL FPI(Full Page Images)의 총 수 |
| wal_bytes | numeric | 쿼리에 의해 생성된 WAL의 총 바이트 수 |
| jit_functions | bigint | 문장에 의해 JIT(Just-in-Time) 컴파일된 함수의 총 수 |
| jit_generation_time | double precision | 문장이 JIT 코드를 생성하는 데 소요된 총 시간 (ms) |
| jit_inlining_count | bigint | 함수가 인라인된 횟수 |
| jit_inlining_time | double precision | 문장이 함수 인라인에 소요된 총 시간 (ms) |
| jit_optimization_count | bigint | 문장이 최적화된 횟수 |
| jit_optimization_time | double precision | 문장이 최적화하는 데 소요된 총 시간 (ms) |
| jit_emission_count | bigint | 코드가 생성된 횟수 |
| jit_emission_time | double precision | 문장이 코드 생성에 소요된 총 시간 (ms) |
2. pg_stat_kcache
pg_stat_kcache는 PostgreSQL에서 파일 시스템 캐시와 I/O 성능을 모니터링하여 데이터베이스의 I/O 관련 정보를 제공합니다. 이를 통해 디스크 I/O가 데이터베이스 성능에 미치는 영향을 분석할 수 있습니다.
특징
- 캐시 적중률 및 I/O 통계 제공
- 쿼리 실행 시 디스크와 메모리 간의 데이터 전송 현황을 추적
- I/O 성능 개선을 위한 진단 도구로 유용
설치 방법
sudo dnf install -y pg_stat_kcache_16
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements, pg_stat_kcache, pg_stat_monitor';
sudo systemctl restart postgresql-16
CREATE EXTENSION pg_stat_kcache;사용 예시
SELECT * FROM pg_stat_kcache();파라미터
pg_stat_kcache 뷰
| 이름 | 타입 | 설명 |
|---|---|---|
| datname | name | 데이터베이스의 이름 |
| plan_user_time | double precision | 데이터베이스의 쿼리 계획에 소요된 사용자 CPU 시간 (초 및 밀리초 단위, pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_system_time | double precision | 데이터베이스의 쿼리 계획에 소요된 시스템 CPU 시간 (초 및 밀리초 단위, pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_minflts | bigint | 데이터베이스의 쿼리 계획 중 페이지 재사용 수 (소프트 페이지 폴트, pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_majflts | bigint | 데이터베이스의 쿼리 계획 중 페이지 폴트 수 (하드 페이지 폴트, pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_nswaps | bigint | 데이터베이스의 쿼리 계획 중 스왑 횟수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_reads | bigint | 데이터베이스의 쿼리 계획 중 파일 시스템 레이어에서 읽은 바이트 수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_reads_blks | bigint | 데이터베이스의 쿼리 계획 중 파일 시스템 레이어에서 읽은 8K 블록 수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_writes | bigint | 데이터베이스의 쿼리 계획 중 파일 시스템 레이어에서 쓴 바이트 수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_writes_blks | bigint | 데이터베이스의 쿼리 계획 중 파일 시스템 레이어에서 쓴 8K 블록 수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_msgsnds | bigint | 데이터베이스의 쿼리 계획 중 IPC 메시지 전송 횟수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_msgrcvs | bigint | 데이터베이스의 쿼리 계획 중 IPC 메시지 수신 횟수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_nsignals | bigint | 데이터베이스의 쿼리 계획 중 수신된 신호 수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_nvcsws | bigint | 데이터베이스의 쿼리 계획 중 자발적 컨텍스트 스위치 횟수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_nivcsws | bigint | 데이터베이스의 쿼리 계획 중 비자발적 컨텍스트 스위치 횟수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| exec_user_time | double precision | 데이터베이스의 쿼리 실행에 소요된 사용자 CPU 시간 (초 및 밀리초 단위) |
| exec_system_time | double precision | 데이터베이스의 쿼리 실행에 소요된 시스템 CPU 시간 (초 및 밀리초 단위) |
| exec_minflts | bigint | 데이터베이스의 쿼리 실행 중 페이지 재사용 수 (소프트 페이지 폴트) |
| exec_majflts | bigint | 데이터베이스의 쿼리 실행 중 페이지 폴트 수 (하드 페이지 폴트) |
| exec_nswaps | bigint | 데이터베이스의 쿼리 실행 중 스왑 횟수 |
| exec_reads | bigint | 데이터베이스의 쿼리 실행 중 파일 시스템 레이어에서 읽은 바이트 수 |
| exec_reads_blks | bigint | 데이터베이스의 쿼리 실행 중 파일 시스템 레이어에서 읽은 8K 블록 수 |
| exec_writes | bigint | 데이터베이스의 쿼리 실행 중 파일 시스템 레이어에서 쓴 바이트 수 |
| exec_writes_blks | bigint | 데이터베이스의 쿼리 실행 중 파일 시스템 레이어에서 쓴 8K 블록 수 |
| exec_msgsnds | bigint | 데이터베이스의 쿼리 실행 중 IPC 메시지 전송 횟수 |
| exec_msgrcvs | bigint | 데이터베이스의 쿼리 실행 중 IPC 메시지 수신 횟수 |
| exec_nsignals | bigint | 데이터베이스의 쿼리 실행 중 수신된 신호 수 |
| exec_nvcsws | bigint | 데이터베이스의 쿼리 실행 중 자발적 컨텍스트 스위치 횟수 |
| exec_nivcsws | bigint | 데이터베이스의 쿼리 실행 중 비자발적 컨텍스트 스위치 횟수 |
pg_stat_kcache_detail 뷰
| 이름 | 타입 | 설명 |
|---|---|---|
| query | text | 쿼리 텍스트 |
| top | bool | 쿼리가 최상위 쿼리인지 여부 |
| datname | name | 데이터베이스의 이름 |
| rolname | name | 역할 이름 |
| plan_user_time | double precision | 쿼리 계획에 소요된 사용자 CPU 시간 (초 및 밀리초 단위, pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_system_time | double precision | 쿼리 계획에 소요된 시스템 CPU 시간 (초 및 밀리초 단위, pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_minflts | bigint | 쿼리 계획 중 페이지 재사용 수 (소프트 페이지 폴트, pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_majflts | bigint | 쿼리 계획 중 페이지 폴트 수 (하드 페이지 폴트, pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_nswaps | bigint | 쿼리 계획 중 스왑 횟수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_reads | bigint | 쿼리 계획 중 파일 시스템 레이어에서 읽은 바이트 수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_reads_blks | bigint | 쿼리 계획 중 파일 시스템 레이어에서 읽은 8K 블록 수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_writes | bigint | 쿼리 계획 중 파일 시스템 레이어에서 쓴 바이트 수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_writes_blks | bigint | 쿼리 계획 중 파일 시스템 레이어에서 쓴 8K 블록 수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_msgsnds | bigint | 쿼리 계획 중 IPC 메시지 전송 횟수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_msgrcvs | bigint | 쿼리 계획 중 IPC 메시지 수신 횟수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_nsignals | bigint | 쿼리 계획 중 수신된 신호 수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_nvcsws | bigint | 쿼리 계획 중 자발적 컨텍스트 스위치 횟수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| plan_nivcsws | bigint | 쿼리 계획 중 비자발적 컨텍스트 스위치 횟수 (pg_stat_kcache.track_planning이 활성화된 경우) |
| exec_user_time | double precision | 쿼리 실행에 소요된 사용자 CPU 시간 (초 및 밀리초 단위) |
| exec_system_time | double precision | 쿼리 실행에 소요된 시스템 CPU 시간 (초 및 밀리초 단위) |
| exec_minflts | bigint | 쿼리 실행 중 페이지 재사용 수 (소프트 페이지 폴트) |
| exec_majflts | bigint | 쿼리 실행 중 페이지 폴트 수 (하드 페이지 폴트) |
| exec_nswaps | bigint | 쿼리 실행 중 스왑 횟수 |
| exec_reads | bigint | 쿼리 실행 중 파일 시스템 레이어에서 읽은 바이트 수 |
| exec_reads_blks | bigint | 쿼리 실행 중 파일 시스템 레이어에서 읽은 8K 블록 수 |
| exec_writes | bigint | 쿼리 실행 중 파일 시스템 레이어에서 쓴 바이트 수 |
| exec_writes_blks | bigint | 쿼리 실행 중 파일 시스템 레이어에서 쓴 8K 블록 수 |
| exec_msgsnds | bigint | 쿼리 실행 중 IPC 메시지 전송 횟수 |
| exec_msgrcvs | bigint | 쿼리 실행 중 IPC 메시지 수신 횟수 |
| exec_nsignals | bigint | 쿼리 실행 중 수신된 신호 수 |
| exec_nvcsws | bigint | 쿼리 실행 중 자발적 컨텍스트 스위치 횟수 |
| exec_nivcsws | bigint | 쿼리 실행 중 비자발적 컨텍스트 스위치 횟수 |
| 이름 | 타입 | 설명 |
|---|---|---|
| pg_stat_kcache_reset() | function | pg_stat_kcache에서 수집한 통계를 리셋하는 함수 (슈퍼유저만 호출 가능) |
| 이름 | 타입 | 설명 |
|---|---|---|
| pg_stat_kcache() | function | pg_stat_kcache 뷰의 카운터를 출력하는 함수 |
3. pg_stat_statements
pg_stat_statements는 PostgreSQL에서 실행된 쿼리에 대한 통계를 수집하는 확장 프로그램입니다. 이 확장을 통해 쿼리 성능을 모니터링하고, 최적화가 필요한 쿼리를 쉽게 식별할 수 있습니다.
특징
- 각 쿼리의 실행 횟수, 평균 실행 시간, 총 실행 시간 등을 수집
- 다양한 쿼리 성능 메트릭을 제공하여 성능 개선에 도움
- 쿼리 텍스트를 기반으로 하는 통계 제공
설치 방법
sudo dnf install -y postgresql16-server
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
sudo systemctl restart postgresql-16
CREATE EXTENSION pg_stat_statements;사용 예시
SELECT * FROM pg_stat_statements;비교: pg_stat_monitor, pg_stat_kcache, pg_stat_statements
| 확장 프로그램 | 주요 기능 | 데이터 수집 방식 | 사용 목적 |
|---|---|---|---|
| pg_stat_monitor | 쿼리 성능 분석 및 통계 수집 | 쿼리 실행 시 발생하는 다양한 통계 수집 | 쿼리 성능 최적화 및 병목 현상 분석 |
| pg_stat_kcache | I/O 성능 및 파일 시스템 캐시 모니터링 | 캐시 적중률 및 I/O 통계 수집 | 디스크 I/O 분석 및 성능 개선 |
| pg_stat_statements | 쿼리 실행 통계 수집 | 쿼리 텍스트 기반 통계 수집 | 쿼리 성능 모니터링 및 최적화 쿼리 식별 |
결론
PostgreSQL의 성능을 최적화하기 위해서는 다양한 도구와 확장을 활용하는 것이 중요합니다.
pg_stat_monitor, pg_stat_kcache, pg_stat_statements는 각각 고유한 기능을 가지고 있어, 이들을 조합하여 사용하면 데이터베이스 성능을 효과적으로 모니터링하고 개선할 수 있습니다. 이들 확장 프로그램을 통해 쿼리 성능 및 I/O 성능을 한눈에 파악하고, 데이터베이스의 최적화를 위한 중요한 정보를 얻을 수 있습니다.
pglover_12