Thursday, June 23, 2011

Capture slow query in MySQL database

Dynamic change the setting without restarting MySQL Server

C:\mysql-advanced-5.5.13-win32\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13-enterprise-commercial-advanced-log MySQL Enterprise Serve
r - Advanced Edition (Commercial)

Copyright (c) 2000, 2010, 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.

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'slow_query_log_file';
+---------------------+----------------------------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------------------------+
| slow_query_log_file | C:\mysql-advanced-5.5.13-win32\data\B5-Donghua1-slow.log |
+---------------------+----------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.03 sec)

mysql> -- testing query
mysql> select sleep(10) from mysql.db limit 1;
+-----------+
| sleep(10) |
+-----------+
| 0 |
+-----------+
1 row in set (10.00 sec)



---------------------------------------------
Permanent settings in my.ini (my.cnf in Unix/Linux)


# The MySQL server
[mysqld]

slow-query-log = 1
slow_query_log_file = C:\mysql-advanced-5.5.13-win32\data\Donghua1-slow.log
long_query_time = 3