MySQL Slow query

slow query (slow log) can help us navigate to specific SQL statements for SQL statement-level optimizations, for example, the slow query log records those execution times that exceed a given SQL statement, thus locating the problem.

Turn on Slow query

View MySQL DB instance parameters for slow queries

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)

among them,

Slow_query_log is the parameter that turns on slow query

slow_query_log_file is the path to the slow query log file

Log_query_time is the maximum value of the query time, which is logged by slow query logging over this time.

Here is an example of starting a slow query, then setting the maximum query time to 2 seconds, performing sleep3 seconds, and viewing the slow query log

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:1501  16:56:54# [email protected]: 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);

The slow query also has a parameter, log_queries_not_using_indexes, that represents the number of times per minute that an unused index SQL statement is allowed to be logged to the slow query log, with a default of 0, which means there is no limit, but in a production environment, this type of statement takes up a large number of slow query log sizes. Trouble for the DBA's analysis.

In addition to using the log file to view the slow query log, you can also view it as a table. In the database named MySQL mode, there is a table called Slow_log, can record the output of the slow query, just change the output mode of the log log_output, parameter log_output is global dynamic, can be dynamically changed at runtime.

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 Slow_log engine room CSV for this table may not be very efficient for queries, but is highly efficient as an append to the log.

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 is not null,  ' rows_sent ' int (one) not null,  ' rows_examined ' int (one) not null,
   
     ' db ' varchar (+) NOT NULL,  ' last_insert_id ' int (one) not null,  ' insert_id ' int (one) not null,  ' server_id '  Int (ten) unsigned not NULL,  ' sql_text ' mediumtext not NULL,  ' thread_id ' bigint (+) unsigned NOT null) engine=csv DEFAULT Charset=utf8 comment= ' Slow log '
   

Another useful argument for slow queries is that LONG_QUERY_IO indicates that SQL statements that exceed the specified logical IO count (logical IO contains physical IO, which represents the sum of the physical IO and buffer pool reads) are logged to the slow query log.

MySQL Slow query