GNU Info

(mysql.info)Table cache

---

Next: Creating many tables Prev: Multiple-column indexes Up: Optimizing Database Structure

---

Enter node , (file) or (file)node

How MySQL Opens and Closes Tables
---------------------------------

`table_cache', `max_connections', and `max_tmp_tables' 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.
However, you can increase the limit on many systems.  Consult your OS
documentation to find out how to do this, because the method for
changing the limit varies widely from system to system.

`table_cache' is related to `max_connections'.  For example, for 200
concurrent running connections, you should have a table cache 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.

The cache of open tables can grow to a maximum of `table_cache'
(default 64; this can be changed with the `-O table_cache=#' option to
`mysqld').  A table is never closed, except when the cache is full and
another thread tries to open a table or if you use `mysqladmin refresh'
or `mysqladmin flush-tables'.

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 the cache is full and no tables can be released, but a new
     table needs to be opened, the cache is temporarily extended as
     necessary.

   * If the cache is in a temporarily-extended state and a table goes
     from in-use to not-in-use state, the table is closed and released
     from the cache.

A table is opened for each concurrent access. This means that if you
have two threads accessing the same table or access the table twice in
the same query (with `AS') the table needs to be opened twice.  The
first open of any table takes two file descriptors; each additional use
of the table takes only one file descriptor.  The extra descriptor for
the first open is used for the index file; this descriptor is shared
among all threads.

You can check if your table cache is too small by checking the mysqld
variable `opened_tables'.  If this is quite big, even if you haven't
done a lot of `FLUSH TABLES', you should increase your table cache.
Note: SHOW STATUS.