mysql内存计算公式
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 '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';
在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'; SET @kilo_bytes = 1024; SET @mega_bytes = @kilo_bytes * 1024; SET @giga_bytes = @mega_bytes * 1024; SET @innodb_buffer_pool_size = 2 * @giga_bytes; SET @innodb_additional_mem_pool_size = 16 * @mega_bytes; SET @innodb_log_buffer_size = 8 * @mega_bytes; SET @thread_stack = 192 * @kilo_bytes; 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 ) ) / @giga_bytes AS MAX_MEMORY_GB;
