SQL 통계(DB)

PostgreSQL의 pg_stat_statements 뷰에서 제공하는 DB 자체의 SQL 통계입니다. SQL별 실행 횟수, 수행 시간, 처리 Row 수, 블록 I/O 등의 델타값을 DB에 직접 조회하지 않고 화면에서 확인할 수 있습니다.

에이전트가 1시간 간격으로 델타값을 계산하여 수집합니다.
에이전트 옵션 statements_order_by 기준(기본값 total_time)으로 statements_row_limit까지 수집합니다. (기본값 5,000건)
DBX 에이전트 1.6.10 버전 이상이 필요합니다.

노트

설정 완료 후 약 1시간 뒤 데이터 수집이 시작됩니다.

사전 설정

화면에 데이터를 표시하려면 DB 설정과 Agent 설정을 필수로 설정해야 합니다.

DB 설정

pg_stat_statements 확장 설치

pg_stat_statements는 PostgreSQL의 contrib 모듈에 포함되어 있습니다. 설치되어 있지 않다면 패키지를 설치합니다.
```
# Red Hat / CentOS
yum install postgresql-contrib

# Debian / Ubuntu
apt-get install postgresql-contrib
```
postgresql.conf 설정

shared_preload_libraries에 pg_stat_statements를 추가합니다. 변경 후 PostgreSQL 재시작이 필요합니다.
```
shared_preload_libraries = 'pg_stat_statements'
```
확장 모듈 생성

모니터링 대상 데이터베이스에서 확장을 생성합니다.
```
CREATE EXTENSION pg_stat_statements;
```

설치 확인

SELECT * FROM pg_stat_statements LIMIT 1;

Agent 설정

PostgreSQL에서는 statements 옵션의 기본값이 true이므로, 별도 설정 없이 SQL 통계가 기본 수집됩니다. 수집을 비활성화하려면 statements=false로 설정하세요.

설정 항목	타입	기본값	설명
`statements`	boolean	`true`	SQL 통계 수집 여부를 설정합니다.

상세 옵션

설정 항목	타입	기본값	설명
`statements_interval`	int	`1`	SQL 통계 수집 간격 (시간 단위). `statements_interval=1`로 지정할 경우 1시간 주기로 수집합니다.
`statements_interval_min`	int	`0`	SQL 통계 수집 간격 (분 단위). `statements_interval_min=10`으로 지정할 경우 10분 주기로 수집합니다. 이 옵션을 설정하면 `statements_interval`보다 우선합니다.
`statements_min_row`	int	`10000`	SQL 통계 데이터를 수집하는 기준입니다. 아래 수집 기준을 참고하세요.
`statements_row_limit`	int	`5000`	수집 최대 건수. `statements_order_by` 기준 상위 건수까지 수집합니다.
`statements_order_by`	String	`"total_time"`	SQL 통계 정보를 수집할 때의 정렬 기준을 지정합니다. (예: `statements_order_by=total_time,temp_blks_read`)
`statements_schema`	String	`""`	`pg_stat_statements` 확장이 whatap.conf의 `db` 옵션에 기입한 DB에 설치되지 않은 경우, 확장이 설치된 DB 명을 지정합니다.

수집 기준(statements_min_row)

pg_stat_statements의 rows(해당 쿼리로 출력하거나 영향을 받는 총 Row 수)가 statements_min_row를 초과할 경우 해당 SQL 데이터를 수집합니다.

노트

고객사의 DB 워크로드에 따라 SQL 수집량이 너무 많거나 적을 경우, statements_min_row와 statements_row_limit 값을 조정하여 수집량을 제어할 수 있습니다.

SQL 통계(DB) 분석하기

기본 옵션

상단 필터 영역에서 조회 기간, 대상 인스턴스, 필터 조건 등을 설정할 수 있습니다.

시간: 조회할 날짜와 시간 선택
인스턴스: 조회 대상 DB 인스턴스 선택
필터: 조건별 데이터 필터링 (다중 조건 시 AND 로직 적용)
정렬 순서: 데이터를 정렬할 기준 컬럼 선택 (예: rows, total_time, calls 등)
조회 건수: 테이블 표시 건수 설정
결과 내 검색: 조회된 결과 내에서 쿼리 키워드 검색
(컬럼 선택): 표시할 컬럼을 추가/삭제, 드래그로 순서 변경 가능
(다운로드): CSV 형식으로 내보내기

SQL 상세 보기

SQL 컬럼을 클릭하면 상세 정보 창이 열립니다. SQL 상세 창 설명은 다음을 참고하세요.

`pg_stat_statements`

이 화면의 데이터는 DB의 pg_stat_statements 뷰에서 가져오기 때문에, DB 설정 변경이나 통계 초기화가 수집 데이터에 직접 영향을 줍니다.

`pg_stat_statements.max`

이 파라미터는 서로 다른 SQL 패턴(queryid)을 최대 몇 개까지 저장할지 결정합니다. 기본값은 5000입니다.

-- 예시: 아래 두 SQL은 같은 queryid로 정규화됩니다.
SELECT * FROM users WHERE id = 10;
SELECT * FROM users WHERE id = 20;
→ SELECT * FROM users WHERE id = $1

저장 가능한 SQL 패턴 수가 limit을 초과하면 가장 적게 실행된 SQL부터 제거됩니다. 이 경우 일부 SQL의 통계가 누락될 수 있습니다.

-- 현재 limit 확인
SHOW pg_stat_statements.max;

-- 현재 저장된 SQL 패턴 수 확인
SELECT COUNT(*) FROM pg_stat_statements;

SQL 패턴 수가 limit에 근접하면 pg_stat_statements.max 값을 늘려주세요. 변경 후 PostgreSQL 재시작이 필요합니다.

`pg_stat_statements_reset()` 초기화

pg_stat_statements_reset() 함수로 통계를 초기화하면 기존 데이터가 모두 삭제되고 새로운 데이터가 기록됩니다. 에이전트가 수집하는 델타값도 초기화되므로 주의하세요.

SELECT pg_stat_statements_reset();

컬럼 안내

기본 정보

컬럼	설명
`query`	정규화된 SQL 문(리터럴 값이 `$1`, `$2` 등으로 치환됨)
`queryid`	동일한 정규화 쿼리를 식별하는 해시 코드
`usename`	쿼리를 실행한 사용자 명
`db`	쿼리가 실행된 데이터베이스 명
`instance`	SQL이 실행된 DB 인스턴스 명

실행 및 시간

컬럼	설명
`calls`	SQL 실행 횟수
`total_time`	총 실행 시간(단위: 밀리초)
`rows`	쿼리로 출력하거나 영향을 받은 총 Row 수

블록 I/O (공유 버퍼)

컬럼	설명
`shared_blks_hit`	공유 버퍼 캐시에서 읽은 블록 수(캐시 히트)
`shared_blks_read`	디스크에서 읽은 공유 블록 수(캐시 미스)
`shared_blks_dirtied`	수정된 공유 블록 수
`shared_blks_written`	디스크에 쓴 공유 블록 수

블록 I/O (로컬 버퍼)

컬럼	설명
`local_blks_hit`	로컬 버퍼 캐시에서 읽은 블록 수
`local_blks_read`	디스크에서 읽은 로컬 블록 수
`local_blks_dirtied`	수정된 로컬 블록 수
`local_blks_written`	디스크에 쓴 로컬 블록 수

임시 블록

컬럼	설명
`temp_blks_read`	읽은 임시 블록 수
`temp_blks_written`	쓴 임시 블록 수

I/O 시간

컬럼	설명
`blk_read_time`	블록 읽기에 소요된 시간(단위: 밀리초). `track_io_timing` 활성화 필요
`blk_write_time`	블록 쓰기에 소요된 시간(단위: 밀리초). `track_io_timing` 활성화 필요

테이블 Fetch

컬럼	설명
`table fetch by rowid`	인덱스를 통해 테이블 Row를 직접 조회한 횟수
`table fetch continued row`	Row가 여러 블록에 걸쳐 저장된 경우 추가 블록을 읽은 횟수

데이터 해석 가이드

튜닝 대상 SQL 식별

total_time이 높고 calls도 높은 SQL

자주 실행되면서 느림 → 최우선 튜닝 대상
rows가 높은 SQL

대량의 Row를 처리 → 불필요한 조회가 아닌지 점검
total_time / calls(평균 실행 시간)가 높은 SQL

개별 실행이 느림 → SQL 튜닝, 실행 계획 점검

캐시 효율 점검

캐시 히트율 = shared_blks_hit / (shared_blks_hit + shared_blks_read)
- 90% 이하이면 shared_buffers 증설 또는 비효율 쿼리 점검
shared_blks_read가 높은 SQL: 디스크 I/O를 많이 유발 → 인덱스 점검, shared_buffers 증설 검토
shared_blks_dirtied가 높은 SQL: 데이터를 많이 변경 → 체크포인트 부하 유발 가능

임시 블록 점검

temp_blks_read, temp_blks_written이 높으면 정렬이나 해시 조인 시 메모리가 부족하여 디스크를 사용 중
work_mem 증설 검토

I/O 시간 점검

blk_read_time이 높은 SQL: 디스크 읽기 대기가 큼 → 스토리지 성능 또는 캐시 효율 점검
blk_read_time 확인을 위해 postgresql.conf에 track_io_timing = on 설정 필요

실전 활용 시나리오

시나리오 1: 느린 SQL 찾기

total_time 기준으로 내림차순 정렬
상위 SQL의 calls를 확인 → 자주 실행되면서 느린 SQL이 최우선 튜닝 대상

시나리오 2: 캐시 효율이 낮은 SQL 찾기

shared_blks_read가 높은 SQL 확인
shared_blks_hit과 비교하여 캐시 히트율 계산
캐시 히트율이 낮으면 해당 SQL의 Plan(실행 계획)을 확인하여 인덱스 추가 여부 결정

시나리오 3: 디스크 I/O 과다 SQL 찾기

blk_read_time이 높은 SQL 확인(track_io_timing = on 필요)
→ 디스크 읽기에 시간을 많이 소비하는 SQL → 인덱스 점검, 불필요한 순차 스캔 제거

사전 설정​

DB 설정​

Agent 설정​

SQL 통계(DB) 분석하기​

기본 옵션​

SQL 상세 보기​

pg_stat_statements​

pg_stat_statements.max​

pg_stat_statements_reset() 초기화​

컬럼 안내​

기본 정보​

실행 및 시간​

블록 I/O (공유 버퍼)​

블록 I/O (로컬 버퍼)​

임시 블록​

I/O 시간​

테이블 Fetch​

데이터 해석 가이드​

튜닝 대상 SQL 식별​

캐시 효율 점검​

임시 블록 점검​

I/O 시간 점검​

실전 활용 시나리오​

시나리오 1: 느린 SQL 찾기​

시나리오 2: 캐시 효율이 낮은 SQL 찾기​

시나리오 3: 디스크 I/O 과다 SQL 찾기​