Monitoring MySQL and MariaDB
MySQL and MariaDB are recognised by their server process, and on a stock Debian or Ubuntu
install the deep probes work with no credentials at all: the agent talks to the server over
its Unix socket the same way root on the box does. Where root is password-protected, one
local command adds a read-only monitoring user and nothing else changes.
How the server is recognised
Two definitions cover the two server binaries, mariadbd and mysqld; they produce the
same metrics under the same names, so rules and dashboards do not care which one runs.
Three thin companion definitions match the installed package (mariadb-server,
mysql-server, mysql-community-server) on systemd hosts and report whether the unit is
active. That split matters: a definition matched on a running process stops matching when
the process dies, so the "is it running" signal deliberately comes from the package side,
which keeps reporting while the server is down.
What is collected
Every minute, over the local socket:
- Connections: current client connections, how many threads are executing right now,
and the configured
max_connections, from which the agent derives the percentage of the connection limit in use. - Slow queries, aborted connects and InnoDB row-lock waits, each both as a running total and as a per-minute rate computed on the host, so a rule can watch the rate rather than a counter that only grows.
- Queries per second since the server started, bytes sent, uptime.
- Facts: version, data directory, socket path, error log path, slow log path and whether slow logging is on.
When the slow query log is enabled, the agent follows it and counts entries into a per-second slow-query rate. A relative slow-log path is resolved against the data directory, the way the server itself resolves it. Logging switched off is reported as exactly that, not as a missing file.
| Signal | Metric |
|---|---|
| Client connections | svc.mysql.connections |
| Queries executing now | svc.mysql.running |
| Connection limit and share used | svc.mysql.max_connections, svc.mysql.connections_used_pct |
| Slow queries, total and per minute | svc.mysql.slow_queries, svc.mysql.slow_queries_per_min |
| Aborted connects, total and per minute | svc.mysql.aborted_connects, svc.mysql.aborted_connects_per_min |
| Row-lock waits, total and per minute | svc.mysql.innodb_row_lock_waits, svc.mysql.innodb_row_lock_waits_per_min |
| Slow log entries per second | svc.mysql.slow_rate |
| Server unit active | svc.mysql.unit_active |
Names, tags and units are in the metrics reference.
Logs. The server error log ships by default at notice level and above, read from the journal where the distribution logs there and from the file the server names otherwise.
Alerts out of the box
| Rule | Fires when | Severity | Ships |
|---|---|---|---|
| MySQL is not running | the unit reports inactive for 5 minutes | warning | on |
| Running out of connections | 85% or more of max_connections in use for 5 minutes | warning | on |
| Clients cannot connect | 10 or more aborted connects a minute for 5 minutes | warning | on |
| Queries are not finishing | 10 or more queries executing at once, held for 5 minutes | warning | on |
| Queries are waiting on row locks | 60 or more row-lock waits a minute for 10 minutes | warning | auto |
| A burst of slow queries | 10 or more slow queries a minute for 10 minutes | warning | auto |
The thresholds come from measurement rather than taste. A healthy server keeps "threads running" near one however busy it is, because a query that finishes in milliseconds is almost never caught in a sample; a number that climbs and stays means queries are queueing behind a lock or a stalled purge. Ten held for five minutes clears normal spikes and still calls a stall while someone can act. Aborted connects sit at a low constant floor on every monitored server (the monitoring session itself opens and closes), so the rule looks for an order of magnitude above it. The two rate rules arm themselves once the counters flow and watch the per-minute rates, never the totals: a lock wait a second for ten minutes is a hot row, ten slow queries a minute for ten minutes is a query plan that went wrong after a deploy or a table that outgrew its index, and both close by themselves when the rate drops. Thresholds and hysteresis for each rule are in the rules catalog.
Credentials
Nothing is needed on a server where root@localhost authenticates by socket, which is the
default on Debian, Ubuntu and MariaDB packages. If root has a password, create a user that
can read status and variables and store it on the host:
sudo wakora secret set mysql --user wakora
The command prompts for the password; it is stored encrypted on the host, bound to that
machine, and never sent anywhere. The definition names the credential mysql, so no
further configuration is needed: the probes pick it up on their next cycle. Until then the
check states plainly that access was denied and which command fixes it. See
Secrets stay local.
When a path is not where the server says it is
The slow log and error log paths come from the running server's variables. To point the agent elsewhere:
sudo wakora --set mysql.slow-log=/var/log/mysql/slow.log
Keys are listed in Agent configuration.
Limitations
- Replication lag and per-database statistics are not collected.
- "Is it running" needs the systemd unit of an installed package. On a host without systemd, or where the server runs from a tarball, the process-matched definition simply disappears when the server dies; the host-level checks still tell you the port closed.
- Multiple instances on one host are not told apart.
- The slow-query rate needs the slow log switched on in the server; the agent does not change server settings.
- Queries go over the driver, never through
mysqlormysqladminon a command line, so no password ever appears in a process list.
Related
- Monitoring PostgreSQL, Monitoring Redis
- Monitoring PHP-FPM for the application side of a LAMP box
- Monitoring for hosting providers on the main site