(mysql.info) table-cache

Info Catalog

(mysql.info) myisam-index-statistics

(mysql.info) optimizing-database-structure

(mysql.info) creating-many-tables

 
 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_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 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_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 the
 -open-files-limit startup option to `mysqld_safe'. See 
 not-enough-file-handles.
 
 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 -table_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_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
  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 very large, even when you have not issued many `FLUSH
 TABLES' statements, you should increase the table cache size. See 
 server-system-variables, and  server-status-variables.
 

Info Catalog

(mysql.info) myisam-index-statistics

(mysql.info) optimizing-database-structure

(mysql.info) creating-many-tables