Read this post if you want to determine the cause of too many connections in MySQL and MariaDB. Too many database connections will, as a result, cause an “error establishing a connection to database” response.
Table of Contents
What does too many database connections mean?
It means that all the available database connections have been used. Thus, too many clients are currently connected. Or, in other words, it is when the number of threads connected exceeds the server variable defining the maximum number of connections. Therefore, you can only accept new database connections once any of the existing database connections are closed.
What can cause too many database connections?
Too many database connections can be caused by simultaneous or old connections that are not released in time. However, there are many things that you can do to mitigate this type of issue.
Firstly, check your code. It could be that your code is experiencing a race condition. A race condition occurs normally during parallel program execution. For example, a Bash script that is executed via Cron can enter a race condition. You must therefore ensure your Bash script is not concurrently executed. Click here for an example.
Secondly, check your database. It is good to limit the maximum number of connections to your database from a specific user. Additionally, ensure the time out for connections is suitable to your needs. This error may also be a symptom of slow queries or bottlenecks.
What is MySQL?
MySQL is an open-source relational database management system that can run on UNIX, Linux, Windows, etc. Additionally, MySQL is free and open-source software under the terms of the GNU General Public License.
What is MariaDB?
MariaDB is a popular database that the original developers made of MySQL. It is a community-developed, commercially supported fork of MySQL. Additionally, it intended to remain free and open-source software under the GNU General Public License. Finally, MariaDB focuses much of the development work on achieving feature parity with MySQL.
What is the maximum number of connections your database will handle?
The maximum number of database connections permitted is calculated based on RAM, disk space, CPU cores, network latency, average database connection size, and more.
For example, MySQL and MariaDB are configured for 151 connections by default. Another example is AWS RDS MySQL, where the default maximum number of connections is the instance type.
For example:
- t2.micro – max connections: 66
- t2.small – max connections: 150
- m3.medium – max connections: 296
- t2.medium – max connections: 312
- m3.large – max connections: 609
- t2.large – max connections: 648
- m4.large – max connections: 648
- m3.xlarge – max connections: 1,237
- r3.large – max connections: 1,258
- m4.xlarge – max connections: 1,320
- m2.xlarge – max connections: 1,412
- m3.2xlarge – max connections: 2,492
- r3.xlarge – max connections: 2,540
How to show the maximum number of connections?
You can use the ‘SHOW variables’ SQL command to determine the max connections on your database.
SHOW VARIABLES LIKE 'max_connections';Result:
What is the source of the database connections?
You can use the ‘SHOW processlist’ SQL command to show you which threads are currently running.
It will, for instance, provide the following details:
- User – The MySQL user who issued the statement.
- Host – The hostname of the client issuing the statement.
- Command – The SQL command that is executed.
The ‘show processlist SQL command:
SHOW processlistResult
How to change the maximum number of connections?
There are two ways to increase or decrease the maximum number of connections.
Update the my.cnf
First, open the “my.cnf” file for your database server so you can add to or edit the configuration.
The “my.cnf” file is typically located in one of the following locations:
- /etc/my.cnf
- /etc/mysql/my.cnf
- $MYSQL_HOME/my.cnf
- ~/.my.cnf
Then, add or edit the max_connections value-line under the [mysqld] section.
For example:
max_connections = 200Finally, restart the server for changes to persist.
Set the maximum connections variable
Run the following SQL command to set the maximum to a new number:
SET GLOBAL max_connections=200;Wrapping up
It is easy to determine the cause of too many connections in MySQL and MariaDB. First, you investigate where the connections are coming from using the ‘SHOW processlist’ command. You can then fix the code or the database configuration.
Very handy! Thanks for the tips.