I just discovered table_definition_cache and I am trying to decide what to set it to. I am messing with my config because of performance issues. On one server I have 36599 tables and when I run SHOW GLOBAL STATUS the value for Opened tables is 930312.
table_definition_cache is set to 20k. On another server I have 45349 tables and when I run SHOW GLOBAL STATUS the value for Opened tables is. The table_definition_cache variable controls the size of this cache.
If a client issues a query that references a table for which the metadata is not in the cache, the server will retrieve the metadata from the data dictionary and add it to the cache. The table_definition_cache system variable defines the number of table definitions that can be stored in the table definition cache. If you use a large number of tables, you can create a large table definition cache to speed up the opening of tables.
table_definition_cache: The maximum number of tables whose metadata can be stored in memory, without having to use file descriptors to read the.frm files. Dictionary Memory Allocated: The amount of memory that is allocated in the innodb dictionary. FLUSH TABLES is to force all tables to be closed to ensure that if someone adds a new table outside of MySQL, all threads will start using the new table.
Table Definition Cache This parameter shows the number of table definitions (SHOW CREATE TABLE \G) that can be stored. This is to speed up opening of tables and only one entry per table. table_definition_cache is used to set the number of table definition objects.
The table definition cache stores metadata (such as column names and data types) of the tables in the database so that this information can be quickly accessed during query execution. Generally table_open_cache and table_definition_cache are set to the same value. The number of open table objects and table definitions are directly related, so setting these two parameters to the same value can ensure that the metadata and open table objects are stored in the cache in a consistent and efficient manner.
wait_timeout. The host_cache_size variable controls the size of the host cache, as well as the size of the Performance Schema host_cache table that exposes the cache contents. The number of table definitions (SHOW CREATE TABLE\G) that can be stored in the table definition cache (table_definition_cache).
If you have a large number of tables (> 400) in your database instance, you should consider a larger table definition cache to increase your database throughput and decrease your query latency. We have more than 2000 databases with more than 500000 tables combined and we are starting to get SQLSTATE [HY000]: General error: 1615 Prepared statement needs to be re-prepared due to limitations of table_definition_cache parameter as it can only be maximum of 2000 in default mysql configuration. Can you add support to increase this value along with table_open_cache value?