banshee/services-collection/services/mysql/ssh/connections

MySQL and MariaDB connections, by SSH

Returns the connections of a MySQL or MariaDB server against its max_connections, the threads running a query (Threads_running), the most ever connected at once, the connections refused because the limit was reached, and the failed connection attempts, read by the mysql client on the database server over SSH. Four watches are offered, each one set on the probe. No password leaves banshee.

Versions

MySQL 5.6 and later, MariaDB 10.0 and later (Connection_errors_max_connections). Generic: no folder of version. Percona Server reads as MySQL.

How it works

The collector runs, on the server, LC_ALL=C mysql --batch --skip-column-names -e "SHOW GLOBAL STATUS; SHOW GLOBAL VARIABLES LIKE 'max_connections'", the statement quoted as one argument, over the local socket. Each line is a name and a value, separated by a tab:

Threads_connected	23
Threads_running	2
Max_used_connections	61
Connection_errors_max_connections	0
Aborted_connects	4
max_connections	151

connections is Threads_connected; connections-percent is it against max_connections. One more connection is kept for an account with SUPER (or CONNECTION_ADMIN): an ordinary account is refused at 100 %.
running is Threads_running, the threads executing a statement now: when it stays high, queries queue up for the processors or for locks.
refused is Connection_errors_max_connections, and aborted is Aborted_connects (a wrong password, a connection dropped during the handshake): both only grow until the server restarts, and their watches raise when they grow.
A server whose socket refuses or that does not answer (ERROR 2002, ERROR 2003) is critical: "MySQL is not running". Any other failure of the client (the account refused, ERROR 1045 or 1698, the client missing) stops the collector misconfigured, with what the client said. A server that lacks one of the figures stops it misconfigured.
Nothing is written: SHOW only reads.

Prerequisites

An SSH account for monitoring, without write rights (monitoring below), on the database server, with the mysql client in its path (installed with the server; MariaDB 11 prints a notice that mysql is an old name, which is ignored).
A database account of the same name, logged in over the local socket by the system account's own name, without any password, and given no privilege: the global status needs none.

MySQL (auth_socket, loaded by default on Debian and Ubuntu; elsewhere INSTALL PLUGIN auth_socket SONAME 'auth_socket.so'):

CREATE USER 'monitoring'@'localhost' IDENTIFIED WITH auth_socket;

MariaDB (unix_socket, loaded by default from 10.4):

CREATE USER 'monitoring'@'localhost' IDENTIFIED VIA unix_socket;

Where socket authentication is not wanted, an account with a password in the system account's own ~/.my.cnf (mode 0600) does it instead, the password staying on the server:

[client]
user = monitoring
password = <the password>

No sudo.
The fingerprint of the server's host key, set in protocol:ssh:fingerprint (not needed when the Security settings trust the first SSH fingerprint presented).

Outputs

Name	Label	Unit	Per instance
`connections`	Threads connected	—	no
`connections-percent`	Connections against max_connections	%	no
`running`	Threads running	—	no
`max-used`	Most connections at once since start	—	no
`refused`	Connections refused at max_connections since start	—	no
`aborted`	Failed connection attempts since start	—	no

Watches

Name	Title	Kind	Defaults	Checked by default
`saturation`	Connections near max_connections	above a threshold, on `connections-percent`	warning above 80 %, critical above 90 %	yes
`refused`	Connections refused	increase, on `refused`	critical beyond 0 in 1 h	yes
`running`	Many threads running	above a threshold, on `running`	warning above 50, for 3 runs	yes
`aborted`	Failed connection attempts	increase, on `aborted`	warning beyond 100 in 1 h	no

"Many threads running" depends on the machine: a few times its number of processors is a usual threshold.

Limits

One server per probe, the one of the default socket; a second instance on the same machine is not reached.
The messages of the client are read in English (LC_ALL=C) to tell a server down from a refused account.
Not checked against a MySQL or a MariaDB: the status variables are those of the MySQL reference manual ("Server Status Variables") and of the MariaDB knowledge base.