Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.4Mb
PDF (A4) - 38.4Mb
PDF (RPM) - 37.7Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 9.0Mb
Man Pages (TGZ) - 197.4Kb
Man Pages (Zip) - 305.9Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  mysqldumpslow — Summarize Slow Query Log Files

4.6.8 mysqldumpslow — Summarize Slow Query Log Files

The MySQL slow query log contains information about queries that take a long time to execute (see Section 5.4.5, “The Slow Query Log”). mysqldumpslow parses MySQL slow query log files and prints a summary of their contents.

Normally, mysqldumpslow groups queries that are similar except for the particular values of number and string data values. It abstracts these values to N and 'S' when displaying summary output. The -a and -n options can be used to modify value abstracting behavior.

Invoke mysqldumpslow like this:

shell> mysqldumpslow [options] [log_file ...]

mysqldumpslow supports the following options.

Table 4.22 mysqldumpslow Options

-aDo not abstract all numbers to N and strings to S
-nAbstract numbers with at least the specified digits
--debugWrite debugging information
-gOnly consider statements that match the pattern
--helpDisplay help message and exit
-hHost name of the server in the log file name
-iName of the server instance
-lDo not subtract lock time from total time
-rReverse the sort order
-sHow to sort output
-tDisplay only first num queries
--verboseVerbose mode

  • --help

    Display a help message and exit.

  • -a

    Do not abstract all numbers to N and strings to 'S'.

  • --debug, -d

    Run in debug mode.

  • -g pattern

    Consider only queries that match the (grep-style) pattern.

  • -h host_name

    Host name of MySQL server for *-slow.log file name. The value can contain a wildcard. The default is * (match all).

  • -i name

    Name of server instance (if using mysql.server startup script).

  • -l

    Do not subtract lock time from total time.

  • -n N

    Abstract numbers with at least N digits within names.

  • -r

    Reverse the sort order.

  • -s sort_type

    How to sort the output. The value of sort_type should be chosen from the following list:

    • t, at: Sort by query time or average query time

    • l, al: Sort by lock time or average lock time

    • r, ar: Sort by rows sent or average rows sent

    • c: Sort by count

    By default, mysqldumpslow sorts by average query time (equivalent to -s at).

  • -t N

    Display only the first N queries in the output.

  • --verbose, -v

    Verbose mode. Print more information about what the program does.

Example of usage:

shell> mysqldumpslow

Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log
Count: 1  Time=4.32s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1

Count: 3  Time=2.53s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1 limit N

Count: 3  Time=2.13s (6s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t1 select * from t1

User Comments
  Posted by Muruges Muthu on December 19, 2011
Count: 19 Time=11.16s (212s) Lock=0.00s (0s) Rows=1.0 (19) ...

Read the results as, this particular query got executed 19 times and 'Time=<Average time taken for one execution 11.16 seconds> (<Time taken for all 19 executions 212 seconds>)' ...
  Posted by Rick James on February 2, 2016
Which -s option should I use? Here are my tips:

`-s t` says to start with the queries that have the most impact on the server. If your system is having general performance problems, use that.

`-s at` is good for looking for queries that are the most sluggish to the end-users.
Sign Up Login You must be logged in to post a comment.