table_cache = threads * opentables
# opentables:表数据文件，表索引文件(MYISAM)
# 打开一个表
# MYISAM
Open_files +2
Opened_files +3
# INNODB
Open_files +0
Opened_files +1
Opened_table_definitions +1
Opened_tables +1
Open_table_definitions +1
Open_tables +1
查看table打开的数量，如果一直在增长，说明cache太小
SHOW GLOBAL STATUS LIKE 'Opened_tables'
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.
每个线程缓存自己打开的表,每个query最大使用的表数量。但temporary表或文件会占用额外的文件描述符
Make sure that your operating system can handle the number of open file descriptors implied by the table_cachesetting. 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.
table_cache不能超过操作系统能处理的文件描述符上限，myisam由于索引与数据分开存放，所以每个表会多占用一个文件描述符
MySQL closes an unused table and removes it from the table cache under the following circumstances:
mysql关闭不使用的表，并从table cache移除的几种情况
-
When the cache is full and a thread tries to open a table that is not in the cache.
cache满了，并且新打开的表不在cache中 -
When the cache contains more than table_cache entries and a table in the cache is no longer being used by any threads.
cache中表数量大于table cache，并且表不再被任何线程使用 -
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.
执行flush tables命令
When the table cache fills up, the server uses the following procedure to locate a cache entry to use:
table cache 满了的处理方式
-
Tables that are not currently in use are released, beginning with the table least recently used.
按LRU(最近最少使用)原则，把不在使用的表进行释放 -
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.
参考: