MySQL Memory Utilization And Core Configuration For Innodb
[create by perrynzhou@gmail.com]
1.who consume mysql memory?
mysql memory comsume with two part:
1.every connection utilization:
connection-memory=
(read_buffer_size+
read_rnd_buffer_size+
sort_buffer_size+
thread_stack+
join_buffer_size+
binlog_cache_size)*max_connections
2.global memory utilization:
global-mempory =
(innodb_buffer_size+
innodb_additional_mem_pool_size+
innodb_log_buffer_size+
key_buffer_size+
query_cache_size)
so, mysql cost total memory should as follow rules:
total using memory of mysql = connection-memory+global-memory
how to configure innodb variables?:
1.innodb_log_file_size:
it best to set this value to (innodb_buffer_size)/innodb_log_files_in_group.this variable to set woking must to restart mysql service.
2.innodb_log_files_in_group:
the number of innodb log files.max value is 100,the value of this variable must be less to max value.
3.innodb_log_buffer_size:
when transaction start ,it will produce logs,this vairbable can buffer logs,and acroding to some rules to flush to log files.that can set range 8M to 256M with large innodb_buffer_pool_size.
4.innodb_buffer_pool_size:
that vairables cache page with lru.max size of this variable is 2^64-1 or 2^32-1 on 64 platform and 32 platform.
