InnoDB memory usage
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.
Print This Post
del.icio.us :: 
digg Comment RSS :: Trackback URI
4 Comments
[...] log buffer) and total amount of memory allocated by Innodb is typically a bit over 13GB. Vadim has posted some numbers on it a while [...]
Pingback :: November 3, 2007 @ 4:43 pm
Thats realy helpull and ponderable article that you discussed, thanks for it,Hope it will also help other readers to understand about concept of allocation of memory by InnoDB,Thanks again for it
Mysql examples
http://mysqlexamples.blogspot.com
Comment :: November 15, 2007 @ 2:51 am
You have not included the allocations done for recv_sys->addr_hash in the overhead. There is a temporary allocation at startup that is ~12% the size of the buffer pool. That memory is freed and replaced by an allocation that is ~3% the size of the buffer pool.
Comment :: November 29, 2008 @ 4:20 pm
And eventually the recv_sys struct is freed when crash recovery finishes. So the pattern is allocate ~12% of the buf pool size, free, allocate ~3%, free, allocate ~3%, free.
Comment :: November 30, 2008 @ 11:01 am