MariaDB tmpdir Disk Full: Internal Temp Tables and the Report That Ate /var/tmp

Nothing about the report had changed: same SQL, same schedule, same parameters, month after month for two years. What had changed was the data, growing at the unremarkable pace of a healthy business, until one Monday it crossed a line that exists in no config file anyone had reviewed. The query’s GROUP BY result set stopped fitting within the memory ceiling MariaDB grants internal temporary tables, the engine silently converted the temp table from in-memory to on-disk, and the spill wrote 8 GB into /var/tmp — a small dedicated mount chosen years ago for exactly the wrong reason, because it was small — in four minutes flat. The report did not just fail. The disk filled, and because /var/tmp was where several other maintenance tools staged their scratch files, the failure rippled sideways into things that had nothing to do with the query. The postmortem title wrote itself: we were not running out of disk; we were running out of memory, and the disk was where we found out.

Internal temporary tables are the least visible I/O path in MariaDB: no table definition, no slow-log line until the query finishes, no warning at the boundary between memory and disk. These are the notes from instrumenting that boundary properly.

When does MariaDB actually write temp tables to disk?

MariaDB materializes an internal temporary table whenever a query needs a working set it cannot stream — GROUP BY without an index that matches the grouping order, UNION without ALL, some DISTINCT and ORDER BY patterns, subquery materialization — and that temp table starts life as an in-memory MEMORY-engine table. The ceiling is the smaller of tmp_table_size and max_heap_table_size, two variables you must raise together because the effective limit is the minimum and raising only one is the classic half-fix. Once the temp table would exceed that ceiling, MariaDB converts it to an on-disk table — Aria since 10.4, MyISAM before — and writes it into tmpdir as one of those anonymous #sql files that appear in disk-full alerts with no obvious owner. BLOB and TEXT columns in the temp table force the disk path regardless of size, because the MEMORY engine will not hold them. The status counters that tell you the story:

-- the two ceilings: the effective limit is the SMALLER one
SELECT @@tmp_table_size, @@max_heap_table_size, @@tmpdir;

-- the spill ratio, computed over a representative week:
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_files';
-- healthy OLTP: disk tables under ~10% of tmp tables;
-- our reporting replica was at 34% before the blowup

Created_tmp_disk_tables divided by Created_tmp_tables is the single number that would have paged us months early: it drifts upward as data grows, quietly, on exactly the trajectory that ended in a full disk. A ratio under 10% on an OLTP primary is comfortable; 34%, which is where our reporting replica sat, means a third of your temp tables are doing disk I/O and the only questions are when the biggest one exceeds the mount and which Tuesday it picks. Aria’s involvement since 10.4 is mostly good news — the crash-recovery story is better than MyISAM’s — and the Aria versus InnoDB notes cover the engine’s behavior in more depth, but no engine choice makes an 8 GB spill fast or small.

How do you find the query before the disk finds it?

The live catch is a processlist that knows what to look for: states like Creating tmp table, Copying to tmp table, and converting HEAP to Aria are the tells, and on a reporting workload they are worth a standing processlist snapshot precisely because the slow log stays silent until the query completes — and a query that fills the disk never completes. The preventive pass is EXPLAIN on every scheduled report query, hunting for Using temporary and Using filesort in the Extra column, then reasoning about whether the working set can outgrow the memory ceiling as the underlying tables grow. Filesort is the second spill path: sorts that exceed sort_buffer_size write merge files into the same tmpdir, so a mount sized for temp tables alone can still be eaten by a large ORDER BY:

-- the live catch: who is spilling right now?
SELECT ID, USER, TIME, STATE, INFO
FROM information_schema.PROCESSLIST
WHERE STATE LIKE '%tmp table%'
   OR STATE LIKE '%converting%';

-- the preventive pass: does this report's plan spill?
EXPLAIN
SELECT account_id, SUM(amount)
FROM ledger_entries
WHERE booked_at >= '2026-07-01'
GROUP BY account_id;
-- look for: Using temporary; Using filesort in Extra

When EXPLAIN flags the pattern, the fixes in order of preference: an index that matches the GROUP BY or ORDER BY so no temp table is needed at all; a query rewrite that pre-aggregates or filters before grouping; then, and only then, a bigger memory ceiling for the sessions that legitimately need it. The plan-reading discipline from the ANALYZE FORMAT=JSON notes applies directly here, because ANALYZE shows the temp table actually being created with real row counts rather than the planner’s optimism.

How do you contain the blast radius when a spill is legitimate?

Some temp tables are legitimately huge — year-end rollups, one-off migrations, analytics that genuinely need a multi-gigabyte working set — and for those the answer is not prevention but containment. Three structural changes came out of our incident. First, tmpdir moved to a dedicated mount sized for the worst legitimate spill times two, so a runaway temp table fills its own scratch space and kills one query instead of sidelining unrelated tooling; the error a query gets when tmpdir fills is fatal to that statement and survivable by the server, which is exactly the failure shape you want. Second, tmpdir accepts a colon-separated list of paths used round-robin, which both spreads I/O and bounds the damage of any single mount. Third, the memory ceiling became session-scoped policy: global tmp_table_size stays modest at 64 MB, and the reporting role raises it for its own sessions, so interactive traffic cannot accidentally allocate its way into a spill and the report that needs 2 GB of temp space asks for it explicitly:

-- session-scoped headroom for the jobs that need it
SET SESSION tmp_table_size = 2147483648;
SET SESSION max_heap_table_size = 2147483648;
-- both, always: the effective ceiling is the minimum

-- my.cnf: dedicated, bounded, spread scratch space
-- [mariadb]
-- tmpdir = /scratch/mysqltmp1:/scratch/mysqltmp2

One neighboring warning, because it bites in the same week you start caring about this: DDL and maintenance operations have their own scratch-file stories, and a COPY-algorithm ALTER on a large table is the other classic way to fill a mount nobody was watching — the algorithm taxonomy in the ALTER TABLE notes is the companion read before your next schema change.

Where MonPG fits

For MonPG product capabilities and setup information, see the MariaDB monitoring page. Use the diagnostics in this article to identify the measurements and operational checks your deployment needs.

Related documentation

• MariaDB: Aria storage engine