# Is there a way to actually limit InnoDB data dictionary? **URL:** \ **Category:** Percona Server for MySQL 5.6\ **Created:** [October 26, 2016, 5:33am UTC](https://forums.percona.com/t/is-there-a-way-to-actually-limit-innodb-data-dictionary/5170 "2016-10-26T05:33:32Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![HTF1](https://avatars.discourse-cdn.com/v4/letter/h/df788c/32.png) [@HTF1](https://forums.percona.com/u/HTF1)\ **Post date:** [October 26, 2016, 5:33am UTC](https://forums.percona.com/t/is-there-a-way-to-actually-limit-innodb-data-dictionary/5170/1 "2016-10-26T05:33:32Z")
I would like downgrade AWS instance to save the costs however due to the number of tables and workload (logical backups) InnoDB data dictionary grows extremely large. ```auto mysql> SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES; +----------+ | COUNT(*) | +----------+ | 1020034 | +----------+ 1 row in set (4.61 sec) mysql> SELECT COUNT(*) FROM information_schema.INNODB_SYS_INDEXES; +----------+ | COUNT(*) | +----------+ | 2628181 | +----------+ 1 row in set (4.99 sec) ``` ```auto mysql> SHOW GLOBAL VARIABLES LIKE 'table_%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | table_definition_cache | 400 | | table_open_cache | 1 | | table_open_cache_instances | 1 | +----------------------------+-------+ 3 rows in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'Open%table%'; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | Open_table_definitions | 400 | | Open_tables | 1 | | Opened_table_definitions | 2312885 | | Opened_tables | 22403462 | +--------------------------+----------+ 4 rows in set (0.00 sec) ``` ```auto mysql> SHOW GLOBAL STATUS LIKE 'Innodb_mem_dictionary'; +-----------------------+------------+ | Variable_name | Value | +-----------------------+------------+ | Innodb_mem_dictionary | 4517841711 | +-----------------------+------------+ 1 row in set (0.00 sec) ``` [table\_definition\_cache](http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_definition_cache) > [@](#): > > For InnoDB, table\_definition\_cache acts as a soft limit for the number of open table instances in the InnoDB data dictionary cache. If the number of open table instances exceeds the table\_definition\_cache setting, the LRU mechanism begins to mark table instances for eviction and eventually removes them from the data dictionary cache. Any idea why this is not happening? I was wondering if enebling innodb\_file\_per\_table would help in this case. Some extra info: ```auto mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 8589934592 | +-------------------------+------------+ 1 row in set (0.00 sec) mysql> SELECT @@version, @@version_comment; +--------------------+---------------------------------------------------------------------------------------------------+ | @@version | @@version_comment | +--------------------+---------------------------------------------------------------------------------------------------+ | 5.6.30-76.3-56-log | Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 | +--------------------+---------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` ```auto # cat /etc/redhat-release CentOS Linux release 7.2.1511 (Core) # free -m total used free shared buff/cache available Mem: 14881 14239 326 6 315 378 Swap: 4095 1237 2858 # ps -o %mem,rss,vsize -C mysqld %MEM RSS VSZ 94.2 14364148 16262404 ``` ---
**Author:** ![HTF1](https://avatars.discourse-cdn.com/v4/letter/h/df788c/32.png) [@HTF1](https://forums.percona.com/u/HTF1)\ **Post date:** [October 30, 2016, 12:58pm UTC](https://forums.percona.com/t/is-there-a-way-to-actually-limit-innodb-data-dictionary/5170/2 "2016-10-30T12:58:00Z")
It looks like your implementation in 5.5 was more reliable: ![](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/0/07ad4f9506e4d3dc20053edd570b7d8aa38a8fe7.png) ---
**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\ **Post date:** [October 30, 2016, 1:23pm UTC](https://forums.percona.com/t/is-there-a-way-to-actually-limit-innodb-data-dictionary/5170/3 "2016-10-30T13:23:20Z")
What is SHOW CREATE TABLE ? Do you have any FOREIGN KEYS defined ? Such tables are not removed from dictionary cache [url][https://blogs.oracle.com/mysqlinnodb/entry/mysql\_5\_6\_data\_dictionary[/url]](https://blogs.oracle.com/mysqlinnodb/entry/mysql_5_6_data_dictionary%5B/url%5D) ---
**Author:** ![HTF1](https://avatars.discourse-cdn.com/v4/letter/h/df788c/32.png) [@HTF1](https://forums.percona.com/u/HTF1)\ **Post date:** [October 30, 2016, 2:32pm UTC](https://forums.percona.com/t/is-there-a-way-to-actually-limit-innodb-data-dictionary/5170/4 "2016-10-30T14:32:38Z")
Thanks Peter, that may be the case here: Number of databases: ```auto mysql> SELECT COUNT(*) FROM information_schema.SCHEMATA; +----------+ | COUNT(*) | +----------+ | 2740 | +----------+ 1 row in set (0.01 sec) ``` Number of tables: ```auto # ls -lR /var/lib/mysql | grep -c "\.frm$" 836242 ``` Number of tables with foreign keys per database: ```auto mysql> SELECT COUNT(DISTINCT TABLE_NAME) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_SCHEMA = ''; +----------------------------+ | COUNT(DISTINCT TABLE_NAME) | +----------------------------+ | 76 | +----------------------------+ 1 row in set (0.01 sec) ``` > [@](#): > > 2740 x 76 = 208240 - that’s ~25% of tables that won’t be removed from dictionary cache ---
**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\ **Post date:** [October 30, 2016, 4:30pm UTC](https://forums.percona.com/t/is-there-a-way-to-actually-limit-innodb-data-dictionary/5170/5 "2016-10-30T16:30:52Z")
Yep. So you need to have enough memory for those or get rid of Foreign keys 🙂 You may wonder how it worked in earlier versions of Percona Server - I think our code did not handle some edge cases very well (some race conditions when you have tables with foreign keys) which could cause problems under high load.