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
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
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.)
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, you should have a table
cache size of at least
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
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
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
default value is 64; this can be changed with the
--table_open_cache option to
mysqld. 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
and a table in the cache is no longer being used by any
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.
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
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
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
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”.
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
statements, you should increase the table cache size. See
Section 5.1.4, “Server System Variables”, and
Section 5.1.6, “Server Status Variables”.