MySQL slow query log settings and Analysis

MySQL slow query log settings and Analysis

The slow query log is used to record some slow query statements, which can help the Administrator analyze the problem. This log is not enabled by default. You need to add a series of parameters in the configuration file to manually start the log.

Edit the my. cnf file and add the following content to the [mysqld] module:

[Mysqld]

Slow_query_log = on # enable slow query log

Slow_query_log_file = filename # specifies the log file storage path, if not specified, the default in the database file directory, named hostname-slow.log

Long_query_time = 2 # the specified number of seconds is considered as a slow query.

Long-queries-not-using-indexes # records query statements without indexes

Min_examined_row_limit = 1000 # records query slow queries caused by up to 1000 queries

Log-slow-admin-statements # record slow queries caused by statements such as ALTER TABLE

Log-slow-slave-statements # record slow queries generated from the server

Restart the MySQL service to make the configuration take effect.

Service mysqld restart

Log on to the database to query the environment

Show variables like '% slow % ';

Execute a slow query statement

Select sleep (3 );

The slow query log is generated, and the execution user and query statement are displayed at the arrow.

It is very troublesome to view text in a log file with a large number of slow queries. We recommend that you use some slow query and analysis tools, such as mysqldumpslow, which will be shown as follows:

In addition, the pt-query-digest or mysqlsla in the third-party percona-toolkit can be analyzed by directly following the slow log file after installation.

This article permanently updates the link address: