How do you calculate mysql max_connections ?

What do you take into consideration ?

up vote 25 down vote accepted

Going to post this as an answer, with the relevant information. 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

  • 1
    KCD provided this link as a max memory calculator: mysqlcalculator.com – Derek Downey Apr 26 '13 at 13:15
  • Derek, do you mean "Global buffers = SUM of all global buffers variables and Thread Buffers = sum of all thread buffers variables"? – Mat Feb 27 '17 at 21:04

WARNING! This is from 2011 using MySQL 5.1.x. Use at your own risk

---- ORIGINAL POST ----

Here's another alternative formula in stored procedure form:

DELIMITER //
CREATE PROCEDURE sproc_show_max_memory ( OUT max_memory DECIMAL(7,4))
BEGIN
SELECT ( @@key_buffer_size + @@query_cache_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@innodb_log_buffer_size + @@max_connections * ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack ) ) / 1073741824 AS MAX_MEMORY_GB INTO max_memory;
END//
DELIMITER ;
CALL sproc_show_max_memory(@show_max_memory);
SELECT @show_max_memory;

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.