Configuring the max_connections setting in MySQL

I've gone through a number of docs and forums but can't quite find the right answer for how to determine the number of maximum connections I should set.

I tried using MySQLTuner, that gave me:

Variables to adjust:
    max_connections (> 500)

which I'm unclear of if it wants me to lower that threshold, or if it is just saying I can have more than 500 connections. My setting currently is for 500.

I ran the following queries:

show variables where Variable_name regexp '^(key_buffer_size|innodb_buffer_pool_size|innodb_log_buffer_size|innodb_additional_mem_pool_size|net_buffer_length|query_cache_size)

which gave me:

+---------------------------------+------------+
| Variable_name                   | Value      |
+---------------------------------+------------+
| innodb_additional_mem_pool_size | 8388608    |
| innodb_buffer_pool_size         | 2147483648 |
| innodb_log_buffer_size          | 8388608    |
| key_buffer_size                 | 8388608    |
| net_buffer_length               | 16384      |
| query_cache_size                | 1048576    |
+---------------------------------+------------+

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| join_buffer_size        | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| read_buffer_size        | 131072  |
| read_rnd_buffer_size    | 262144  |
| sort_buffer_size        | 262144  |
| thread_stack            | 262144  |
+-------------------------+---------+


I put that into:

32122936 - (8388608 + 2147483648 + 8388608 + 8388608 + 16384 +1048576) / (8388608 + 8388608 + 131072 + (3* 262144))


and get back 32122813.1546 which sounds like far too many connections. I also tried http://www.mysqlcalculator.com/ which gave me about 2000. That numbers seems the closest.

show variables where Variable_name regexp '^(sort_buffer_size|myisam_sort_buffer_size|read_buffer_size|join_buffer_size|read_rnd_buffer_size|thread_stack)

which gave me:

%%CODEBLOCK_2%%

I put that into:

%%CODEBLOCK_3%%

and get back 32122813.1546 which sounds like far too many connections. I also tried http://www.mysqlcalculator.com/ which gave me about 2000. That numbers seems the closest.
;

which gave me:

%%CODEBLOCK_2%%

I put that into:

%%CODEBLOCK_3%%

and get back 32122813.1546 which sounds like far too many connections. I also tried http://www.mysqlcalculator.com/ which gave me about 2000. That numbers seems the closest.

There is a simple answer:
Keep the number of connections as small as you can.
The good value is 120% of

Max_used_connections | 501

As I can see there were an issue, when you ran out f number of connections.

Rethink the way your application uses connections to the database, maybe it is not closing them properly?
Put on monitoring

Threads_connected

status variable
If Threads_connected reaches 70% of the max_connections your monitoring should send an alert and you must connect to the database and debug which user consumes the most connections.

Query to debug:

select count(*),user from information_schema.processlist group by user order by count(*) desc;

If you are completely sure, that your app uses no more than X connections, start optimizing RAM

Increase RAM if it is necessary.
If the average of Threads_connected in monitoring is relatively small and there are a couple of spikes, that figures out troubles, it is ok to put max_connections to 120% of maximum of Threads_connected measured in 3 months, Max_used_connections will identify only spikes, which may be irrelevant, because there could be an issue with an app causing huge number of connections/not closing previous connections.

StackExchange Database Administrators Q#233886, answer score: 3