8.4.3.1 How MySQL Opens and Closes Tables

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 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:

The status variable "Flush_commands" will give you an idea of how often you run flushes.

-Robin

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.

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.

This section really is telling an incomplete story on Windows inparticular, and could lead to considerable confusion if you've everwatched your server "come down" from a high open_files/open_tablesperiod.

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 ofmax_open_files = 2048 with absolute peak traffic, you would think itimpossible that those numbers would crawl back down without youissuing 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 tominimal table and file usage (for example, if you have no late-nightload).