pg_stat_statements로 쿼리 성능 모니터링하기

게시: July 13, 2023

pg_stat_statements 모듈은 PostgreSQL에서 쿼리 실행 통계 정보를 수집하고 분석하는 모듈이다. 이 모듈을 활성화하면 쿼리의 실행 횟수, 실행 시간, 실행 계획 등의 정보를 수집할 수 있다. 이를 통해 쿼리 성능을 모니터링하고 성능이 저하되는 쿼리를 찾아 최적화할 수 있다. 수집하는 정보는 다음과 같다.

쿼리 실행 횟수 (calls)
총 실행 시간 (total_time): 평균수행시간 * 실행횟수, 단위: ms
평균 실행 시간 (mean_time)
평균 블록 시간 (mean_blk_time)
행 수 (rows)
평균 행 수 (mean_rows) 등

위 데이터로 가장 자주 실행된 쿼리, 가장 오래 실행된 쿼리, 가장 많은 시간을 소비한 쿼리 등을 찾을 수 있다. 이를 토대로 성능이 저하되는 쿼리를 찾아 성능 개선을 시도해볼 수 있다.

NOTE
쿼리 실행 통계 정보를 수집하려면 최소 한 번은 쿼리를 실행해야 한다. 모듈이 통계 정보를 수집하기 시작하면 pg_stat_statements 뷰를 통해 쿼리 실행 통계 정보를 확인할 수 있다.
-- pg_stat_statements 뷰
SELECT * FROM pg_stat_statements;

Docker-compose에서 사용하는 방법

Docker-compose 클러스터에서 pg_stat_statements 모듈을 활성화하려면 command에 shared_preload_libraries 옵션으로 pg_stat_statements 모듈을 추가해야 한다.

Docker-compose 설정

docker-compose.yaml에 다음과 같이 옵션을 설정하면 PostgreSQL이 시작될 때 pg_stat_statements 모듈을 로드한다.

version: '3.7'
db:
  image: library/postgres:11.1-alpine
  restart: unless-stopped
  ports:
    - 5432:5432
  environment:
    - POSTGRES_USER=postgres
    - POSTGRES_PASSWORD=postgres
    - POSTGRES_DB=postgres
  volumes:
    - ./data:/var/lib/postgresql/data
  command:
    [
      "-c",
      "shared_preload_libraries=pg_stat_statements"
    ]

모듈 활성화

모듈을 활성화하려면 CREATE EXTENSION pg_stat_statements; 명령을 실행해야 한다. 이 명령을 실행하면 PostgreSQL 서버가 실행 중인 데이터베이스에 모듈을 추가한다. 모듈이 추가된 후에는 pg_stat_statements 뷰를 사용하여 쿼리 실행 통계 정보를 확인할 수 있다.

doker-compose exec db psql -U postgres -c 'CREATE EXTENSION pg_stat_statements;'

모듈 비활성화

모듈을 비활성화하려면 DROP EXTENSION pg_stat_statements; 명령을 실행해야 한다. 이 명령을 실행하면 PostgreSQL 서버가 실행 중인 데이터베이스에서 모듈을 제거한다.

doker-compose exec db psql -U postgres -c 'DROP EXTENSION pg_stat_statements;'

Slow query 찾기

pg_stat_statements 뷰를 사용하여 쿼리 실행 통계 정보를 확인할 수 있다.

다음 예시에서는 pg_stat_statements 뷰로 쿼리 실행 통계 정보를 검색하고 있는데 이것은 PostgreSQL에서 실행된 쿼리 실행 정보를 담고 있다.

SELECT query, calls, total_time, rows
  FROM pg_stat_statements
 WHERE userid = (
     SELECT usesysid
        FROM pg_user
      WHERE usename = current_user
    )
;

위 예시에서는 userid 칼럼을 사용하여 현재 사용자가 실행한 쿼리 실행 통계 정보만을 검색하고 있다. calls는 쿼리 실행 횟수, total_time은 총 실행 시간, rows는 쿼리 실행 결과 행 수를 나타낸다.

여기서 쿼리 실행 횟수가 많은 쿼리와 총 실행 시간이 긴 쿼리를 확인한다. 위의 예시에서는 calls와 total_time 정보를 사용하여 쿼리 실행 횟수가 많은 쿼리와 총 실행 시간이 긴 쿼리를 확인할 수 있다.

TIP
실행 횟수가 많은 쿼리와 총 실행시간이 긴 쿼리가 성능에 영향을 미치기 때문에 이 두 정보를 사용하여 성능에 영향을 미치는 쿼리를 찾고 최적화를 시도해볼 수 있다.

Slow query 실행계획 확인하기

Slow query를 최적화하기 위해서는 실행 계획을 확인해야 한다. 실행 계획을 확인하려면 EXPLAIN 명령을 사용해야 한다. EXPLAIN 명령은 쿼리 실행 계획을 확인할 수 있도록 쿼리 실행 계획 정보를 출력한다.

EXPLAIN SELECT * FROM my_table WHERE my_col = 'my_val';

my_table 테이블에서 my_col 컬럼이 my_val인 행을 검색하는 예를 들었다. 이 쿼리의 실행 계획을 확인하려면 위와 같이 EXPLAIN 명령을 사용하면 쿼리를 실행하면 된다.

Slow query를 최적화하기 위해서는 실행 계획을 확인하고 분석해서 인덱스를 추가하거나 쿼리를 수정하여 성능을 개선해야 한다. pg_stat_statements 모듈은 이러한 성능 최적화를 작업을 도와주는 유용한 모듈이다.

Twitter Facebook LinkedIn

Seungho Jeong