Tuesday, August 16, 2016

Memory been allocated to MySQL

Memory calculation, the formula is:
global buffers + (thread buffers * max_connections) = a bit less than your RAM (so the OS and some MySQL admin stuff can work).

Global buffers
: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, query_cache_size

Thread buffers: sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, binlog_cache_size, thread_stack, tmp_table_size

mysql> SELECT (@@key_buffer_size
    -> + @@query_cache_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
    -> + @@tmp_table_size )
    -> ) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
|       30.0272 |
+---------------+
1 row in set (0.00 sec)

Note: This is the maximum memory been allocated/utilized by MySQL at the time of peak activity.
(which is generally not the case, as all the threads using all the parameter values for given interval of time). However, at worst case, this would be the maximum memory been allocated to MySQL.

No comments:

Post a Comment