Calculating Maximum Connections for MySQL Server

Calculating Maximum Connections for MySQL Server

Backup your my.cnf file

The my.cnf file is the MySQL configuration file that needs to be modified to optimize the performance of MySQL database servers. Make sure you always backup a copy of this very important configuration before changing it.

Determine the advisable value for max_connections

How high should we set max_connections? Well, it depends on how much memory (RAM) do you have on the server.
You might want to use the formula to come up with the value we are looking for.

max_connections = (Available RAM - Global buffers) / Thread buffers

But before that, we need to know the values of the variables on our right side - available RAM, global buffers, and thread buffers.

Determine the Available RAM

To determine the Available RAM, issue the following command.

free -tb

Here's the result of the command in my case:
The number in red is the available memory measured in bytes.

Determine the Global and Thread Buffers

In a mysql console, issue the following statement:

SHOW VARIABLES LIKE '%buffer%';

The following are considered global buffers:

key_buffer_size
innodb_buffer_pool
innodb_log_buffer
innodb_additional_mem_pool
net_buffer_size

The following are considered thread specific buffers:

sort_buffer_size
myisam_sort_buffer_size
read_buffer_size
join_buffer_size
read_rnd_buffer_size