위키
포럼
도구
바뀐글
랜덤

"MySQL 슬로우 쿼리 로그 설정"의 두 판 사이의 차이

85번째 줄: 85번째 줄:
==테스트==
==테스트==
<source lang='dos'>
<source lang='dos'>
[root@jmnote ~]# mysql -uroot -pP@ssw0rd
[root@jmnote ~]# mysql -uroot -pP@ssw0rd -e 'SELECT SLEEP(4)'
Welcome to the MySQL monitor.  Commands end with ; or \g.
+----------+
Your MySQL connection id is 95
| SLEEP(4) |
Server version: 5.5.34-log MySQL Community Server (GPL)
+----------+
 
|        0 |
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
+----------+
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
</source>
</source>
<source lang='dos'>
<source lang='dos'>
mysql> use mysql;
[root@jmnote ~]# mysql -uroot -pP@ssw0rd -e 'SELECT SLEEP(4)'
Reading table information for completion of table and column names
+----------+
You can turn off this feature to get a quicker startup with -A
| SLEEP(4) |
 
+----------+
Database changed
|        0 |
+----------+
</source>
</source>
<source lang='dos'>
mysql> SELECT COUNT(DISTINCT a.name) FROM help_topic a, help_topic b;
+------------------------+
| COUNT(DISTINCT a.name) |
+------------------------+
|                    511 |
+------------------------+
1 row in set (4.91 sec)
</source>
<source lang='dos'>
mysql> SELECT COUNT(DISTINCT a.name) FROM help_topic a, help_topic b;
+------------------------+
| COUNT(DISTINCT a.name) |
+------------------------+
|                    511 |
+------------------------+
1 row in set (4.60 sec)
</source>
<source lang='dos'>
mysql> Ctrl-C -- exit!
Aborted
</source>
:→ {{키|Ctrl|C}} 눌러 종료
<source lang='dos'>
<source lang='dos'>
[root@jmnote ~]# cat /var/log/mysql/log-slow-queries.log
[root@jmnote ~]# cat /var/log/mysql/log-slow-queries.log
133번째 줄: 105번째 줄:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                Id Command    Argument
Time                Id Command    Argument
# Time: 140803 23:09:40
# Time: 140803 23:19:41
# User@Host: root[root] @ localhost []
# User@Host: root[root] @ localhost []
# Query_time: 4.905590 Lock_time: 0.000148 Rows_sent: 1  Rows_examined: 511
# Query_time: 4.001873 Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use mysql;
SET timestamp=1407075581;
SET timestamp=1407074980;
SELECT SLEEP(4);
SELECT COUNT(DISTINCT a.name) FROM help_topic a, help_topic b;
# Time: 140803 23:19:50
# Time: 140803 23:09:46
# User@Host: root[root] @ localhost []
# User@Host: root[root] @ localhost []
# Query_time: 4.600852 Lock_time: 0.000120 Rows_sent: 1  Rows_examined: 511
# Query_time: 4.001939 Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1407074986;
SET timestamp=1407075590;
SELECT COUNT(DISTINCT a.name) FROM help_topic a, help_topic b;
SELECT SLEEP(4);
</source>
</source>


2014년 8월 3일 (일) 23:20 판

MySQL 슬로우 쿼리 로그 설정
MySQL 느린 쿼리 로그 설정

1 방법

vi /etc/my.cnf

[mysqld]
log-slow-queries = 로그파일명
long_query_time = 시간(초)
→ [mysqld] 아래에 2줄을 추가한다.

2 실습

현재 설정 확인
[root@jmnote ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet=4M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
→ 슬로우 쿼리 설정 없음 확인
로그 폴더 생성
[root@jmnote ~]# mkdir /var/log/mysql
[root@jmnote ~]# chown mysql:mysql /var/log/mysql

[root@jmnote ~]# ll /var/log/ | grep mysql
drwxr-xr-x 2 mysql mysql    4096 Aug  3 22:46 mysql
-rw-r----- 1 mysql mysql   53059 Jun 15 02:08 mysqld.log
로그 설정
[root@jmnote ~]# vi /etc/my.cnf

[mysqld]의 아래 적당한 곳에 다음 내용 기입 

long_query_time=3
log-slow-queries=/var/log/mysql/log-slow-queries.log
설정 확인
[root@jmnote ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet=4M
long_query_time=3
log-slow-queries=/var/log/mysql/log-slow-queries.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
MySQL 재시작 (설정 적용)
[root@jmnote ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

[root@jmnote ~]# cat /var/log/mysql/log-slow-queries.log
/usr/libexec/mysqld, Version: 5.5.34-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

3 테스트

[root@jmnote ~]# mysql -uroot -pP@ssw0rd -e 'SELECT SLEEP(4)'
+----------+
| SLEEP(4) |
+----------+
|        0 |
+----------+

[root@jmnote ~]# mysql -uroot -pP@ssw0rd -e 'SELECT SLEEP(4)'
+----------+
| SLEEP(4) |
+----------+
|        0 |
+----------+

[root@jmnote ~]# cat /var/log/mysql/log-slow-queries.log
/usr/libexec/mysqld, Version: 5.5.34-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 140803 23:19:41
# User@Host: root[root] @ localhost []
# Query_time: 4.001873  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1407075581;
SELECT SLEEP(4);
# Time: 140803 23:19:50
# User@Host: root[root] @ localhost []
# Query_time: 4.001939  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1407075590;
SELECT SLEEP(4);

4 같이 보기

5 참고 자료

원본 주소 "https://zetawiki.com/w/index.php?title=MySQL_슬로우_쿼리_로그_설정&oldid=75373"
문서 댓글 ({{ doc_comments.length }})
{{ comment.name }} {{ comment.created | snstime }}