How do I increase the MySQL connections for my server?

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.