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
  • Backed up and highly secure
  • Sync with all devices

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 labor-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.

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;
Was this article helpful?
Go to Main Menu