대량의 데이터를 조회하거나
잘못된 인덱스 사용으로 인해 장시간 Mysql 프로세스가 물리는 현상이 발생 할 경우
어떤 쿼리문이 이런 현상을 야기 시켰느냐에 대한 분석 시
Slow Query 를 사용하시면 손 쉽게 부하가 증가되는 쿼리를 찾으실 수 있으실 것입니다.
<사용 방법>
1. mysql 환경 설정 파일인 my.cnf 파일에 아래 내용을 추가 하시면 됩니다.
[mysqld]
long_query_time=초제한(1~10)
log-slow-queries=로그파일명
ex)
[mysqld]
log-slow-queries = /temp/mysql-slow.log
long_query_time = 3
위의 의미는 쿼리타임이 3초를 초과하는 쿼리에 대해 /temp/mysql-slow.log 파일에 로그를 남기라는 의미 입니다.
2. mysql 구동시 적용하는 방법이 있습니다.
$ mysqld_safe --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 수
Reference URL
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
http://www.sitepoint.com/forums/showthread.php?t=397521
Setting my.cnf for log slow queries
MySQL을 운용하다보면 궁극적으로 봉착하는 문제가 바로 Slow queriy 다. 이 Slow query 야말로 모둔 query의 적이라 해도 과언이 아니라고 생각한다. MySQL에서는이 Slow query를 log로 기록할수 있게 되어있는데 이 설정이 꽤나 delicate 하다. 다음은 MySQL 5.0에서 동작하는 설정이다.
1. 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 |
+———————————+—————————+
2. 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
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초 이상 걸리는 쿼리만 추출 )
SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_bbs_154`;
# Time: 070306 5:00:40
# Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 329849
select count(indexno) as su from tb_comment_160 where wdate>=1173020400 and wdate<=1173106800;
# Time: 070306 14:13:22
# Query_time: 15 Lock_time: 0 Rows_sent: 37014 Rows_examined: 37014
SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_bbs_154`;
# Time: 070307 5:00:40
# Query_time: 14 Lock_time: 0 Rows_sent: 37014 Rows_examined: 37014
SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_bbs_154`;
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`;
출처 : http://leepfe.tistory.com/163
