MySQL monitoring optimization (iii) Slow query

as the name implies, the slow query log is a long execution of query, that is, we often say slowquery, by setting--log-slow-queries[=file_name] to open the function and set the record location and file name. The slow query log is a simple text format that allows you to view the content in a variety of text editors. It records the moment of execution, the time spent executing, executing the user, connecting the host and other related information. MySQL also provides a tool program mysqlslowdump for analyzing full query logs to help database managers resolve possible performance issues.

1. Configure Slow Query

Linux:
Added in MySQL config file my.cnf: log-slow-queries=/opt/data/slowquery.log (Specify log file location, can be empty, the system will give a default file Host_name-slow.log)

long_query_time=2 (record over time, default is 10s)

Log-queries-not-using-indexes (log down without using the indexed query, depending on the situation to decide whether to open)
Windows:
In My.ini [mysqld] Add the following statement: Log-slow-queries = E:\web\mysql\log\mysqlslowquery.log

Long_query_time = 2 (other parameters as above)

The configuration of the slow query can also be set by executing commands so that the MySQL service is not restarted.

Set global slow_query_log=on;
Set global long_query_time=1; #设置记录查询超过多长时间的sql
Set global slow_query_log_file= '/opt/data/slow_query.log '; #设置mysql慢查询日志路径, this path requires write access
This is not a way to restart the MySQL service.

2. Query MySQL slow query status

SHOW VARIABLES like '%query% ';
Use this statement to see if the current MySQL slow query is turned on, and where the slow query log file for MySQL is.
Slow_query_log #是否开启慢查询
Slow_query_log_file #日志的存放位置
Long_query_time #超过多少秒的查询就写入日志

3. Parsing MySQL slow query log

Use the Mysqldumpslow command to parse the MySQL slow query log.
The mysqldumpslow command parameters are as follows:
-S, is the way to indicate the sort, C, T, L, R are in accordance with the number of records, time, query time, the number of records returned to sort, AC, at, AL, AR, indicating the corresponding flashbacks;
-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;

For example, to follow the top 20 SQL for the longest SQL execution time
Mysqldumpslow-s t-t 20-g ' select '/opt/data/slowquery_2016050921.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 '/opt/data/slowquery_2016050921.log

MySQL monitoring optimization (iii) Slow query