Wednesday, April 16, 2008

Variable's Day Out #8: innodb_additional_mem_pool_size


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


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.

   2: .
   3: .
   4: ----------------------
   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: Hope you enjoyed reading this post.

No comments: