MySQL Slow query log analysis

One: Query the status of slow log, as shown in the sample code, slow log is already open.

Mysql>Show variables like '%slow%';+---------------------+------------------------------------------+|Variable_name|Value|+---------------------+------------------------------------------+|Log_slow_queries|  on                                       ||Slow_launch_time| 2                                        ||Slow_query_log|  on                                       ||Slow_query_log_file| /Mysqllog/Slow_log/slow_queries_3306.Log |+---------------------+------------------------------------------+4Rowsinch Set(0.00Sec

There are two ways to do this if you do not have the slow query log turned on:

1. Add Slow_query_log = on and Long_query_time = 1 in the configuration file [Mysqld], and then restart MySQL to take effect.
2. Set global slow_query_log=1; Open online. If MySQL restarts, it will fail, and if it is to be permanently active, the configuration file will be modified.

II: Slow Log related parameters detailed

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: How to safely empty the slow query log online

• Stop slow Log

MySQL>set global slow_query_log=00 rows affected (0.27 sec)

Mysql>Show variables like '%slow%';+---------------------+------------------------------------------+|Variable_name|Value|+---------------------+------------------------------------------+|Log_slow_queries| OFF                                      ||Slow_launch_time| 2                                        ||Slow_query_log| OFF                                      ||Slow_query_log_file| /Mysqllog/Slow_log/slow_queries_3306.Log |+---------------------+------------------------------------------+4Rowsinch Set(0.00Sec
#检查慢查询日志的状态

• To reset the path path for the slow query log

MySQL>set global slow_query_log_file='/mysqllog/slow_log/slow_queries _3306_new.log'0 rows affected (0.03 sec)

• Turn on the slow query log and set Long_query_time.

MySQL>set global slow_query_log=10 rows affected (0.01  sec) MySQL>set global long_query_time=1;

#检查状态是否成功开启
Mysql>Show variables like '%slow%';+---------------------+----------------------------------------------+|Variable_name|Value|+---------------------+----------------------------------------------+|Log_slow_queries|  on                                           ||Slow_launch_time| 2                                            ||Slow_query_log|  on                                           ||Slow_query_log_file| /Mysqllog/Slow_log/Slow_queries_3306_new.Log |+---------------------+----------------------------------------------+4Rowsinch Set(0.00Sec

• Check slow SQL in the new log file

Mysql> SelectSleepTen) asA1  asb;+---+---+|A|B|+---+---+| 0 | 1 |+---+---+1Rowinch Set(10.00sec) MySQL> [[email protected] ~]$ more/Mysqllog/Slow_log/Slow_queries_3306_new.Log...... Time Id Command argument# time:140213  6: -: -# User@Host: Root[Root]@ localhost[]# Query_time:10.000365Lock_time:0.000000Rows_sent:1Rows_examined:0SET timestamp=1392273864;SelectSleepTen) asA1  asb

• Slow query log before backup

/mysqllog/slow_log/slow_queries_3306. Log /mysqlbackup/slow_log/slow_queries_3306. log. bak. 20140213

Four: Analysis tool Mysqlsla installation and use, Mysqlsla is hackmysql.com launched a MySQL log analysis tool, the function is very powerful. The data report is very helpful for analyzing the reasons of slow query, including the frequency of execution, the amount of data, and the consumption of queries.

• Installing the Mysqlsla under CentOS

Yum Install Perl Perl perl-devel-y #安装依赖包 wget  FTP://ftp.tw.freebsd.org/pub/ distfiles/mysqlsla-2.03.tar.gz#下载 perl  makefile.pl #预编译  make #编译  Make  Install Installation
PS: It is best to install this tool on a local test server and then copy your slow query log locally for analysis to avoid affecting the production environment database.

• Detailed parameters

LT: Indicates the log type, with slow, general, binary, MSL, UDL. sf:[+-][type],[type] has select, CREATE, DROP, UPDATE, INSERT, such as "+select,insert", does not appear by default--that is, not included. DB: The log of which library to process. Top: Indicates how many first bars are sorted by rule.  Sort: Sorted by some rule, t_sum sorted by total time, c_sum by total number of times.

• Example uses

Mysqlsla-lt Slow mysql- -sort-lt slow/root/slow_queries_1013.log >/tmp/ Fx.log

• Analysis results

total queries (queries totals), the number of de-SQL (unique) Output report content sort (sorted by) The most significant slow SQL statistics, including average execution time, waiting lock time, total number of result rows, total number of rows scanned. Count, the number of executions of SQL and the percentage of total slow log count. Time, execution times, including total time, average time, minimum, maximum time, and percentage of total slow SQL time. The% of time, removing the fastest and slowest SQL, covers the execution times of 95% SQL. Lock time, waiting for the lock.  the  The slow SQL waits for lock time. Rows sent, resulting row statistics, including average, minimum, maximum number. Rows examined, the number of lines scanned. database, which databases users, which user, IP, accounts for all users execute SQL percent query abstract, abstract SQL statement Query sample, SQL statement

MySQL Slow query log analysis