Detailed examples of mysql recording time-consuming sql statements

mysql records time-consuming sql

mysql can record time-consuming sql or unused index sql in the slow log for optimization and analysis. This article mainly introduces the relevant information on the detailed explanation of mysql record time-consuming SQL examples. Here is the implementation method. Friends in need can refer to it. I hope it can help everyone.

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>

Related recommendations:

mysql records time-consuming sql_MySQL

Use MySQL to analyze SQL time-consuming issues

About how to deal with time-consuming php files

The above is the detailed content of Detailed examples of mysql recording time-consuming sql statements. For more information, please follow other related articles on the PHP Chinese website!