Basic Details
The max_connections variable sets the maximum number of concurrent client connections.
max_connections – Usage
Adjustment of the max_connections variable increases or decreases the max number of connections. Since MySQL 5.5, the default value of max_connections has been 151. This provides 150 client connections and 1 extra connection for administrator accounts with the coonection_admin privilege. This extra connection allows admin accounts the ability to connect for troubleshooting or other purposes when connections are maxed out.
The 'too many connections' error message indicates that all connections are in use and no new connections can be opened until an existing connection is closed.
The 'too many connections' error message indicates that all connections are in use and no new connections can be opened until an existing connection is closed.
max_connections – Configuration
max_connections system variable can be configured using the command line or configuration file:
Command Line:
mysqld> set global max_connections = XX
Replace XX with value to suit your database needs. This change will only be applied to new connections. Make a new connection to the server and the values will update. SET GLOBAL will not persist through a server restart.
Configuration File:
[mysqld]
max_connections = XX
Replace XX with value to suit your database needs. Must restart MySQL server to see changes applied. This change will be permanent until the max_connections value in the configuration file is changed again.
Command Line:
mysqld> set global max_connections = XX
Replace XX with value to suit your database needs. This change will only be applied to new connections. Make a new connection to the server and the values will update. SET GLOBAL will not persist through a server restart.
Configuration File:
[mysqld]
max_connections = XX
Replace XX with value to suit your database needs. Must restart MySQL server to see changes applied. This change will be permanent until the max_connections value in the configuration file is changed again.
max_connections – Considerations
In most cases, max_connections does not need to be increased. MySQL servers that are exceeding the default number of maximum connections typically have other performance issues that need correction.
In situations where increasing max_connections is appropriate, increasing max_connections by 100 connections at a time, until there is no longer a 'too many connections' error message.
When adjusting max_connections, it's important to consider the system's resources. What is the available RAM? What kinds of queries will the connections be using? This plays into how much RAM is used by each connection, as easy queries take significantly less RAM than other resource-intensive queries. Improperly setting max_connections too high will have detrimental impacts on RAM availability and query performance.
Try to increase open_files_limit in case you can't increase max_connection variable.
In situations where increasing max_connections is appropriate, increasing max_connections by 100 connections at a time, until there is no longer a 'too many connections' error message.
When adjusting max_connections, it's important to consider the system's resources. What is the available RAM? What kinds of queries will the connections be using? This plays into how much RAM is used by each connection, as easy queries take significantly less RAM than other resource-intensive queries. Improperly setting max_connections too high will have detrimental impacts on RAM availability and query performance.
Try to increase open_files_limit in case you can't increase max_connection variable.
Don't worry about RAM or incrementally increasing max_connections until the 'too many connections' error is resolved. Releem handles all of the analysis, calculations, and considerations for you. Releem then recommends a new MySQL configuration and applies it automatically to improve your MySQL Performance Score.
