Monitoring the MySQL/MariaDB Maximum Connection Cap with Nagios

Just another beautiful day to have databases issues. This time, I have a beautiful MariaDB Galera Cluster running as a backend of a FreeSWITCH Cluster. It seems that operation does not take a single rest and it is keeping adding new extensions. New extensions mean more calls, which it can be translated to more databases queries and connections.

The issue here is not changing the max_connection value in the database configuration file. The issue, as I see, is that doing that will waste memory that could be used somewhere else. Therefore, the value needs to increased gradually; at some point, this will not be enough and more tunings, including database register cleaning might be done. For now, just let's configure an alert that will tell us when it is a good time to start thinking what to do.

The SQL Query

Please note I am not the original author of this query. But, since this is very handy, and it took me more than 3 hours to find it, I decided to post it.

SELECT ( pl.connections / gv.max_connections ) * 100 AS percentage_used_connections FROM ( SELECT COUNT(*) AS connections FROM information_schema.processlist ) AS pl, ( SELECT variable_value AS max_connections FROM information_schema.global_variables WHERE variable_name = "MAX_CONNECTIONS" ) AS gv

This query is awesome because it takes the value of max_connections and your current connections and makes a factor. So, it answers with a percent.

Be careful, you need a database user with enough privileges to access the information.

The Nagios Plugin

Install the Nagios plugin package nagios-plugins-mysql, it provides a plugin named check_mysql_query. Execute it something like this: 

/usr/lib64/nagios/plugins/check_mysql_query -u root -q 'SELECT ( pl.connections / gv.max_connections ) * 100 AS percentage_used_connections FROM ( SELECT COUNT(*) AS connections FROM information_schema.processlist ) AS pl, ( SELECT variable_value AS max_connections FROM information_schema.global_variables WHERE variable_name = "MAX_CONNECTIONS" ) AS gv' -w 85 -c 95

This will answer with a warning if 85% of the connections are being used at this moment, and with a critical signal if 95%.

Good luck!