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.
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
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.
plz tell us the mysql version whenever