MySQL slow query log record and Analysis

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.
Instance:
[Mysqld]
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: http://www.ccvita.com/category/mysql

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

Note:
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 hackmysql.com (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.
Http://www.willamowius.de/mysql-tools.html

 

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.
Http://code.google.com/p/mysql-log-filter/

 

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.
Http://myprofi.sourceforge.net/

 

 

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