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.

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.

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.

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.

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

Now, 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:

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

Add a line to the entry:

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

Save and exit the file.

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:

Adjust the new maximum number of your choice of the new max_connections value with the command:

For example, to set max_connections to 200, the command is:

Finally, exit MySQL/MariaDB with the command: