mysql max connections calculator
max_connections = (Available RAM – Global Buffers) / Thread Buffers
available ram =MySQL ram
Global buffers
SHOW VARIABLES LIKE ‘%buffer%’;
innodb_buffer_pool, key_buffer_size, query_cache_size, etc
Available RAM = Global Buffers + (Thread Buffers x max_connections)
max_connections = (Available RAM – Global Buffers) / Thread Buffers
global buffers
innodb_buffer_pool_size
innodb_log_buffer_size
innodb_additional_mem_pool
net_buffer_length
total size of global buffers
thread buffers
sort_buffer_size
myisam_sort_buffer_size
read_buffer_size
join_buffer_size
read_mid_buffer_size
total threaded buffer size
generally 10% global buffers 1gb vs 100Mb.
max connections 151 default.
Ram= 1000+ (100*151)
1000+15100
1010+(112*27)
3024+1010=4GB.
otherway
check previous max used connections
increase
check mysql error logs.
Update max connection limit in mysql.conf
my.cnf
max_connections = 500
mysql> SHOW VARIABLES LIKE “max_connections”;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 151 |
+—————–+——-+
1 row in set (0.03 sec)
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 151 |
+—————–+——-+
1 row in set (0.03 sec)
Check max used connections by mysql previous
maximum number of connections that have been in use simultaneously since the server started.
mysql> SHOW GLOBAL STATUS LIKE ‘max_use%’;
+—————————+———————+
| Variable_name | Value |
+—————————+———————+
| Max_used_connections | 26 |
| Max_used_connections_time | 2021-02-12 02:39:15 |
+—————————+———————+
2 rows in set (0.02 sec)
+—————————+———————+
| Variable_name | Value |
+—————————+———————+
| Max_used_connections | 26 |
| Max_used_connections_time | 2021-02-12 02:39:15 |
+—————————+———————+
2 rows in set (0.02 sec)
Variable_name | Value |
+————————+——-+
| max_connections | 151 |
| mysqlx_max_connections | 100 |
+————————+——-+
+————————+——-+
| max_connections | 151 |
| mysqlx_max_connections | 100 |
+————————+——-+
he maximum number of concurrent client connections X Plugin can accept. This is the X Plugin equivalent of max_connections;
mysql> SHOW GLOBAL VARIABLES LIKE ‘max_user_connections’;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| max_user_connections | 0 |
+———————-+——-+
1 row in set (0.00 sec)
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| max_user_connections | 0 |
+———————-+——-+
1 row in set (0.00 sec)
Aborted connection mysql
mysql> show global status like “%Aborted%”;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| Aborted_clients | 0 |
| Aborted_connects | 47073 |
| Mysqlx_aborted_clients | 0 |
+————————+——-+
3 rows in set (0.00 sec)
+————————+——-+
| Variable_name | Value |
+————————+——-+
| Aborted_clients | 0 |
| Aborted_connects | 47073 |
| Mysqlx_aborted_clients | 0 |
+————————+——-+
3 rows in set (0.00 sec)
https://journeyontux.files.wordpress.com/2011/12/max_connection_measurements.xls
mysql> SHOW STATUS LIKE ‘Threads_connected’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_connected | 1 |
+——————-+——-+
1 row in set (0.00 sec)
also follow mysql memory allocation & optimization