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 fewer than 12 tables.
MySQL 是多线程的,因此可能有许多客户端同时对给定表发出查询。为了尽量减少多个客户端会话在同一个表上具有不同状态的问题,表由每个并发会话独立打开。这会使用额外的内存,但通常会提高性能。对于MyISAM
表,每个打开表的客户端的数据文件都需要一个额外的文件描述符。(相比之下,索引文件描述符在所有会话之间共享。)
table_open_cache
和
系统变量影响服务器保持打开
的max_connections
最大文件数。如果增加其中一个或两个值,您可能会遇到操作系统对每个进程打开的文件描述符数量施加的限制。许多操作系统允许您增加打开文件的限制,尽管该方法因系统而异。请查阅您的操作系统文档以确定是否可以增加限制以及如何增加限制。
table_open_cache
是相关的max_connections
。例如,对于 200 个并发运行连接,指定表缓存大小至少为,其中
是您执行的任何查询中每个连接的最大表数。您还必须为临时表和文件保留一些额外的文件描述符。
200 *
N
N
确保您的操作系统可以处理设置所暗示的打开文件描述符的数量
table_open_cache
。如果
table_open_cache
设置得太高,MySQL 可能会用完文件描述符并出现拒绝连接或无法执行查询等症状。
还要考虑到MyISAM
存储引擎需要为每个唯一的打开表提供两个文件描述符。对于分区MyISAM
表,打开表的每个分区都需要两个文件描述符。(当MyISAM
打开一个分区表时,它会打开这个表的每个分区,不管给定的分区是否被实际使用。请参阅
MyISAM 和分区文件描述符的使用。)要增加 MySQL 可用的文件描述符的数量,请设置open_files_limit
系统变量。请参阅
第 B.3.2.16 节,“未找到文件和类似错误”。
The cache of open tables is kept at a level of
table_open_cache
entries. The
server autosizes the cache size at startup. To set the size
explicitly, set the
table_open_cache
system
variable at startup. MySQL may temporarily open more tables
than this to execute queries, as described later in this
section.
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_open_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 not currently in use are released, beginning with the table least recently used.
If a new table must 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 MyISAM
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
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 tbl_name
OPENHANDLER
or the
thread terminates. When this happens, the table is put back in
the table cache (if the cache is not full). See
Section 13.2.4, “HANDLER Statement”.
tbl_name
CLOSE
To determine whether your table cache is too small, check the
Opened_tables
status
variable, which indicates the number of table-opening
operations since the server started:
mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741 |
+---------------+-------+
如果该值非常大或增加很快,即使您没有发出很多FLUSH
TABLES
语句,请
table_open_cache
在服务器启动时增加该值。