MySQL - huge Dictionary memory allocated Our MySQL 5.5.38 servers have 1000-3000 InnoDB databases each (with about 100 tables each). All data is in single file. Most of DBs are active. While it's expected that information schema should be huge - on those servers Dictionary memory allocated from 3 to 15 GB, competing with buffer pool.

Is this an acceptable scenario? What risks are here, what settings can be used to manage this dictionary memory?



BUFFER POOL AND MEMORY

Total memory allocated 519405568; in additional pool allocated 0
Dictionary memory allocated 3839216241
Buffer pool size   30976
Sorry for the late answer, but I've been running into a similar situation--3.22 GB allocated for an Innodb data dictionary and eventually my server runs out of RAM and restarts MySQL.

From what I have read, there is no way to manage the size of the Innodb data dictionary in MySQL 5.5, but apparently there is a way in MySQL 5.6 and in Percona Server, so if you need to manage limits for the data dictionary, upgrade to MySQL 5.6 or newer.

According to the Percona guys and authors of High Performance MySQL, every time an innodb table is opened, an object for that table is loaded into the data dictionary. That object doesn't get removed, so the size of the dictionary increases over time. They also say that an overlarge Innodb data dictionary causes problems with database servers that have thousands of large tables, which it seems like you certainly have.

High Performance MySQL pg 356

Percona Server InnoDB Data dictionary

MySQL 5.6 InnoDB Data Dictionary