(mysql.info.gz) Table cache
Info Catalog
(mysql.info.gz) Open tables
(mysql.info.gz) Optimizing Database Structure
(mysql.info.gz) Creating many tables
7.4.8 How MySQL Opens and Closes Tables
---------------------------------------
The `table_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_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 in a join.
You also need to 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_cache' setting. If `table_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 for MySQL with the
`--open-files-limit' startup option to `mysqld_safe'. Not enough
file handles.
The cache of open tables will be kept at a level of `table_cache'
entries. The default value is 64; this can be changed with the
`--table_cache' option to `mysqld'. Note that MySQL may temporarily
open even more tables to be able to execute queries.
An unused table is closed and removed 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_cache' entries and a
thread is no longer using a table.
* 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, in least
recently used order.
* 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 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
isn't full). `HANDLER' HANDLER.
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 quite big, even when you haven't issued a lot of `FLUSH
TABLES' statements, you should increase your table cache size. See
Server system variables and Server status variables.
Info Catalog
(mysql.info.gz) Open tables
(mysql.info.gz) Optimizing Database Structure
(mysql.info.gz) Creating many tables
automatically generated byinfo2html