Activities

December 2009
M T W T F S S
« Nov   Jan »
 123456
78910111213
14151617181920
21222324252627
28293031  

Enable slow log query on MySQL

Question.

I want to Monitor the query which is taking longer execution time and killing server resource badly. Pls note that “set sql_log_off = ‘ON’; will not work on older version of Mysql 5.0.x.

Solution.

1. Create mysql log directory and permit mysql user to write on it
#mkdir /var/log/mysql
#touch /var/log/mysql/log-slow-queries.log
#chown mysql.mysql -R /var/log/mysql
2. Then stop the mysql server

[root@rc-169 ~]# service mysql stop
Shutting down MySQL...                                     [  OK  ]
[root@rc-169 ~]#

3. Running mysql with slow log query logging enabled.
# mysqld_safe –log-slow-queries=/var/log/mysql/mysql-slow.log&
Or you can add the lines on my.cnf to enabling logging all the time mysql starts

[root@web-test1 ~]# vi /etc/my.cnf

and added the two lines set-variable=long_query_time=1 and log-slow-queries=/var/log/mysql/log-slow-queries.log under mysqld daemon.

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

then save

#chown mysql.mysql -R /var/log/mysql-slow-queries.log

The following script will create a slow log query for the testing purpose

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> select BENCHMARK(10000000, MD5('sysadmin'));
+--------------------------------------+
| BENCHMARK(10000000, MD5('sysadmin')) |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (15.32 sec)
mysql>

We can enable general query log for quick debugging on latest Mysql revision. What we have to do is just execute the command from the terminal. As it is a logging of all queries executed on the mysql, the file size will be increased drastically, So use it wisely. enable and disable for a short period.

mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

This command will start to write sql query executed on a log file under data directory (/var/lib/mysql)

If you want to store the query on a table
you may choose this option

mysql > set global log_output='table';

The above will showing enabling general_log at run time. But this is not a good approch to set for a long time since general logs are growing up rapidly. The best way is set this on a “my.cnf” file

How do I enable general_log in Mysql using my.cnf

Edit the file “my.cnf

[root@rc-167 ~]# cat /etc/my.cnf
[mysqld]
datadir= /var/lib/mysql/
socket= /var/lib/mysql/mysql.sock
max_connections = 500
default-storage-engine=MYISAM
long_query_time         = 1
log-slow-queries        = /var/log/mysql/mysql-slow.log
log = /var/log/mysqld.general.log

Here,
long_query_time, log-slow-queries : Used for catching mysql slow log when a query is taking more than 1 second to execute.
log : Used to logging all the mysql queries executed by database engine and this is only for debugging purpose.

You need to turn iff thus feature immediately after your testing. Otherwise this file would take bigger size and causing disk space issue.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>