A Detailed Guide to Using mysqldumpslow for MySQL Slow Query Log Analysis

How to Use the mysqldumpslow Tool to Analyze Slow Query Logs

mysqldumpslow is a Perl script tool included with MySQL for parsing and analyzing slow query log files. It helps database administrators quickly identify slow-executing SQL statements and is a common tool for performance tuning.

1. Locate the Tool

First, find the mysqldumpslow tool on your system. It is typically located in the MySQL installation directory.

find / -name mysqldumpslow

Alternatively, use the which command:

which mysqldumpslow

2. Common Analysis Command Examples

Here are some of the most commonly used analysis commands, assuming your slow query log file path is /var/log/mysql/slow.log.

Top 10 SQL Statements by Frequency

Sort by the number of occurrences (Count) in descending order, showing the top 10.

mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

Top 10 SQL Statements by Largest Result Sets

Sort by the average number of rows returned (Rows sent) in descending order, showing the top 10.

mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

Top 10 Longest-Running SQL Statements Containing "LEFT JOIN"

Sort by average query time (Query time) in descending order, filtering for statements containing "left join", showing the top 10.

mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/slow.log

3. View Full Help Information

To see all available parameters and their descriptions, use the --help option.

mysqldumpslow --help

4. Core Parameter Details

Below are the most important and commonly used parameters for mysqldumpslow:

• -s ORDER: Specifies the sort order. This is one of the most important parameters.
  • c: Sort by count (frequency).
  • t: Sort by average query time.
  • l: Sort by average lock time.
  • r: Sort by average rows sent.
  • at: Sort by average query time (default).
  • al: Sort by average lock time.
  • ar: Sort by average rows sent.
  • ac: Sort by average count.

  Note: Parameters c, t, l, r sort in descending order (largest first). The prefixed versions (ac, at, al, ar) sort in ascending order (smallest first).

• -t NUM: Show only the top NUM results (Top N).
• -g PATTERN: Filter using a case-insensitive regular expression, showing only statements containing this pattern.
• -r: Reverse the sort order. Often used with -s, e.g., -s t -r sorts query time in ascending order.
• -a: Do not abstract numbers to 'N' and strings to 'S'. By default, the tool abstracts specific values to group similar queries; this parameter shows the raw values.

5. Usage Tips and Best Practices

• Combine Commands: Typically, first use -s c -t 20 to find the most frequent slow queries, then use -s t -t 10 to identify the most time-consuming queries for optimization.
• Check Path: If mysqldumpslow is not in your system PATH, specify the full path, e.g., /usr/local/mysql/bin/mysqldumpslow.
• File Permissions: Ensure the user running the command has read permission for the slow query log file.
• Interpreting Results: In the output, abstracted values (like N, S) represent a class of queries. Using the -a parameter shows specific values but causes similar queries to be counted separately.

By mastering mysqldumpslow, you can quickly pinpoint key performance bottleneck SQL statements from vast slow query logs, providing clear direction for subsequent optimization work like index tuning and SQL rewriting.