max_connections = (Available RAM – Global Buffers) / Thread Buffers
For getting the available RAM in bytes.
free -b
total used free shared buff/cache available
Mem: 8253255680 6625886208 172158976 931852288 1455210496 409915392
For getting the details about Global buffers, access MySQL and run the following command.
SHOW VARIABLES LIKE '%buffer%';
Output be like this:
mysql> SHOW VARIABLES LIKE '%buffer%'; +------------------------------+-----------+ | Variable_name | Value | +------------------------------+-----------+ | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 134217728 | | innodb_change_buffering | all | | innodb_log_buffer_size | 8388608 | | join_buffer_size | 131072 | | key_buffer_size | 8388608 | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 16384 | | preload_buffer_size | 32768 | | read_buffer_size | 131072 | | read_rnd_buffer_size | 262144 | | sort_buffer_size | 2097152 | | sql_buffer_result | OFF | +------------------------------+-----------+ 14 rows in set (0.00 sec)
Ram: 8253255680
Global Buffers = key_buffer_size + innodb_buffer_pool_size +innodb_log_buffer_size + innodb_additional_mem_pool_size +net_buffer_length +query_cache_size
Thread Buffers: sort_buffer_size + myisam_sort_buffer_size + read_buffer_size + join_buffer_size + read_rnd_buffer_size+ thread_stack
It puts the data together for the calculation.
Global Buffers = 8388608+134217728+8388608+0+16384+0 =151011328
Thread Buffers = 8388608+2097152+131072+131072+262144+0 = 11010048
max_connections = (Available RAM – Global Buffers) / Thread Buffers
= (8253255680 - 151011328 ) / (11010048)
= 735.89
Mainly max_connections will be in the range of 200 to 320 for small websites and for large websites it vary from 700 to 1024. It mainly depends upon the ram that you have. Setting the value higher tends to use the MySQL resource concurrently by the connections and leads to MySQL down.