Basic Details
The table_open_cache variable is responsible for setting the maximum number of tables the server can have open during a single cache instance.
table_open_cache – Usage
In MySQL, tables can be queried simultaneously. Tables are opened independently for each query. This increases memory usage but improves server performance.
Configuration of the table_open_cache variable prevents tables often queried by the same client, from having to be reopened. Table_open_cache is auto-sized on server startup. When the cache is at capacity and another table needs to be opened, MySQL will start by removing the least recently used table. When all space is currently being used by opened and in-use tables, new tables will still be opened, but as soon as a table becomes idle it will be removed from the cache until the set table_open_cache value is reached.
All tables open in the cache can be emptied with the flush-tables command.
Command Line table_open_cache variable configuration:
To configure table_open_cache in the command line, enter:
- - table-open-cache = #
Replace the bolded # with the best table cache value for your database.
Configuration File table_open_cache variable configuration:
You can also change the value of table_open_cache using a configuration file. To do so, you will use mysqld section. You will need to restart MySQL:
[mysqld]
table-open-cache = #
Adjust the bolded # with the cache value that suits your database needs.
Configuration of the table_open_cache variable prevents tables often queried by the same client, from having to be reopened. Table_open_cache is auto-sized on server startup. When the cache is at capacity and another table needs to be opened, MySQL will start by removing the least recently used table. When all space is currently being used by opened and in-use tables, new tables will still be opened, but as soon as a table becomes idle it will be removed from the cache until the set table_open_cache value is reached.
All tables open in the cache can be emptied with the flush-tables command.
Command Line table_open_cache variable configuration:
To configure table_open_cache in the command line, enter:
- - table-open-cache = #
Replace the bolded # with the best table cache value for your database.
Configuration File table_open_cache variable configuration:
You can also change the value of table_open_cache using a configuration file. To do so, you will use mysqld section. You will need to restart MySQL:
[mysqld]
table-open-cache = #
Adjust the bolded # with the cache value that suits your database needs.
Deciding on table_open_cache value
When evaluating if table_open_cache needs to be adjusted, compare the variable opened_tables with the server uptime variable. Tables should not be increasing very rapidly. If so, increase the table_open_cache value until the opened tables value significantly slows. Consider the number of threads and the number of tables in your database when deciding a cache value.
Multiply maximum concurrent connections (max_connections) by the maximum number of tables per join. Be aware of operating system memory limitations. If table_open_cache is set too high, failed queries and connections are likely.
Try to increase open_files_limit in case you can't increase table_open_cache variable.
Multiply maximum concurrent connections (max_connections) by the maximum number of tables per join. Be aware of operating system memory limitations. If table_open_cache is set too high, failed queries and connections are likely.
Try to increase open_files_limit in case you can't increase table_open_cache variable.
