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
N is the maximum number of tables per
join in any of the queries which you execute. You also need to
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_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 to MySQL using
--open-files-limit startup option to
Sección A.2.17, “No se encontró el fichero”.
The cache of open tables is kept at a level of
table_cache entries. The default value is 64;
this can be changed with the
option to mysqld. Note that MySQL may
temporarily open more tables than this in order to execute
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 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 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
dedicated table object is allocated for the thread. This table
object is not shared with other
threads and is not closed until the thread calls
HANDLER or until the thread terminates. When this
happens, the table is returned to the table cache (if the cache
isn't full). See Sección 13.2.3, “Sintaxis de
You can determine whether your table cache is too small by
checking the mysqld status variable
mysql> SHOW STATUS LIKE 'Opened_tables'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Opened_tables | 2741 | +---------------+-------+
If the value is large, even when you have not issued a lot of
FLUSH TABLES statements, you should increase
the table cache size. See
Sección 5.3.3, “Variables de sistema del servidor” and
Sección 5.3.4, “Variables de estado del servidor”.
Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a firstname.lastname@example.org.