MySQL slow Query

MySQL slow Query

The slow query (slow log) helps us locate specific SQL statements for SQL statement optimization. For example, the slow query log records the SQL statements whose execution time exceeds the given value, locate the problem.

Enable slow Query

View MySQL database instance slow query parameters

1. Mysql> show variables like '% slow % ';
2. + --------------------------- + ------------------------------------- +
3. | Variable_name | Value |
4. + --------------------------- + ------------------------------------- +
5. | Log_slow_admin_statements | OFF |
6. | Log_slow_slave_statements | OFF |
7. | Slow_launch_time | 2 |
8. | Slow_query_log | OFF |
9. | Slow_query_log_file |/var/lib/mysql/localhost-slow.log |
10. + --------------------------- + ------------------------------------- +
11. 5 rows in set (0.00 sec)
13. Mysql> show variables like '% long_query_time % ';
14. + ----------------- + ----------- +
15. | Variable_name | Value |
16. + ----------------- + ----------- +
17. | Long_query_time | 10.000000 |
18. + ----------------- + ----------- +
19. 1 row in set (0.02 sec)

mysql> show variables like '%slow%';+---------------------------+-----------------------------------+| Variable_name             | Value                             |+---------------------------+-----------------------------------+| log_slow_admin_statements | OFF                               || log_slow_slave_statements | OFF                               || slow_launch_time          | 2                                 || slow_query_log            | OFF                               || slow_query_log_file       | /var/lib/mysql/localhost-slow.log |+---------------------------+-----------------------------------+5 rows in set (0.00 sec)mysql> show variables like '%long_query_time%';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.02 sec)

Where,

Slow_query_log is the parameter for enabling slow query.

Slow_query_log_file is the path of the slow query log file.

Log_query_time is the maximum query time. When this time is exceeded, log records are queried slowly.

The following example shows how to start a slow query, set the maximum query time to 2 seconds, and run sleep3 seconds to view the slow query log.

1. Mysql> set global slow_query_log = ON;
2. Query OK, 0 rows affected (0.03 sec)
4. Mysql> set long_query_time = 2;
5. Query OK, 0 rows affected (0.00 sec)
7. Mysql> show variables like '% long_query_time % ';
8. + ----------------- + ---------- +
9. | Variable_name | Value |
10. + ----------------- + ---------- +
11. | Long_query_time | 2.000000 |
12. + ----------------- + ---------- +
13. 1 row in set (0.00 sec)
15. Mysql> select sleep (3 );
16. + ---------- +
17. | Sleep (3) |
18. + ---------- +
19. | 0 |
20. + ---------- +
21. 1 row in set (3.00 sec)
23. Mysql> system cat/var/lib/mysql/localhost-slow.log
24. /Usr/sbin/mysqld, Version: 5.6.22-log (MySQL Community Server (GPL). started:
25. Tcp port: 3306 Unix socket:/var/lib/mysql. sock
26. Time Id Command Argument
27. # Time: 150102 16:56:54
28. # User @ Host: root [root] @ localhost [] Id: 1
29. # Query_time: 3.001084 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
30. Use test2;
31. SET timestamp = 1420189014;
32. Select sleep (3 );

mysql> set global slow_query_log=ON;Query OK, 0 rows affected (0.03 sec)mysql> set long_query_time=2;Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%long_query_time%';+-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| long_query_time | 2.000000 |+-----------------+----------+1 row in set (0.00 sec)mysql> select sleep(3);+----------+| sleep(3) |+----------+|        0 |+----------+1 row in set (3.00 sec)mysql> system cat /var/lib/mysql/localhost-slow.log/usr/sbin/mysqld, Version: 5.6.22-log (MySQL Community Server (GPL)). started with:Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sockTime                 Id Command    Argument# Time: 150102 16:56:54# User@Host: root[root] @ localhost []  Id:     1# Query_time: 3.001084  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0use test2;SET timestamp=1420189014;select sleep(3);

Slow query also has a parameter log_queries_not_using_indexes, which indicates the number of times that no index is used to record slow query logs per minute. The default value is 0, indicating no limit, but in the production environment, these statements occupy a large number of slow query logs, which causes difficulties for DBA analysis.

In addition to viewing slow query logs using log files, you can also view logs using tables. In mysql mode, a table named slow_log records the output of slow queries. You only need to change the log output mode log_output. The log_output parameter is global and dynamic, it can be dynamically changed at runtime.

1. Mysql> show variables like '% log_output % ';
2. + --------------- + ------- +
3. | Variable_name | Value |
4. + --------------- + ------- +
5. | Log_output | FILE |
6. + --------------- + ------- +
7. 1 row in set (0.00 sec)
9. Mysql> set global log_output = 'table ';
10. Query OK, 0 rows affected (0.00 sec)
12. Mysql> select sleep (3 );
13. + ---------- +
14. | Sleep (3) |
15. + ---------- +
16. | 0 |
17. + ---------- +
18. 1 row in set (3.00 sec)

mysql> show variables like '%log_output%';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output    | FILE  |+---------------+-------+1 row in set (0.00 sec)mysql> set global log_output='table';Query OK, 0 rows affected (0.00 sec)mysql> select sleep(3);+----------+| sleep(3) |+----------+|        0 |+----------+1 row in set (3.00 sec)

Note that the engine room csv of the slow_log table may not be very efficient in query, but it is very efficient to append and insert logs.

1. Mysql> show create table slow_log \ G
2. * *************************** 1. row ***************************
3. Table: slow_log
4. Create Table: create table 'slow _ log '(
5. 'Start _ time' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
6. 'User _ host' mediumtext not null,
7. 'Query _ time' time not null,
8. 'Lock _ time' time not null,
9. 'Rows _ sent' int (11) not null,
10. 'Rows _ examined' int (11) not null,
11. 'Db' varchar (512) not null,
12. 'Last _ insert_id 'int (11) not null,
13. 'Insert _ id' int (11) not null,
14. 'Server _ id' int (10) unsigned not null,
15. 'SQL _ text' mediumtext NOT NULL,
16. 'Thread _ id' bigint (21) unsigned NOT NULL
17. ) ENGINE = csv default charset = utf8 COMMENT = 'lowlog'

mysql> show create table slow_log\G*************************** 1. row ***************************       Table: slow_logCreate Table: CREATE TABLE `slow_log` (  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `user_host` mediumtext NOT NULL,  `query_time` time NOT NULL,  `lock_time` time NOT NULL,  `rows_sent` int(11) NOT NULL,  `rows_examined` int(11) NOT NULL,  `db` varchar(512) NOT NULL,  `last_insert_id` int(11) NOT NULL,  `insert_id` int(11) NOT NULL,  `server_id` int(10) unsigned NOT NULL,  `sql_text` mediumtext NOT NULL,  `thread_id` bigint(21) unsigned NOT NULL) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

Another useful parameter for slow queries is long_query_io, which indicates that the logical IO count will exceed the specified number of logical IO reads (logical IO includes physical IO, indicating the sum of the number of reads of physical IO and buffer pool) the SQL statement is recorded in the slow query log.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: