Table Cache

The server caches open file descriptors when possible to avoid repeated file-opening operations, but a cache that is too small will not hold all the file descriptors you need. The Opened_tables variable indicates the number of times the server had to open files to access tables. It provides a measure of whether your table cache is large enough.

The table_cache system variable controls the number of entries in table cache. Its default value is 64. The goal when configuring the table cache is to make it large enough that the server need not repeatedly open frequently accessed tables. A larger table cache requires more file descriptors. Operating systems place a limit on the number of file descriptors allowed to each process, so a table_cache cannot be made arbitrary large. Some OS do allow per-process file descriptor limit to be reconfigured.

The per-process limit on the number of open files. This limits the maximum size of the table cache that holds file descriptors for table files. You can tell MySQL to allocate more file descriptors with the —open-files-limit option, but that option cannot be increased beyond the per-process limit allowed by the operating system. If your operating system can be reconfigured, you might be able to increase this limit, which effectively allows a larger maximum table cache size.

Open_tables: how many tables are currently open

Opened_tables: how many table-opening operations the server has performed since it started.

To determine whether the table cache is large enough, check the Open_tables and Opened_tables status variables over time. Open_tables indicates how many tables are currently open, and Opened_tables indicates how many table-opening operations the server has performed since it started. If Open_tables usually is at or near the value of table_cache, and the value of Opened_tables increase steadily, it indicates that the table cache is being used to its capacity and the server often has to close tables in the cache so that it can open other tables. This is a sign that the table cache is too small and the we should increase the value of table_cache.

1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used. See http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

This variable controls the number of open tables that are cached. Table cache is related to max connections. For example, for 200 concurrent running connections, you should have a table cache of at leat 200 * N, where N is the maximum number of tables in a join. If you increase this value, the number of file descriptors needed by MySQLD will also increase. You can check if you need to increase the table cache by checking the Opened_tables variable. If this variable is big and you don't do FLUSH TABLES a lot (which just forces all tables to be closed and reopenend), then you should increase the value of this variable. See http://www.interworx.com/support/docs/nodeworx/mysql/howto-mysql-options

When the server opens a table, it maintains information about that table in the table cache, which is used to avoid reopening tables when possible. The next time a client tries to access the table, the server can use it immediately without opening the table again if it is found in the cache. However, if the cache is full and a client tries to access a table that isn't found there, some open table must be closed to free an entry in the cache for the new table. The table that is closed must then be reopened the next time a client access it.

The table_cache system variable controls the number of entries in the table cache. Its default value is 64. The goal when configuring the table cache is to make it large enough that the server need not repeatedly open frequently accessed tables. You must balance the fact that with a larger table cache the servers requires more file descriptors.

Operating systems place a limit on the number of file descriptors allowed to each process, so the table cache cannot be made arbitrarily large. However, some operating systems allow the per-process file descriptor limit to be reconfigured.

To determine whether the table cache is large enough, check the Open_tables and Opened_tables status variables over time. Open_tables indicates how many tables currently are open, and Opened_tables indicates how many table-opening operations the server has performed since it started. If Open_tables is usually at or near the value of table_cache, and the value of Opened_tables increase steadily, it indicates that the table cache is being used to its capacity and the the server often has to close tables in the cache so that it can open other tables. This is a sign that the table cache is too small and that you should increase the value of table_cache

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License