[MySQL] Slow Query Logging 설정하기 / 슬로우 쿼리 로깅 설정

개요

슬로우 쿼리 개선을 위해 스프링 부트 환경에서 AOP 기반으로 슬로우 쿼리 로그를 남기는 인터셉터를 구현했다. 다만, JPA 환경이냐 Mybatis 환경이냐에 따라 다른 방식으로의 구현이 필요했고, Interceptor 타겟을 설정하는 부분에 따라 로깅을 남기는 구조였기 때문에 '정말 모든 쿼리를 다 체크하고 있을까?' 라는 의문이 들었다.

 

 그러던 중 Real MySQL의 슬로우 쿼리 관련 챕터를 읽게 되었고, MySQL 자체에 슬로우 쿼리 관련 설정이 있다는 것을 알게 되었다. 이 설정들을 적용해보고, 어떤 형태로 슬로우 쿼리가 추출되는지 확인해보았다.

 

MySQL 슬로우 쿼리 로깅 활성화

 

먼저 MySQL에 접속 후 여러 옵션값들을 확인하고 설정해야한다.

 

1) slow_query_log

슬로우 쿼리 로깅 활성화 여부를 나타내는 설정값이다. 기본값은 OFF이므로 ON 또는 1로 설정한다.

show variables like '%slow_query_log%'; // 슬로우쿼리 활성화 여부 확인

set global slow_query_log = 'ON'; // 슬로우쿼리 활성화

 

2) long_query_time

슬로우 쿼리에 대한 기준 시간을 나타내는 설정값이다. 기본값은 10초로 되어있어 1초로 수정하였다.

show variables like '%long_query_time%'; // 슬로우 쿼리 기준 시간 확인

set global long_query_time = 1; // 슬로우 쿼리 기준 시간을 1초로 변경

 

 

set global 명령어를 통해 변수들을 설정할 경우 MySQL이 재시작될 때 설정값들이 초기화된다. 영구 설정을 하고자하면 my.ini나 my.cnf 파일을 다음과 같이 수정하면 된다.

// my.ini 파일
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

 

 

3) log_output

슬로우 쿼리를 남길 수 있는 방법은 두 가지로, 테이블 혹은 디스크이다. 온프레미스 환경에서는 디스크에 저장하는 것을 선호한다. 테이블로 설정할 경우 슬로우 쿼리가 발생할때마다 쓰기 작업이 발생하며, 데이터가 많아질 경우 부하가 발생할 수 있기 때문인데, 이를 방지하기 위해 슬로우 쿼리에 적재된 데이터를 주기적으로 삭제해주는 작업이 필요하다.

 AWS RDS 환경일 경우 파일 시스템에 직접 접근하지 못하므로 테이블에 저장하기도 한다. 참고로, 디스크로 설정한 후 RDS 설정에 SlowQuery Logging 을 활성화하면 Cloud Watch 의 로그 그룹에 저장된다. 필자의 경우 테이블에 적재되도록 설정하였다.

 

show variables like '%log_output%'; // 슬로우 쿼리 저장 타입 확인 (TABLE or FILE)

set global log_output = 'TABLE'; // 슬로우 쿼리를 테이블에 저장하도록 설정

 

 

슬로우 쿼리 테이블 확인

슬로우 쿼리 테이블은 mysql.slow_log 이다. 아래 명령어를 사용해 데이터가 쌓였는지 확인해보자. 아마 비어있을건데, 다음은 슬로우 쿼리를 직접 발생시켜 적재되는지를 확인해볼 것이다.

select * from mysql.slow_log order by start_time desc;

 

 

컬럼	설명
query_time	쿼리 실행 시간 (실질적 시간)
lock_time	테이블 락 시간
rows_sent	클라이언트에 전송된 행 수
rows_examined	스캔한 전체 행 수

* rows_examined 값이 매우 크면 인덱스가 제대로 안 쓰이고 있을 가능성이 높다.

 

슬로우 쿼리 발생시키기

 

슬로우 쿼리가 발생했을 때, mysql.slow_log 테이블에 적재되는지 확인하기 위해 select sleep(2) 쿼리를 날려보자.

 만약 슬로우 쿼리에 로그가 남지 않는다면, 아까 설정했던 global 설정 값들이 현재 세션에는 적용되지 않은 상태일 가능성이 있다. 이 경우 재접속하면 된다.

select sleep(2);

select * from mysql.slow_log order by start_time

mysql.slow_log 조회 결과

 

sql_text 값이 BLOB 형태로 출력된다면 아래 쿼리를 통해 조회하면 된다.

SELECT 
  start_time,
  user_host,
  CAST(sql_text AS CHAR) AS sql_text,
  query_time,
  lock_time,
  rows_sent,
  rows_examined
FROM mysql.slow_log;