Innodb引擎初始内存分配【InnoDB memory usage】

原文地址：http://www.mysqlperformanceblog.com/2006/05/30/innodb-memory-usage/

大家关于如何去计算MySQL 的Innodb引擎所消耗的内存大小有较多的疑惑，下面我就将举例说明一下MySQL在启动Innodb引擎的时候是如何对内存进行分配的。

下面是一些重要的常量计算方式：

NBLOCKS=count of block in innodb_buffer_pool = innodb_buffer_pool_size / 16384

即NBLOCKS=innodb_buffer_pool 的页数=innodb_buffer_pool_size / 16384，使用show engine innodb status中的Buffer pool size就是该值

OS_THREADS

如果innodb_buffer_pool_size >= 1000Mb 则OS_THREADS=50000

或者当8Mb<=innodb_buffer_pool_size <1000Mb时，OS_THREADS=10000

剩下的OS_THREADS=1000 

这套OS_THREADS的计算方法只在*nixes系统上有效，Windows的计算方法这里就不再讨论了。

下面是一些需要进行计算的量：

• innodb_buffer_pool #innodb的缓存
• innodb_additional_mem_pool_size #innodb的额外内存大小
• innodb_log_buffer_size #innodb日志缓存大小
• adaptive index hash, size= innodb_buffer_pool / 64 #自适应哈希索引大小
• system dictionary hash, size = 6 * innodb_buffer_pool_size / 512 #系统字典哈希索引大小
• memory for sync_array, which is used for syncronization primitives, size = OS_THREADS * 152 #同步数组元数据的内存大小
• memory for os_events, which are also used for syncronization primitives, OS_THREADS * 216 #同步系统事件元数据的内存大小
• and memory for locking system, size=5 * 4 * NBLOCKS #系统锁定的内存大小

所以对于最终的Innodb初始化内存分配计算公式如下：

innodb_buffer_pool_size + innodb_log_buffer_size + innodb_additional_mem_pool_size + 812 / 16384 * innodb_buffer_pool_size + OS_THREADS * 368

其中 16384 * innodb_buffer_pool_size*812/16384简化成innodb_buffer_pool_size / 20

当innodb_buffer_pool > =1000MB时， OS_THREADS*368 = 17.5MB

当8Mb<=innodb_buffer_pool_size <1000Mb时OS_THREADS*368= 3.5MB 

（译者注：我的计算是896 / 16384 * innodb_buffer_pool_size ）

下面举个例子：

假设你设置的 innodb_buffer_pool_size=1500M，innodb_additional_mem_pool_size =20M, innodb_log_buffer_size = 8M，则分配给Innodb的内存大小为1500M + 20M + 8M + 1500/20M + 17.5M = 1620.5M.

在使用的过程中还需要考虑到有别的内存占用，这些内存你都需要考虑进去。

There are many questions how InnoDB allocates memory. I’ll try to give some explanation about the memory allocation at startup.
Some important constants:
NBLOCKS=count of block in innodb_buffer_pool = innodb_buffer_pool_size / 16384
OS_THREADS= if (innodb_buffer_pool_size >= 1000Mb) = 50000
else if (innodb_buffer_pool_size >= 8Mb) = 10000
else = 1000 (it’s true for *nixes, for Windows there is a bit another calculation for OS_THREADS)
So InnoDB uses:
innodb_buffer_pool
innodb_additional_mem_pool_size
innodb_log_buffer_size
adaptive index hash, size= innodb_buffer_pool / 64
system dictionary hash, size = 6 * innodb_buffer_pool_size / 512
memory for sync_array, which is used for syncronization primitives, size = OS_THREADS * 152
memory for os_events, which are also used for syncronization primitives, OS_THREADS * 216
and memory for locking system, size=5 * 4 * NBLOCKS
So the final formula for innodb:
innodb_buffer_pool_size + innodb_log_buffer_size + innodb_additional_mem_pool_size + 812 / 16384 * innodb_buffer_pool_size + OS_THREADS * 368
For simplicity we can use: 812 / 16384 * innodb_buffer_pool_size ~~ innodb_buffer_pool_size / 20
and OS_THREADS*368 = 17.5MB if innodb_buffer_pool > 1000MB
= 3.5MB if > 8MB
For example if you have innodb_buffer_pool_size=1500M, innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M, InnoDB will allocate = 1500M + 20M + 8M + 1500/20M + 17.5M = 1620.5M.
Take the additional memory into account when you are planning memory usage for your server.

There are many questions how InnoDB allocates memory. I’ll try to give some explanation about the memory allocation at startup.
Some important constants:
NBLOCKS=count of block in innodb_buffer_pool = innodb_buffer_pool_size / 16384
OS_THREADS= if (innodb_buffer_pool_size >= 1000Mb) = 50000
else if (innodb_buffer_pool_size >= 8Mb) = 10000
else = 1000 (it’s true for *nixes, for Windows there is a bit another calculation for OS_THREADS)
So InnoDB uses:
innodb_buffer_pool
innodb_additional_mem_pool_size
innodb_log_buffer_size
adaptive index hash, size= innodb_buffer_pool / 64
system dictionary hash, size = 6 * innodb_buffer_pool_size / 512
memory for sync_array, which is used for syncronization primitives, size = OS_THREADS * 152
memory for os_events, which are also used for syncronization primitives, OS_THREADS * 216
and memory for locking system, size=5 * 4 * NBLOCKS
So the final formula for innodb:
innodb_buffer_pool_size + innodb_log_buffer_size + innodb_additional_mem_pool_size + 812 / 16384 * innodb_buffer_pool_size + OS_THREADS * 368
For simplicity we can use: 812 / 16384 * innodb_buffer_pool_size ~~ innodb_buffer_pool_size / 20
and OS_THREADS*368 = 17.5MB if innodb_buffer_pool > 1000MB
= 3.5MB if > 8MB
For example if you have innodb_buffer_pool_size=1500M, innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M, InnoDB will allocate = 1500M + 20M + 8M + 1500/20M + 17.5M = 1620.5M.
Take the additional memory into account when you are planning memory usage for your server.