Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  How MySQL Opens and Closes Tables How MySQL Opens and Closes Tables

When you execute a mysqladmin status command, you should see something like this:

Uptime: 426 Running threads: 1 Questions: 11082
Reloads: 1 Open tables: 12

The Open tables value of 12 can be somewhat puzzling if you have only six tables.

MySQL is multi-threaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session. This uses additional memory but normally increases performance. With MyISAM tables, one extra file descriptor is required for the data file for each client that has the table open. (By contrast, the index file descriptor is shared between all sessions.)

The table_open_cache and max_connections system variables affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. Many operating systems permit you to increase the open-files limit, although the method varies widely from system to system. Consult your operating system documentation to determine whether it is possible to increase the limit and how to do so.

table_open_cache is related to max_connections. For example, for 200 concurrent running connections, specify a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.

Make sure that your operating system can handle the number of open file descriptors implied by the table_open_cache setting. If table_open_cache is set too high, MySQL may run out of file descriptors and refuse connections, fail to perform queries, and be very unreliable.

You should also take into account the fact that the MyISAM storage engine needs two file descriptors for each unique open table. For a partitioned MyISAM table, two file descriptors are required for each partition of the opened table. (Note further that when MyISAM opens a partitioned table, it opens every partition of this table, whether or not a given partition is actually used. See MyISAM and partition file descriptor usage.) You can increase the number of file descriptors available to MySQL using the --open-files-limit startup option to mysqld. See Section B.5.2.18, “'File' Not Found and Similar Errors”.

The cache of open tables is kept at a level of table_open_cache entries. The server autosizes the cache size at startup. To set the size explicitly, set the table_open_cache system variable at startup. Note that MySQL may temporarily open more tables than this to execute queries.

MySQL closes an unused table and removes it from the table cache under the following circumstances:

  • When the cache is full and a thread tries to open a table that is not in the cache.

  • When the cache contains more than table_open_cache entries and a table in the cache is no longer being used by any threads.

  • When a table flushing operation occurs. This happens when someone issues a FLUSH TABLES statement or executes a mysqladmin flush-tables or mysqladmin refresh command.

When the table cache fills up, the server uses the following procedure to locate a cache entry to use:

  • Tables that are not currently in use are released, beginning with the table least recently used.

  • If a new table needs to be opened, but the cache is full and no tables can be released, the cache is temporarily extended as necessary. When the cache is in a temporarily extended state and a table goes from a used to unused state, the table is closed and released from the cache.

A MyISAM table is opened for each concurrent access. This means the table needs to be opened twice if two threads access the same table or if a thread accesses the table twice in the same query (for example, by joining the table to itself). Each concurrent open requires an entry in the table cache. The first open of any MyISAM table takes two file descriptors: one for the data file and one for the index file. Each additional use of the table takes only one file descriptor for the data file. The index file descriptor is shared among all threads.

If you are opening a table with the HANDLER tbl_name OPEN statement, a dedicated table object is allocated for the thread. This table object is not shared by other threads and is not closed until the thread calls HANDLER tbl_name CLOSE or the thread terminates. When this happens, the table is put back in the table cache (if the cache is not full). See Section 13.2.4, “HANDLER Syntax”.

You can determine whether your table cache is too small by checking the mysqld status variable Opened_tables, which indicates the number of table-opening operations since the server started:

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
| Variable_name | Value |
| Opened_tables | 2741  |

If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, increase the table cache size. See Section 5.1.4, “Server System Variables”, and Section 5.1.6, “Server Status Variables”.

Download this Manual
User Comments
  Posted by Robin Powell on July 7, 2005
The status variable "Flush_commands" will give you an idea of how often you run flushes.

  Posted by Richard Bronosky on April 21, 2008
I think it is unclear what the difference between Open_tables and Opened_tables are. I will explain as I understand it, and I hope I will be corrected where I may be inaccurate.

Open_tables is a status variable indicating the number of file descriptors that are currently open by all running threads. When multiple threads access a single table simultaneously, multiple file descriptors are used. This makes it possible, and likely, that Open_tables exceeds the number of tables (SELECT COUNT(*) FROM information_schema.TABLES;) on your instance.

table_cache is a system variable the defines the number of file_descriptors that are allocated into a pool to be used for Open_tables as needed. When the MySQL threads need to open "greater than table_cache" number of tables, additional file descriptors are opened. Each time one of these additional file descriptors is opened, the status variable Opened_tables is incremented.

Opened_tables is a status variable that keeps a running tally of the number of additional file descriptors that have been allocated for opening tables at times when the available file descriptors in table_cache have been depleted. These files descriptors are released when demand subsides. There is no way of knowing what the maximum number used descriptors has been over time since the variable Opened_tables is the tally since the instance's Uptime.

It is not uncommon to see a value of 0 for Opened_tables. This simply means that the table_cache has never been exceeded.

Example :
> show status like 'Open%';
| Variable_name | Value |
| Open_files | 487 |
| Open_streams | 0 |
| Open_tables | 325 |
| Opened_tables | 0 |
4 rows in set (0.01 sec)
> select @@table_cache;
| @@table_cache |
| 512 |
1 row in set (0.02 sec)
  Posted by Horst Pralow on May 19, 2008
The status variable 'Opened_tables' will also be incremented by 2 each time you create a temporary table.

Thus if you are using temporary tables, a large value in the global status varaiable 'Opened_tables' does not necessarily indicate that your table_cache value is too small, and increasing table_cache will not stop Opened_tables growing.

  Posted by Jay Maran on February 14, 2009
Note the difference between the global value of Opened_tables and the session value. Typically, you want the global value.

mysql> show status like 'opened%';
| Variable_name | Value |
| Opened_tables | 0 |

mysql> show global status like 'opened%';
| Variable_name | Value |
| Opened_tables | 687 |

  Posted by Sanford Whiteman on December 29, 2009
> [Table is closed when] a table flushing operation occurs. This
> happens when someone issues a FLUSH TABLES statement or executes a
> mysqladmin flush-tables or mysqladmin refresh command.

This section really is telling an incomplete story on Windows in
particular, and could lead to considerable confusion if you've ever
watched your server "come down" from a high open_files/open_tables

If you believe the docs as-is, then if you have, say, open_tables =
700 out of table_cache = 1024, and open_files = 1800 out of
max_open_files = 2048 with absolute peak traffic, you would think it
impossible that those numbers would crawl back down without you
issuing an explicit flush. That isn't the case at all. The flush_time
= 1800 on a default Windows system. That means that every half-hour,
your tables are flushed and you can see the system settle back down to
minimal table and file usage (for example, if you have no late-night

While flush_time can also be used on *nix, there it is 0 by default.

  Posted by DBA Lead on November 29, 2010
great tutorial on how mysql information_schema databases uses table_cache and what issues might arise if its set too high
  Posted by Zillur Bhuiyan on July 20, 2015
Optimizing Mysql table_open_cache is important specially for database which has large number of tables.

For setting up optimum value of table_open_cache you have to consider
1) average thread/user for the database;
2) total tables

You can read this article to know more about this.
Sign Up Login You must be logged in to post a comment.