mysql-Slow Query log

1 Slow query definition

means that MySQL logs all SQL statements that execute a time threshold that exceeds the Long_query_time parameter setting. The slow query log is the log that logs these SQL.

2 Turn on slow query log

Locate the MySQL configuration file my.cnf. Add below Mysqld

Log-slow-queries = D:/mysql/log/mysqld-slow-query.log  #日志存在的位置. (Note the permissions of the problem, can not set, the system will give a default file Host_name-slow.log) Long-query-time = 5   #SQL执行时间阈值, the default is 10 seconds. The #log-long-format   #说log-long-format option is used to format the log, which is an extended record of the event. The extension can record information about who issued the query and when the query was issued. Allows us to better understand the operation of the client. #log-slow-admin-statements  #将慢管理语句例如OPTIMIZE table, ANALYZE table, and ALTER TABLE are logged in the slow query log. Log-queries-not-using-indexes #如果运行的SQL语句没有使用索引, the MySQL database will also log this SQL statement to the slow query log file.

Restart the MySQL service when the configuration is complete.

3 Testing

For convenience I only configured the path of the slow query log in MY.CNF.

MySQL version is 5.1.73

about Long_query_time: starting with MySQL 5.1, Long_query_time began to record the SQL statement run time in microseconds, previously recorded only in seconds. This allows for more accurate logging of SQL uptime for DBA analysis.

into MySQL.

Mysql> show VARIABLES like "%slow%"; #查看慢查询日志相关的信息

mysql> set global long_query_time=0.01; #将mysql位置的值改为 0.01

Mysql> Show variables like "Long_query_time"; #查看是否生效

Mysql> Select Sleep (2); #延迟2秒执行 Note: This SQL execution should have generated the relevant information record in the log file of the slow query.

Mysql> show global status like '%slow% '; #查看慢查询语句的次数

You can see that SQL has been logged in the log file.

mysql-Slow Query log