EmergencyEMERGENCY? Get 24/7 Help Now!

InnoDB memory usage

 | May 30, 2006 |  Posted In: Insight for DBAs


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.

Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.


  • 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

  • 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.

  • 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.

Leave a Reply