当前位置：网站首页>isn't it?Is there anyone who can't locate the slow query problem of MySQL online?

isn't it?Is there anyone who can't locate the slow query problem of MySQL online?

2022-08-05 11:47:00 【InfoQ】

1. The role of the slow query log

The slow query log is not enabled by default. It is recommended to manually enable it to help us locate online problems.

SQL whose execution time exceeds the threshold will be written to the slow query log, which can help us record SQL statements that execute too long, locate online slow SQL problems, and facilitate SQL performance tuning.

2. Configuration of slow query log

2.1 Check whether slow query log is enabled

show variables like 'slow_query_log';

The default is OFF, not enabled, which can be turned on manually.

2.2 Open slow query log

One way is to use MySQL command to open:

set global slow_query_log=1;

Another way is to modify MySQLAfter the configuration file is restarted, the MySQL service is turned on.

Modify the configuration file my.cnf and add the following command
slow_query_log = ON

2.3Setting the threshold for slow query logs

The default threshold for slow query logs is 10, in seconds.

For online service, 10 seconds is too long, we can modify it manually.

One isModify by MySQL command, for example, modify it to 1 second:

set long_query_time=1;

Another method is to modify the MySQL configuration file, restart the MySQL service, and enable it.

Modify the configuration file my.cnf and add the following command
long_query_time = 1

2.4 Modifying the slow query log location

Use MySQL commands to view slow queriesLog location:

show variables like '%slow_query_log_file%';

If you want to modify the slow query log location, you can modify the MySQL configuration file, restart the MySQL service, and enable it.

Modify the configuration file my.cnf and add the following command
slow_query_log_file = /usr/local/mysql/data/localhost_slow.log

2.5 Record more slow query SQL

By default, management statements will not be recorded in the slow query log. Management statements include ALTER TABLE, ANALYZE TABLE, CHECKTABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, REPAIR TABLE, etc.

The management statement is alsoVery important, if you want to be logged, you can modify it through the MySQL command:

set global log_slow_admin_statements=ON;

By default, statements that do not use an index will not be logged.

Enough to deceive people!If you are not careful, you will fall into the pit. If you don't record the statements that do not use the index, why do you need to query the log slowly?

Want to recordStatements that do not use indexes can be modified by the command:

set global log_queries_not_using_indexes=ON;

3. Use of slow query log

Manually create a slow SQL to test the effect, userThere are 1 million meter data in the table:

select * from user;

Then take a look at the content of the slow query log file:

cat /usr/local/mysql/data/localhost_slow.log

SQL statements and execution times are logged.

4. Analyze slow query logs

Sometimes there are many slow query logs, and it is not very convenient to view them manually. Fortunately, MySQL provides tools for analyzing slow query logs

mysqldumpslow

Common parameters are

-s: Indicates which way to sort:
 c: number of visits
 l: lock time
 r:Return records
 t: query time
 al: average lock time
 ar: average number of returned records
 at: average query time
-t: how many previous records are returnedData;