MySQL slow log query full parse: From parameters, configuration to analysis tool "go"

Turn from:

MySQL Slow log query full parse: From parameters, configuration to analysis tools-mysql-dbaplus community-around the database, big data, PAAs Cloud, the most focused on the "data" of the learning communication and professional community
Http://dbaplus.cn/news-11-694-1.html

The concept of a slow query log

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.

Official documentation, the log of the slow query is described below (some of the information, specific reference to the official relevant links):

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least Min_examined_row_limit rows to be examined. The minimum and default values of Long_query_time are 0 and ten, respectively. The value can be specified to a resolution of microseconds. For logging to a file, times is written including the microseconds part. For logging to tables, the only integer times is written; The microseconds part is ignored.

By default, administrative statements is not logged, nor is queries that does not use the indexes for lookups. This behavior can is changed usinglog_slow_admin_statements and Log_queries_not_using_indexes, as described later.

Second, slow query log related parameters

MySQL slow Query related parameters explained:

• 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.

• 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 is more resource-intensive 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.

Three, 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:

Modify the My.cnf file, add or modify parameters after Slow_query_log and slow_query_log_file, and then restart the MySQL server as follows:

Slow_query_log =1

Slow_query_log_file=/tmp/mysql_slow.log

For the parameter slow_query_log_file of the slow query, which specifies the path of the slow query log file, the system defaults to a default file Host_name-slow.log (if no parameter slow_query_log_file is specified)

So what kind of SQL will be logged into the slow query log after the slow query log is turned on? This is controlled by the parameter long_query_time, the value of Long_query_time is 10 seconds by default, can be modified using the command, or it can be modified in the MY.CNF parameter. When the runtime is exactly equal to long_query_time, it is not recorded. In other words, the MySQL source is judged to be greater than long_query_time, not greater than equals. Starting with MySQL 5.1, Long_query_time began to record the SQL statement run time in microseconds, previously recorded only in seconds. If you log into a table, only the integer part is recorded, and the Microsecond section is not recorded.

As shown above, I modified the variable long_query_time, but the value of the query variable long_query_time is still 10, hasn't it been modified? Note: With the command set global long_query_time=4 modified, you need to reconnect or open a new session to see the modified values. You use show variables like ' long_query_time ' to view the variable value of the current session, you can also use show global variables like ' long_query_time ' instead of reconnecting to the session; As shown below:

Execute the following SQL statement inside MySQL, and then we check the corresponding slow query log, we will find information similar to the following.

The Log_output parameter is how the specified 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 is more resource-intensive 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.

System variable log-queries-not-using-indexes: Queries that do not use indexes are also logged in the slow query log (optional). It is recommended to turn this option on if you are tuning. In addition, this parameter is turned on, and the SQL that uses full index scan is also logged to the slow query log.

This option does not necessarily mean, that no, index is used. For example, a query, uses a full index scan uses an index but would be logged because the index would not limit the N Umber of rows.

The system variable log_slow_admin_statements indicates whether slow-managed statements such as analyze table and ALTER TABLE are recorded in the slow query log.

The system variable log_slow_slave_statements represents:

By default, a replication slave does not write replicated queries to the slow query log. To change this, use thelog_slow_slave_statements system variable.

When the slow query log was enabled, this variable enables logging for queries that has taken more than Long_query_time SE Conds to execute on the slave. This variable is added in MySQL 5.7.1. Setting this variable have no immediate effect. The state of the variable applies in all subsequent START SLAVE statements.

Parameter--log-short-format

The server writes less information to the slow query log if you use the--log-short-format option.

Command-Line Format	--log-short-format
Permitted Values	Type	Boolean
	Default	FALSE

Also, if you want to query how many slow query records you have, you can use system variables.

Log Analysis Tool Mysqldumpslow

In a production environment, if you want to manually analyze logs, find and analyze SQL, it is obviously a physical activity, MySQL provides log analysis tool Mysqldumpslow.

View Mysqldumpslow's Help information:

-S, which means to sort by which way;

• C: Access Count

• L: Lockout time

• R: Return record

• T: Query time

• Al: Average lockout time

• AR: Average number of returned records

• At: Average query time

-T, which is the meaning of top N, which is to return the data of the previous number of bars;

-G, you can write a regular matching pattern, the case is not sensitive;

Like what:

• Get the maximum number of 10 SQL to return a recordset.

  Mysqldumpslow-s r-t 10/database/mysql/mysql06_slow.log

• Get the most visited 10 sql

  Mysqldumpslow-s c-t 10/database/mysql/mysql06_slow.log

• Get the query that contains the left connection in the first 10 lines sorted by time.

  Mysqldumpslow-s t-t 10-g "left join"/database/mysql/mysql06_slow.log

• It is also recommended to combine these commands when using | And more use, otherwise there may be a situation where the brush screen appears.

• Mysqldumpslow-s r-t 20/mysqldata/mysql/mysql06-slow.log | More

Resources:

• Https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

• Https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_long_query_time

The author introduces the Xiaoxiang dancer

• Kerry, an English name, insists on being a DBA who writes code. Focusing on Oracle, MS SQL, mysql,hbase and other databases as well as NoSQL, NET, Java and other types of technology, database management, database development, performance optimization, data mining, BI, etc. have a strong interest.

MySQL slow log query full parse: From parameters, configuration to analysis tool "go"