MySQL slow query log record and Analysis

Source: Internet
Author: User
Tags mysql slow query log perl script
How to record MySQL slow query SQL logs

Modify the mysqld section of my. CNF:
Long_query_time = 1 // defines the slow query time. 1 indicates 1 second.
-- Log-Slow-queries [= file_name] // record slow query to log files
-- Log-queries-not-using-indexes // records SQL statements that do not use indexes to log files.
Long_query_time = 1
Log-Slow-queries =/usr/local/mysql5.0.40/var/slow_query.log
Log-queries-not-using-indexes = true

"Too connector connections" could not find the problem. Later, it was found that the slow query of MySQL would be of great help.

Open MySQL slow Query
MySQL slow query record logs are useful for tracking MySQL load tuning issues in the PHP + MySQL system, such as installing a lot of discuz! Plug-in users, so that you can probably find out which plug-ins have code problems. In fact, it is very easy to enable MySQL slow query logs. You only need to add the log-Slow-queries and long_query_time parameters to the MySQL configuration file.

Today, a friend asked me to bring the record up. For more MySQL optimization information, see here:

Enable MySQL slow query in Windows
In Windows, the configuration file of MySQL is usually my. ini. Find [mysqld] and add
Log-Slow-queries = F: \ mysql \ log \ mysqlslowquery. Log
Long_query_time = 2

Enable MySQL slow query in Linux
The configuration file of MySQL in Windows is usually my. CNF. Find [mysqld] and add
Log-Slow-queries =/data/mysqldata/slowquery. Log
Long_query_time = 2

Log-Slow-queries = F: \ mysql \ log \ mysqlslowquery. log is the location where the query log is stored. Generally, this directory requires the write permission of the MySQL running account. Generally, this directory is set to the MySQL data storage directory;
2 In long_query_time = 2 indicates that the query takes more than two seconds to record;

Enable slow log
There are two activation methods:

1. In my. CNF, log-Slow-queries [= file_name]

2. When the mysqld process is started, specify the five common tools to be compared using the -- log-Slow-queries [= file_name] Option.

Mysqldumpslow, mysqlsla, myprofi, MySQL-explain-Slow-log, mysqllogfilter

Mysqldumpslow is the log analysis tool for slow query officially provided by MySQL. The output chart is as follows:

The main function is to count the number of occurrences of different slow SQL statements (count), the maximum execution time (time), the total time consumed (time), and the lock wait time (LOCK ), the total number of rows sent to the client (rows), the total number of rows scanned (rows), the user and the SQL statement itself (abstracted format, such as limit 1, 20 with limit N, N indicates ).

Here are some useful parameters:
-S sorting option: C query times R returns the number of record rows t query time
-T: only Top N queries are displayed.
Mysqldumpslow-s r-T 10 slow. Log


Mysqlsla, a log analysis tool launched by (This website also maintains mysqlreport, mysq1_xchk, and other useful MySQL tools)

Data reports are very helpful for analyzing the causes of slow queries, including execution frequency, data volume, and query consumption.
The format description is as follows: total queries (queries total), number of de-duplicated SQL statements (unique) The most important slow SQL statement statistics in sorting the output report content (sorted, including average execution time, lock wait time, total number of result rows, and total number of scanned rows. count, the number of SQL executions and percentage of the total slow log count. time, execution time, including the total time, average time, minimum, maximum time, and time as a percentage of the total slow SQL time. 95% of time removes the fastest and slowest SQL statements, and the SQL Execution time with a coverage rate of 95%. lock time, the waiting time. 95% of lock, 95% of slow SQL wait lock time. rows sent, the number of result rows, including average, minimum, and maximum.
Rows examined: number of rows scanned. database, which database is users, which user, IP address, accounting for the percentage of SQL statements executed by all users query abstract, abstract SQL statement query sample, SQL statement in addition to the above output, the official website also provides many customized parameters and is a rare tool. mysql-explain-Slow-log, a Perl script written by the Germans.


The function is a bit flawed, not only printing all slow logs to the screen, but also counting only the quantity. not recommended. mysql-log-filter, an analysis tool found on Google Code. provides two executable scripts: Python and PHP.


Compared with the official mysqldumpslow function, the query time statistics (average, maximum, and accumulative) are added. Other functions are similar to mysqldumpslow.
In addition to statistical information, the featured features are also formatted and formatted to ensure the overall output is concise. If you like concise reports, we recommend that you use them.

Myprofi is an open-source analysis tool written in PHP. The project is on SourceForge.



Function, lists the total number and type of slow queries, deduplicated SQL statements, the number of executions, and the percentage of the total number of slow logs.
The overall output style is more concise than mysql-log-filter, saving a lot of unnecessary content. It is recommended for users who only want to view SQL statements and the number of executions.
Tools/functions General Statistics Advanced Statistics Script Advantages
Mysqldumpslow Supported Not Supported Perl Official MySQL built-in
Mysqlsla Supported Supported Perl Powerful functions, complete data reports, and strong customization capabilities.
Mysql-explain-Slow-Log Supported Not Supported Perl None
Mysql-log-Filter Supported Partially supported Python or PHP Keep the output concise without losing its functionality
Myprofi Supported Not Supported PHP Very streamlined
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: 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.