| System Variable | Name | table_definition_cache | |
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | Type | integer | |
| Default | -1 (autosized) | ||
| Min Value | 400 | ||
| Max Value | 524288 | ||
The number of table definitions (from .frm files) that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. The minimum value is 400. The default value is based on the following formula, capped to a limit of 2000:
400 + (table_open_cache / 2)
For InnoDB, table_definition_cache acts as a soft limit for the number of open table instances in the InnoDB data dictionary cache. If the number of open table instances exceeds the table_definition_cachesetting, the LRU mechanism begins to mark table instances for eviction and eventually removes them from the data dictionary cache. The limit helps address situations in which significant amounts of memory would be used to cache rarely used table instances until the next server restart. The number of table instances with cached metadata could be higher than the limit defined by table_definition_cache, becauseInnoDB system table instances and parent and child table instances with foreign key relationships are not placed on the LRU list and are not subject to eviction from memory.
Additionally, table_definition_cache defines a soft limit for the number of InnoDB file-per-table tablespaces that can be open at one time, which is also controlled by innodb_open_files. If bothtable_definition_cache and innodb_open_files are set, the highest setting is used. If neither variable is set, table_definition_cache, which has a higher default value, is used. If the number of open tablespace file handles exceeds the limit defined by table_definition_cache or innodb_open_files, the LRU mechanism searches the tablespace file LRU list for files that are fully flushed and are not currently being extended. This process is performed each time a new tablespace is opened. If there are no “inactive” tablespaces, no tablespace files are closed.
Optimize MySQL table_open_cache
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable. To optimize MySQL table_open_cache you need to consider lot of factors. However, these are the general procedure to decide whether this variable should be tuned.
1. Find current value of open_tables and opened_tables
mysql> show global status like 'open%'; +--------------------------+---------+ | Variable_name | Value | +--------------------------+---------+ | Open_files | 1583 | | Open_streams | 0 | | Open_table_definitions | 1400 | | Open_tables | 2000 | | Opened_files | 2619222 | | Opened_table_definitions | 110583 | | Opened_tables | 482099 | +--------------------------+---------+ 7 rows in set (0.00 sec)
2. Find out Table cache hit rate
Table cache hit rate = table_open_cache*100/Opened_tables.
= 2000*100/482099
= 0.41%
In general it should be more than 50%. So you need to increase value of table_open_cache, though there are lots of reasons to have a high value of Opened_tables. Like FLUSH TABLES will close all open tables and reopen it which significantly increases Opened_tables value.
At this stage you are almost sure table_open_cache system variable is not tuned properly. Now you have to optimize MySQL table_open_cache and find out perfect value for this. To find tuned value of table_open_cache value follow the steps:
1. Find out total tables of your database
mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
+----------+
| COUNT(*) |
+----------+
| 2020 |
+----------+
1 row in set (0.05 sec)
- Find threads currently connected to your database.
mysql> show global status like '%Threads_connected%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 39 |
+-------------------+-------+
1 row in set (0.00 sec)
It would be best if you take threads connected at busiest time of your database or take several times at different time and make an average.
3. Calculate the tune value of table_open_cache and set it
Table_open_cache = total_tables*Threads_connected
= 2020*39
= 78780
As all the threads (user) are not generally access all tables. I think you should set 50% of the value calculated. Because too big value of this variable has some other side effects. So the formula becomes
Table_open_cache = total_tables*Threads_connected*.50
4. Along with table_open_cache you should also tune open_files_limit system variable.
In general it is 2x of table_open_cache.
open_files_limit= Table_open_cache*2
open_files_limit is not a dynamic variable. So you should set it in my.cnf file and restart MySQL.
*Make sure that your operating system can cope with the number of open file descriptors required by the table_open_cache setting.
- Go to your Mysql configuration file (in linux it is /etc/my.cnf) and set the table_open_cache and open_files_limit
vi /etc/my.cnf
table_open_cache=39390
open_files_limit=78780
6. Restart the MySQL ( In Linux it is like)
/etc/init.d/mysqld restart
or
/etc/init.d/mysql restart
The database I have taken has 2020 tables so the value of table_open_cache is little big. For your case it may be significantly small
| Property | Value |
|---|---|
| Command-Line Format | --table-definition-cache=# |
| System Variable | table_definition_cache |
| Scope | Global |
| Dynamic | Yes |
| Type | Integer |
| Default Value (>= 5.6.8) | -1 (signifies autosizing; do not assign this literal value) |
| Default Value (<= 5.6.7) | 400 |
| Minimum Value | 400 |
| Maximum Value | 524288 |
The number of table definitions (from .frm files) that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. The minimum value is 400. The default value is based on the following formula, capped to a limit of 2000:
400 + (table_open_cache / 2)[출처] Optimize MySQL table_open_cache|작성자 bomyzzang