Mysql slow query log enabling and Storage Format

Source: Internet
Author: User
Tags mysql command line mysql slow query log

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 !!!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.