Mysql slow query log enabling and Storage Format
Mysql version is mysql5.6.22. The installation environment is Windows 7.
1. Use this query log to find the SQL statement with high efficiency, record it, and monitor it.
You can use the following statement to query and set slow query logs.
(1) check whether the slow query log is enabled
Show variables like '% show_query_log %'; or show variables like '% show_query_log %' \ G (used in mysql command line)
The query result is as follows:
Variable_name: slow_query_log
Value: OFF
The Value above indicates that it is not enabled.
Variable_name: slow_query_log_file
Value: D: \ ProgramFiles \ MySQL5.6.22 \ mysql_master \ data \ LHY-slow.log
The preceding Value indicates the log storage path.
Set the Enable status:
Set global slow_query_log = on;
(2) Check whether query logs with no indexes are enabled
Show variables like '% log_queries_not_using_indexes %'; or show variables like '% log_queries_not_using_indexes %' \ G (used in mysql command line)
The query result is as follows:
Variable_name: log_queries_not_using_indexes
Value: OFF
The Value above indicates that it is not enabled.
Set the Enable status:
Set global log_queries_not_using_indexes = on;
(3) View query logs that have exceeded the specified time
Show variables like '% long_query_time %'; or show variables like '% long_query_time %' \ G (used in mysql command line)
The query result is as follows:
Variable_name: long_query_time
Value: 10.000000
Above value: 10 s indicates the SQL statement with a record execution time of more than 10 seconds
Set the execution time to 1 s.
Set global long_query_time = 1;
Exit;
Note 1: after the modification is executed, you must log out and log on again.
NOTE 2: If the setting time is too short, too many logs will soon fill the disk space. Therefore, Disk Cleanup should be performed regularly. Here, 1 is set to view the execution effect, in the production environment, you must set your own settings.
After the preceding three steps are completed, any SQL statement executed from the database will be recorded in the log. You can view the log information in the first step.
The preceding settings are processed on the console. After the database is restarted, the settings become invalid;
The long-term effective way is to find the my. ini file under the mysql installation directory, if this file is not available, only the mysql-default.ini File
Back up the file, change it to mysql. ini, and add the following configuration information under [mysqld] in the file.
Slow_query_log = on
Slow_query_log_file = D:/ProgramFiles/MySQL5.6.22/mysql_master/data/LHY-slow.log
Log_queries_not_using_indexes = on
Long_query_time = 1
2. Storage Format
# Time: 150401 11:24:27
# User @ Host: root [root] @ localhost [127.0.0.1] Id: 7
# Query_time: 0.034002 Lock_time: 0.000000 Rows_sent: 3 Rows_examined: 3
Use libu;
SET timestamp = 1427858667;
Select * from aaa;
The analysis is as follows:
(1) Time: execution Time
(2) User @ Host: Host information for SQL Execution
(3) Query_time: SQL Execution information, Lock_time: Lock time, Rows_sent: Number of sent (result) rows, Rows_examined: Number of scanned rows
(4) timestamp: execution time
(5) select * from aaa;: query statement content
3. Slow query log analysis tool
Five tools:Mysqldumpslow, mysqlsla, myprofi, mysql-explain-slow-log, mysqllogfilter
Mysqldumpslow mysql built-in analysis tool
Sorry: I have not found any way to use these tools on Windows. If any of you have any tutorials, please @ me. Thank you !!!
