One of InnoDB’s features is that memory allocated for internal tables definitions is not limited and may grow indefinitely. You may not notice it if you have an usual application with say 100-1000 tables. But for hosting providers and for user oriented applications ( each user has dedicated database / table) it is disaster. For 100.000+ tables InnoDB is consuming gigabytes of memory, keeping definition in memory all time after table was once opened. Only way to cleanup memory is to drop table or restart mysqld – I can’t say this is good solution, so we made patch which allows to restrict memory dedicated for data dictionary.
Patch was made by request of our customer Vertical Response and released under GPL, so you can download it there http://percona.com/blog/files/patches/innodb_dict_size_limit_standalone.patch. Currently patch is on testing stage, but later will be included into our releases. To limit memory we introduce new variable innodb_dict_size_limit (in bytes).
Some internals: There is already implemented in InnoDB LRU-based algorithm to keep only recent table entries, but it was not used by reason that InnoDB has to know if table is used or not on MySQL level. We made it by checking MySQL table_cache. If table is placed in table_cache we consider it as used, if not – we can delete it from InnoDB data dictionary. So there is the trick – if you have big enough table_cache, memory consumed by data dictionary may exceed innodb_dict_size_limit, as we can’t delete any table entry from it.
To finalize this post small marketing message – if you faced bug or problem which exists for long time and is not going to be solved by MySQL / InnoDB – contact us regarding Custom MySQL Development.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.