querytop
Show the ten most frequently executed SELECT queries from MariaDB's query log, turning on query logging first if needed
- Command:
/go/querytop - Category: Monitoring
- Required options: None
- Output: A header and up to ten lines of
count query - Last updated: 2026-11-19
On this page: Overview · Quick start · Syntax · Output · Examples · Troubleshooting · Details · See also
Overview
/go/querytop reads MariaDB's general query log, /var/log/mysql.log, and prints the ten SELECT statements that appear most often.
If the log is missing, nearly empty, or hasn't been written to in the last hour, the command assumes query logging is off and turns it on by running my --log. That step regenerates /etc/my.cnf and restarts MariaDB. It then asks you to try again after five minutes.
[!WARNING] The first run on a server without query logging has side effects: MariaDB restarts, which drops connections, and the general query log stays on afterwards. That log records every query, including any that contain passwords or personal data, and can grow quickly. See Turning logging off.
Quick start
# Show the most frequently executed SELECT queries
/go/querytop
On the first run, it starts logging and exits. Run it again after about five minutes.
Syntax
/go/querytop
The command takes no options. Any arguments are written to the log but otherwise ignored.
Output
When the log is active:
MOST FREQUENTLY EXECUTED SQL QUERIES:
-------------------------------------
1432 SELECT option_value FROM wp_options WHERE option_name = 'siteurl' LIMIT 1
876 SELECT COUNT(*) FROM wp_posts
...
Each line is the number of times that exact text appears, followed by the query text from SELECT to the end of the line.
When logging has to be started:
Start MariaDB logging...
Try again after 5 minutes.
| Outcome | What you get |
|---|---|
| Log is active | The ranking, exit status 0. |
| Logging was started | The two messages above (plus any output from my), exit status 1. |
The exit status 1 after starting logging means "try again later". It is not an error.
Examples
# First run: starts logging and exits
/go/querytop
# Run again after a few minutes
/go/querytop
# Check that the general query log is on
mariadb -e "SHOW VARIABLES LIKE 'general_log%';"
# Check the size of the log
ls -lh /var/log/mysql.log
# Turn logging off again (regenerates the configuration and restarts MariaDB)
/go/my
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
Start MariaDB logging... then Try again after 5 minutes. |
The log is missing, nearly empty (10 bytes or less), or hasn't changed in the last hour. | Wait a few minutes, then run again. |
| The same message every time | Logging never started because my stopped early (under 512 MB of RAM, or the backup lock is held), or the server runs almost no queries, so the log doesn't change within an hour. Running my without --log also empties the log. |
Read the messages printed by my. Generate some traffic and run again. |
| MariaDB restarted when you ran it | The command ran my --log. |
Expected on first use. Avoid running it on a live server during busy periods. |
| The log keeps growing after you finished | The general query log stays on until my is run without --log. |
See Turning logging off. |
Only SELECT queries appear |
The command only matches SELECT. |
Use the reports from mysqltop for all statement types. |
| The same query appears many times with different values | Queries are compared as exact text, so WHERE id = 1 and WHERE id = 2 are counted separately. |
Use the top- report from mysqltop, which groups similar queries. |
| A line that isn't a real query appears | The match is any text containing select in any letter case, including text inside stored data. |
Ignore it, or check the log by hand. |
| The command is slow | The log can be very large on a busy server, and the whole file is sorted. | Run my to move the log aside (it restarts MariaDB), or keep the logging window short. |
| Monitoring queries appear in the ranking | The general log also records queries from tools such as mysqltop. |
Ignore them, or filter them out by hand. |
Details
Requirements
- Run it as
root. - The general query log must be on and writing to
/var/log/mysql.log. The command turns it on itself if needed.
How it works
flowchart TD
A["querytop"] --> B["Write log line"]
B --> C{"/var/log/mysql.log exists?"}
C -- "No" --> E["Run my --log"]
C -- "Yes" --> D{"Over 10 bytes and changed in the last hour?"}
D -- "No" --> E
E --> F["Print: try again after 5 minutes, exit 1"]
D -- "Yes" --> G["Count SELECT queries"]
G --> H["Print the top 10"]
When it starts logging
The command treats logging as active only if both are true:
| Check | Meaning |
|---|---|
| The log is larger than 10 bytes | A freshly emptied log holds a single newline, so it counts as empty. |
| The log changed within the last 3600 seconds | On a busy server, the general log changes constantly. |
If either check fails, or the file doesn't exist, the command runs my --log. That rewrites the configuration with the slow query log and the general query log turned on, and restarts MariaDB. It does not check whether logging was already on, so a quiet server can be restarted again on a later run.
What it counts
The command runs this pipeline on the log:
- keeps every part of a line that starts with
SELECT(any letter case) and runs to the end of the line - sorts the lines and counts identical ones
- prints the ten highest counts
This has some limits:
- Only
SELECTstatements are counted.INSERT,UPDATE, andDELETEstatements are not, except for anySELECTpart inside them. - Queries are not normalized, so similar queries with different values are counted as different queries.
- The log doesn't say which database or account ran a query, and this output doesn't either.
- Only the first line of a query that spans several lines is used.
- It covers the whole log, not only the last hour. The log is emptied only when
myruns.
Turning logging off
Nothing in this command turns logging off. Run my without --log. It comments out the logging settings, moves the old logs into /var/log/go/mysql/, and restarts MariaDB. It also regenerates /etc/my.cnf from scratch, so read its page before running it.
Exit status
| Situation | Exit status |
|---|---|
| Logging was started | 1 |
| The ranking was printed | 0 |
Log file
Each call appends a line with all its arguments to /var/log/go/querytop.log:
[Nov 19 12:00:00]# querytop
See also
| Command | Description |
|---|---|
my |
Regenerate the MariaDB configuration file with settings tuned to the server's RAM, CPU, and database usage, then restart MariaDB |
mysqltop |
Report current MariaDB activity and today's heaviest queries, and respond automatically when the server load is high |
index |
Automatic indexing for MySQL performance optimization |
mysql |
Create, edit, or delete a MySQL/MariaDB database and user for a hosting account |
dbls |
List all databases and their sizes |
cpu |
Check the top ten CPU-consuming commands or services |