[데이터베이스] MySQL 부하 체크 Slow Query // mysql_slow_log_filter , parser

안녕하세요. 오늘은 MySQL 부하 체크 Slow Query ,mysql_slow_log_filter , parser 에 대해 알아보도록 하겠습니다. 

 

대량의 데이터를 조회하거나 잘못된 인덱스 사용으로 인해 장시간 mysql 프로세스가 물리는 현상이 발생할 경우 어떤 쿼리문이 이런 현상을 야기 시켯는지에 대한 분석 시 slow query를 이용하면 쉽게 부하가 증가되는 쿼리를 찾을 수 있습니다.

- mysql 데몬 구동 시 적용하는 방법

 

$ mysqld_safe --datadir=datadir --log-slow-queries[=file_name] &

< 로그 파일의 내용 >  
# Time: 080218 13:25:06  
# User@Host: XXXXX[xxxxx] @ localhost [127.0.0.1]  
# Query_time: 8  Lock_time: 0  Rows_sent: 1  Rows_examined: 98767  
use CrediMail;  
SELECT count(*) FROM test  
   
위의 양식으로 slow 쿼리가 남게 됩니다.  
slow log 로 남은 쿼리들은 explain 을 사용하여 정상적인 index를 타고 있는지 체크 해 보는 방법으로  
문제점을 찾아 가시면 될 것입니다.  
ex) mysql>explain 문제시 되는 쿼리;  
   
<참고>  
Query_time : 쿼리 처리 시간  
Lock_time : lock 이 걸린 횟수  
Row_sent : 조회 결과 Row 수  
Rows_examined : 조회 대상 Row 수  
   
  
1. Setting my.cnf for log slow queries  
   
MySQL을 운용하다보면 궁극적으로 봉착하는 문제가 바로 Slow queriy 다. 이 Slow query 야 말로 모든 query의 적이라 해도 과언이 아니라고 생각한다. MySQL에서는이 Slow query를 log로 기록할수 있게 되어있는데 이 설정이 꽤나 delicate 하다. 다음은 MySQL 5.0에서 동작하는 설정이다.  



* Check the gloval variables  
   

in the mysql command prompt,  
show global variables WHERE Variable_name LIKE ‘l%’;  
+???????????+?????????+  
| Variable_name | Value |  
+???????????+?????????+  
| language | /usr/share/mysql/english/ |  
| large_files_support | ON |  
| large_page_size | 0 |  
| large_pages | OFF |  
| license | GPL |  
| local_infile | ON |  
| locked_in_memory | OFF |  
| log | OFF |  
| log_bin | OFF |  
| log_bin_trust_function_creators | OFF |  
| log_error | |  
| log_slave_updates | OFF |  
| log_slow_queries | ON |  
| log_warnings | 1 |  
| long_query_time | 3 |  
| low_priority_updates | OFF |  
| lower_case_file_system | OFF |  
| lower_case_table_names | 0 |  
+???????????+?????????+  
* Configuration  
   

[mysqld]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
# Default to using old password format for compatibility with mysql 3.x  
# clients (those using the mysqlclient10 compatibility package).  
old_passwords=1  
# added 1007/10/05  
max_connections = 10000  
table_cache = 256  
wait_timeout = 900  
max_connect_errors = 10000  
key_buffer = 16M  
sort_buffer = 1M  
set-variable = long_query_time=3  
log-slow-queries=/var/log/mysqld-slow.log  
[mysql.server]  
user=mysql  
basedir=/var/lib  
[mysqld_safe]  
log-error=/var/log/mysqld.log  
#log-slow-queries=/var/log/mysqld-slow.log  
pid-file=/var/run/mysqld/mysqld.pid  
# added 2007/10/05  
#set-variable = long_query_time=3  
#log-slow-queries=/var/log/mysqld-slow.log  
   
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]  
Parse and summarize the MySQL slow query log. Options are  
  --verbose    verbose  
  --debug      debug  
  --help       write this text to standard output  
  -v           verbose  
  -d           debug  
  -s ORDER     what to sort by (t, at, l, al, r, ar etc), 'at' is default  
  -r           reverse the sort order (largest last instead of first)  
  -t NUM       just show the top n queries  
  -a           don't abstract all numbers to N and strings to 'S'  
  -n NUM       abstract numbers with at least n digits within names  
  -g PATTERN   grep: only consider stmts that include this string  
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),  
               default is '*', i.e. match all  
  -i NAME      name of server instance (if using mysql.server startup script)  
  -l           don't subtract lock time from total time  
   
   
2. Slow query filter   
http://www.mysqlperformanceblog.com/files/utils/mysql_slow_log_filter  
   
용도  : slow query log 에서 실행 시간이 일정 시간 이상이 되는 쿼리 와 일정 row 이상 access하는 쿼리만 filter 해 낼 수 있음  
   
사용법  
   
# cat ‘slow query log’ msql_slow_log_filter -T timesec -R numrows  ( T, R option은 단독으로 사용할 수 있음 )  
Ex)  
# cat  kidsmbbs1-slow.log | msql_slow_log_filter  -R 400000  ( 400000 row 이상 access 하는 쿼리만 추출 )  
   
SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_comment_121`;  
# Time: 070307  5:01:11  
# Query_time: 4  Lock_time: 0  Rows_sent: 31864  Rows_examined: 455198  
select distinct code from tb_comment_121;  
# Time: 070307  5:06:17  
# Query_time: 5  Lock_time: 0  Rows_sent: 455596  Rows_examined: 455596  
SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_comment_121`;  
# Time: 070308  5:01:09  
# Query_time: 4  Lock_time: 0  Rows_sent: 31905  Rows_examined: 455596  
select distinct code from tb_comment_121;  

   

   
# cat  kidsmbbs1-slow.log | msql_slow_log_filter  -T 8  ( 8초 이상 걸리는 쿼리만 추출 )  
   

3. Aggregating slow query log   
http://www.mysqlperformanceblog.com/files/utils/mysql_slow_log_parser   
    
용도 : slow query log 에 있는 slow query에 대해서 aggregate 한 정보를 보여줌, 즉 실행 횟수, 평균 수행 시간, 총 수행 시간 , access 한 rows  , 개인적으로 mysqldumpslow 보다 보기  좋은 것 같습니다.   
    
### 4 Queries   
### Total time: 12, Average time: 3   
### Taking 3 , 3 , 3 , 3  seconds to complete   
### Rows analyzed 144673, 150153, 185656 and 189447   


> SELECT /*!XXX SQL_NO_CACHE */ * FROM `tb_bbs_limit`;   
> SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_bbs_limit`;   
    
    
### 2 Queries   
### Total time: 10, Average time: 5   
### Taking 5 , 5  seconds to complete   
### Rows analyzed 78261 and 81439   
use ykids_bbs;   
SELECT /*!XXX SQL_NO_CACHE */ * FROM `tb_bbs_XXX`;