mysql / mariadb 슬로우쿼리(slowquery) 탐지하기

mysql 또는 mariadb 에는 slow query 를 탐지해서 log 파일에 저장해주는 기능이 있다.

DB 튜닝을 위해, 속도가 느린 쿼리를 탐지해서 알려주는 기능은 참 유용한 기능이다.

 

현재, slow query 탐지 기능이 동작중인지 확인하려면, 쿼리창에 아래와 같이 실행해주면 알 수 있다.

 

show variables like 'slow_query_%'; show variables like 'long_query_%';

 

 

slow_query_log 가 OFF 상태로 동작중이지 않다.

 

탐지 기능을 켜려면,

 

mysql 이 설치된 폴더의 data 폴더아래 my.ini 설정 파일을 수정해야 한다.

 

MariaDB 10.5 버전이 설치되어 있다면, 아마도 아래와 같은 경로일 것이다.

 

C:\Program Files\MariaDB 10.5\data\my.ini

 

파일을 편집기로 열어서 아래와 같은 내용을 추가해준다.

 

slow_query_log = 1 slow_query_log_file = C:/slowlog/mysql-slow.log long_query_time = 5

 

slow_query_log = 1

SLOW QUERY 탐지 기능을 사용한다는 의미이다.

 

slow_query_log_file = C:/slowlog/mysql-slow.log

슬로우 쿼리가 저장되는 경로와 파일명인데, 경로 규칙에 주의 해야 한다.

\ 문자가 아니라 / 문자로 폴더가 구분된다.

 

long_query_time = 5

초단위로 입력하며, 5초 이상의 쿼리를 탐지하겠다는 내용이다.

 

 

mysql 서비스를 재시작해야, 변경된 내용이 적용된다.

 

# mysql net stop MySQL & net start MySQL

# mariadb net stop mariadb & net start mariadb

 

윈도우의 서비스 항목에서 재시작하거나, command 창에서 재시작 해도 된다.

 

 

 

이제 변경된 설정을 확인해보면 된다.

 

show variables like 'slow_query_%'; show variables like 'long_query_%';

 

 

설정한 경로에 log 파일도 생성되었다.

 

 

슬로우쿼리 탐지가 잘 되는지 테스트 쿼리를 날려서 확인해 볼 수 있다.

 

SELECT SLEEP(5), 'slow query test.';

 

 

슬로우쿼리가 탐지될때마다, 로그 파일의 뒤에 계속 추가가 된다.

 

여기까지만 해도, 주기적으로 로그파일을 열어보고, 확인하면 되지만, 실시간으로 알림을 받을 수 있는 방법이 있다.

 

C# 의 FileSystemWatcher 를 이용하면, 파일의 내용이 변경되는걸 감지해서, slack 이나 naverworks 등의 메신저로 단체 채팅방에 알림메세지를 발송 할 수 있다.

 

 

FileSystemWatcher watcher = new FileSystemWatcher(); watcher.Filter = "mysql-slow.log"; watcher.Path = "C:\\slowlog\\"; watcher.IncludeSubdirectories = false; watcher.NotifyFilter = NotifyFilters.DirectoryName |    NotifyFilters.LastWrite |    NotifyFilters.FileName |    NotifyFilters.Size; watcher.Changed += Watcher_Changed; watcher.EnableRaisingEvents = true;

 

그리고, 파일에 추가되는 내용만 알림을 해야 하기 때문데, 파일 스트림의 position 을 전역변수로 기록해두자.

 

FileInfo fileinfo = new FileInfo(this.logFullPath); if (this.streamPosition > fileinfo.Length) {        this.streamPosition = fileinfo.Length; }

 

Watcher_Changed 이벤트에서는 파일이 변경되었으니, append 된 내용만 알림을 보내면 된다.

 

using (FileStream stream = fileinfo.Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {   stream.Position = this.streamPosition;   using (StreamReader reader = new StreamReader(stream))   {     string mesageContent = reader.ReadToEnd()     // MessageSend(messageContent);   } }

 

이렇게 해서 테스트를 해보면, 실시간 알림이 안오고, 한참 있다가 알림이 오게 되는걸 볼 수 있다.

 

이유는, 윈도우 시스템 (NTFS) 이 파일에 쓰기 버퍼를 사용하기 때문이다.

즉, 일정한 시간이 지나야 버퍼를 실제 물리적인 파일에 저장하기 때문이다.

 

윈도우 시스템이 실시간으로 파일을 변경하도록 하기 위해서는

로그파일을 Open / Close 를 주기적으로 해주면 된다.

 

 

try {   // NTFS 의 파일 캐싱으로 인해 로그파일이 변화를 watcher 가 즉시 탐지하지 못한다.   // 파일 변화를 즉시 감지하기 위해 open 모드로 읽고 닫기를 한다.   File.Open(this.logFullPath, FileMode.Open).Close(); } catch (Exception) { }

 

try catch 를 쓰는 이유는, 로그 파일을 mysql 이 잡고 있기 때문에, Exception 이 발생하기 때문이다.

하지만, 이렇게 주기적으로 해당 파일을 건들어 주면, 윈도우 시스템이 버퍼를 파일로 저장하는 효과를 얻을 수 있어,

실시간 파일 감시가 가능해지기 때문이다.

 

이렇게 해서, 프로그램을 돌려놓고, slow query 를 발생해보면, 알림이 오는것을 확인할 수 있다.

 

 

네이버웍스 메신저르 알림을 보낸 샘플이다.

 

주의할점은 슬로우 쿼리는 트랜잭션 단위의 쿼리가 아니라, 쿼리문장 단위로 탐지를 한다는 점이다.