Analysis of the Principles of MySQL Slow Query Related Parameters

MySQL slow query, whose full name is slow query log, is a log record provided by MySQL, which is used to record statements in MySQL whose response time exceeds the threshold. In a specific environment, SQL statements whose running time exceeds the long_query_time value will be recorded in the slow query log. The default value of long_query_time is 10, which means that statements that run for more than 10 seconds are logged. By default, the MySQL database does not start the slow query log, and this parameter needs to be set manually.

Of course, if it is not necessary for tuning, it is generally not recommended to enable this parameter, because enabling slow query logs will more or less have a certain performance impact.

In addition, the slow query log supports writing log records to files and database tables.

MySQL slow query related parameter explanation:

• l slow_query_log: Whether to enable the slow query log, 1 means enabled, 0 means disabled.
• l log-slow-queries: The storage path of the slow query log of the old version (version below 5.6) of MySQL database. You can leave this parameter unset, the system will default to a default file host_name-slow.log
• l slow-query-log-file: The storage path of the slow query log of the new version (5.6 and above) of the MySQL database. You can leave this parameter unset, the system will default to a default file host_name-slow.log
• l long_query_time: slow query threshold. When the query time exceeds the set threshold, a log is recorded.
• l log_queries_not_using_indexes: Queries that do not use indexes are also recorded in the slow query log (optional).
• l log_output: log storage method. log_output='FILE' means saving logs to files. The default value is 'FILE'. log_output='TABLE' means storing the logs in the database.

So how do you configure the slow query log? By default, the value of slow_query_log is OFF, which means that the slow query log is disabled. You can enable it by setting the value of slow_query_log as follows:

show variables like '%slow_query_log%';

+----------------------+-----------------------------------------------+
| Variable_name | Value |
+----------------------+-----------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
+----------------------+------------------------------------------------+

2 rows in set (0.00 sec)

Using set global slow_query_log=1 to enable the slow query log only takes effect on the current database and will become invalid after MySQL is restarted. If you want it to take effect permanently, you must modify the configuration file my.cnf (the same applies to other system variables)

set global slow_query_log=1;

To add or modify the slow_query_log and slow_query_log_file parameters in my.cnf, as shown below:

slow_query_log = 1

slow_query_log_file = /tmp/mysql_slow.log

The slow_query_log_file parameter is used to specify the storage path of the slow query log. The default is host_name-slow.log file.

show variables like 'slow_query_log_file';

+---------------------+-----------------------------------------------+

 | Variable_name | Value |

 +---------------------+-----------------------------------------------+

 | slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |

 +---------------------+-----------------------------------------------+

 1 row in set (0.00 sec)

After the slow query log is enabled, what kind of SQL will be recorded in the slow query log? This is controlled by the parameter long_query_time. By default, the value of long_query_time is 10 seconds. It can be modified using the command or in the my.cnf parameter.

The situation where the running time is exactly equal to long_query_time will not be recorded; that is, in the MySQL source code, it is judged to be greater than long_query_time, rather than greater than or equal to.

Starting from MySQL 5.1, long_query_time begins to record the SQL statement execution time in microseconds, which was previously recorded only in seconds. If recorded in the table, only the integer part will be recorded, not the microsecond part

Note that in some cases when you use the local setting method, the following may occur, see below:

show variables like 'long_query_time%';
# View the current long_query_time +-----------------+-----------+

 | Variable_name | Value |

 +-----------------+-----------+

 | long_query_time | 10.000000 |

 +-----------------+-----------+

set global long_query_time=4;
# Set the current long_query_time time show variables like 'long_query_time';
# Check the long_query_time again

As shown above, the variable long_query_time is modified, but the value of the query variable long_query_time is still 10. Is it possible that it has not been modified? Note: After using the command set global long_query_time=4 to modify the time, you need to reconnect or open a new session to see the modified value. Use show variables like 'long_query_time' to view the variable value of the current session only. You can also use show global variables like 'long_query_time'; instead of reconnecting the session.

The log_output parameter specifies how the slow query log is stored:

l log_output='FILE' means to save the logs into a file. The default value is also 'FILE'.

l log_output='TABLE' means to store the logs in the database, so that the log information will be written to the mysql.slow_log table. It also supports two log storage methods, which can be separated by commas when configuring, such as: log_output='FILE,TABLE'.

Logging to the system's dedicated log table consumes more system resources than logging to files. Therefore, if you need to enable slow query logs and obtain higher system performance, it is recommended to log to files first.

show variables like '%log_output%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output | FILE |

+---------------+-------+

set global log_output='TABLE';

show variables like '%log_output%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output | TABLE |

+---------------+-------+

select sleep(5) ;

+----------+

| sleep(5) |

+----------+

| 0 |

+----------+

After we execute sleep5 above, this operation will be recorded in the slow query log. Let's take a look:

mysql> select * from mysql.slow_log;

 +---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+

 | start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |

 +---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+

 | 2016-06-16 17:37:53 | root[root]@localhost [] | 00:00:03 | 00:00:00 | 1 | 0 | | 0 | 0 | 1 | select sleep(3) | 5 |

 | 2016-06-16 21:45:23 | root[root]@localhost [] | 00:00:05 | 00:00:00 | 1 | 0 | | 0 | 0 | 1 | select sleep(5) | 2 |

 +---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.