"Notes" InnoDB memory allocation

Original: http://www.mysqlperformanceblog.com/2006/05/30/innodb-memory-usage/

There are a lot of questions about how InnoDB allocates memory. Here I try to explain how memory is allocated at startup. The important constants are listed first:

Nblocks=innodb number of blocks in 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 (for linux such systems are correct, there is another way of computing for Windows)

So the amount of memory used by the InnoDB:

L INNODB Buffer pool memory

L INNODB additional memory pool size

L INNODB Record buffer size

L Adaptive Hash Index, size = InnoDB buffer pool/64

L system directory hash, size = 6 * InnoDB buffer pool/512

L Synchronous Array (Sync_array) uses memory for synchronization unit utilization, size = Os_threads * 152

L System Event (os_events) memory, also for synchronization unit utilization, size = Os_threads * 216

L system-locked memory, size =5 * 4 * nblocks

So, the final InnoDB formula:

Buffer pool size + buffer record size + additional memory pool size + 812/16384 * Buffer pool size + os_threads * 368
For simplicity: 812/16384 * Buffer pool Size ~ ~ Buffer Pool Size/20

and os_threads*368 = 17.5MB if buffer pool size > 1000MB

= 3.5MB If buffer pool size > 8MB

For example, such as your buffer pool size =1500m, additional memory pool size =20m, record buffer size =8m, then InnoDB allocated memory = 1500M + 20M + 8M + 1500/20m + 17.5M = 1620.5M.

When you plan to take advantage of your server memory, you need to consider the use of additional memory.