How do you calculate mysql max_connections variable?

Question

How do you calculate mysql max_connections ?

What do you take into consideration ?

Good question, and I wanted to know the answer as well. Reading through this older blog about it: http://mysqlhacker.com/kabir/performance/calculating-maximum-connections-for-mysql-server.html — Derek Downey, Feb 15 '11 at 14:31

score 33 · Accepted Answer · edited Jun 15 '20 at 09:05

33

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

edited Jun 15 '20 at 09:05

Community

1

answered Feb 15 '11 at 17:42

Derek Downey

23,440
11
78
104

2

KCD provided this link as a max memory calculator: http://www.mysqlcalculator.com/ – Derek Downey Apr 26 '13 at 13:15
1

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
@DerekDowney You should add the memory calculator as an answer. Thanks. – Xin Dec 10 '19 at 09:58

score 11 · Answer 2 · edited Mar 28 '17 at 18:13

11

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.

edited Mar 28 '17 at 18:13

marc_s

8,932
6
45
51

answered Feb 15 '11 at 23:43

randomx

3,934
4
30
43

1

I just read your formula. Pretty slick !!! BTW +1 !!! – RolandoMySQLDBA Jul 20 '11 at 20:43
2

It's probably time to revisit this formula given all the changes in 5.5 and 5.6, but it's still kind of handy. – randomx Aug 09 '12 at 15:28
http://www.khankennels.com/presentations/pdf/performance_tuning.pdf – Ibrahim Nov 20 '12 at 20:42

score 0 · Answer 3 · answered Feb 15 '11 at 19:11

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.

How do you calculate mysql max_connections variable?

3 Answers3

Linked