In MySQL there is a parameter that enables logging of queries that take more than x seconds to execute calls Slow Queries. Basically what it does is put them in a log file and then check with mysqldumpslow. This mechanism may be useful to improve the overall performance of the database if we reduce the value of a progressive manner to the execution times of "leave" before declaring a query such as "slow" or slow query.
To activate it you must:
- Check that is not already active from the mysql client
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 77
Server version: 5.0.32-Debian_7etch11-log Debian etch distributionType 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show variables like '%slow%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| log_slow_queries | OFF |
| slow_launch_time | |
+-------------------------+-----------+
2 rows in set (0.00 sec)
- Edit the my.cnf configuration file (usually / etc / mysql / my.cnf), uncomment the following parameters.
[mysqld]
log-slow-queries=/var/log/mysql-slow-queries.log
long_query_time = 1
log-queries-not-using-indexes
- Create the log file and restart the server.
#touch /var/log/mysql-slow-queries.log
#chown mysql.root /var/log/mysql-slow-queries.log
#/etc/init.d/mysql stop
#/etc/init.d/mysql start
- We found that the change has been successful:
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 77
Server version: 5.0.32-Debian_7etch11-log Debian etch distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show variables like '%slow%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| log_slow_queries | ON |
| slow_launch_time| 1 |
+-------------------------+-----------+
2 rows in set (0.00 sec)
To consult can use mysqldumpslow-tx [file], where x is the minimum of consultation. Example:
# mysqldumpslow /var/log/mysql/mysql-slow.log
Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 1 Time=9.00s (9s) Lock=0.00s (0s) Rows=459887.0 (459887), root[root]@localhost select * from table1
Using this tool also detects the "black arts" of some when they build their queries ... or we can complete our Daily checklist and attach here on slow querys.