Use mysqldumpslow and mysqlsla to analyze mysql slow query log _ MySQL

Use mysqldumpslow and mysqlsla to analyze mysql slow query log mysqldump

BitsCN.com

Use mysqldumpslow and mysqlsla to analyze mysql slow query logs
MySQL optimization is not a permanent task, but a constant battle. Among them, the analysis of slow query logs is an important method. I used to look at it manually. However, this is not a long-term plan. today I tried mysqldumpslow and mysqlsla, and I feel much more efficient.

Mysqldumpslow
Mysqldumpslow is a perl script officially provided, so you don't have to bother installing Bala. as long as you have a mysql environment, you can basically use it:
Mysqldumpslow-s c

The parameter (-s c) means to sort by the number of slow queries. However, mysqldumpslow has a bad disadvantage, that is, the query results only have some abstract SQL statements, and there is no actual running SQL example.

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 slow SQL statements
Number of occurrences (Count ),
Maximum execution Time (Time ),
Total Time consumed ),
Lock ),
Total number of Rows sent to the client (Rows ),
Total number of Rows scanned (Rows ),
The user and the SQL statement itself (abstract the format, for example, limit 1, 20 is represented by limit N, N ).

Mysqlsla
Mysqlsla is a log analysis tool launched by hackmysql.com (This website also maintains mysql tools such as mysqlreport and mysq1_xchk)
Data reports are very helpful for analyzing the causes of slow queries, including execution frequency, data volume, and query consumption.

The installation method is as follows:
Tar zxf http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
Cd mysqlsla-2.03
Perl Makefile. PL
Make
Make install

Easy to use:
/Path/to/mysqlsla slow. log



The format is described as follows:
Total number of queries (queries total), number of SQL statements after deduplication (unique)
Sorted)
The most important slow SQL statement statistics, 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 belongs
Users, which user and IP address account 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, which are a rare tool.

Author: "keyboard goes out of the rivers and lakes"

BitsCN.com