mysqltop
Report current MariaDB activity and today's heaviest queries, and respond automatically when the server load is high
- Command:
/go/mysqltop - Category: Monitoring
- Required options: None
- Output: Up to three text sections on screen, plus three daily report files
- Last updated: 2026-11-19
On this page: Overview · Quick start · Syntax · Output · Examples · Troubleshooting · Details · See also
Overview
/go/mysqltop looks at what MariaDB is doing right now and at the queries it has run, and it can also take action when the server is under heavy load. It:
- prints the queries currently running, the connections per user, and the busiest databases
- saves three daily reports of the heaviest queries to
/var/log/go/mysql/ - when the server load reaches 100% or more, treats the busiest database's website as under attack: it turns on Cloudflare Under Attack Mode for that domain and sends an email and a WhatsApp alert
- when the load is between 10% and 99%, checks for bot traffic with
iptop
[!WARNING] This is not only a report. At high load it changes Cloudflare settings and sends alerts. See Automatic response and the known problems before relying on it.
The index lists this command as "Identify the most accessed databases on a GoAdmio server". It does more than that.
Quick start
# Show current activity, write today's reports, and react if the load is high
/go/mysqltop
Syntax
/go/mysqltop
The command takes no options. Any arguments are written to the log but otherwise ignored.
Output
Each section is printed only when it has rows, and is followed by a line of dashes.
| Section | What it shows |
|---|---|
Currently Running Queries: |
ID, USER, DB, COMMAND, TIME, STATE, and INFO for every connection that has a database selected. |
Active Queries: |
The ten users with the most connections, with their counts. |
High CPU Threads: |
The ten databases with the highest total connection TIME, with their connection counts. |
The three section names are not always accurate. See Reading the screen output.
When the load is 100% or more, it also prints Turn On Under Attack Mode: <domain>.
The daily reports are written silently to files. See Daily reports.
Examples
# Run it
/go/mysqltop
# See the reports written today
ls /var/log/go/mysql/
head -n 20 /var/log/go/mysql/slowest-$(date +%F).log
head -n 20 /var/log/go/mysql/top-$(date +%F).log
# Check the numbers behind the load percentage
awk '{print $1}' /proc/loadavg
grep -c processor /proc/cpuinfo
# See when the automatic response ran
tail -n 5 /var/log/go/underattack.log
# Delete reports older than 30 days (the command never deletes them)
find /var/log/go/mysql \( -name 'slow-20*' -o -name 'slowest-20*' -o -name 'top-20*' \) -mtime +30 -delete
# Make the next reports cover today only (clears the statistics for all digests)
mariadb -e "TRUNCATE performance_schema.events_statements_summary_by_digest;"
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
Instance (<name>) already running. |
A backup, or another command that uses the same lock file, is running. A lock older than one hour is ignored. | Wait. Note that nothing is reported and no response happens while the lock is held. |
| No sections appear | No connection has a database selected, so every query returned no rows. | Normal on an idle server. |
Lines that only say NULL in Currently Running Queries: |
Idle connections have no query text, and a missing value makes the whole row NULL. |
Normal. Only connections with every field filled in show details. |
The Active Queries: counts look too high |
It counts every connection, idle ones included, and the command's own connection is counted under root. |
Treat the number as connections, not running queries. |
| The wrong database or website is blamed | The ranking uses total connection TIME, which includes idle time of sleeping connections. |
See Known problems. |
A password prompt appears, or the alert says Website Under DDoS Attack with no domain |
At high load the command looks up the busiest database with a password variable that the script never sets. | See Known problems. |
Turn On Under Attack Mode: is blank |
The busiest database isn't registered in GoAdmio, or the lookup failed. | See Known problems. |
| Under Attack Mode was turned on without an attack | Any load of 100% or more triggers it, including heavy queries, backups, and legitimate traffic. | Check the traffic, and see underattack for how to turn the mode off. |
| Repeated emails and WhatsApp messages while the load stays high | The script has no cooldown. | Check whether sendmail and sendwhatsapp limit repeats. They aren't covered here. |
| A report file is empty or missing | performance_schema is off, nothing was seen today, /var/log/go/mysql doesn't exist, or MariaDB was restarted (which clears the statistics). |
Check mariadb -e "SELECT @@performance_schema;", and create the folder. my turns performance_schema on. |
| The counts in a report are larger than today's traffic | The figures are totals since MariaDB last started (see Daily reports). | Truncate the digest table (see Examples), or read them as running totals. |
Access denied errors |
The command connects as the root database user without a password, which needs socket authentication. | Check that mariadb works as root without a password. |
The /var/log/go/mysql folder fills up |
New files are written every day and none are deleted. | Delete old files (see Examples). |
Details
Requirements
- Run it as
root. column,sqlite3, and the helper commandsunderattack,iptop,sendmail, andsendwhatsapp.- A MariaDB root account that works without a password (socket authentication).
performance_schematurned on for the daily reports.- The folder
/var/log/go/mysql.
How it works
flowchart TD
A["mysqltop"] --> B["Write log line"]
B --> C{"Lock held in the last hour?"}
C -- "Yes" --> X["Message, exit 1"]
C -- "No" --> D["Work out the server load"]
D --> E["Print the three sections"]
E --> F["Write the three daily reports"]
F --> G{"Load"}
G -- "100% or more" --> H["Under Attack Mode, iptop, email, WhatsApp"]
G -- "10% to 99%" --> I["iptop robot"]
G -- "Below 10%" --> J["Done"]
Server load
The load is the 1-minute load average divided by the number of processor threads, as a whole percentage:
load % = int(1-minute load average × 100 ÷ threads)
A value of 100 means the load average equals the thread count. It is a load figure, not CPU usage, even though the alert text calls it CPU.
Reading the screen output
- Currently Running Queries lists connections that have a database selected, so the command's own connection is not shown. Idle connections print as
NULL(see Troubleshooting). - Active Queries counts all connections per user, idle or not, including this command's own connection.
- High CPU Threads orders databases by the sum of their connections'
TIME.TIMEis the seconds a connection has spent in its current state, so a pool of idle connections can outrank a database that is actually busy. The second column is a connection count, although it is headedActive Queries.
Daily reports
Three files are written for the current date, each with the top 100 query patterns seen today. Each run overwrites that day's file, so it holds the latest snapshot. Times are in seconds.
| File | Sorted by | Columns |
|---|---|---|
/var/log/go/mysql/slow-YYYY-MM-DD.log |
Average time | Database, Exec Count, Avg Exec Time, Sum Exec Time, Query, Last Seen |
/var/log/go/mysql/slowest-YYYY-MM-DD.log |
Total time | Database, Exec Count, Sum Exec Time, Avg Exec Time, Query, Last Seen |
/var/log/go/mysql/top-YYYY-MM-DD.log |
Execution count | Database, Exec Count, Query |
The reports come from MariaDB's performance_schema statement digests. A query pattern is included if it was last seen today, but its counts and times are totals since MariaDB last started, or since the table was last cleared. Statements run without a selected database are left out. The statistics live in memory, so a restart (for example by my) clears them.
Automatic response
| Server load | Action |
|---|---|
| 100% or more | See below. |
| 10% to 99% | Runs iptop robot (checks for non-human visitors). See iptop. |
| Below 10% | Nothing. |
At 100% or more, the command:
- finds the database with the highest total connection
TIME - looks up that database's domain in GoAdmio's list of databases (
/var/lib/sqlite/mysql.db) - prints
Turn On Under Attack Mode: <domain>and adds a line to/var/log/go/underattack.log - runs
underattack <domain>, which enables Cloudflare Under Attack Mode for that domain (seeunderattack) - runs
iptop underattack - sends an email and a WhatsApp message titled
Under DDoS Attack (CPU: <load>%)
The script assumes that a load of 100% or more means the busiest database's site is being attacked. It never turns Under Attack Mode off. Under Attack Mode makes visitors pass a browser check, which can block legitimate automated clients such as API callers. This page doesn't cover what iptop does with its robot and underattack arguments, or how sendmail and sendwhatsapp handle repeated calls.
Known problems
- Missing password variable. The lookup of the busiest database uses
mariadb -uroot -p$MySQLRootPassword, but this script never setsMySQLRootPassword. Unless it is exported in the environment, the password is empty. Run from a terminal,mariadbprompts for a password. Run without a terminal, the lookup fails, the database name is empty, and the domain is empty. The command then still callsunderattackwith no domain and sends alerts that readWebsite Under DDoS Attack. Whatunderattackdoes with an empty domain isn't covered here. - Idle time inflates the ranking. The database chosen for Under Attack Mode is picked by total connection
TIME, so an idle connection pool can make an innocent database the target. - Load alone triggers the response. There is no check for the actual traffic.
- Skipped during backups. While a backup holds the lock, the command exits immediately, so there is no monitoring or response, usually when the load is highest.
- No cooldown. The script itself doesn't limit how often it sends alerts.
Lock
The command checks /usr/local/goadmio/lock/backup.lock and exits if the file is younger than one hour. It only checks the lock. It doesn't create it.
Exit status
| Situation | Exit status |
|---|---|
| The lock is held | 1 |
| Anything else | The status of the last command that ran, normally 0. |
Log file
Each call appends a line with all its arguments to /var/log/go/mysqltop.log. When the automatic response runs, a line is also added to /var/log/go/underattack.log:
[Nov 19 12:00:00]# Turn On Under Attack Mode: example.com (/go/mysqltop)
See also
| Command | Description |
|---|---|
querytop |
Show the most frequently executed MySQL SQL queries |
underattack |
Enable Cloudflare Under Attack Mode for a domain |
iptop |
Track the most visited IP addresses on a server |
my |
Regenerate the MariaDB configuration file with settings tuned to the server's RAM, CPU, and database usage, then restart MariaDB |
index |
Automatic indexing for MySQL performance optimization |
cpu |
Check the top ten CPU-consuming commands or services |
loadavg |
Check the weekly server load average |
ddos |
Track and block DDoS attack IPs in GoAdmio |