Question about "Transparent Huge Pages (THP)" affecting mysql mentioned in blog article

MySQL & MariaDB
4

Here’s the best calculation function you can use:

SELECT CONCAT((@@key_buffer_size + @@query_cache_size + (@@innodb_buffer_pool_size * 1.05 + 20*1024*1024) + @@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 + @@tmp_table_size
+ @@thread_stack)) / 1024/1024/1024, ' GB') AS "POTENTIAL MEMORY USAGE";

You can see there are several buffers allocated for each connection, so there is a limit based on max_connections. However, you can still grow beyond this if you had a really bad query which examined millions/billions of rows. Something like this would allocate multiple sort buffers.

If you increase the VM to 8GB, and changed absolutely nothing else, then you’d see memory behave the same. If your query load changed, then your memory might change as well.

1 Like