The basic formulas are:
Available RAM = Global Buffers + (Thread Buffers x max_connections)
max_connections = (Available RAM – Global Buffers) / Thread Buffers
To get the list of buffers and their values:
SHOW VARIABLES LIKE '%buffer%';
Here’s a list of the buffers and whether they’re Global or Thread:
Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size
Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack
MariaDB [(none)]> SHOW VARIABLES LIKE '%buffer%'; +---------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------+-----------+ | aria_pagecache_buffer_size | 134217728 | | aria_sort_buffer_size | 134217728 | | bulk_insert_buffer_size | 8388608 | | innodb_blocking_buffer_pool_restore | OFF | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_populate | OFF | | innodb_buffer_pool_restore_at_startup | 0 | | innodb_buffer_pool_shm_checksum | ON | | innodb_buffer_pool_shm_key | 0 | | innodb_buffer_pool_size | 134217728 | | innodb_change_buffering | all | | innodb_log_buffer_size | 8388608 | | join_buffer_size | 131072 | | join_buffer_space_limit | 2097152 | | key_buffer_size | 16777216 | | mrr_buffer_size | 262144 | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 8192 | | preload_buffer_size | 32768 | | read_buffer_size | 262144 | | read_rnd_buffer_size | 524288 | | sort_buffer_size | 524288 | | sql_buffer_result | OFF | +---------------------------------------+-----------+
Lets find out the RAM:
# free -b
total used free shared buff/cache available
Mem: 3975184384 978608128 1691045888 9445376 1305530368 2661937152
Lets get our data together.
RAM = 3975184384
Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_length, query_cache_size
or, from above…
Global Buffers: 16777216 + 134217728 + 8388608 + 0 + 8192 + 0 = 159391744
Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack
or, from above…
Thread Buffers: 524288 + 8388608 + 262144 + 131072 + 524288 + 0 = 9830400
With this information, the following is the calculation:
max_connections = (Available RAM – Global Buffers) / Thread Buffers
max_connections = (3975184384 – 159391744) / 9830400
So the formula shows 378 Max Connections on this machine
Test info with mysqltuner
Log into your server with a root or sudo user via SSH.
Download MySQLTuner by executing the following command:
wget -O mysqltuner.pl https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl --no-check-certificate
Give the script 775 permissions:
chmod 775 mysqltuner.pl
Run the script with the following command:
perl mysqltuner.pl
Resources:
Handy Calculator Download: https://journeyontux.wordpress.com/2011/12/22/calculate-number-of-connections-for-mysql-server/
Another Calculator: http://www.mysqlcalculator.com/