How can I increase the max_connections in MySQL?
When trying to connect to my database error:
error connecting: Error: ER_CON_COUNT_ERROR: Too many connections.
How can I increase max_connections?
From what I know, if you would like to persistently have this setting for your MySQL databases, you would modify the
max_connection setting in your MySQL configuration file. The location of this file may vary depending on the version of MySQL:
You could also determine where the location of the configuration file is by running
mysqld --help --verbose. That command will output something along these lines:
Default options are read from the following files in the given order: /etc/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
In the configuration file, you'll want to find the [mysqld] section. Under that section, you'll want to insert/modify the line to
max_connections = $defaultvalue. You would set
$defaultvalue as the max connection desired.
Using this method would require you to restart your MySQL server by running the command:
/etc/init.d/mysql restart. If that doesn't work, try running
sudo systemctl restart mysqld
If you would like to increase the max connections temporarily, you would have to modify a variable within MySQL. You should be able to achieve this by doing the following:
Access your MySQL command line tool
show variables like "max_connections";
This will return an output as such:
Variable_name Value max_connections 100
If you have the proper permissions, change the variable by running the command:
set global max_connections = $DesiredValue;. You would insert the desired max connections value in the place of
Changes using this method will take effect immediately, but will be reset to default values once MySQL is restarted.
The maximum value you can set
max_connections to is 100000. The minimum value is 1, while the default value is 151. Do keep in mind that setting a higher value may have affect the performance of your Linode.
While I cannot provide the optimal setting, these questions may help you determine what value would be best:
- How many users are using the application?
- How many applications connect to the database?
- What is the load and how queries are given?
It is best to consider the maximum amount of connections you have had in the past before setting this number, so you’ll have a buffer between that upper number and the