How to increase mysql connections

How to increase mysql connections

If you are getting “too many connections” errors in MySQL. By default the max_connections is 100, you need to change the max_connections to allow more connections, you will need to have enough RAM/memory to handle the increased number.

Login to mysql server

# mysql --host=localhost --user=username --password=mypass

Check current max_connections setting

mysql> show variables like "max_connections";

You will get

+—————–+——-+

| Variable_name | Value |

+—————–+——-+

| max_connections | 100 |

+—————–+——-+

To increase max_connections temporary (settings will be resetted after MySQL is restarted)

mysql> set global max_connections = 200;

To increase max_connections permanently

# nano /etc/my.cnf

For MySQL 3.x add:

set-variable = max_connections = 250

For MySQL 4.x and 5.x add:

max_connections = 250

Restart MySQL to make the changes.

Max connection can be calculated by the formula

Available RAM = Global Buffers + (Thread Buffers x max_connections)

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

Global Buffers:

SELECT ( @@key_buffer_size+@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@innodb_additional_mem_pool_size+@@query_cache_size) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

Thread Buffers: 

SELECT ( @@sort_buffer_size+ @@myisam_sort_buffer_size+ @@read_buffer_size+ @@join_buffer_size+ @@read_rnd_buffer_size+@@thread_stack) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;