The worst table cache I ever operated belonged to a multi-tenant SaaS that had made one decision years before I arrived: every customer got their own set of tables, all in one schema on one MySQL 8.0 instance. By the time it landed on my desk there were just over 50,000 tables in that schema, and the access pattern was perfectly round-robin — a request came in, picked the tenant's tables, ran its queries, and the next request was for a different tenant. The server had 32 cores and was burning about 70 percent user CPU at the 9 a.m. peak while serving barely 2,000 queries per second. No slow queries to speak of. The tell was Opened_tables: it was climbing by roughly 3,800 per second, sustained, for the entire business day. mysqld was spending most of its life opening table handles and evicting other handles to make room for them, because the default table_open_cache of 4,000 could hold eight percent of the working set on a good minute.
That week taught me the table cache is not a trivia knob you set once at install. It is a working-set cache with hard multipliers — connections times tables per statement — and when you miss it, you pay in file descriptors, dictionary lookups, and CPU that looks for all the world like a busy application. Here is what the cache holds, which counters expose the churn before your CPU graph does, why tenant-per-table schemas blow through every default, how sizing interacts with max_connections and the OS descriptor limit, and the honest ending: tuning bought us a quiet server, but the schema was still wrong.
What is the MySQL table cache actually caching?
Open table handles — the in-memory objects the server builds when a thread first touches a table — plus the file descriptors underneath them. Two properties surprise people. First, the cache is per-thread in effect: if ten concurrent connections are all reading the same table, that table can occupy ten cached handles at once, because each thread gets its own handle rather than sharing one. Second, an open InnoDB handle means an open .ibd file, so every cached handle is also a file descriptor held against the process limit (InnoDB keeps its own open-file accounting capped by innodb_open_files, which in 8.0 defaults to following table_open_cache; old MyISAM tables cost two descriptors each, one for data and one for indexes). When the cache is full and a thread needs a table that is not cached, the server evicts an unused handle to make room. If every cached handle is currently in use, the cache temporarily grows past its configured size rather than making the query wait — which is where the overflow counter comes in below. The cache also gets flushed wholesale by FLUSH TABLES, by FLUSH TABLES WITH READ LOCK during some backup flows, and by table eviction churn, so a monitoring agent that flushes on a schedule can manufacture the exact Opened_tables spike you are trying to diagnose.
Which status variables tell you the cache is too small?
Open_tables for current occupancy, Opened_tables as the cumulative miss counter, and in 8.0 the split counters Table_open_cache_hits, Table_open_cache_misses, and Table_open_cache_overflows for a cleaner read. Opened_tables counts every time a thread had to open a table that was not already in its cache, so the raw number is meaningless on its own — uptime matters. Sample it twice, a minute apart, and look at the rate. My rule of thumb from a decade of these: in steady state, with the working set warm, Opened_tables should be close to flat — a handful per second at most on a busy box. Thousands per second for hours is not a busy application; it is a cache that cannot hold the working set, and the CPU bill follows. The 8.0 counters make the same point as a ratio: if misses are a meaningful fraction of hits, the cache is churning. Overflows tell you something subtler — the server had to exceed table_open_cache temporarily because everything cached was in use — and a steadily rising overflow count at your configured size is a sign the peak demand exceeds the cache, not just the distinct-table count. The diagnostic block I run first:
-- capacity versus current occupancy
SHOW GLOBAL VARIABLES LIKE 'table_open_cache';
SHOW GLOBAL STATUS LIKE 'Open_tables';
-- the churn counter: sample twice, 60s apart, divide the delta by 60
SHOW GLOBAL STATUS LIKE 'Opened_tables';
-- 8.0 hit/miss/overflow split since startup
SHOW GLOBAL STATUS LIKE 'Table_open_cache%';
-- the two multipliers behind worst-case descriptor demand
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL VARIABLES LIKE 'open_files_limit';
-- who holds handles right now (8.0 performance_schema)
SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT(*) AS handles
FROM performance_schema.table_handles
GROUP BY OBJECT_SCHEMA, OBJECT_NAME
ORDER BY handles DESC
LIMIT 10;On the tenant box, Open_tables sat pinned at exactly 4,000 all day — the cache full, churning — while Opened_tables did its 3,800-per-second march. The last query in that block is the one that confirmed the round-robin pattern: no hot table at the top of the list, just a long flat tail of tenant tables each held by one or two threads. When you want per-statement or per-table context beyond these counters, the sys schema views I reach for are the ones in the sys schema field notes.
Why does one-table-per-tenant blow through every default?
Because a cache only helps when the working set fits, and this design makes the working set every table in the schema. The default table_open_cache of 4,000 in 8.0 is generous for a normal application — a few hundred tables, with a hot subset shared by most queries. Against 50,000 tables touched uniformly, 4,000 entries is an eviction treadmill: by the time a tenant is queried again, its handles were evicted hours ago, so essentially every first touch of every statement is a miss. A miss is not free. The server has to build the table object, consult the data dictionary, and open the underlying file — and on the other side, evicting a handle means closing all of that down. Multiply the churn by the per-thread property: 300 concurrent connections, each touching a handful of distinct tenant tables per request, want thousands of live handles at once, and the demand profile never stabilizes because the next request always belongs to a different tenant. That is where the 70 percent CPU went. The query layer was fine; the open-close cycle underneath it was the workload. No value of query tuning, indexing, or buffer pool work touches this — the buffer pool can be perfectly warm, as ours was, while the handle layer thrashes above it. If your InnoDB memory is already well-tuned along the lines of buffer pool sizing and CPU is still high with a climbing Opened_tables, look here next.
How do you size table_open_cache against max_connections and OS limits?
Start from worst-case demand — max_connections times the largest number of tables a single statement touches — then check the operating system will actually give you the file descriptors, because the kernel limit wins every argument. The documented worst case is that every connection holds a handle on every table its queries use simultaneously, plus temporary tables. For a normal schema, max_connections × N is an upper bound you will never hit, and that is fine — this knob is a ceiling, not a reservation, so oversizing costs memory and nothing else. The arithmetic for the tenant box: 300 max connections, a dozen tables touched per request, round-robin across 50,000 tables. The worst case was only 3,600 simultaneous handles, but the churn-free cache needed to hold the tables touched within an eviction window — we landed on 20,000 as the point where the Opened_tables rate collapsed, with table_definition_cache raised to match. Before applying it, check the descriptor budget. mysqld reports what it got in open_files_limit; the value comes from the OS — ulimit -n for a hand-started server, LimitNOFILE in the systemd unit for a packaged one — and if the OS caps you below what table_open_cache implies, the server clamps or warns at startup and your tuning silently does not take hold. We raised LimitNOFILE to 400,000 in the unit file, reloaded systemd, restarted, and only then moved the cache variables. The honest cost sheet: each cached handle costs some memory and one descriptor, so 20,000 is tens of megabytes and 20,000 descriptors held open — trivial on a modern box, but you must actually grant them. And a bigger cache does nothing for the first touch of a cold table; it only stops the re-opening.
What does table_definition_cache do, and when does it matter?
It caches table definitions — the dictionary metadata the server needs before it can even build a handle — separately from the handles themselves. Before 8.0 this was the .frm parsing cache; in 8.0, with the transactional data dictionary, it is the cache of dictionary objects the server has materialized. The default is autosized from table_open_cache with a floor of 400, and for ordinary schemas you never think about it. At 50,000 tables it becomes the second half of the same treadmill: even when a handle open was unavoidable, the definition lookup underneath it was also missing, adding dictionary reads to every table open. Symptoms overlap almost perfectly with table-open churn — the same climbing Opened_tables, the same CPU — so I size them together: table_definition_cache at least as large as the number of distinct tables in the hot working set, which for us meant matching it to the 20,000 we chose for handles, knowing the full 50,000 would never stay warm. It is cheap insurance — mostly memory — and undersizing it on a many-table schema quietly taxes every cache miss with a second miss.
What does tuning buy you, and what does it not fix?
It buys you a quiet server and time; it does not buy you a sound design, and you should budget the migration it is buying time for. After the change — table_open_cache and table_definition_cache at 20,000, LimitNOFILE raised, restart done off-peak — Opened_tables went from 3,800 per second to under 50, CPU at the same traffic fell from about 70 percent to about 25 percent, and p99 latency on tenant queries dropped by a third even though not one query plan changed. That is a real win and I would do it again. But here is the cost sheet nobody puts in the tuning guide: backups still had to walk 50,000 tables, DDL still meant touching a schema per tenant, information_schema queries still crawled, upgrades still feared the dictionary, and every new tenant made the treadmill longer. Cache tuning turned an emergency into a chronic condition, and chronic conditions still need treatment. The treatment, which the team shipped over the following two quarters, was consolidating tenants into shared tables keyed by tenant_id — the classic fix for the one-table-per-tenant shape — which took the table count from 50,000 to under a hundred and made the cache question disappear entirely. My line after living both sides: if your Opened_tables rate is high because your schema is the working set, tune the cache this week and schedule the schema fix this quarter. Only doing the first is how the second never happens.
Where MonPG stands on MySQL
I build MonPG, so plainly: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this piece — the Opened_tables rate next to Open_tables occupancy, Table_open_cache_overflows trending against your configured size, descriptor headroom under open_files_limit — are exactly the kind of thing the MySQL work is designed to surface as timelines instead of support tickets. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first approach runs on the PostgreSQL side, and the rest of these MySQL field notes live on the blog.