MySQL 5.0 에서 slow query log 설정 > 서버관리

• 목록

근데, 잘 안된다. ㅠㅠ

====

MySQL 버전이 올라가면서 slow query 로그 설정이 바뀌었다.
그래서, 5.0 버전에서의 느린 쿼리 세팅을 정리해 둔다


1. 로그가 저장될 파일을 수동으로 만들고 mysql 에게 쓰기 권한을 줘야 한다.

touch /var/log/mysql_query.log
chown mysql.mysql /var/log/mysql_query.log

2. my.cnf 설정

[mysqld]
long_query_time=1   
log-slow-queries=/var/log/mysql_query.log

3. mysql 재시작



** 출처 : http://tanveerhaiderbaig.blogspot.kr/2013/10/mysql-50-slow-query-log.html

Friday, October 11, 2013

MYSql 5.0 Slow Query Log

Performance is always tricky to maintain. few days back I heard from users that they are having some performance issues on site , search is not working as required and some other
components.Developers as always have no clue :) , so I decided to start slow query log.It is easy in 5.1 or above and you don't need to restart mysqld. I am using mysql 5.0
on CentOs,so I have to edit some configurations files and a restart of mysqld service.
I found it handy when you don't have clue from application , so let rdbms work for you.


Step-1 : Create directory where you need to have log file. I have created it in /backup
mkdir    /backup/mysql_slow_log

Step-2 : Create log file .If you will edit configurations and restart mysqld, it will not create file for you, so you have to make it manually.
touch /backup/mysql_slow_log/slow.log

Step-3 : setting folder permissions.
chown mysql.mysql -R /backup/mysql_slow_log/

Step-4 : edit mysql configurations for logging. You need to add 2 parameters under "[mysqld]" in /etc/my.cnf (your mysql conf file) . you can set more parameter like  " log_queries_not_using_indexes" etc
1-log-slow-queries  =log file location
2-long_query_time =time in seconds . if you set it to 1, it mean log queries that are consuming 1 or more seconds.

vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
log-slow-queries=/backup/mysql_slow_log/slow.log
long_query_time =1   

Step-5 : Restart mysql service
service mysqld restart

At this stage mysql will start writing log file for you can use mysqldumpslow to make more readable format
mysqldumpslow /backup/mysql_slow_log/slow.log

if you want to disable logging, just remove entries from my.cnf/ini and restart service.

추천0