Mysql slow query log

Slow query logs mainly record some logs with slow queries. We can view those queries from these logs for a long time, and then optimize the queries.

1, View slow query log status

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       | D:\MYSQL\mysql-5.7.20-winx64\data\gaolin-1-slow.log |
+---------------------------+-----------------------------------------------------+

mysql> show variables like '%long%';

+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+
Variable name explain
slow_query_log_file Location of slow query logs
slow_query_log ON means ON, OFF means end
long_query_time The unit is: s, which can be accurate to us. If the query time exceeds this length, it will be recorded in the slow query log
slow_launch_time If the thread takes longer to establish than this value, the slow? Launch? Time counter will increase

2, Solve the problem of log opening

3, Modify parameters related to slow query log

Enable slow query log

mysql> set global slow_query_log=1;

Modify the slow query time value of the current session. Once we stop the current session, the long query time value will be restored

mysql> set session long_query_time=0.001;

#

mysqldumpslow view slow query log analysis

Tags: MySQL Session

Posted on Fri, 31 Jan 2020 04:07:46 -0800 by imderek