Rank the Worst Queries in a MariaDB Slow Log with mysqldumpslow

A slow query log full of raw lines is only useful once mysqldumpslow turns it into a ranked list you can act on. You will finish with a ranked summary of a MariaDB slow query log, plus filters for the query patterns you actually need to investigate. The installed command is mysqldumpslow, a symlink to mariadb-dumpslow from the mariadb-client package, version 10.11.14-0ubuntu0.24.04.1 on this system.

Allow about fifteen minutes. You need shell access and read access to the slow-log file. This guide only reads and summarises a log. It does not enable slow logging, restart MariaDB or change a query.

1. Confirm the installed command

Check the name and package before relying on an option. This is an ordinary command and does not need elevated privileges:

$ command -v mysqldumpslow
/usr/bin/mysqldumpslow
$ readlink -f /usr/bin/mysqldumpslow
/usr/bin/mariadb-dumpslow
$ dpkg-query -W -f='${Package} ${Version}\n' mariadb-client
mariadb-client 1:10.11.14-0ubuntu0.24.04.1

The legacy name remains useful in scripts and in existing runbooks. The current MariaDB name is mariadb-dumpslow; both names invoke the same installed program here.

2. Run the default summary

Give the command one or more slow-log paths. If you omit the path, the program looks for a matching server slow log based on its host and instance defaults. An explicit path is easier to audit:

$ mysqldumpslow /var/log/mysql/mariadb-slow.log
Reading mysql slow query log from /var/log/mysql/mariadb-slow.log
Count: 12  Time=1.82s (21s)  Lock=0.00s (0s)  Rows=4.0 (48), app[app]@localhost
 select id, name from products where category_id = N

Real output depends on your log. The default summary abstracts numbers to N and strings to 'S', then groups similar statements. That is the useful default: different category IDs can appear as one workload pattern instead of twelve separate lines.

If the file is protected, read access is the only reason to use elevated privileges in these examples:

$ sudo mysqldumpslow /var/log/mysql/mariadb-slow.log

Do not make the log world-readable just to avoid sudo. Slow logs may contain query text, schema names and sensitive values.

3. Limit the report to the top patterns

Use -t when you want a short investigation queue. This example shows the first five entries in the normal sort order:

$ mysqldumpslow -t 5 /var/log/mysql/mariadb-slow.log
Reading mysql slow query log from /var/log/mysql/mariadb-slow.log
Count: 12  Time=1.82s (21s)  Lock=0.00s (0s)  Rows=4.0 (48), app[app]@localhost
 select id, name from products where category_id = N
... up to five grouped entries ...

The report is not a live measurement and the first entry is not automatically the best optimisation target. It is a summary of the records present when the command reads the file. Compare the count, total time, lock time and rows with what the application considers important.

4. Choose a sort that answers one question

The -s option changes the ranking. Use average query time for slow individual executions, count for frequent patterns, or rows examined when the server is scanning too much data:

$ mysqldumpslow -s at -t 10 /var/log/mysql/mariadb-slow.log
$ mysqldumpslow -s c -t 10 /var/log/mysql/mariadb-slow.log
$ mysqldumpslow -s ae -t 10 /var/log/mysql/mariadb-slow.log

The installed manual names at average query time, c count and ae aggregate rows examined. It also documents row, lock, sent-row and affected-row measures and their average forms. Check the local help before copying a sort name into automation:

$ mysqldumpslow --help

By default, results are sorted with the largest values first. Add -r to reverse the order. Reversing a report can be useful for checking its tail, but it is an easy distraction when you meant to inspect the worst entries.

5. Filter by a query pattern

Use -g to consider only queries matching a grep-style pattern. Quote the pattern so the shell does not interpret punctuation:

$ mysqldumpslow -g 'products' -s c -t 10 /var/log/mysql/mariadb-slow.log

Filtering is text matching, not SQL parsing. A pattern may match comments, values or a different statement than you intended. Start with a distinctive table or verb, inspect the output, then make the pattern more specific. If the result is empty, remove the filter and confirm that the log contains the expected statements before debugging the regular expression.

6. Decide whether abstraction is hiding useful detail

Keep the default abstraction when you are comparing workload shapes. Add -a when the literal numbers and strings matter to the investigation:

$ mysqldumpslow -a -g 'category_id' -t 20 /var/log/mysql/mariadb-slow.log

There is a privacy trade-off: less abstraction can expose identifiers or application data in terminal output and saved reports. The -n option changes how numbers with at least a chosen number of digits are abstracted within names. For a first pass, leave both behaviours at their defaults and only relax them for a clear diagnostic question.

7. Check common failures

• File not found: confirm rotation and the current path with ls -l /var/log/mysql/. Do not guess a filename and treat an empty result as proof that the server is healthy.
• Permission denied: use sudo for this read, or ask an administrator for a redacted report. Avoid changing ownership or permissions on the log.
• Unexpected grouping: remember that numbers and strings are abstracted by default. Re-run with -a only when the values are needed and safe to display.
• Unexpected ranking: state the chosen -s value explicitly. A count ranking answers a different question from an average-time ranking.
• No useful rows: verify that this is a file-based slow log and that it contains complete slow-query records. The tool summarises files; it does not query the MariaDB server.

Nothing in this workflow changes MariaDB configuration, so there is no undo step. Remove any report you created from the log according to your normal data-retention rules, especially if you used -a.