GoAdmio

mysqltop

Report current MariaDB activity and today's heaviest queries, and respond automatically when the server load is high

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:

[!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

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

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:

  1. finds the database with the highest total connection TIME
  2. looks up that database's domain in GoAdmio's list of databases (/var/lib/sqlite/mysql.db)
  3. prints Turn On Under Attack Mode: <domain> and adds a line to /var/log/go/underattack.log
  4. runs underattack <domain>, which enables Cloudflare Under Attack Mode for that domain (see underattack)
  5. runs iptop underattack
  6. 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

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