Understanding MySQL table_open_cache and Its Impact on DTS Performance

What is table_open_cache

According to MySQL 5.7 documentation, table_open_cache controls the total number of tables that can be opened simultaneously by all threads. Increasing it raises the number of file descriptors required by mysqld.

It works together with max_connections to affect the maximum number of open files. If either value is too high, the operating system limit on file descriptors may be reached.

Default is 2000 and is a global variable.

classTable__cache design

MySQL is multithreaded; each thread may need to access the same table. Repeatedly opening and closing table file handles is costly. Therefore MySQL implements a per‑thread Table_cache (classTable__cache) to reduce contention on the global LOCK_open mutex.

Excerpt from the source code:

The idea behind this cache is that most statements don't need to go to a central table definition cache to get a TABLE object and therefore don't need to lock LOCK_open mutex. Instead they only need to go to one Table_cache instance (the specific instance is determined by thread id) and only lock the mutex protecting this cache. DDL statements that need to remove all TABLE objects from all caches need to lock mutexes for all Table_cache instances, but they are rare.

Thus MySQL obtains a local Table_cache per thread, reducing global lock contention and improving concurrent query performance. DDL operations still require a global lock.

Typical workflow

When a query is executed, the thread checks its local Table_cache for the required table handle.

If not found, it checks the global table_open_cache.

If still not found, the table file is opened from disk and a new table object is created.

The new object is added to both the global cache and the thread‑local cache.

If the global cache is full, an LRU algorithm evicts unused entries.

Problems caused by improper configuration

Cache too small with many tables : Queries become slow; show processlist shows states like “opening table” lasting several seconds.

Cache too large : Memory consumption rises sharply (e.g., raising from 2000 to 10000 increased RAM from ~600 MB to ~2.5 GB).

classTable__cache performance degradation : An excessively large hash‑based cache can actually slow lookups.

Why DTS can trigger the issue

DTS (Data Transmission Service) synchronizes data via binlog, but for tables without a primary key it adds a marker column and performs full‑table scans.

When many tables lack primary keys, the scans generate heavy load, increase opening‑table latency, and push the global cache to its limits, causing LRU evictions and performance loss.

Scanning pressure on tables without primary keys

DTS adds a flag column and scans all rows of such tables. Large numbers of wide tables cause significant I/O and cache pressure.

Why binlog cannot be used

Without a primary key or unique index, a row cannot be uniquely identified, making UPDATE/DELETE replication ambiguous. DTS therefore resorts to full scans to ensure consistency.

Configuration recommendations

Monitor the Opened_tables status variable. If it is large and FLUSH TABLES is not used frequently, increase table_open_cache .

Beware that even a large cache cannot compensate for a massive number of tables accessed frequently; the benefit diminishes.

References

How MySQL Opens and Closes Tables: https://dev.mysql.com/doc/refman/5.7/en/table-cache.html

Table_cache Class Reference: https://dev.mysql.com/doc/dev/mysql-server/8.4.3/classTable__cache.html#details