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
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
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.
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
The following script will create a slow log query for the testing purpose
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.
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
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”
[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