May 15, 2025 • Written by ROMAN AGABEKOV
This article is about the MySQL table_open_cache variable and its role in optimizing database performance. It explains how tables are opened independently for each query, increasing memory usage but enhancing server efficiency. The article details how configuring table_open_cache helps prevent frequent reopening of tables by caching often-used ones, discusses cache management including eviction of least recently used tables, and provides instructions on how to set this variable via command line or configuration file for better resource management.
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.
Releem automatically tunes table_open_cache and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
Releem automatically tunes table_open_cache and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
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.
FAQ:
Understanding MySQL table_open_cache
Understanding MySQL table_open_cache
What is table_open_cache in MySQL?
table_open_cache controls the number of table instances MySQL can keep open at once. A higher value reduces the need to repeatedly open and close tables, improving performance in workloads with many queries.
How do I tune table_open_cache in MySQL?
Monitor the Opened_tables status variable. If it increases rapidly, MySQL is frequently opening tables, which suggests table_open_cache is too low. Increase the value incrementally and observe performance.
What are the default values for table_open_cache in MySQL 5.7 and 8.0?
• MySQL 5.7: 2000
• MySQL 8.0: 4000
You can check your current value with:
SHOW VARIABLES LIKE 'table_open_cache';
How do I set table_open_cache in my.cnf?
Add or modify this line under the [mysqld] section:
table_open_cache = 4096
Then restart MySQL for the change to take effect.
What is a recommended value for table_open_cache?
For most workloads, 2000–4000 is a good starting point. For systems with high concurrency or many tables, consider increasing it. Avoid exceeding max_connections * 2 unless you have sufficient memory.
What’s the difference between table_open_cache and table_definition_cache?
• table_open_cache limits the number of open table instances.
• table_definition_cache limits the number of cached table metadata definitions.
Both should be tuned together for best performance.
How can I tell if table_open_cache is too low?
A consistently high value for Opened_tables indicates frequent table openings. This suggests the cache size is too small and should be increased.
Does table_open_cache affect memory usage?
Yes. Each open table consumes memory. Larger values improve performance but increase memory usage. Monitor system resources to avoid over-allocation.
Is table_open_cache relevant for MariaDB?
Yes. MariaDB uses the same variable. The tuning approach is also similar—monitor Opened_tables and adjust the cache accordingly.
How does table_open_cache impact overall performance?
A well-sized table_open_cache reduces I/O and CPU overhead from opening and closing tables. This improves query response times and server efficiency under heavy load.
table_open_cache controls the number of table instances MySQL can keep open at once. A higher value reduces the need to repeatedly open and close tables, improving performance in workloads with many queries.
How do I tune table_open_cache in MySQL?
Monitor the Opened_tables status variable. If it increases rapidly, MySQL is frequently opening tables, which suggests table_open_cache is too low. Increase the value incrementally and observe performance.
What are the default values for table_open_cache in MySQL 5.7 and 8.0?
• MySQL 5.7: 2000
• MySQL 8.0: 4000
You can check your current value with:
SHOW VARIABLES LIKE 'table_open_cache';
How do I set table_open_cache in my.cnf?
Add or modify this line under the [mysqld] section:
table_open_cache = 4096
Then restart MySQL for the change to take effect.
What is a recommended value for table_open_cache?
For most workloads, 2000–4000 is a good starting point. For systems with high concurrency or many tables, consider increasing it. Avoid exceeding max_connections * 2 unless you have sufficient memory.
What’s the difference between table_open_cache and table_definition_cache?
• table_open_cache limits the number of open table instances.
• table_definition_cache limits the number of cached table metadata definitions.
Both should be tuned together for best performance.
How can I tell if table_open_cache is too low?
A consistently high value for Opened_tables indicates frequent table openings. This suggests the cache size is too small and should be increased.
Does table_open_cache affect memory usage?
Yes. Each open table consumes memory. Larger values improve performance but increase memory usage. Monitor system resources to avoid over-allocation.
Is table_open_cache relevant for MariaDB?
Yes. MariaDB uses the same variable. The tuning approach is also similar—monitor Opened_tables and adjust the cache accordingly.
How does table_open_cache impact overall performance?
A well-sized table_open_cache reduces I/O and CPU overhead from opening and closing tables. This improves query response times and server efficiency under heavy load.