The latency spikes had a perfectly regular heartbeat: every thirty seconds, a blip of two to four hundred milliseconds across random queries on a 5.7 primary, all day, every day, invisible in averages and obvious in p99. The slow log showed nothing at those timestamps because no individual query was slow — everything was just briefly waiting. The culprit turned out to be the monitoring agent I had installed myself: it polled information_schema.TABLES for per-table sizes and row estimates every thirty seconds, on an instance hosting 40,000 tables across multi-tenant schemas. Each poll made InnoDB recompute table statistics, open table definitions by the thousand, and hold dictionary-side mutexes that ordinary queries kept bumping into. The observer was disturbing the observed, at a thirty-second interval, forever. Metadata in MySQL feels free because SELECTs against information_schema return instantly on a dev box with fifty tables. On a real instance, some of those reads are among the most expensive statements running on your server, and almost nobody has them in their slow query digest.
Why is reading metadata not free?
Because parts of information_schema are not tables — they are views computed on demand from live engine state, and asking a question can trigger the work needed to answer it. The notorious case in 5.7 is statistics. With innodb_stats_on_metadata at its default of ON, any read of table statistics through information_schema — TABLES, STATISTICS, and friends — causes InnoDB to refresh its cardinality estimates for the tables you touched, and a refresh means index dives: random reads into index pages to re-estimate cardinalities. On a forty-thousand-table instance, my monitoring poll was kicking off statistics refreshes across every table it reported on, thirty seconds apart, around the clock. Statistics refreshes do not just cost I/O; they can change query plans mid-flight for statements optimized against the old estimates, and they serialize against parts of the dictionary. The second cost is table opening: metadata reads that enumerate tables force open table definitions and file handles, and on instances with more tables than table cache entries, each poll evicts and re-opens in a churn that shows up as mutex contention — the same machinery the table_open_cache notes cover from the sizing side. The third cost is that in 5.7, big unfiltered information_schema scans materialize through internal temporary tables, which adds disk I/O to a query that looks like a catalog read.
What changed in MySQL 8.0, and what did not?
8.0 rebuilt information_schema on top of the transactional data dictionary, and the pathological cases got dramatically better — catalog reads no longer open table files wholesale, and the old temp-table materialization tricks are gone. What survived is the statistics problem, in a new costume. Statistics in 8.0 are cached, and information_schema_stats_expiry controls how long a cached statistic is considered fresh: the default is 86400 seconds, one day. Read TABLES twice in a day and the second read is cheap; read it after expiry and InnoDB recalculates on demand, synchronously, inside your query. Set the expiry to zero and every single metadata read triggers fresh statistics — which some "we need live numbers" dashboards effectively do. So the 8.0 version of my incident is subtler: a monitoring tool reading unfiltered table metadata slightly after each expiry boundary, plus a statistics recalculation triggered for every table it touched, landing on top of whatever the optimizer is doing with those same tables. The fix surface is the same idea as 5.7 with different knobs: stop recalculating statistics as a side effect of reading metadata.
-- 5.7: stop metadata reads from triggering stats refreshes
-- (persistent stats should already be on; this is the metadata trigger)
SET GLOBAL innodb_stats_on_metadata = OFF;
-- 8.0: pin the stats cache long enough that reads stay reads
SET GLOBAL information_schema_stats_expiry = 604800; -- one week
-- find who is actually running expensive catalog queries right now
SELECT ID, USER, HOST, TIME, INFO
FROM information_schema.PROCESSLIST
WHERE INFO LIKE '%information_schema%'
ORDER BY TIME DESC;
-- measure what your monitoring agent really costs: watch opened tables
-- and dictionary-side stalls around each poll interval
SHOW GLOBAL STATUS LIKE 'Opened_tables';
SHOW GLOBAL STATUS LIKE 'Opened_table_definitions';Which metadata reads are actually dangerous at scale?
Unfiltered enumeration is the enemy; point lookups against the dictionary are nearly always fine. The dangerous shapes, from my own incident history. Anything joining information_schema.TABLES to itself or aggregating across all schemas without a TABLE_SCHEMA predicate — the classic "give me all databases with sizes" dashboard query — is work proportional to total table count, executed at poll frequency. INFORMATION_SCHEMA.INNODB_SYS_* and the 8.0 INNODB_* dictionary views on instances with heavy DDL churn can serialize against the operations they describe. Reads of INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS on a server with thousands of concurrent transactions are usually fine individually but get expensive when a monitoring agent snapshots them every ten seconds on a box already at the edge; the low-overhead performance_schema setup covers the sibling problem of instrumentation budgets. And a special mention for ORMs and schema-migration tools that introspect the full catalog at application startup: forty app instances booting together after a deploy can stampede the dictionary hard enough to look like an outage. That one does not appear in any poll interval — it appears in your deploy timeline.
How do you find the offenders, and what do you replace them with?
The smoking gun is correlation, and the tool is the digest you probably already collect. Enable the statements digest in performance_schema (or mine the slow log with a digest tool as in the slow log digest workflow) and look for information_schema queries ranked by total time, not by average — the per-execution cost is small enough to hide from average-based sorting while the aggregate is enormous. In my case the agent's poll was 1.8 seconds per execution, 2,880 executions a day, 87 minutes of dictionary work daily on a production primary. Then check what the poll is for, because the fix is usually substitution, not deletion. Table sizes for capacity trending can come from a once-a-night information_schema read into a metrics pipeline — daily granularity loses nothing for disk forecasting. Row-count estimates for dashboards can come from cached statistics with the expiry pinned, as above. Anything that needs live transactional state should read the specific INNODB_* view it needs with tight filters, not enumerate the catalog to find one table. And per-tenant accounting queries can usually be pushed to a replica: metadata reads on a replica cost the replica, not the primary. The sys schema daily-driver queries are worth a look here too — several of them wrap exactly the expensive views with saner defaults, though they inherit the underlying cost if you run them in a loop.
What are the rules that keep metadata reads boring?
Three, and they are all cheap. First, statistics recalculation belongs to the optimizer's schedule, not to your monitoring schedule: innodb_stats_on_metadata=OFF on 5.7, a long information_schema_stats_expiry on 8.0, and explicit ANALYZE TABLE after bulk loads or schema changes when you actually want fresh estimates. Second, every automated consumer of information_schema gets a review like a schema change: what does it read, how often, filtered how, from which host — and the answer "everything, every thirty seconds, unfiltered, from the primary" gets rejected the way a missing WHERE clause would. Third, watch Opened_tables and Opened_table_definitions as leading indicators: a monitoring change that doubles their rate is degrading the server whether or not latency has moved yet, and catching it there beats catching it in p99. Metadata is the water you swim in as a DBA, and it is easy to forget it has viscosity. The day you graph your monitoring's cost against the latency it was hired to detect is the day the jokes about the observer effect stop being funny and start being configuration.
Where MonPG stands on MySQL
I build MonPG, so the honest line: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this piece — catalog-read cost attributed to its source, Opened_tables rates as leading indicators, statistics-expiry behavior correlated with plan changes — are exactly what the MySQL work is designed to surface, so the monitoring itself never becomes the unexplained blip in your p99. 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 today, and the rest of these MySQL field notes live on the blog.