Mysql Slow Log analysis

Mysqldumpslow is a tool that MySQL comes with to analyze slow queries, of course, not just this tool, but Percona-toolkit is Percona A collection of a set of command-line tools that are used to perform a variety of manual execution of very complex and troublesome MySQL related tasks, including the following:

Check master and slave data consistency/record valid archive/server information summary/analysis and statistics log, in order to save this block use Mysqldumpslow command to do analysis.

Need to turn on the MySQL slow query log, otherwise can not be statistical analysis, open MySQL slow query log needs to be configured in the MySQL configuration file:

Slow_query_log = # defines a query count of more than 1 seconds to a variable slow_queriesslow-query-log-file = Mysql-slow.loglong_query_time = 1

-S, which is Order

Al Average Lockout time

AR average return recording time

At average query time (default)

C Count

L Lock Time

R return Record

T query time

-T, which is the meaning of top N, which is to return the data of the previous number of bars

-G, you can write a regular match pattern behind, case insensitive

Cases:

Sort by average query time, and take the first 20 of the sort:

Take the following SQL as an example:

Mysqldumpslow-s at-t Mysql-slow.log

Count:1 time=19.26s (19s) lock=0.00s (0s) rows=1000.0 (+), kaifa[kaifa]@[10.10.254.2] Select Cou from (SELECT Co UNT (*) as cou from ' Foot_step ' where Is_deleted=n Group by user_id) c ORDER BY cou Desc LIMIT N, n

Count:sql Number of occurrences: 1 times in Slow_log

Time:sql execution to return for a length of time: 19s

(19s): Total time for this SQL execution 19s

Lock: locking time is 0s

rows=1000.0: Send to client 1000 rows

(1000): Total scan sweep to 1000 rows

The following is the SQL itself: account number, SQL statement.

Optimization recommendations: SQL already contains the limit value segment, you can add more conditions to filter more accurate, such as time period.

The main function is to count the different slow SQL

Number of occurrences (count),

The maximum time to execute,

Total time Spent,

Time to wait for the lock (lock),

The total number of rows sent to the client (rows),

Total number of rows scanned (rows),

The user and the SQL statement itself (the format is abstracted, for example, limit 1, 20 is indicated by the limit n,n).

This article is from the "Ant" Blog, please be sure to keep this source http://215687833.blog.51cto.com/6724358/1908956

Mysql Slow Log analysis