Variable's Day Out #8: innodb_additional_mem_pool_size

Properties:

Applicable To	InnoDB
Server Startup Option	--innodb-additional-mem-pool-size=<value>
Scope	Global
Dynamic	No
Possible Values	Integer: Range: 524288 (512K) - 4294967295 (4G)
Default Value	1048576 (1M)
Category	Performance

Description:

As per MySQL documentation, this pool is used to store data dictionary information and other internal data structures. If InnoDB runs out of memory on this pool, it starts allocating from OS.

Most of the additional memory pool usage goes to tables in the data dictionary and connections. Normally, with increasing number of tables you may need to allocate more memory to this pool. But as modern OS'es have good and fast memory allocating functions, this variable does not hit performance.

Anyhow, if you need to see how much of additional memory pool is being used, it's available via SHOW ENGINE INNODB STATUS.

   1: SHOW ENGINE INNODB STATUS \G

   2: .

   3: .

   4: ----------------------

   5: BUFFER POOL AND MEMORY

   6: ----------------------

   7: Total memory allocated XXXXXXXXXXXXX; in additional pool allocated XXXXXXX

   8: Dictionary memory allocated XXXXX

   9: .

  10: .

additional pool allocated will show amount of memory allocated in additional memory pool, the usage. And out of usage, Dictionary memory allocated will show amount of memory being used for data dictionary.

Best value: There is hardly any impact of a smaller value for this variable. Still it is better to have a value which around 20% more than what SHOW ENGINE INNODB STATUS shows for additional pool allocated. It's proportional to (number of tables + number of concurrent connections). Read more:

• MySQL manual entry on innodb_additional_mem_pool_size
• What to tune in MySQL ... @mysqlperformanceblog.com
• Heikki Tuuri on "Calculating innodb_additional_mem_pool_size?"

Hope you enjoyed reading this post.