MySQL 느린(Slow) 쿼리를 찾아내는 방법

Oracle에서 느린 쿼리를 추출하는 방법은 ‘동적성능뷰’라고 하는 v$로 시작하는 오라클 내부 통계정보를 찾아보는 것입니다. MySQL에서 Slow Query Log를 통해 추적할 수 있습니다. 이 로그는 일정 시간(수 seconds) 이상 걸리는 쿼리를 파일 또는 테이블에 기록하여 데이터베이스를 쿼리를 튜닝하는 방법이죠. 

 

MySQL에서 Slow Query를 찾아내는 방법

 

Slow Query Log 활성화 방법

1. 설정확인

현재 Slow Query Log 설정이 되어있는지 부터 확인해야 합니다. 이미 설정되어 있을 수도 있으니 꼭 먼저 확인하고 접근해야 합니다. 

SHOW VARIABLES LIKE ‘slow_query_log’; SHOW VARIABLES LIKE ‘slow_query_log_file’; SHOW VARIABLES LIKE ‘long_query_time’; → MySQL 8 버전 이상에서는 long_query_time으로 조회해야 함

 

만약 결과가 아래와 같이 나오면 설정이 되어 있는 것 입니다. 만약, slow_query_log가 'OFF' 이면 아직 Log를 쌓고 있지 않기 때문에 활성화 해야 합니다.

slow_query_log	ON
slow_query_log_file	/var/log/mysql/slow-query.log
long_query_time	2.000000

 

2. Slow Query Log 활성화

만약, Slog Query Log가 활성화 되지 않았다면, MySQL 설정 파일(my.cnf 또는 my.ini)에 아래 내용을 추가해야 합니다. 

[root@mysql ~]# vi /etc/my.cnf slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow-query.log long_query_time = 2 →  2초 이상 걸리는 쿼리를 기록합니다.

 

그런데 사용하는 데이터베이스가 AWS RDS 서비스로 MySQL엔진을 사용하고 있다면 서버에서 수정할 수는 없습니다. 왜냐하면, RDS는 serverless 서비스이기 때문에 서버에 접속하는 것이 아니라, 실제 AWS 콘솔을 통해 변경할 수 있다는 점 참고하세요. 

 

3. MySQL 재시작

이 설정이 적용하려면 mysql database 서버를 재시작해야 합니다. 

sudo service mysql restart

만약, 재시작 하지 않고 동적설정은 MySQL 5.1.6버전부터 가능합니다. 아래 명령어를 이용하기 바랍니다. 

SET GLOBAL slow_query_log = ‘ON’;  SET GLOBAL slow_query_log_file = ‘/var/log/mysql/slow-query.log‘; SET GLOBAL long_query_time = 2;

 

4. Slow 쿼리 확인

Slow Query Log 파일을 직접 열어서 느린 쿼리를 확인할 수 있습니다. 로그 파일은 my.ini 파일에 설정한 위치에 있습니다. 

분석 도구 활용

Slow Query Log를 설정했으니, 이제 분석 도구를 이용하여 더 효과적으로 모니터링 할 차례입니다. 

 

1. mysqldumpslow 

mysqldumpslow는 Slow 쿼리 로그를 요약하는 프로그램입니다. 예를 들어, 빈번하게 실행된 쿼리 상위 10개를 보고 싶다면 아래 명령어를 사용합니다. 

$ mysqldumpslow -t 10 /var/log/mysql/slow-query.log

 

2. pt-query-digest

이 도구는 Percona Toolkit의 일부로, Slow 쿼리를 분석하는 도구입니다. 설치 후 다음 명령어를 통해 분석이 가능합니다. 이는 쿼리의 빈도, 총 시간, 평균 시간, 최대 시간 등을 요약해서 보여줍니다.

$ pt-query-digest /var/log/mysql/slow-query.log

3. CloudWatch

만약, MySQL을 AWS에서 사용하고 있다면 CloudWatch를 사용하는 것이 SQL을 모니터링 하는 방법으로 제일 좋습니다. 왜냐하면 데이터베이스이 성능 문제를 실시간으로 쉽게 감지하고 해결할 수 있기 때문입니다.