GoAdmio

querytop

Show the ten most frequently executed SELECT queries from MariaDB's query log, turning on query logging first if needed

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

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:

  1. keeps every part of a line that starts with SELECT (any letter case) and runs to the end of the line
  2. sorts the lines and counts identical ones
  3. prints the ten highest counts

This has some limits:

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