How do you calculate mysql max_connections ?
What do you take into consideration ?
Going to post this as an answer, with the relevant information. The basic formulas are:
To get the list of buffers and their values:
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
Here's another alternative formula in sproc form:
I’m assuming your using a MySQL database > version 5.1.x and you’re a privileged user. But you play with the max connections by inserting whatever number you want and see the results.
I'm staring with number I'm getting from simple calulation: expected_number_of_requests_per_second * expected_average_request_processing_time * 2.
For later tuning, I'm always using monitoring system with historical data and trying to have 20% reserved in case of some peak. It's a bit more complex when you are using some connections pooling (which is usually a good idea) - then you need to monitor number of used connections in pool.