“This is the fifth day of my participation in the First Challenge 2022. For details: First Challenge 2022

Abstract: Slow query logs can help DBAs find SQL statements that execute slowly, which can help optimize the database

🍁 1. An overview

🐴 1.1 features

The slow query log of MySQL is used to record statements whose response time exceeds the threshold. In particular, the SQL whose response time exceeds the value of e long_query_time is recorded in the slow query log. The default value of long_query_time is 10, which means that the statement is run for more than 10 seconds. By default, the Mysql database does not enable slow query logging. You need to manually set this parameter. However, it is not recommended to enable this parameter unless it is necessary for tuning, because slow query logging may affect performance. Slow query logging supports writing log records to files as well as to database tables.Copy the code

🐴 1.2 points

A slow query log is generated only when no result is returned within a specified period of time. • Slow query logs can help DBAs identify SQL statements that execute slowly, helping with database optimization. • Slow log query is disabled by default. You are advised to enable slow log query. • Manually enable when sampling and analysis is required.Copy the code

Mysql > show variables like ‘%long_query_time%’;

🍁 2. Slowly query log usage

🐴 2.1 Enabling logs

Slow_query_log: specifies whether the slow_query_log function is enabled. (Mandatory) long_query_time: specifies whether the slow_query_time function is enabled. If the value exceeds the specified value, the slow_queries are regarded as slow queries and are recorded in the log file (mandatory) log-slow-queries: Slow query log file (optional), automatically create a [hostname]-slow. Log file in \data\ that is, the slow query function can be turned on or off correctly only when the above three conditions are met.Copy the code

Mysql > show variables like ‘%slow_%’;

mysql> set global slow_query_log=1; Mysql > show variables like ‘%slow_%’;

🐴 2.2 Log Modification

To change the slow query duration and log path, perform the following operations: Modify the configuration file my.cnf, Under (mysqld) below to join/mysqld slow_query_log = ON slow_query_log_file = / usr/local/mysql/data/missile. The log long_query_time = 1 MySQL > select sleep(4);Copy the code

⛳️ 3. Analysis tools

We can find by looking at the slow query log that it is very messy, when the data volume is large, several G of log may be generated a day, there is no way to clear analysis. Therefore, here, we use the slow query log analysis tool mysqlDumpslow to analyze logs. Mysqldumpslow --help -s: indicates the sort method: c: access times L: lock time r: return records t: query time al: average lock time AR: Average number of returned records at: average query time -t: number of previous records returned. -g: it is case-insensitive and followed by a regular matching mode. Run the mysqldumpslow -s r -t 10 /var/lib/mysql.log command to obtain the 10 most frequently accessed SQL statements: Mysqldumpslow -s c -t 10 /var/lig/ mysql.show. log Mysqldumpslow - 10 - g s t - t "left the join"/var/lig/mysql/show log suggest another | when using the command structure and more use, otherwise likely explodes the screen:  mysqldumpslow -s r -t 10 /var/lig/mysql/show.log | moreCopy the code

Recommended reading:

MySQL8.0 new features to look at, performance and double 叒 to improve MySQL wulinesocialis, SQL learning will be used to test Linux7.6 source code install Mysql8 Oracle inspection script, server can be directly deployed MySQL root password forget to find a magic way to monitor Zabbix, Oracle listens for log clearing from deployment to application

You can like, collect, pay attention to, comment on me, there are database related questions to contact me or exchange yo ~!