Along with Oracle and Microsoft SQL server, MySQL is one of the most popular management systems for relational databases. Nowadays the software developed by MySQL AB belongs to the product portfolio of the Oracle Corporation and is proprietarily marketed under a dual license. Since 2009, an open source MySQL fork, known as MariaDB, has also been available from the original developers. Here we give...
Learn how to solve the MySQL/MariaDB "Too many connections" error, which occurs when all available MySQL/MariaDB connections are in use. This error may occur in a script which connects to MySQL/MariaDB, or on a webpage which is generated (in whole or in part) from elements provided by a MySQL/MariaDB database.
Choose a new maximum number of connections
The system variable max_connections determines the number of connections which MySQL/MariaDB will accept. The default value is 151 connections, which allows 150 normal connections plus one connection from the SUPER account.
The first thing to decide is what new maximum value you want to set for max_connections. There are several considerations to take into account when increasing the number of MySQL/MariaDB connections. The maximum number which can be supported by the system will depend on:
- The amount of available RAM.
- How much RAM each connection takes (simple queries will require less RAM than more labor-intensive connections).
- The acceptable response time.
According to the MySQL documentation, most Linux systems should be able to support 500-1000 connections without difficulty.
HiDrive Cloud Storage with IONOS!
Based in Germany, HiDrive secures your data in the cloud so you can easily access it from any device!
The max_connections variable will need to be changed in two places:
Fortunately, by using this method, you will not need to restart MySQL/MariaDB, and therefore will not need to experience any downtime.
Show the Current max_connections Value
To see the current number of max_connections log in to the MySQL/MariaDB command line client with the command:
mysql -u root -p
Use the command:
This will output a list of all of the variables which are set for MySQL/MariaDB. Scroll up through the list to find the value for max_connections.
Open the file /etc/my.cnf for editing with the command:
sudo nano /etc/my.cnf
Directly beneath the first line:
Add a line:
max_connections=[desired new maximum number]
For example, to set max_connections to 200, the first two lines of the file will read:
Save and exit the file.
Log in to the MySQL/MariaDB command line client with the command:
mysql -u root -p
Set the new max_connections value with the command:
SET GLOBAL max_connections=[desired new maximum number];
For example, to set max_connections to 200, the command is:
SET GLOBAL max_connections=200;
Exit MySQL/MariaDB with the command:
Web hosting with a personal consultant!
The host with the most: fast and scalable, including a free domain for one year and an email address. Trust web hosting from IONOS!