How does MySQL clear slow Query files,

How does MySQL clear slow Query files,

I. Overview

This chapter mainly describes how to generate a new slow Query file online when the slow Query file is large.

Test environment: mysql 5.6.21

Step 2

Configure slow Query

The default my. cnf file is in the/etc/directory.

Vim/etc/my. cnf

# Slow_queryslow_query_log = 1 # enable slow query slow_query_log_file =/var/lib/mysql/mysql-slow.log # Set the slow query path long_query_time = 0.01 # Set the slow query time

The configuration takes effect only after the mysql service is restarted.

Query whether the slow query configuration is successful

Test slow Query

use information_schema;select * from tables;

The statement execution time took 1.1 seconds and 314 rows of records were returned;

View slow Query files:Cat mysql-slow.log

The slow query log file records the slow query records every day. Each record starts with Time:. It records the logon information, query Time, and lock Time, number of returned rows, number of scanned records, and executed statements.

Generate slow query log files online

1. Disable global slow Query

Copy codeThe Code is as follows: SET global slow_query_log = 0

Whether to disable global slow Query

Copy codeThe Code is as follows: show variables like '% query_log % ';

Set a new slow Query file

Copy codeThe Code is as follows: SET global slow_query_log_file = '/var/lib/mysql/mysql-slow_new.log'

Enable slow Query

Copy codeThe Code is as follows: SET global slow_query_log = 1;

Copy codeThe Code is as follows: show variables like '% query_log % ';

A new slow Query file is generated in the slow query path.

The slow query information is recorded in the new log file,

At this time, we can archive the slow Query files before mv.

Restart mysql Service

Service mysql restart

Copy codeThe Code is as follows: show variables like '% query_log % ';

After the mysql service is restarted, the slow log file will be changed to the previous one in my. cnf file, so if you want to restart the service after the slow log file or just set the mysql-slow_new.log, You need to modify the global settings while modifying my. cnf file to ensure that the file is modified after restart.

Summary

Configuring slow queries is helpful for collecting statements with poor performance at ordinary times, and many tools are dedicated to analyzing slow query logs. percona-toolkit is a good tool for analyzing slow queries, if you are not using it, you can check it out.

The above is all the content of this article, hoping to help you learn.

Articles you may be interested in:

• How does mysql enable slow query log records?
• MySQL slow query and Optimization Test
• Summary of slow query analysis caused by MySQL prefix Index
• In-depth explanation of mysql slow query settings
• Mysql correctly and securely clears the online slow query log slow log process
• Mysqlsla slow query and analysis tool usage notes
• Mysql slow query usage
• Fault Caused by a slow MySQL Query