What are MySQL Connections? 

MySQL connections, simply put, are sessions. When you connect to your MySQL instance, you initiate a session, or, in other words, you a connection starts. 

For MySQL 5.5, for example, the limit of allowed connections is 151. If the number is exceeded, we will get a “Too many connections” error which means that all the available connections are used up and you need to terminate a session to free some of them up. The error looks somewhat like this: 

ERROR 1040: Too many connections 

Why Monitor Connections? 

As with everything software- related, MySQL needs to be monitored. This should be apparent without any questions: in order to improve the performance of our database queries, we monitor our database performance and add indexes and partitions, we keep an eye on sessions and other things. In order to improve database availability, we should set up replication. In order to improve the security of our databases, we should use strong passwords, hash all the passwords we store with a strong hashing algorithm (say, BCrypt) and preferably check whether our data appears in any leaked data breaches to protect ourselves from identity theft. In order to improve database capacity, we use or add hard drives to our infrastructure. 

However, what if we want to monitor small specific things inside of MySQL? One of those things might be the number of connections that are initiated. How exactly do we monitor connections? Do we do it manually? Do we use tools? What tools do we use and how do we monitor connections using them? 

Well, we have a couple of ways to deal with this issue. One of the most common and apparent is the fact that we can put MySQL queries to use. For example, by issuing a query like SHOW PROCESS LIST; we would have a list of all of the connections that are currently initiated inside of MySQL. However, in some cases, using such queries might not be ideal – we simply might not have the time to run queries at that moment, especially when we deal with a large number of SQL database instances. In that case, we might need to turn to tools that aim to solve our database issues – the monitoring of our MySQL database connections can be done with dbWatch.

Monitoring Connections with dbWatch 

In general, if we use tools to monitor anything, doing so should be simple. Thankfully, dbWatch is here to simplify our database tasks. Monitoring our database performance is quite simple – to monitor the number of connections, for example, launch dbWatch, import your MySQL server into it to start monitoring. Then observe the left side of the tool – you should see a few categories: 

In this case, expand the Availability category and,  you should see a “Maximum connections alert” – such an alert is very minimal (the only configurable parameters that it has are warning and alarm thresholds that are used to either trigger a warning or an alarm), but it’s very useful if you want to monitor your MySQL database connections. Just right- click on it and click “Run”, wait a couple of seconds, and it should be completed. 

Double click the details – you should see the full message. In this case, we are using six connections – we are far away from the maximum amount of connections that are allowed (we’re only using 4% of the maximum amount of connections), so that’s good news. 

If you want to, you can define different values for the parameters,  too – click Configure and set them as you wish: 

In this case, the default value for triggering a warning is 90. and the default value for triggering an alarm is 95 (the values are to be specified in percentages, not actual amounts, so keep that in mind when setting them up) – set the job up and consider running it from time to time to make sure your MySQL connections are up to par with the performance requirements for your database. 

Summary

Monitoring the performance of your MySQL database instances is very important., However, what is also important is the monitoring of small things inside of these instances. One of these things is the number of database connections – use dbWatch to monitor these and you should be well on your way to improved MySQL database instance performance in the future. 

Should you want to learn more about dbWatch, stay and have a look through the dbWatch blog, and if you have any questions, feel free to contact the team – they will be glad to help you out in any case. 

-------------------

Other blogs:

Monitoring SQL Server Collations in dbWatch

SQL Server and Hard Drives with dbWatch

-------------------

About the Author:

Lukas Vileikis is an ethical hacker and a frequent conference speaker.

Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania.

He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.