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 를 발생해보면, 알림이 오는것을 확인할 수 있다.
네이버웍스 메신저르 알림을 보낸 샘플이다.
주의할점은 슬로우 쿼리는 트랜잭션 단위의 쿼리가 아니라, 쿼리문장 단위로 탐지를 한다는 점이다.
'데이터베이스' 카테고리의 다른 글
| [SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 3. 인덱스튜닝 (0) | 2025.03.03 |
|---|---|
| [SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 2. SQL 분석도구 (0) | 2025.03.02 |
| [SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 1. SQL 수행 구조 (0) | 2025.03.01 |
| [SQLD/SQLP 요점정리] 2과목. SQL 기본 및 활용 (0) | 2025.02.26 |
| [SQLD/SQLP 요점정리] 1과목. 데이터 모델링의 이해 (0) | 2025.02.25 |
