Skip to content

When MySQL temp tables spill to disk on cPanel — reading the symptom, fixing the cause

Created_tmp_disk_tables climbing means queries are exceeding the in-memory temp table size. The right fix is sometimes a config knob; more often it's an index or a query rewrite.

A cPanel customer reports the site is slower than usual under peak load. SHOW PROCESSLIST shows queries in Copying to tmp table on disk. The MySQL status counter Created_tmp_disk_tables is climbing by 100 per minute. Each disk-based temp table is 10–100x slower than the same operation in memory. The instinct is to raise tmp_table_size, which works for some cases and just delays the problem in others — the real fix usually involves understanding why the temp tables are so big in the first place.

What’s happening

MySQL creates internal temp tables for sorts, joins, group-by, derived tables (subqueries in FROM), and UNION operations. Small temp tables stay in MEMORY engine (capped by tmp_table_size and max_heap_table_size — the smaller of the two wins). When the temp table exceeds the cap, MySQL converts it to disk-based (MyISAM or InnoDB on disk depending on version) and continues. Disk-based is slow.

Three things cause temp table spill:

  1. tmp_table_size is too small for legitimate workload. A 16MB default can easily be exceeded by a JOIN producing intermediate results larger than that.
  2. The query is doing more work than it should. A JOIN with a missing index produces a Cartesian-product temp table; an ORDER BY without an index sorts everything on disk.
  3. A column type forces disk even on small temp tables. MEMORY engine doesn’t support TEXT/BLOB; if the query references a TEXT column, MySQL skips MEMORY and goes straight to disk regardless of size.

How to verify

mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp_tables'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'"
mysql -e "SHOW VARIABLES LIKE 'tmp_table_size'"
mysql -e "SHOW VARIABLES LIKE 'max_heap_table_size'"
mysql -e "SHOW FULL PROCESSLIST" | grep -i tmp

The ratio Created_tmp_disk_tables / Created_tmp_tables is the percentage of temp tables that spilled to disk. Below 5% is normal. Above 25% means something is wrong.

The fix

  1. Identify which queries are spilling. Run with the slow query log enabled at a low threshold (separate article), then pt-query-digest --filter '$event->{Tmp_table_on_disk}' to filter to disk-spilling queries.
  2. For each top spiller, run EXPLAIN:
    EXPLAIN SELECT ... FROM big_table JOIN other_table ...
    Look at the Extra column. Using temporary; Using filesort means both a temp table and a sort — usually fixable with an index that covers both the JOIN key and the ORDER BY.
  3. For queries that include TEXT/BLOB columns, see if you can rewrite to not include them in the intermediate result. A common pattern: select only the IDs in the subquery, then JOIN back to the table for the TEXT in the outer query:
    -- bad: TEXT in derived table forces disk temp
    SELECT t.id, t.content FROM (
      SELECT id, content FROM posts WHERE published=1 ORDER BY date
    ) t LIMIT 20;
    
    -- good: derived table holds only IDs
    SELECT t.id, p.content FROM (
      SELECT id FROM posts WHERE published=1 ORDER BY date
    ) t JOIN posts p ON t.id = p.id LIMIT 20;
  4. Raise tmp_table_size and max_heap_table_size when the workload legitimately needs more:
    /etc/my.cnf.d/cpanel.cnf:
    [mysqld]
    tmp_table_size = 64M
    max_heap_table_size = 64M
    Both must match — MySQL uses the smaller. Higher means more memory per connection — multiply by max_connections for the worst case.
  5. For one-off heavy queries that you can’t rewrite quickly, set per-session:
    SET SESSION tmp_table_size = 256 * 1024 * 1024;
    SET SESSION max_heap_table_size = 256 * 1024 * 1024;
    -- your big query here
    Reverts when the connection closes. Doesn’t impact other connections.
  6. For MySQL 5.7+ / MariaDB 10.2+, the internal temp table engine can be set to InnoDB instead of MEMORY:
    internal_tmp_disk_storage_engine = InnoDB
    InnoDB temp tables can be faster for certain workloads. Test on staging first.
  7. Watch the metric over time: Created_tmp_disk_tables rate should drop after fixes. Below 5% of Created_tmp_tables is the target.

For environments where temp table spill is recurring, Stack Harbor’s environment management team correlates the spill rate against application queries and proposes the index and query changes that close the loop without inflating memory.