Monitor MySQL Connections

Many people will have experienced a "Too many connections" error when trying to connect to the mysqld server, this means that all available connections are in use by other clients, and no doubt impacted the availability of your application. Being one of the main causes of MySQL availability issues, it is therefore essential to monitor the number of connections in use, and receive a timely warning if you are approaching the limit of your MySQL Server.

The maximum available connections is defined by the max_connections server variable. It defaults to 151 in later versions of MySQL, and often people increase this to hundreds more to offset the chances of a too many connections error occurring.

A nice easy way to monitor connections in use compared with the maximum available connections is with the SQL provided below:

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 will provide a percentage figure output e.g. X% of your connections are in use etc. Why not plug it into your alerting framework (Alerts) and set a threshold e.g. 80%, and then receive a warning as soon as your connection count creeps up into the danger zone?