Dong's Note
0%

mysql内存计算公式

Posted on In 
mysql used mem = 
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 )

在mysql 中输入如下命令，可自动计算自己的当前配置最大的内存消耗：

SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
SHOW VARIABLES LIKE ‘innodb_additional_mem_pool_size’;
SHOW VARIABLES LIKE ‘innodb_log_buffer_size’;
SHOW VARIABLES LIKE ‘thread_stack’;

1
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;

当前连接数的内存消耗：

1
2
SET @current_connections = (select count(1) from information_schema.PROCESSLIST);
SELECT ( @@key_buffer_size + @@query_cache_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@innodb_log_buffer_size + @current_connections * ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack ) ) / 1073741824 AS CUR_MEMORY_GB;

在mysql 中输入如下命令，可显示各占内存参数大小

SHOW VARIABLES LIKE ‘key_buffer_size’;
SHOW VARIABLES LIKE ‘query_cache_size’;
SHOW VARIABLES LIKE ‘tmp_table_size’;
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
SHOW VARIABLES LIKE ‘innodb_additional_mem_pool_size’;
SHOW VARIABLES LIKE ‘innodb_log_buffer_size’;
SHOW VARIABLES LIKE ‘read_buffer_size’;
SHOW VARIABLES LIKE ‘read_rnd_buffer_size’;
SHOW VARIABLES LIKE ‘sort_buffer_size’;
SHOW VARIABLES LIKE ‘join_buffer_size’;
SHOW VARIABLES LIKE ‘binlog_cache_size’;
SHOW VARIABLES LIKE ‘thread_stack’;