[mysql] SQL 최적화 : slow query log 설정하기

---

sql 최적화 또는 mysql 최적화를 위해서 slow query(=heavy query)를 찾아서 수정하는 것이 가장 효과적이고 일반적인 대응입니다.
이 포스팅은 mysql 에서 slow query를 로그를 수집하기 위한 설정을 설명합니다.

Slow Query Log 설정

MySQL Version 5.1.6 이상을 기준으로 설명합니다. 

slow_query_log 활성화

Slow Query Log 옵션을 활성화합니다. ON으로 설정할 경우 활성화되고, 로그 출력 대상은 log_output시스템 변수에 의해 제어됩니다. 여기서는 slow_query_log_file 통해서 설정합니다. 

set global slow_query_log = 'ON';

 

log_queries_not_using_indexes 활성화

Slow Query Log가 활성화된 상태에서 log_queries_not_using_indexes 변수를 활성화하면 모든 행을 검색할 것으로 예상되는 쿼리가 로그로 기록됩니다.

set global log_queries_not_using_indexes = 'ON'

 

slow_query_log_file 설정

Slow Query Log의 파일의 이름을 설정합니다. 기본값은 host_name-slow.log이고, 임의로 변경할 수 있습니다

set global slow_query_log_file ='/var/lib/mysql/xxx-slow-query.log';

 

long_query_time 설정

퀴리가 long_query_time(초)보다 오래 걸리면 서버는 Slow_queries 상태 변수를 증가시킵니다. slow_query_log가 활성화된 경우 쿼리는 slow_query_log_file에 기록됩니다. 이 값은 CPU 시간이 아닌 실시간으로 측정되므로 부하가 적은 시스템에서는 임계값 미만인 쿼리가 부하가 높은 시스템에서는 임계값보다 높을 수 있습니다
* Slow_queries :  long_query_time 설정된  시간(초) 이상 소요된 쿼리 수입니다. 이 카운터는 slow_query_log의 활성화 여부에 관계없이 증가합니다.

set global long_query_time = 20;

 

설정값 확인

show variables like '%slow%';

---

• Related Links:
  • MYSQL Doc : The Slow Query Log
  • MYSQL Doc : Server Variables 

---