MySQL Created_tmp_disk_tables: temp tables spilling to disk

Queries that GROUP BY, ORDER BY, DISTINCT, or materialize derived results build internal temporary tables. When those tables exceed memory limits, MySQL spills them to disk. The status counter Created_tmp_disk_tables tracks this, and its ratio to Created_tmp_tables is the signal to watch during a latency incident.

A low ratio is normal. A high ratio means queries pay a random-disk penalty for intermediate results. You see that as rising query latency, increased disk I/O on the data directory, and connection pile-up. This guide covers how to read the ratio, find the offending patterns, and fix them without just adding RAM.

What this means

MySQL exposes two cumulative counters:

• Created_tmp_tables - every internal temporary table created.
• Created_tmp_disk_tables - those that spilled to disk.

The ratio to monitor is:

Created_tmp_disk_tables / Created_tmp_tables

Operational thresholds:

• < 10% - usually acceptable.
• > 25% - investigate. Queries are spilling frequently enough to hurt latency.
• > 50% - critical. Disk temp tables are the dominant path.

The memory limit that governs spill depends on the in-memory temp table engine:

• MEMORY engine - default in MySQL 5.7, or when internal_tmp_mem_storage_engine = MEMORY. Spill happens when the table exceeds MIN(tmp_table_size, max_heap_table_size). Changing only one variable is ineffective; the smaller value wins.
• TempTable engine - default in MySQL 8.0+. It stores VARCHAR/VARBINARY in memory, but BLOB/TEXT columns still force on-disk storage. temptable_max_ram controls the in-memory budget before TempTable overflows to disk.

On-disk internal temp tables use InnoDB and land in the session temporary tablespace (ibtmp1). tmpdir is only used for TempTable memory-mapped overflow or for external sort files. A full tmpdir is a separate failure mode from a high Created_tmp_disk_tables ratio; monitor both.

flowchart TD
    A[High disk temp ratio] --> B{BLOB/TEXT in GROUP BY?}
    B -->|Yes| C[Always on disk: rewrite query]
    B -->|No| D{Sort_merge_passes rising?}
    D -->|Yes| E[Filesort spill: add ORDER BY index]
    D -->|No| F{tmp_table_size / max_heap too low?}
    F -->|Yes| G[Raise memory limits]
    F -->|No| H[Capture query plans and optimize]

Common causes

Quick checks

Run these safe, read-only checks to characterize the spill.

-- Ratio and memory settings
SHOW GLOBAL STATUS WHERE Variable_name IN ('Created_tmp_tables', 'Created_tmp_disk_tables');
SHOW GLOBAL VARIABLES WHERE Variable_name IN ('tmp_table_size', 'max_heap_table_size', 'internal_tmp_mem_storage_engine');
SHOW GLOBAL VARIABLES LIKE 'temptable_max_ram';

-- Related plan-quality signals
SHOW GLOBAL STATUS WHERE Variable_name IN ('Sort_merge_passes', 'Select_full_join', 'Select_scan');

-- Worst offending digests (MySQL 8.0+)
SELECT DIGEST_TEXT,
       COUNT_STAR AS exec_count,
       SUM_CREATED_TMP_TABLES,
       SUM_CREATED_TMP_DISK_TABLES,
       ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_latency_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_DISK_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 10;

# ibtmp1 growth and tmpdir usage
ls -lh /var/lib/mysql/ibtmp1
df -h $(mysql -e "SHOW VARIABLES LIKE 'tmpdir';" | awk 'NR==2{print $2}')

How to diagnose it

1. Compute the ratio over a window. Do not trust a single sample. Take two readings 1-5 minutes apart and calculate delta(Created_tmp_disk_tables) / delta(Created_tmp_tables).
2. Confirm user impact. Correlate the ratio with Slow_queries rate, P99 latency, or Threads_running. A high ratio without latency impact is a planning ticket, not a page.
3. Find the queries. Query performance_schema.events_statements_summary_by_digest for digests with high SUM_CREATED_TMP_DISK_TABLES.
4. Inspect the plans. Run EXPLAIN (or EXPLAIN ANALYZE on MySQL 8.0.18+) and look for Using temporary and Using filesort.
5. Identify the engine. Check internal_tmp_mem_storage_engine. If it is TempTable, check whether the TempTable RAM budget (temptable_max_ram) is exhausted.
6. Check memory limits. For the MEMORY engine, compare the effective limit MIN(tmp_table_size, max_heap_table_size) against the estimated intermediate result size.
7. Check disk targets separately. Confirm whether growth is in ibtmp1 or tmpdir. They have different fixes.
8. Look for schema anti-patterns. Any BLOB/TEXT column in a GROUP BY or DISTINCT forces disk regardless of size.

Metrics and signals to monitor

Fixes

Rewrite queries that force on-disk temp tables

BLOB/TEXT columns in a GROUP BY, DISTINCT, or ORDER BY always produce on-disk temp tables. The fix is to remove the column from the grouping, group on a derived hash or surrogate key, or pre-aggregate in application code. You cannot tune past this with memory limits.

Add or fix indexes

If EXPLAIN shows Using temporary; Using filesort, try a covering index that matches the GROUP BY or ORDER BY columns in order. A well-ordered index lets MySQL stream rows without building a temp table. Fix Select_full_join by adding indexes on join columns.

Resize memory limits correctly

For the MEMORY engine, raise tmp_table_size and max_heap_table_size together and keep them equal. The effective limit is the minimum of the two. Do not set them arbitrarily large. Each connection can allocate up to the limit, so aggregate memory consumption is max_connections × limit.

For the TempTable engine in MySQL 8.0+, if temp tables spill because they exceed the TempTable RAM budget, raise temptable_max_ram. Monitor total system RAM; under high concurrency aggregate temp table memory can pressure the OOM killer.

Contain disk usage

ibtmp1 autoextends and does not shrink without a MySQL restart. Cap it in the configuration file and restart:

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

Warning: this requires a restart. A cap prevents runaway growth, but queries fail with “The table is full” when the limit is hit. Only use a cap if you have monitoring and an optimization plan in place. Keep tmpdir on a separate partition so that TempTable overflow or external sort files cannot fill the data volume.

Kill runaway queries as a temporary measure

If a specific report or batch job is generating huge temp tables and causing an outage, KILL QUERY <id> or KILL <thread_id> stops it. This is disruptive, but it gives immediate relief while you rewrite the query.

Prevention

• Set long_query_time to 1 second or lower and review the slow query log for patterns that correlate with temp table spikes.
• Require EXPLAIN review in staging for any new query that aggregates, sorts, or joins large tables.
• Keep tmp_table_size and max_heap_table_size equal and sized for your workload. Revisit them when data or concurrency grows.
• Monitor disk space on both the data directory (for ibtmp1) and tmpdir independently.
• Track per-digest temp table counters in performance_schema to catch query regressions before they cascade into saturation.

How Netdata helps

• Surfaces Created_tmp_disk_tables, Created_tmp_tables, and the computed disk-temp ratio.
• Correlates temp table spill with Sort_merge_passes, Select_full_join, and Threads_running on the same time axis so you can confirm the failure mode quickly.
• Alerts on ratio thresholds (> 25% investigate, > 50% critical) and on derivative spikes in Created_tmp_disk_tables.
• Shows per-second disk charts for the MySQL data directory and tmpdir, helping distinguish ibtmp1 growth from tmpdir exhaustion.
• Displays query latency percentiles from performance_schema digests alongside temp table counters to confirm user impact.

Related guides

• How MySQL actually works in production: a mental model for operators
• MySQL Aborted_connects and Aborted_clients climbing: diagnosis
• MySQL adaptive hash index latch contention: high CPU, low throughput
• MySQL InnoDB buffer pool hit ratio collapse: the cliff edge
• MySQL slow after restart: buffer pool warm-up and the cold cache
• MySQL innodb_buffer_pool_size tuning: 60-80% of RAM and when that breaks
• MySQL Innodb_buffer_pool_wait_free > 0: buffer pool memory pressure
• MySQL InnoDB checkpoint age: the redo log capacity signal nobody watches
• MySQL connection exhaustion: detection, diagnosis, and prevention
• MySQL innodb_deadlock_detect=OFF: when deadlock detection becomes the bottleneck
• MySQL ERROR 1213: Deadlock found when trying to get lock; try restarting transaction
• MySQL FLUSH TABLES WITH READ LOCK stall: backups that freeze the server