Table_definition_cache

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 S.

table_definition_cache Usage When a client establishes a connection to a MySQL server, the server maintains a cache of table definitions in memory so that it can quickly look up metadata about tables that are used in queries. The table_definition_cache variable controls the size of this 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. It then adds the table definition to the table_definition_cache in the server layer and also to the Data Dictionary in the InnoDB storage engine layer. So, next time when the table needs to be read, InnoDB can simply get the table definition from the Data Dictionary cache.

Is the above flow correct? If not, How does MySQL open the tables? 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. 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 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.

MySQL about Table cache settings, read this article is enough Introduction:This article sorts out the understanding of the parameters of table_definition_cache, table_open_cache and table_open_cache_instances, I hope it will be helpful to everyone. 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. 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 data dictionary.