2

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

Improve this question
2
  • How much RAM? Nearly 4G of dictionary? What variable are you setting? Buffer_pool is only 30K -- I'm surprised it works at all ! Or is that 30K blocks? In general, 200K tables is a very bad schema design. SHOW VARIABLES LIKE 'innodb%'; and SHOW GLOBAL STATUS LIKE Innodb%`;'
    – Rick James
    Commented Nov 15, 2016 at 16:15
  • buffer pool is measured in pages. The question is not about innodb, but about memory.
    – Alexander Tarasul
    Commented Nov 17, 2016 at 3:39

1 Answer 1

Reset to default
2

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

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.