MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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 fewer than 12 tables.
MySQL is multithreaded, 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 *
, where
N
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 exhibit
symptoms such as refusing connections or failing to perform
queries.
Also take into account 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. (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.)
To increase the number of file descriptors available to MySQL,
set the open_files_limit
system variable. See
Section B.3.2.17, “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. MySQL may temporarily open more tables
than this to execute queries, as described later in this
section.
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 not currently in use are released, beginning with the table least recently used.
If a new table must 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
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 tbl_name
OPENHANDLER
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 Statement”.
tbl_name
CLOSE
To determine whether your table cache is too small, check the
Opened_tables
status
variable, 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_open_cache
value at
server startup.