table_open_cache: how to tune MySQL table cache safely
Tuning table_open_cache variable
Written By ROMAN AGABEKOV | 2022-02-13
Last update | OCT 22, 2026
table_open_cache controls how many table objects MySQL keeps open, and table_open_cache_instances divides that total into smaller caches.
If it is too low, MySQL may repeatedly open tables and waste work. If it is too high, it can increase file descriptor pressure and hide the real cause of table churn.
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.
If it is too low, MySQL may repeatedly open tables and waste work. If it is too high, it can increase file descriptor pressure and hide the real cause of table churn.
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
table_open_cache sets the target number of open table objects MySQL keeps available for reuse. MySQL can temporarily exceed this target when all cached table instances are in use.
- Risk: file descriptor limits, too many open files, masking schema/query issues.
- Restart: Not required for a runtime change. Changes made only in my.cnf take effect at startup.
- Rollback: Restore the previous running value and any saved configuration changed during the test.
- Verification: Compare table openings, open file usage, query latency and errors under a similar workload.
What Releem checks:
- Opened table counters and cache pressure.
- Related limits such as open_files_limit.
- Query/schema patterns that create table churn.
- Whether a config change is safer than query/schema cleanup.
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.
For a deeper dive into MySQL system variables and how they affect performance, check out our detailed guide Digging into MySQL Variables.
For a deeper dive into MySQL system variables and how they affect performance, check out our detailed guide Digging into MySQL Variables.
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.
table_open_cache can be changed while MySQL is running, but changes made only in my.cnf take effect at the next startup.
Changing the running value:
An administrator can use SET GLOBAL to change the current value without restarting MySQL. This change alone does not survive a restart.
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.
table_open_cache can be changed while MySQL is running, but changes made only in my.cnf take effect at the next startup.
Changing the running value:
An administrator can use SET GLOBAL to change the current value without restarting MySQL. This change alone does not survive a restart.
Replace the "4000" with the best table cache value for your database.
SET GLOBAL table_open_cache = 4000;It does not save the change for future startups.
To change the running value and save it for future startups, use this alternative:
To change the running value and save it for future startups, use this alternative:
SET PERSIST table_open_cache = 4000;Configuration File table_open_cache variable configuration:
You can also change the value of table_open_cache using a MySQL configuration file. To do so, you will use mysqld section:
You can also change the value of table_open_cache using a MySQL configuration file. To do so, you will use mysqld section:
Adjust the # with the cache value that suits your database needs.
[mysqld]
table-open-cache = # After that restart MySQL.
Saving the file does not change the running server. MySQL reads the setting at its next startup.
Saving the file does not change the running server. MySQL reads the setting at its next startup.
Deciding on table_open_cache value
When evaluating whether table_open_cache needs to be adjusted, check how quickly Opened_tables increases during the workload you are investigating. A high total alone does not mean the cache is too small. Check cache misses and overflows as well, and consider whether a recent restart or table flushing caused the additional table openings.
A larger cache can require more memory and file descriptors, which are handles used to access open files. If MySQL runs out of file descriptors, queries or connections can fail.
Try to increase MySQL open_files_limit in case you can't increase table_open_cache variable.
- Start with your server’s current value. The documented default for Oracle MySQL 8.0 and 8.4 is 4000, but the right value depends on your workload.
- If frequent table openings and cache misses suggest insufficient cache capacity, check available memory and the operating system’s open-file limit before testing a higher value.
- Compare query response times, errors and resource usage under a similar workload.
- Keep the change only if it improves the original problem without causing resource pressure.
A larger cache can require more memory and file descriptors, which are handles used to access open files. If MySQL runs out of file descriptors, queries or connections can fail.
Try to increase MySQL 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 cached open table instances across all threads. 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 how quickly Opened_tables increases during the workload you are investigating. Check cache misses and overflows too, since recent restarts or table flushing can also explain additional openings.
If the evidence suggests the cache is too small, check available memory and open file limits before testing a higher value. 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?
Start with your server’s current value. Oracle MySQL 8.0 and 8.4 have a documented default of 4000, but the right value depends on your workload. Consider increasing it only when cache activity suggests insufficient capacity and enough memory and file descriptors are available.
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.
How can I tell if table_open_cache is too low?
Frequent table openings and cache misses during the affected workload can indicate cache pressure. Check overflows and consider recent restarts or table flushing before deciding whether to increase the value. A large historical Opened_tables total alone does not mean the cache is too small.
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 cached open table instances across all threads. 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 how quickly Opened_tables increases during the workload you are investigating. Check cache misses and overflows too, since recent restarts or table flushing can also explain additional openings.
If the evidence suggests the cache is too small, check available memory and open file limits before testing a higher value. 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?
Start with your server’s current value. Oracle MySQL 8.0 and 8.4 have a documented default of 4000, but the right value depends on your workload. Consider increasing it only when cache activity suggests insufficient capacity and enough memory and file descriptors are available.
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.
How can I tell if table_open_cache is too low?
Frequent table openings and cache misses during the affected workload can indicate cache pressure. Check overflows and consider recent restarts or table flushing before deciding whether to increase the value. A large historical Opened_tables total alone does not mean the cache is too small.
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.
Explore More
Releem Score Description. What is Releem Score? How to use Releem Score to improve MySQL Performance
Complete list of steps to increase open_files_limit and prevent limiting MySQL variables
In this article, we'll walk you through a series of SQL commands that allow you to check the size of databases, tables, and indexes directly using the MySQL command line interface. This guide is especially useful if you prefer working with SQL commands over graphical tools like MySQL Workbench or phpMyAdmin.
See how Releem
detects configuration risk
detects configuration risk