Understanding MySQL Table Cache: Experimental Verification and Operational Recommendations

MySQL's table cache stores table definition metadata, and many tuning tips circulate about this parameter. This article conducts experiments to verify the cache's effect.

First, a test database and an empty table are created, a connection is opened, and the initial session state is inspected.

In a separate window, strace is used to trace MySQL server file operations.

When a SELECT is issued on the newly created table, the operation does not hit the table cache; strace shows the mysqld process opening the table definition file ( test_tbl.frm ).

A second SELECT on the same table hits the table cache, and no new file operations appear in strace .

These observations indicate that the table cache saves the overhead of reading table definition files.

To explore whether a cache miss always incurs a file read, a new session with a distinct identifier is opened and a SELECT is performed. The cache is again missed, suggesting that the table cache is thread‑local, each thread maintaining its own cache of table definitions.

However, strace only shows a stat call (checking file existence) and no open , implying that a miss does not necessarily trigger a file read; another cache, the table_definition_cache , may be used instead.

Operational advice: MySQL documentation recommends calculating table_open_cache as max_concurrency × max_tables_per_join . The experiments confirm that the table cache is per‑thread, requiring one cache entry per concurrent thread, and that a join involving N tables needs at least N entries in the cache. Multiplying these two numbers yields the suggested configuration value.

Feel free to leave comments or questions about MySQL technical topics.