MySql: Slow queries, queries to optimize control

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:

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

   Type '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)
    

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

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

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