How much memory Innodb Dictionary can take ?

May 6, 2010

Author

Peter Zaitsev

Insight for DBAs

MySQL

Share this Post:

The amount of memory Innodb will require for its data dictionary depends on amount of tables you have as well as number of fields and indexes. Innodb allocates this memory once table is accessed and keeps until server is shut down. In XtraDB we have an option to restrict that limit.

So how much memory can it really take ? Here is some production stats from real system:

<br>mysql> select count(*) from INNODB_SYS_TABLES;<br>+----------+<br>| count(*) |<br>+----------+<br>|    48246 |<br>+----------+<br>1 row in set (8.04 sec)<br><br>mysql> select count(*) from INNODB_SYS_INDEXES;<br>+----------+<br>| count(*) |<br>+----------+<br>|   451773 |<br>+----------+<br>1 row in set (2.75 sec)<br>

1

mysql> select count(*) from INNODB_SYS_TABLES; +----------+ | count(*) | +----------+ | 48246 | +----------+ 1 row in set (8.04 sec) mysql> select count(*) from INNODB_SYS_INDEXES; +----------+ | count(*) | +----------+ | 451773 | +----------+ 1 row in set (2.75 sec)

In this case The memory stats from SHOW INNODB STATUS look like:

<br>Total memory allocated 1101004800; in additional pool allocated 0<br>Internal hash tables (constant factor + variable factor)<br>    Adaptive hash index 17721976        (17701384 + 20592)<br>    Page hash           1107208<br>    Dictionary cache    702440032       (4427312 + 698012720)<br>    File system         20924944        (82672 + 20842272)<br>    Lock system         2659016         (2657176 + 1840)<br>    Recovery system     0       (0 + 0)<br>    Threads             408536  (406936 + 1600)<br>Dictionary memory allocated 698012720<br>

1

Total memory allocated 1101004800; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 17721976 (17701384 + 20592) Page hash 1107208 Dictionary cache 702440032 (4427312 + 698012720) File system 20924944 (82672 + 20842272) Lock system 2659016 (2657176 + 1840) Recovery system 0 (0 + 0) Threads 408536 (406936 + 1600) Dictionary memory allocated 698012720

So considering about 700M were allocated for dictionary cache in this case we have about 15KB per Table or 1.5KB per Index. These numbers will change a lot depending on your table structure – This given schema had many tables with 50+ columns but I believe it can be helpful as ballpark figure.

Also note Dictionary Cache is not included in “Total Memory Allocated” in SHOW INNODB STATUS any more, as that allocations were moved to use malloc() and so Innodb does not track them any more.