What goes in to defining the "per thread" memory calculation

[willstocks]

Hello!
Sorry, I'm relatively new to MySQL tuning and I've been looking in a couple of places to work out how exactly to optimize my db config.
Are you able to confirm what goes in to the "per thread" memory calculation?
Total buffers: 248.0M global + 17.1M per thread (75 max threads)
The "obvious" items I can see are:

(the script used to generate this is: MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"] )

Is there something I'm missing, because this calc only comes out to 9.1MB, 8MB shy of the calculation being shown by MySQLTuner

[domlat]

I have been looking at this because of the huge difference in maximum memory consumption per thread between two otherwise similar servers (Same mariadb version, more or less stock config).
Turned out the older server was running Version 1.7.15 of mysqltuner, and the newer was on 1.7.19.
1.7.19 adds up these variables:
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
thread_stack
max_allowed_packet
join_buffer_size
1.7.15 omitted max_allowed_packet. This is quite serious a change! All my existing servers have been configured on the basis of older versions which seriously under-report max memory usage!

[ibaldonl]

Well... that's not strictly true though...

max_allowed_packet doesn't get allocated entirely on every thread but instead, it is progressively increased as needed up to that maximum.

So strictly speaking, you are not over-provisioning that much except if you always use for every connection the entire max_allowed_packet which is unlikely.

What MySQLTuner shows is a worst-case-scenario but you need to apply your own judgment to estimate really how much memory it will use.

One way is to see the actual max memory used since the server started minus global buffers and divide it by the max number of concurrent connections and then multiply that result to the max number of connections you will allow and add the global buffers again.

MySQLTuner could help in that regard so maybe this issue could be taken as an enhancement idea.

[djaho]

@ibaldonl I think that you idea to use the actual max memory since the server started minus global buffers, divided by max number of used connections is quite good. This could be displayed as a different metric, showing how much memory will mysql probably use if all of the connections are used.
I will fork the repo and check if I can add this metric.

[domlat]

Thanks for the replies. Can I ask nicely that someone posts to this thread when a new version with this change is published?

[djaho]

@ibaldonl did you already have something in mind on how to get the actually reached max memory usage of the mysql?
I checked the values which VmPeak gives on Ubuntu machine and it appears that this value is higher than it should be.

[ibaldonl]

Yeah, we shouldn't trust the virtual memory requested by applications and also that wouldn't work in a server without ssh access like for example cloud providers, etc.
MySQL has a global variable telling the maximum total amount of memory that it requested to the OS and I think MySQLTuner already reports it so we already have it in there, it's just some math that is needed, all the data is in MySQLTuner already I think....

[djaho]

I tried to find this value you mentioned which would let me know how much memory did the MySQL actually request from the OS, but it doesn't appear to be there.
The mysqltuner currently calculated the maximum reached memory usage based on the buffer size, max allowed memory usage per connection and maximum reached number of concurrent connections.
You can see in this issue (#502) that it actually grossly overcalculates the peak reached memory usage.

# Global memory
# Max used memory is memory used by MySQL based on Max_used_connections
# This is the max memory used theoretically calculated with the max concurrent connection number reached by mysql
    $mycalc{'max_used_memory'} =
      $mycalc{'server_buffers'} +
      $mycalc{"max_total_per_thread_buffers"} +
      get_pf_memory();

And here is how the max_total_per_thread_buffers is calculated:
$mycalc{'max_total_per_thread_buffers'} = $mycalc{'per_thread_buffers'} * $mystat{'Max_used_connections'};

[ibaldonl]

Doh! My bad! I thought there was a variable for that, didn't expect MySQLTunner to calculate it that way, sorry.
I even looked at all the MySQL 8.0.20 variables and couldn't find any :-(
I think that the best source of information about the actual memory used is the RSS then.

[djaho]

Yes, but as you mentioned, this won't work on servers without SSH access, not to even mention the cross platform incompatibilities.
I think that it would make more sense to just rename the label for the maximum reached memory usage to something like "Potentially maximum reached memory usage," as this would make more sense now, since we use the maximum available packet size for the calculation here.

[jmrenouard]

Issue fixed with last PR: Fixing wrong calculation in memory usage (regression found) #679

Thanks for your patience and reporting this issue
@jmrenouard