Detailed explanation of how mysql records time-consuming SQL statement instances

This article mainly introduces the relevant information about the detailed explanation of the SQL instance that mysql records time-consuming. Here is the implementation method. I hope it can help everyone. Friends in need can refer to

mysql record consumption When sql

mysql can record time-consuming sql or unused index sql in the slow log for optimization analysis.

1. Enable mysql slow query log:

Mysql slow query log is very useful for tracking problematic queries, and can analyze resource-intensive queries in the current program. sql statement, how to open the slow query log record of mysql?

mysql> show variables like 'log_slow_queries';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | OFF | 
+------------------+-------+
1 row in set (0.01 sec)

mysql>

This means that the slow log function is not enabled. To enable it, you need to modify the mysql configuration file. In the configuration file Add the following two parameters to "[mysqld]":

long_query_time=1
log-slow-queries=/var/mysql/logs/slow.log

Description

long_query_time

This parameter represents the measurement time of slow query, the unit is seconds, the minimum is 1, the default value is 10, any sql statement whose execution time exceeds long_query_time will be recorded in the slow query log

log-slow-queries[=file_name]

The file_name parameter is optional. The default value is host_name-slow.log. If the file_name parameter is specified, mysql will slow it down. The query log is recorded to the file set by file_name. If file_name provides a relative path, mysql will record the log to the data directory of mysql. This parameter can only be added in the configuration file and cannot be executed on the command line. .

2. Configuration of recording unused index queries into slow log

Add "log_queries_not_using_indexes to the mysql startup configuration file or command line parameters ” parameter can be used to add unused index query statements to the slow log.

The sample is as follows:

##

[root@localhost mysqlsla-2.03]# more /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
log_bin=/tmp/mysql/bin-log/mysql-bin.log
log_bin=ON
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1


log_slow_queries=/tmp/127_slow.log
long_query_time=1
log_queries_not_using_indexes

.......

After restarting mysql, the check results are as follows:

mysql> show variables like 'log_slow_queries';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | ON | 
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 2  | 
+-----------------+-------+
1 row in set (0.00 sec)

mysql>

The above is the detailed content of Detailed explanation of how mysql records time-consuming SQL statement instances. For more information, please follow other related articles on the PHP Chinese website!