7.4.8. 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 threads having different states on
the same table, the table is opened independently by each
concurrent thread. 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 threads.)
The table_open_cache,
max_connections, and
max_tmp_tables system variables affect the
maximum number of files the server keeps open. If you increase
one or more 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 allow 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 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 also have to take
into account that the MyISAM storage engine
needs two file descriptors for each unique open table. You can
increase the number of file descriptors available to MySQL using
the --open-files-limit startup option to
mysqld_safe. See
Section A.2.17, “File Not Found”.
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
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 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.3, “HANDLER Syntax”.
You can determine whether your table cache is too small by
checking the mysqld status variable
Opened_tables:
mysql> SHOW STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741 |
+---------------+-------+
If the value is very large, even when you have not issued many
FLUSH TABLES statements, you should increase
the table cache size. See
Section 5.2.2, “Server System Variables”, and
Section 5.2.4, “Server Status Variables”.
