Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "MySQL slow query case analysis". The editor shows you the operation process through the actual case. The operation method is simple, fast and practical. I hope this "MySQL slow query case analysis" article can help you solve the problem.

1 concept

The slow query of MySQL, whose full name is the slow query log, is a kind of logging provided by MySQL, which is used to record statements whose response time exceeds the threshold in MySQL.

In a specific environment, SQL statements that run longer than the long_query_ time value are recorded in the slow query log.

The default value for long_query_time is 10, which means to record statements that run for more than 10 seconds.

By default, the MySQL database does not start slow query logs, and you need to set this parameter manually.

Of course, it is generally not recommended to enable this parameter if it is not needed for tuning, because turning on the slow query log will have a more or less performance impact. Slow log support for writing log records to files and database tables.

2 parameters

Explanation of parameters related to MySQL slow query:

Slow_query_log: whether to enable slow query log. 1: enable, 0: disable.

Log-slow-queries: the storage path of slow query logs in MySQL database in the old version (version 5.6 or later). If you do not set this parameter, the system will default to a default file host_name-slow.log

Slow-query-log-file: the storage path of slow query logs in the new version (version 5.6 and above) of MySQL database. If you do not set this parameter, the system will default to a default file host_name-slow.log

Long_query_time: slow query threshold. Log is recorded when the query time exceeds the set threshold.

Log_queries_not_using_indexes: queries that do not use indexes are also recorded in the slow query log (optional).

Log_output: log storage method. Log_output='FILE' means to save the log to a file, and the default value is' FILE'. Log_output='TABLE' means to store the log in the database.

3 configuration

3.1 slow_query_log

By default, the value of slow_query_log is OFF, which means that slow log is disabled and can be enabled by setting the value of slow_query_log, as shown below:

Mysql > show variables like'% slow_query_log%' +-- + | Variable_name | Value | +- -+-+ | slow_query_log | OFF | | slow_query_log_file | / home/WDPM/MysqlData/mysql/DB-Server-slow.log | +- -+-+ 2 rows in set (0.00 sec) mysql > set global slow_query_log=1 Query OK, 0 rows affected (0.09 sec)

Use set global slow_query_log=1 to enable the slow query log, which only works for the current database, and will become invalid after MySQL restart.

If you want to take effect permanently, you must modify the configuration file my.cnf (as do other system variables).

My.cnf adds or modifies the parameters slow_query_log and slow_query_log_file, as shown below

Slow_query_log = 1slow_query_log_file = / tmp/mysql_slow.log

Then restart the MySQL server.

3.2 slow_query_log_file

This parameter is used to specify the storage path of the slow query log. The default is host_name-slow.log file.

Mysql > show variables like 'slow_query_log_file' +-- + | Variable_name | Value | +- -+-+ | slow_query_log_file | / home/WDPM/MysqlData/mysql/DB-Server-slow.log | + -+ 1 row in set (0.00 sec)

3.3 long_query_time

After enabling the slow log, what kind of SQL will be recorded in the slow log?

This is controlled by the parameter long_query_time. By default, the value of long_query_time is 10 seconds, which can be modified using the command or in the my.cnf parameter.

Cases where the elapsed time is exactly equal to long_query_time are not recorded.

In other words, in the mysql source code, it is judged to be greater than long_query_time, not greater than or equal to.

Starting with MySQL 5.1, long_query_time began to record the run time of SQL statements in microseconds. Previously, it only recorded in seconds.

If recorded in the table, only the integer part will be recorded, not the microsecond part.

Mysql > show variables like 'long_query_time%' +-+-+ | Variable_name | Value | +-+-+ | long_query_time | 10.000000 | +-+-+ 1 row in set (sec) mysql > set global long_query_time=4 Query OK, 0 rows affected (0.00 sec) mysql > show variables like 'long_query_time' +-+-+ | Variable_name | Value | +-+-+ | long_query_time | 10.000000 | +-+-+ 1 row in set (10.000000 sec)

As shown above, I modified the variable long_query_time, but the value of the query variable long_query_time is still 10, haven't I changed it?

Note: after using the command to modify, you need to reconnect or open a new session to see the modified value. Set global long_query_time=4

View is the value of the variable for the current session. Show variables like 'long_query_time'

You can also use instead of reconnecting to the session. Show global variables like 'long_query_time'

3.4 log_output

The log_output parameter specifies how the log is stored.

Log_output='FILE' means to save the log to a file, and the default value is also 'FILE'.

Log_output='TABLE' means to store the log in the database so that the log information is written to the mysql.slow_ log table.

At the same time, two log storage methods are supported, which can be separated by commas when configured, such as log_output='FILE,TABLE'.

Logging to a dedicated log table of the system consumes more system resources than recording files.

Therefore, if you need to enable slow query logging and be able to achieve higher system performance, it is recommended that you log to a file first.

Mysql > show variables like'% log_output%';+-+-+ | Variable_name | Value | +-+-+ | log_output | FILE | +-+-+ 1 row in set (0.00 sec) mysql > set global log_output='TABLE' Query OK, 0 rows affected (0.00 sec) mysql > show variables like'% log_output%';+-+-+ | Variable_name | Value | +-+ | log_output | TABLE | +-+-+ 1 row in set (0.00 sec) mysql > select sleep (5) +-+ | sleep (5) | +-+ | 0 | +-+ 1 row in set (5.00 sec) mysql > mysql > select * from mysql.slow_log +-+- -+ | start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread _ id | +-+-- + -+ | 2016-06-16 17:37:53 | root [root] @ localhost [] | 00:00:03 | 00:00:00 | 1 | 0 | 0 | 0 | 1 | select sleep (3) | 5 | | 2016-06-16 21:45:23 | root [root] @ localhost [] | 00:00:05 | 00:00:00 | 1 | 0 | 0 | 0 | 1 | select sleep (5) | 2 | +- -+ -- + 2 rows in set (0.00 sec)

‎ 3.5 Log query-do not use-Index ‎

This system variable specifies that queries that do not use indexes are also recorded in the slow query log (optional).

If tuning, it is recommended to turn on this option.

In addition, if this parameter is enabled, SQL using full index scan will also be recorded in the slow query log.

Mysql > show variables like 'log_queries_not_using_indexes' +-- +-+ | Variable_name | Value | +-+-+ | log_queries_not_using_indexes | OFF | +- -+-+ 1 row in set (0.00 sec) mysql > set global log_queries_not_using_indexes=1 Query OK, 0 rows affected (0.00 sec) mysql > show variables like 'log_queries_not_using_indexes' +-- +-+ | Variable_name | Value | +-+-+ | log_queries_not_using_indexes | ON | +- -+-+ 1 row in set (0.00 sec) 3.6 log_slow_admin_statements

This system variable indicates whether slow management statements such as ANALYZE TABLE and ALTER TABLE are recorded in the slow query log.

Mysql > show variables like 'log_slow_admin_statements' +-- +-+ | Variable_name | Value | +-+-+ | log_slow_admin_statements | OFF | + -+-+ 1 row in set (0.00 sec) 3.7 Slow_queries

If you want to query how many slow query records there are, you can use the Slow_queries system variable.

Mysql > show global status like'% Slow_queries%';+-+-+ | Variable_name | Value | +-+-+ | Slow_queries | 2104 | +-+-+ 1 row in set (2104 sec)

In addition, there are log_slow_slave_statements and-log-short-format parameters, which can be found on the MySQL website.

4 mysqldumpslow tool

In a production environment, if you want to manually analyze logs, find and analyze SQL, it is obviously manual work.

MySQL provides log analysis tool mysqldumpslow

View help information for mysqldumpslow:

[root@DB-Server ~] # mysqldumpslow-- helpUsage: mysqldumpslow [OPTS... ] [LOGS... ] Parse and summarize the MySQL slow query log. Options are-- verbose verbose-- debug debug-- help write this text to standard output-v verbose-d debug-s ORDER what to sort by (al, at, ar, c, l, r, t) 'at' is default (sorting method) al: average lock time (average lock time) ar: average rows sent (average number of returned records) at: average query time (average query time) c: count (access count) l: lock time (lock time) r : rows sent (return record) t: query time (query time)-r reverse the sort order (largest last instead of first)-t NUM just show the top n queries (return the first n pieces of data)-a don't abstract all numbers to N and strings to's'- n NUM abstract numbers with at least n digits within names-g PATTERN grep: only consider stmts that include this string (regular matching pattern) Case insensitive)-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is'*', i.e. Match all-i NAME name of server instance (if using mysql.server startup script)-l don't subtract lock time from total time

For example, get the top 10 SQL that returns the recordset.

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

Get the 10 most visited SQL

Mysqldumpslow-s c-t 10 / database/mysql/mysql06_slow.log gets the first 10 queries sorted by time with left concatenation in them. Mysqldumpslow-s t-t 10-g "left join" / database/mysql/mysql06_slow.log

It is also recommended to use these commands in combination with | and more, otherwise screen brushing may occur.

Mysqldumpslow-s r-t 20 / mysqldata/mysql/mysql06-slow.log | more's content on "slow query instance Analysis of MySQL" ends here. Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.