Mysql records time-consuming SQL statements

Author: skate
Time: 2012/02/17
 
Mysql records time-consuming SQL statements
 
Mysql records time-consuming SQL statements or SQL statements without indexes in slow log for optimization analysis.
 
1. mysql slow query log enabled:
 
Mysql slow query logs are very useful for queries with tracing problems. We can analyze the resource-consuming SQL statements in the current program. How can we open mysql slow query log records?
 
Mysql> show variables like 'Log _ slow_queries ';
+ ------------------ + ------- +
| Variable_name | Value |
+ ------------------ + ------- +
| Log_slow_queries | OFF |
+ ------------------ + ------- +
1 row in set (0.01 sec)
 
Mysql>
 
This indicates that the slow log function is not enabled. To enable the configuration file of mysql, add the following two parameters in the configuration file "[mysqld:
 
Long_query_time = 1
Log-slow-queries =/var/mysql/logs/slow. log
 
Description
Long_query_time
This parameter indicates the Metric Time of slow query. The unit is second and the minimum value is 1. The default value is 10. All SQL statements whose execution time exceeds long_query_time are recorded in the slow query log.
 
Log-slow-queries [= file_name]
The file_name parameter is optional. The default value is the host_name-slow.log. If the file_name parameter is specified, mysql records the slow query log to the file set by file_name, if file_name provides a relative path, mysql records logs to the data directory of mysql. This parameter can only be added to the configuration file and cannot be executed in the command line.
 
2. Record the configuration in slow log if no index is used
Add the "log_queries_not_using_indexes" parameter to the mysql STARTUP configuration file or command line parameters to add unused index query statements to slow log.
 
Example:
[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. 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/pai_slow.log
Long_query_time = 1
Log_queries_not_using_indexes
 
.......
 
After you restart 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>
 
 
 
-------- End ------