These following reasons cause MySQL to run out connections.
1). Slow Queries
2). Data Storage Techniques
3). Bad MySQL configuration
If you have encountered the error “Too many connections” while trying to connect to a MySQL
Server, that means it reached the maximum number of connections, or all available permitted are in
use by other clients and your connection attempts will get rejected.
That number of connections is defined via the max_connections system variable. To open for more
connections, you can set a higher value for max_connections.
To see the current value of max_connections, run this command:
SHOW VARIABLES LIKE "max_connections";
Sample output:
By default, it’s set to 151. But MySQL actually allows up to max_connections + 1, which is 151 + 1 for
the default setting. The extra connection can be used by the user with SUPER privilege only.
To increase the max_connections value, let’s say 500, run this command:
SET GLOBAL max_connections = 500;
The command takes effect right after you execute it, but it only applies to the current session. If you
want it to be permanent until you re-adjust it the next time, you have to edit the configuration file
my.cnf (normally it’s stored in /etc/my.cnf).
Under the [mysqld] section add the following line:
max_connections = 500
Then restart the MySQL server to take effect.
One thing to keep in mind that there is no hard limit to setting up maximum max_connections value,
but increasing it will require more RAM to run. The number of maximum connections permitted has
to be calculated based on how much RAM you have and how much is used per connection. In many
cases, if you run out of usable disc space on your server partition or drive, MySQL might also return
this error.
The maximum number can be estimated by the formula:
max.connection=(available RAM-global buffers)/thread buffers
So increase it with caution.
