Search code examples
multithreadingmysqlmysql-variables

Am I calculating maximum connection limit on our MySQL db correctly?

I have a MySQL database in production and I am trying to calculate the optimised number to set the MySQL max_connections value to - Am I doing this correctly as my sums seem quite high to me.

As far as I can tell the logic from the link below is as follows using the URLs added at the end of this post :

I have ssh'd into the relevant server and in the MySQL terminal found the relevant variables using a query similar to this SHOW VARIABLES LIKE '%buffer%'; This gives me variable info like this..

enter image description here

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

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

To find out the available RAM I have simply run free -b in the terminal which returns this :

Mem: 134902571008

To calculate the relevant Globa buffers & Thread buffers I have simply added the values up based on these values

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

This gives me the following numbers :

Global Buffers = (67108864 + 2147483648 + 16777216 + 0 + 0 + 33554432) = 2264924160

Thread Buffers = (1048576 + 67108864 + 1048576 + 1048576 + 8388608 + 262144) = 78905344

So using this logic - max_connections = (Available RAM – Global Buffers) / Thread Buffers

I presume the calculation is correct - this gives me 1680 max_connections.. does this seem correct?

max connections = (134902571008 - 2264924160) / 78905344 = 1680.97165698

Solution

  • Your reasoning is sound, but you're not taking a few things into account.

    • The operating system needs some RAM for other processes, for the OS kernel itself, and for filesystem cache. How much is needed is variable and hard to predict. So it's not a good idea to try to "optimize" the amount of RAM allocation. Just allow some headroom.

    • The MySQL Server process also needs some additional RAM for things that aren't counted in either global buffer configuration values or per-thread configuration values. There isn't a good way to calculate this.

    • Some of the values you think are per-thread can in theory be used multiple times over by a single thread, like tmp_table_size and join_buffer_size.

    • It's very unlikely that all threads will use their maximum allowed per-thread allocation simultaneously. In a running MySQL Server, you may have 1000 clients connected, but 990 of them are "Sleeping" if you view them in SHOW PROCESSLIST. That is, they're connected, but not running a query at any given moment. Sort of like when you ssh into a server but your shell is just sitting at the prompt not running a command yet.

    The bottom line is that RAM allocation is not an exact science. You shouldn't try to optimize it by calculations.

    Instead, optimize it by observing the system over time, and trying to raise values incrementally, to see if it improves SQL performance without overallocating RAM. This way the allocation will be sized right for the SQL client traffic you need it to serve.