[MYSQL] SLOW_LOG 기록 및 확인 방법

MYSQL

슬로우 쿼리 로그는 설정에서 정의된 시간보다(second) 오랫동안 실행 된 쿼리에 대해서 기록을 한다

슬로우 쿼리 로그 활성화(설정 파일)

슬로우 쿼리 로그를 활성화하기 위해서는 MySQL Configuration 파일을 찾아야 한다. 아래 명령어를 수행하면 my.cnf 파일을 찾을 수 있다. 아래 디렉터리 중 한 곳에 my.cnf가 있을 것이다. 여러 디렉토리에 동시에 my.cnf 파일이 존재하면 왼쪽 디렉토리 순서대로 우선 적용된다.

$ mysql --verbose --help | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

파일을 찾았다면 아래처럼 수정한다.

[mysqld]
# 슬로우 쿼리를 TABLE로 출력한다. FILE로도 설정할 수 있다
# FILE로 설정했다면 slow_query_log_file로 출력할 파일 위치를 설정할 수 있다
log_output = TABLE

# 슬로우 쿼리 활성화
slow_query_log = 1

# 아래 변수에 지정된 초(seconds)이상 쿼리가 수행되면 슬로우 쿼리에 기록된다
# 여기서는 테스트를 위해 0.05초로 지정했다
long_query_time = 0.05

# log_ouput을 TABLE로 설정했다면 slow_query_log_file 변수는 주석 처리하자
# MySQL 버그인지 필자의 개발환경 문제인지 모르겠지만, log_output을 TABLE로 설정했을 때
# 해당 변수가 설정되어 있으면 슬로우 쿼리가 활성화되지 않는 문제가 발생했다
# slow_query_log_file=...
# ...

파일을 수정했다면 MySQL 서버를 재시작한다. 만약 MySQL 서버를 재시작하지 않고 슬로우 쿼리를 활성화하고 싶다면 MySQL 서버에서 아래 명령어를 실행하면 된다. 다만 MySQL 서버가 재시작했을 때 원상 복구된다.

SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.05;

아래 명령어를 MySQL 서버에서 실행하면 슬로우 쿼리가 설정되었는지 확인할 수 있다.

SHOW VARIABLES WHERE
Variable_Name LIKE 'slow_query_log' OR
Variable_Name LIKE 'long_query_time' OR
Variable_Name LIKE 'log_output';

2. 슬로우 쿼리 로그 조회

슬로우 쿼리를 활성화 했으니 잘 동작하는지 확인해보자. 필자는 테스트를 위해 아래 깃허브의 덤프를 사용했다. https://github.com/datacharmer/test_db/blob/master/employees.sql

덤프를 모두 로드했으면 아래 쿼리를 실행해 보자. 컴퓨터 성능에 따라 다르겠지만 InnoDB 에서 전체 테이블 레코드의 개수를 세는 것은 보통 인덱스 풀 스캔을 해야 하기 때문에 0.05초 이상 걸릴 것이다. 필자의 컴퓨터에서는 0.054 초가 걸렸다. 여러 번 같은 쿼리를 수행하면 쿼리 캐시 때문에 결과가 금방 나올 수도 있다. 그럴 때는 long_query_time 을 조절해서 테스트하면 된다.

SELECT COUNT(*) FROM employees; # 0.054 sec

SQL 사용

mysql DB의 slow_log 테이블을 조회하면 슬로우 쿼리를 확인할 수 있다. sql_text 컬럼이 blob이기 때문에 sql_text 컬럼을 CONVERT 해서 실제 쿼리를 확인하자. 2번째 쿼리는 주요한 내용만 뽑은 쿼리이다.

SELECT * FROM mysql.slow_log;
# 또는
SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, db, CONVERT(sql_text USING utf8 ) sql_text
FROM mysql.slow_log;

 

• start_time: 쿼리가 시작된 시간
• user_host: 쿼리를 실행한 사용자
• query_time: 쿼리가 실행되는 데 걸린 시간(초)
• lock_time: 테이블 잠금에 대한 대기 시간(초)
• rows_sent: 실제 클라이언트에 전달된 레코드의 수
• rows_examined: 쿼리가 처리되기 위해 실제 접근한 레코드의 수
• db: 쿼리가 수행된 데이터베이스
• sql_text: 실제 수행된 쿼리