The alert fired at 2:40 in the morning on a Tuesday. A multi-tenant events platform — one MariaDB 10.11 primary, one replica, an events table with 480 million rows — had a single read query that had run in about 40 milliseconds for months and was now taking nine seconds. Nothing had been deployed. No schema change, no config change, no traffic spike. What had changed was the data: over the previous six weeks, one enterprise tenant, tenant 7, had grown to own roughly 60% of every row in the table, and the optimizer was still planning queries as if every tenant owned an equal slice. For that tenant it estimated a few thousand matching rows, chose a tidy index range scan, and then performed tens of millions of random page fetches. Every small tenant on the same query was fine. The big one was melting.
The fix was not an index and it was not a hint. It was teaching the optimizer that the data was skewed, using a MariaDB feature that had been sitting in the server the whole time: engine-independent histogram statistics. This is what I learned turning that feature on — where the statistics live, how collection actually works, which knobs matter, and the cases where the histogram itself becomes the problem.
Why did the optimizer get the row estimates so wrong?
Because its default model of the world is uniformity. When MariaDB plans a query with a condition on a non-indexed or partially indexed column, and it has no better information, it falls back to averages: the number of distinct values divided into the row count. With three thousand active tenants and 480 million rows, the average tenant owns 160,000 rows. An equality condition on tenant_id therefore "matches about 160,000 rows" no matter which tenant you ask about. For 2,999 tenants that estimate is close enough. For tenant 7, holding 288 million rows, it is wrong by three orders of magnitude — and the plan chosen for 160,000 estimated rows (an index scan with per-row lookups) is a catastrophe at 288 million actual rows, where a sequential scan would win easily.
This is the classic skew failure, and it is not a MariaDB defect — every cost-based optimizer inherits it. What differs between databases is what you can hand the optimizer to correct the assumption. MySQL 8.0 added histograms you collect per column. PostgreSQL has always kept most-common-value lists and histogram bounds in pg_statistic, maintained by autovacuum's ANALYZE side. MariaDB's answer predates MySQL's: engine-independent statistics, shipped in MariaDB 10.0, stored in plain tables in the mysql system database and available for any storage engine, not just InnoDB.
Where does MariaDB keep histogram statistics?
In three ordinary tables in the mysql database: table_stats, column_stats, and index_stats. The column_stats table is the one that matters here. For every column you have collected statistics on, it stores the minimum and maximum values, the null ratio, the average value length, the average frequency (that uniformity estimate above), and — when you ask for them — a histogram: its size, its type, and the bucket data itself in a binary column. Because these are real tables, you can inspect the optimizer's planning inputs with plain SELECT.
The catch is that the feature is off by default. The use_stat_tables system variable controls whether the optimizer reads these tables at all; out of the box it is set to never use them, so statistics you collect sit there doing nothing. In current releases the variable accepts escalating levels — complementary, which blends the collected statistics with the engine's own estimates, up to always, which trusts them outright. The conservative move, and the one I made that night, is complementary: let the histogram correct the estimates where it has data and leave everything else alone. One more property worth knowing: unlike InnoDB's transient statistics, which the server can silently recalculate and which evaporate on restart unless made persistent, these statistics live in tables. They survive restarts, they replicate, and nothing touches them until you do.
How do you collect histograms in MariaDB?
With a form of ANALYZE TABLE that people arriving from MySQL do not expect. A plain ANALYZE TABLE on MariaDB updates the storage engine's own statistics — it does not populate column_stats, and no histogram appears. The engine-independent statistics have their own syntax:
-- collect persistent statistics, with a histogram, for the skewed column
ANALYZE TABLE events
PERSISTENT FOR COLUMNS (tenant_id) INDEXES ();
-- make the optimizer actually read what you collected
SET GLOBAL use_stat_tables = 'complementary';
-- see what landed in the system table
SELECT column_name, hist_size, hist_type, min_value, max_value,
nulls_ratio, avg_frequency
FROM mysql.column_stats
WHERE db_name = 'app' AND table_name = 'events';The PERSISTENT keyword is the whole point: the statistics are written into the mysql.* tables and stay there until you recollect or drop them, rather than being recalculated on some schedule you do not control. You name the columns that carry skew — collecting histograms on every column of every table is wasted work, since the collection pass has to read the data it describes. On that 480-million-row table the collection took about eleven minutes of read-heavy I/O at 3am, which I judged cheap against nine-second queries at 9am. Two knobs shape the histogram itself. histogram_size sets the number of buckets, up to 255, defaulting to 254. And histogram_type chooses SINGLE_PREC_HB or DOUBLE_PREC_HB — height-balanced histograms stored in single or double precision. Height-balanced means each bucket holds roughly the same number of rows, so the bucket boundaries migrate to where the data is dense; that is exactly what you want for skewed values. The single-precision default is smaller and is what most systems should run; double precision buys finer bucket-boundary resolution on columns where single precision rounds away the skew you are trying to capture. I have never needed it, but I check its existence the way you check a fire exit.
One contrast for the MySQL-minded reader: there is no separate UPDATE HISTOGRAM statement and no sample-size clause here — PERSISTENT FOR is the collection mechanism, and the same form with a different column list is how you refresh it. What you must not do is assume that the ANALYZE TABLE your maintenance cron already runs is doing any of this — it is not.
How does the optimizer actually use the histogram?
To replace the uniformity assumption with a measured distribution when it estimates selectivity. When the optimizer evaluates a range condition on a column with a histogram — created_at greater than some timestamp, tenant_id within some set, an amount between two bounds — it walks the buckets and sums the fraction of rows the condition covers instead of assuming values are evenly spread. That fraction is what feeds the cost model, and the cost model is what picks the index. In the incident, the histogram on tenant_id made visible what the average had hidden: one value occupying 60% of the buckets' worth of rows. The estimate for tenant 7's query jumped from 160,000 rows to a number in the hundreds of millions, the index range scan stopped looking cheap, and the plan flipped to a scan that completed in under a second. The other 2,999 tenants kept their estimates and their plans. That asymmetry — fix the outlier without disturbing the majority — is the entire value proposition.
Whether the optimizer trusts these statistics, and how much, is governed by optimizer_use_condition_selectivity, a variable that controls how aggressively condition selectivity is used in plan costing. Older releases exposed the same idea as the use_cond_selectivity flag inside optimizer_switch; current releases have moved it to the dedicated variable, whose default in modern 10.x releases already enables the useful levels. If you are on an older release and histograms appear to change nothing in EXPLAIN, that variable is the first place to look — statistics collected but never consulted are a silent no-op. Verify with EXPLAIN before and after collection: the rows column for the skewed predicate should move by orders of magnitude, and if it does not, the statistics are not being read. When I need to see which other queries share the same pattern, the sys schema's statement digests are the fastest inventory — the same workflow I described in the sys schema field notes, and the queries translate directly to MariaDB.
When do histograms mislead?
Most often when they are stale, and staleness has a specific shape: bulk loads. The histogram is a photograph. If you load 200 million rows after the picture was taken — a backfill, a migration, a new tenant onboarding — the optimizer plans against a distribution that no longer exists, and it will do so confidently. Our post-incident review found the near-miss: the nightly import job that had been growing tenant 7 for weeks ran after nothing recollected statistics, so every plan during the growth period was compiled against last month's histogram. The fix was procedural, not technical: any job that changes a table's row count by a meaningful fraction ends with a targeted ANALYZE TABLE ... PERSISTENT FOR on the skewed columns. Histograms do not refresh themselves; the persistence that makes them predictable also makes them rot.
Two quieter failure modes deserve mention. First, bucket resolution: 254 buckets over hundreds of millions of rows means each bucket still spans a wide value range, and skew that lives inside a single bucket is invisible. Raising histogram_size helps only up to its 255 ceiling; past that, the honest answer is that some distributions need an application-level workaround, like a generated column that splits the hot value out. Second, correlation: the histogram describes one column at a time. A condition on tenant_id and created_at together is estimated as two independent selectivities multiplied, and if the big tenant's rows are also the newest rows — as ours were — the joint estimate is still wrong even with perfect per-column histograms. Recognizing that pattern is what separates a monitoring dashboard from a guess, and it is the same class of reasoning behind the deadlock postmortem workflow: the counters tell you what, the distribution tells you why.
The cost sheet, honestly stated: collection reads the table, so schedule it; storage in mysql.column_stats is negligible; the optimizer risk is bounded because you chose which columns carry statistics; and the operational debt is one recollection step appended to every bulk-load runbook. Against nine-second queries caused by an invisible tenant, that is the cheapest trade I have made all year.
Where MonPG fits
The signals that would have caught this before the 2:40 page are trend lines, not thresholds: per-query latency drifting upward week over week for one plan shape, EXPLAIN row estimates diverging from actuals, and table cardinality growing faster than the statistics' last-collected time. Full disclosure, as in every article of this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and query-plan regression is high on the list of signals it is being built around: statement latency trends, row-estimate drift, and statistics staleness surfaced before the optimizer makes the expensive choice. Until that ships, the column_stats queries and scheduled recollection above are your early-warning kit. And if your fleet also runs PostgreSQL, that monitoring is live today — see the PostgreSQL overview, or browse the field notes on the blog.