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.