InnoDB memory usage

Posted on:



Share Button

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.

Share Button

Vadim Tkachenko

Vadim leads Percona's development group, which produces the Percona Server, Percona Server for MongoDB, Percona XtraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.


, ,

Insight for DBAs

  • 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://mysqlha.blogspot.com/

    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.


  • http://mysqlha.blogspot.com/

    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.


  • how much RAM is required to serve 100 GB innodb database with Quad core 64 bit server on centos platofrm? we have 16 GB RAM 12 GB allocated to innodb buffer pool, server performance is very bad with this configuration, system always use all swap.


  • Yogesh,

    It depends upon your application usage. If you are using innodb table with just inserts only even for 300GB Memory of 1G is enough, but if its a concurrent channels of complex selects, inserts or updates often to the table then you might want to revise your innodb parameters.
    1. Know your application functionality.
    2. Rotate tables or data that are obsolete or not required anymore. (Backup, archive and recreate the table structure). Note: Truncate of data is quite longer and not a best way to treat its indeces.
    3. Monitor your application for query slowness, load avg and cpu and memory consumption and IO waits and try to fine tune the query and application usage where ever applicable. Thus, prohibits from too much overheads to IO at a particular time. Gather the requirements, test it and be confident before you touch anything on server parameters.
    4. Once you have done all these options and have points to prove that nothing can be done from mysql internals. Then you may try your observations by using different OS/Raid/HW/PCI or with more memory to the server etc..

    THUMB RULE : Try to keep cost to the company on procurement as lowest as possible, don’t jump into procurement HW/SW packages before tweaking application,DB and Networking options which you can fix all by yourselves.


  • plz tell us the mysql version whenever


Leave a Reply

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.

Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.

No, thank you. Please do not ask me again.