MySQL Slow log query

MySQL's slow query log is a log record provided by MySQL, which is used to record a statement in MySQL that has a response time exceeding the threshold, which refers to SQL that runs longer than the Long_query_time value, and is recorded in the slow query log. The default value of Long_query_time is 10, which means to run statements above 10S. By default, the MySQL database does not start the slow query log, we need to manually set this parameter, of course, if not tuning needs, it is generally not recommended to start this parameter, because the slow query log on more or less to bring some performance impact. The slow query log supports writing log records to a file, and also supports writing log records to a database table.

One, slow query log related parameters

• Slow_query_log: Whether to turn on the slow query log, 1 means on, 0 is off.
• Log-slow-queries: Old version (under 5.6) MySQL database slow query log storage path. You can not set this parameter, the system will default to a default file Host_name-slow.log
• Slow-query-log-file: New version (5.6 and later) MySQL database slow query log storage path. You can not set this parameter, the system will default to a default file Host_name-slow.log
• Long_query_time: The slow query threshold, which logs when the query time exceeds the threshold value set. When log_query_time=2 indicates that the log is logged when the query exceeds 2 seconds;
• Log_queries_not_using_indexes: Queries that do not use indexes are also logged in the slow query log (optional).
• Log_output: How the log is stored. log_output= ' file ' means that the log is stored in a file and the default value is ' file '. log_output= ' table ' means that the log is stored in the database so that the log information is written to the Mysql.slow_log table. MySQL database supports the same two types of log storage, the configuration is separated by commas, such as: log_output= ' file,table '. Logging to the system's dedicated log table consumes more system resources than logging to a file, so it is recommended to log to a file if you need to enable slow query logging and you need to be able to achieve higher system performance.

Second, slow query log configuration

By default, the value of Slow_query_log is off, which means that the slow query log is disabled and can be turned on by setting the value of Slow_query_log as follows:

Using Set global slow_query_log=1, the slow query log is turned on only for the current database and will expire if MySQL restarts. If you want to take effect permanently, you must modify the configuration file My.cnf (as well as other system variables). For example, it looks like the following:

For more information on MySQL slow query log, see: http://www.cnblogs.com/kerrycode/p/5593204.html

MySQL Slow log query