(EXT) Mysqldumpslow Summary of usage instructions

Original: http://blog.csdn.net/langkeziju/article/details/49301993

Mysqldumpslow Instructions for use
Mysqldumpslow--help
Usage:mysqldumpslow [OPTS ...] [LOGS ...]

Parse and summarize the MySQL slow query log. Options are

--verbose verbose
--debug Debug
--help write this text to standard output

-V Verbose
-D Debug
-S ORDER what to sort by (Al, at, AR, C, L, R, T), ' on ' is default
Al:average Lock Time
Ar:average rows Sent
At:average Query Time
C:count
L:lock time
R:rows sent
T:query time
-R Reverse the sort order (largest last instead of first)
-T NUM just show the top n queries
-A don ' t abstract all numbers-N and strings to ' S '
-N NUM abstract numbers with at least n digits within names
-G PATTERN Grep:only Consider stmts that include the This string
-H HOSTNAME HOSTNAME of DB Server for *-slow.log filename (can be wildcard),
Default is ' * ', i.e. match all
-I name name of server instance (if using Mysql.server startup script)
-L don ' t subtract lock time from total time

Use several commands frequently
-S ORDER what to sort by (t, at, L, AL, R, AR etc), "at" is default
-T NUM just show the top n queries
-G PATTERN Grep:only Consider stmts that include the This string

-S, is the order, the description is not detailed enough to write, I use down, including read the code, mainly have
C,t,l,r and Ac,at,al,ar, respectively, are sorted by query count, query time, lock time, and number of records returned, preceded by an average of a
-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
Example
Mysqldumpslow-t 10-s t-g "left join" Host-slow.log
The results of the analysis using mysqldumpslow do not show a specific complete SQL statement,
Description
1: If the actual SQL statement is as follows: SELECT * from Sms_send WHERE service_id=10 GROUP by content LIMIT 0, 1000;
The results of the Mysqldumpslow display will be:
count:1 time=1.91s (1s) lock=0.00s (0s) rows=1000.0 (+), vgos_dba[vgos_dba]@[10.130.229.196]
SELECT * from Sms_send WHERE service_id=n GROUP by content LIMIT N, N;

2: If we execute a SELECT * from Sms_send WHERE service_id=20 GROUP by content LIMIT 10000, 1000;
The results of the Mysqldumpslow display will be:
Count:2 time=2.79s (5s) lock=0.00s (0s) rows=1.0 (2), vgos_dba[vgos_dba]@[10.130.229.196]
SELECT * from Sms_send WHERE service_id=n GROUP by content LIMIT N, N;

Although these two statement conditions are different,
1: One is server_id=10, one is server_id=20
2: One is limit 0, 1000, one is limit 10000, 1000
However, the Mysqldumpslow analysis will consider this to be a type of statement that will be combined to display.

3: Suppose we execute select * from Sms_send WHERE service_id<=10 GROUP by content LIMIT 0, 1000;
Execution Mysqldumpslow result is
Count:1 time=2.91s (2s) lock=0.00s (0s) rows=1000.0 (+), vgos_dba[vgos_dba]@[10.130.229.196]
SELECT * from Sms_send WHERE service_id<=n GROUP by content LIMIT N, N;
We can see that it and the SQL statements we wrote above are two types

Analysis Results of Mysqldumpslow
Count tells us that this type of statement executes several times, and time tells us the maximum duration of this type of statement execution, time=2.79s (5s) (5s)
Refers to the amount of time that this type of statement takes to execute
Count:2 time=2.79s (5s) lock=0.00s (0s) rows=1.0 (2), vgos_dba[vgos_dba]@[10.130.229.196]
Tells us to execute 2 times, the maximum time is 2.79s, total time 5s,lock time 0s, the number of results of a single return is 1 records, 2 times Total return 2 records m
Mysqldumpslow-s t-t Slow.log
The result of the query is 10 of the slowest execution SQL statements, where-s t refers to the total execution time of statements of this class type
Count:1 time=2.91s (2s) lock=0.00s (0s) rows=1000.0 (+), vgos_dba[vgos_dba]@[10.130.229.196]
And
Count:2 time=2.79s (5s) lock=0.00s (0s) rows=1.0 (2), vgos_dba[vgos_dba]@[10.130.229.196]
The result of the comparison is
Count:2 time=2.79s (5s) lock=0.00s (0s) rows=1.0 (2), vgos_dba[vgos_dba]@[10.130.229.196] row in front,
Consider the length of the comparison to be (5s) and (2s) instead of 2.79s and 2.91s
The-s at comparison is also (5s)/count:2 and (2s)/count:1
So:-S at is count:1 time=2.91s (2s) lock=0.00s (0s) rows=1000.0 (+), vgos_dba[vgos_dba]@[10.130.229.196] in front.

rows=1.0 (2) is shown in the following logic
(2) means a total of 2 recordsets returned in Count:2, row=1.0 display (2)/count:2, if Count is 3 at this point,
Then row is calculated as rows=2/3,rows=0.67.

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)

(EXT) Mysqldumpslow Summary of usage instructions