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 run, call 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 "leave" before declaring a query such as "slow" or slow query.

To activate it we 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 was 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)

For reference we 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 (459,887), root [root] @ localhost select * from table1

Using this tool also allows the "black arts" of some when building your queries ...or we can complete our daily checklist and attach. More info here on slow querys.