pg_stat_statements 소개: 확장 기능 설치와 설정

PostgreSQL의 pg_stat_statements 확장 기능은 쿼리 통계를 모으는 도구야. 이걸로 어떤 쿼리가 제일 자주 실행되는지, 어떤 쿼리가 제일 오래 걸리는지, 그리고 데이터베이스 리소스가 얼마나 효율적으로 쓰이고 있는지 볼 수 있어. EXPLAIN으로 일일이 쿼리를 분석하는 대신, 데이터베이스 전체의 성능을 한눈에 볼 수 있지.

pg_stat_statements를 쓰는 장점:

실시간 모니터링: 지금 당장 어떤 쿼리가 데이터베이스를 힘들게 하는지 바로 볼 수 있어.

시스템 전체 성능 분석: 직접 분석하려고 고른 쿼리만이 아니라, 데이터베이스의 모든 쿼리에 대한 정보가 다 보여.

느린 쿼리 찾기: 어떤 쿼리가 제일 오래 걸리는지 쉽게 알 수 있어.

반복되는 쿼리 파악: 캐시 최적화나 인기 쿼리에 인덱스를 추가하는 데 도움 돼.

pg_stat_statements 설치와 설정

이제 pg_stat_statements가 왜 필요한지 알았으니까, 하나씩 설치하고 설정하는 방법을 알아보자.

1. PostgreSQL 준비 상태 확인하기. 네 PostgreSQL이 pg_stat_statements 확장 기능을 지원하는지 먼저 확인해야 해. 이 확장 기능은 PostgreSQL 9.2부터 기본으로 들어가 있어. 확장 기능이 있는지 확인하려면 이렇게 해봐:

SELECT extname FROM pg_extension;

만약 pg_stat_statements가 목록에 없다면, 관리자(어드민)가 설치를 안 했을 수도 있어.

설치되고 활성화된 확장 기능은 이렇게 보여야 해:

2. 확장 기능 추가하기.

pg_stat_statements를 활성화하려면, PostgreSQL의 프리로드 라이브러리 목록에 추가해야 해. 이건 postgresql.conf 설정 파일에서 해.

단계:

1. postgresql.conf 파일을 찾아. 보통 PostgreSQL 데이터 디렉토리에 있어.
2. 파일을 열어서 수정해.
3. 아래 줄을 추가하거나 수정해:

   shared_preload_libraries = 'pg_stat_statements'

이게 왜 필요하냐면, pg_stat_statements는 시스템 레벨에서 쿼리를 추적해야 해서 미리 로드되어야 하거든.

4. 변경사항을 저장하고 PostgreSQL 서버를 재시작해서 적용해. 리눅스에서는 아래 명령어를 써:

   sudo systemctl restart postgresql
   

로컬에서 개발하거나 테스트 중이면, 그냥 서버 재시작만 해도 돼.

3. 데이터베이스에 확장 기능 생성하기. PostgreSQL 서버를 재시작한 다음, 원하는 데이터베이스에 pg_stat_statements 확장 기능을 만들어야 해. psql이나 다른 툴로 데이터베이스에 접속해서 아래 명령어를 실행해:

CREATE EXTENSION pg_stat_statements;

문제 없이 끝나면, 이제 pg_stat_statements가 네 데이터베이스에서 활성화된 거야.

4. pg_stat_statements 파라미터 설정하기.

확장 기능을 설치한 후에는 통계 수집이 잘 되도록 파라미터를 설정하는 게 좋아. 주요 파라미터는 postgresql.conf 파일에서 지정할 수 있어.

주요 파라미터

• pg_stat_statements.track
• 어떤 쿼리를 추적할지 정해.
• 값:
  • all — 모든 쿼리 추적 (디버깅이나 분석에 추천).
  • top — 최상위 쿼리만 추적.
  • none — 추적 안 함.
• 설정 예시:

pg_stat_statements.track = 'all'

• pg_stat_statements.max

  • 통계에 저장할 쿼리 최대 개수야.
  • 기본값: 5000.
  • 쿼리가 많은 시스템이면, 이 값을 늘려도 돼. 예를 들어:

    pg_stat_statements.max = 10000
    

• pg_stat_statements.save

  • 서버 재시작 후에도 통계를 저장할지 정해.
  • 값: on 또는 off.
  • on으로 두는 걸 추천해:

    pg_stat_statements.save = on
    

파라미터를 바꾼 후에는 PostgreSQL 서버를 다시 재시작해.

pg_stat_statements 동작 확인하기

이제 확장 기능이 설치되고 설정됐으니까, 잘 작동하는지 확인해보자. 쿼리 통계 데이터를 보려면 아래 쿼리를 실행해봐:

SELECT
    queryid,        -- 쿼리의 유니크 아이디
    query,          -- 쿼리 텍스트
    calls,          -- 쿼리 호출 횟수
    total_time,     -- 전체 실행 시간 (밀리초)
    rows            -- 쿼리로 반환된 행 수
FROM pg_stat_statements
ORDER BY total_time DESC;

각 컬럼이 뭘 의미하는지 볼까?

• queryid: 쿼리의 유니크 아이디. 파라미터만 다른 동일 쿼리를 찾을 때 유용해.
• query: 실행된 SQL 쿼리 텍스트.
• calls: 쿼리가 몇 번 실행됐는지.
• total_time: 전체 실행 시간 (모든 호출의 합).
• rows: 쿼리로 반환된 전체 행 수.

예를 들어, calls = 100이고 total_time = 50000 (50초)인 쿼리가 시스템에서 제일 오래 걸린다면, 이 쿼리는 꼭 최적화해야 해.

pg_stat_statements 활용 시나리오

1. 가장 느린 쿼리 찾기. 가장 오래 걸리는 쿼리를 찾으려면 total_time으로 정렬해봐:

SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

2. 가장 자주 실행되는 쿼리 찾기. 가장 많이 실행된 쿼리를 찾으려면 calls로 정렬해봐:

SELECT query, calls, total_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

3. 인덱스 사용 분석. 느린 쿼리가 많으면 인덱스 사용을 꼭 확인해봐. 예를 들어, WHERE가 있는 쿼리에서 인덱스가 없으면 성능이 진짜 안 좋아질 수 있어.

pg_stat_statements 데이터 초기화

가끔 통계를 싹 리셋하고 처음부터 분석하고 싶을 때가 있어. 이럴 땐 아래 명령어를 써:

SELECT pg_stat_statements_reset();

리셋하면 모든 통계가 지워지고, 데이터 수집이 다시 시작돼.

실전 꿀팁

통계 수집량을 적당히 제한해: 트래픽이 엄청 많은 시스템이면 pg_stat_statements.max 값을 너무 크게 하지 말고, 적당히 조절해서 오버헤드를 줄여.

통계는 주기적으로 초기화해: 성능 분석을 시작하기 전에 통계를 리셋하면, 예전 데이터랑 섞이지 않아서 더 정확하게 볼 수 있어.

느린 쿼리는 꼭 신경 써: 자주 실행되지 않아도, 한 번 느린 쿼리가 전체 데이터베이스를 힘들게 할 수 있어.

이제 pg_stat_statements 확장 기능을 설치, 설정, 그리고 쿼리 성능 분석에 어떻게 쓰는지 알게 됐어. 다음 강의에서는 이걸로 느린 쿼리를 더 깊게 찾고, 실행을 최적화하는 방법을 배울 거야.