Solve a MySQL/MariaDB "Too many connections" error

The MySQL ‘Too many connections’ error occurs when more queries are sent to a MySQL database than can be processed. The error can be fixed by setting a new number of maximum connections in the configuration file or globally.

How does the MySQL ‘Too many connections’ error happen?

A database can only handle a limited number of queries at a time. If the maximum is exceeded, MySQL displays the error message above. This is the case, for example, when a PHP script tries to establish too many simultaneous connections to the relational database. If you’re using a web application that uses a MySQL database, it is possible that the MySQL ‘Too many connections’ error occurs when demand is very high.

HiDrive Cloud Storage
Store and share your data on the go
  • Store, share and edit data easily
  • ISO-certified European data centres
  • Highly secure and GDPR compliant

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. SUPER is a MySQL privilege that grants admin rights to the user.

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 labour-intensive connections).
  • The acceptable response time.

According to the MySQL documentation, most Linux systems should be able to support 500 to 1,000 connections without difficulty.

HiDrive Cloud Storage
Store and share your data on the go
  • Store, share and edit data easily
  • ISO-certified European data centres
  • Highly secure and GDPR compliant

Change max_connections

The max_connections variable can be changed in two places:

Update the my.cnf file, so that the new value is used if the MySQL/MariaDB server is restarted.

Use the SET GLOBAL command to update the value on the running MySQL/MariaDB server. In this case, there is no need to restart MySQL/MariaDB, so you do not have to allow for any downtime of your database.

Display the number of connections in MySQL

To check the current number of max_connections log in to the MySQL/MariaDB command line client with the following command:

mysql -u root -p

Now, use the command:

SHOW variables;

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.

Update my.cnf file

Open the file /etc/my.cnf for editing with the command:

sudo nano /etc/my.cnf

Directly beneath the first line, you’ll see the following entry:

[mysqld]

Add a line to the entry:

max_connections=[desired new maximum number]

For example, to set max_connections to 200, the first two lines of your configuration file should look like this:

[mysqld]
max_connections=200

Save and exit the file.

Set number of connections globally

You can also set the maximum number of connections for your database globally. First log in to the MySQL/MariaDB command line client using the command:

mysql -u root -p

Adjust the new maximum number of your choice of 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;

Finally, exit MySQL/MariaDB with the command:

quit;
Web Hosting
Secure, reliable hosting for your website
  • 99.9% uptime and super-fast loading
  • Advanced security features
  • Domain and email included
Was this article helpful?