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.
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.1The 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.
Checkpoint: If command -v prints nothing, stop and install or repair the client package through your normal system administration process. Do not copy a binary from an unrelated host.
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 = NReal 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.logDo not make the log world-readable just to avoid sudo. Slow logs may contain query text, schema names and sensitive values.
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.
Checkpoint: Save the exact command and the log path beside any ticket or performance note. A report made from a rotated or truncated file cannot be compared fairly with the previous one.
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.logThe 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 --helpBy 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.
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.logFiltering 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.
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.logThere 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.
ls -l /var/log/mysql/. Do not guess a filename and treat an empty result as proof that the server is healthy.sudo for this read, or ask an administrator for a redacted report. Avoid changing ownership or permissions on the log.-a only when the values are needed and safe to display.-s value explicitly. A count ranking answers a different question from an average-time ranking.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.
mysqldumpslow symlink.