Slow Query 확인법 (in Mysql, Postgresql)

느린 쿼리를 개선하는 것은 사용자의 입장에서도 중요하지만 DB 레벨의 효율적 자원관리를 위해서도 꼭 필요한 일이다. 

어떻게 쿼리의 실행 시간들을 확인할 수 있을까? 직접 일일이 실행시켜보면서 시간을 재야 하나? 

다행히도 일부 DB들은 그들 서버에서 수행되는 쿼리 중 서버에 악영향을 미칠 수 있는 Slow Query(슬로우 쿼리)들을 따로 저장해두고, 이를 개발자들이 확인할 수 있도록 하고 있다.

이 글에서는 슬로우 쿼리의 확인이 용이한 두 DB, Mysql과 Postgresql에서 이를 설정하고 확인하는 법을 다루려고 한다.

 

Mysql

설정법

Mysql에서는 아래 내용을 my.cnf에 추가하면 1초 이상 걸리는 쿼리를 슬로우 쿼리로 상정하고 이에 대한 수집이 시작된다. 물론 설정 파일을 수정했기 때문에 DB 재시작은 필요하다.

slow_query_log = 1
long_query_time = 1

위 설정이 제대로 적용되었는지 확인하기 위해서는, mysql에 접속하여 아래 명령어를 실행해보면 확인이 가능하다.

SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';

제대로 옵션이 적용되었다면, slow_query_log 값은 'ON'이 되고, long_query_time 값은 1이 되어있다.

확인법

슬로우 쿼리 설정을 제대로 적용하고 난 뒤엔, 설정한 기준 시간을 넘기는 쿼리에 대해서는 로그에 쌓이게 된다.

따라서 이를 확인하기 위해서는 슬로우 쿼리 로그 파일의 위치를 먼저 확인해야 한다. 이를 확인하는 방법은 아래와 같다.

SHOW GLOBAL VARIABLES LIKE 'slow_query_log_file';

확인한 로그 파일의 위치를 곧바로 열어서 내용을 확인해도 되지만, 로그가 많이 쌓여있는 경우엔 읽기가 꽤나 어렵기 때문에 mysqldumpslow 명령어를 통해 확인하면 더 보기 쉬워진다.

명령어를 실행할 때 부가적으로 줄 수 있는 옵션들이 다양하나, 해당 글에서는 평균 실행 시간을 기준으로 상위 10개의 쿼리 만을 출력하도록 옵션을 주도록 하겠다. 더 상세한 옵션에 대한 설명은 아래 링크의 공식문서를 확인해보면 잘 나와있다.

MySQL :: MySQL 8.0 Reference Manual :: 4.6.10 mysqldumpslow — Summarize Slow Query Log Files

평균 실행 시간을 기준으로 상위 10개의 쿼리 만을 출력하기 위해서는 아래와 같이 명령어를 수행해주면 된다.

mysqldumpslow -s at -t 10 {슬로우 쿼리 로그파일 경로}

명령어를 수행하면 이제 경로의 슬로우 쿼리 로그파일을 읽어들이고 이를 토대로 분석하여 결과물을 출력한다. 로그가 많이 쌓인 경우엔 분석하는데 시간이 더 들 수 있다.

 

Postgresql

PG에서는 구글링 시 슬로우 쿼리를 확인하기 위한 방법이 크게 3가지 정도 있다고 한다.

1. 슬로우 쿼리 판단 기준 시간을 설정하고 이를 초과하는 쿼리들을 로그에 쌓는 방법(Mysql의 슬로우 쿼리 로그 적재 방식과 유사)
2. 전체 서버 실행 쿼리에 대한 실행계획을 로그에 적재
3. 전체 서버 실행 쿼리에 대한 통계값을 테이블(뷰)를 통해 확인하는 방법(pg_stat_statements)

이 중 첫번째 방식이 위에서 설명한 Mysql의 방식과 유사하여 이를 소개하려고 했는데, 로그 파일 분석을 대신 진행해주는 mysqldumpslow 같은 명령어를 찾지 못해서 실질적으로 사용성이 부족하다고 판단했다.

대신 세번째 방식에 대해 소개하도록 하겠다.

설정법

pg_stat_statements 확장을 활성화하기 위해서는 우선 PG의 설정파일인 postgresql.conf를 수정해야 한다.

파일 내 shared_preload_libraries 변수가 선언이 되어있는지 확인하고, 선언이 되어있지 않다면 아래처럼 작성해준다.

shared_preload_libraries = 'pg_stat_statements'

만약 이미 선언이 되어 있다면 이미 존재하는 문자열 내에 콤마(,)로 구분하고 pg_stat_statements 를 추가해주면 된다.

설정파일을 수정한 뒤엔, 우선 DB 서버를 재시작 해주자.

그 뒤 DB 서버에 접속하여 아래 쿼리를 실행해서 확장을 실행해주자. 

CREATE EXTENSION pg_stat_statements;

여기까지 왔다면 이제 쿼리를 통해 서버 내 실행된 쿼리들의 통계를 확인할 수 있다.

확인법

pg_stat_statements 뷰가 갖는 칼럼의 종류가 꽤나 많아서 결과를 보기힘들지 모르겠으나, 그럼에도 불구하고 가장 심플한 방법은 아래 쿼리를 사용하는 것이다.

SELECT * FROM pg_stat_statements;

위 쿼리를 실행할 경우, 특정 쿼리가 실행된 횟수, 실행 시간(평균, 최대, 최소, 총합), 버퍼 캐시에서 가져온 블록 수, 디스크에서 가져온 블록 수 등이 출력되는데 이제 해당 칼럼 등을 통해 조회하고 싶은 순서대로 쿼리를 작성하여 조회를 하면 더 효과적인 슬로우 쿼리 탐지가 가능해진다.

pg_stat_statements 확장에 대한 상세한 설명이나 쿼리를 실행했을 때 나오는 칼럼들에 대한 설명이 추가적으로 필요하다면 아래 한글화된 공식문서를 참고해도 될 것 같다.

pg_stat_statements