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.
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.