MYSQL 튜닝 및 원인분석을 위한 SLOW로그파일 활용(지정된 시간이상 질의를 계속하는 질의문 잡아내기)

MYSQL 튜닝 및 원인분석을 위한 SLOW로그파일 활용(지정된 시간이상 질의를 계속하는 질의문 잡아내기)

이번에 설명드릴 MYSQL의 로그파일은 MYSQL 질의속도에 대한 튜닝을 하기위한 결정적인 단서를 잡을 수 있는 아주 훌륭한 방법이 될 수 있습니다.   

즉,  MYSQL의 질의에 대한 응답속도가 현저하게 떨어지거나 MYSQL 데이터베이스와 연동되는 특정 웹프로그램이 시스템자원을 현저하게 많이 차지하는 등의 문제가 발생하였다면 이번 예에서 설명드리는 SLOW로그에서 어떤 웹프로그램이 문제인가를 파악해 보시기 바랍니다.   

즉, SLOW로그란 MYSQL의 환경변수인 “long_query_time”의 설정시간(초단위) 이상의 쿼리시간을 가지는 특정SQL 쿼리문에 대한 로그만을 기록하는 로그파일입니다.   

따라서 SLOW로그파일을 활용한다면 어떤 웹프로그램이 쿼리시간(실행시간)을 많이 차지하는가를 확인 할 수 있습니다.   

  즉,  어느날 갑자기 MYSQL의 응답속도가 현저하게 떨어진다면 SLOW로그로 쿼리응답속도가 일정시간 이상 지속되는 쿼리문을 찾아서 조치하면 될 것입니다.   

SLOW쿼리옵션형식 :  --log-slow-queries=슬로우쿼리파일명

아래는 이와 같은 MYSQL의 SLOW로그파일의 활용에 대한 설명입니다.   

먼저, /usr/local/mysql/bin/디렉토리에서 “mysqld_safe”으로 MYSQL을 실행시킬 때에  “--log-slow-queries=”옵션을 사용하여 SLOW쿼리로그를 기록하도록 지정 하였습니다.   

즉, 위의 예에서 SLOW로그파일명으로 SLOW_QUERY_LOG라고 지정을 하였기 때문에 /usr/local/mysql/data/디렉토리에는 SLOW_QUERY_LOG라는 파일명이 생성되어 SLOW쿼리로그를 기록할 것입니다.   

다음은 /usr/local/mysql/data/디렉토리는 위의 예에서 지정한 SLOW쿼리로그를 확인한 것입니다.   

그리고 위의 “SLOW_QUERY_LOG”파일에는 MYSQL의 환경변수 “long_query_time”에 초단위로 지정된 시간보다 초과하는 쿼리문만을 기록합니다.   

  아래는 MYSQL의 환경변수 “long_query_time”의 값을 확인하는 방법을 나타낸 것입니다.   

 즉, mysqladmin이라는 MYSQL유틸리티를 이용하여 MYSQL의 환경변수들을 모두 출력하여 grep으로 “long_query_time”에 대한 부분만을 확인한 것입니다.   

 (mysqladmin에 대한 자세한 설명은 이 책의 “mysqladmin”편을 참조하시기 바랍니다.   

)

위의 결과를 보시면 “long_query_time”의 변수값이 10(초)로 설정되어 있기 때문에 MYSQL의 SQL쿼리문이 10초이상 지속될 때에는 그 쿼리문의 내역을 /usr/local/mysql/data/디렉토리에 있는 SLOW_QUERY_LOG파일에 기록할 것입니다.   

  따라서 이 로그파일을 확인해 본다면 MYSQL의 과부하를 일으키는 웹프로그램파일(SQL쿼리문)을 찾을 수 있을 것입니다.   

결론적으로 이 SLOW로그파일을 활용하시면 어떤 쿼리문에서 MYSQL 과부하를 일으키는지 정확하게 알 수 있기 때문에 필자의 경우에 MYSQL 튜닝방법으로 굉장히 애용하는 방법입니다.