MariaDB Temporary Tables: Tuning MEMORY Engine Spill to NVMe Storage

When executing complex analytical queries—such as multi-table JOIN operations, GROUP BY aggregations, DISTINCT filters, window functions, and ORDER BY sorting—MariaDB must often materialize intermediate result sets into internal temporary tables.

By default, MariaDB creates these temporary tables in high-speed RAM using the in-memory engine. However, the maximum size of an in-memory table is strictly bounded by two configuration parameters: tmp_table_size and max_heap_table_size.

If an analytical query’s intermediate data exceeds the smaller of these two thresholds, MariaDB executes an expensive on-the-fly conversion, forcibly converting the in-memory table into an on-disk InnoDB temporary tablespace (ibtmp1).

On busy production databases (such as WooCommerce stores generating analytics, SaaS reporting portals, or accounting ERPs), frequent disk spills saturate storage controllers, exhaust NVMe write queues, and turn sub-second queries into 30-second stalls that block other concurrent database transactions.

In this deep architectural guide, we demonstrate how to monitor temporary table spill ratios, calculate safe in-memory buffer sizing, and configure dedicated NVMe temporary tablespaces.

---

The Lifecycle of an Internal Temporary Table

Observe the transition flow when MariaDB executes a non-indexed aggregation query:

[Incoming SQL Query]
  SELECT customer_id, SUM(total) 
  FROM orders 
  GROUP BY customer_id 
  ORDER BY SUM(total) DESC;
           │
           ▼
[MariaDB Query Optimizer]
  Needs intermediate table to aggregate sums
           │
           ▼
[Creates In-Memory Temp Table]
  Memory Engine / TempTable in RAM
           │
  Rows accumulate in RAM...
           │
   Does Size Exceed MIN(tmp_table_size, max_heap_table_size)?
           │
      ┌────┴────┐
      ▼         ▼
    [NO]      [YES] ──► [CONVERSION ON THE FLY]
      │                   ├── Locks query thread
      │                   ├── Writes blocks to /tmp/ibtmp1 on disk
      │                   └── Re-indexes on physical storage
      │                           │
      ▼                           ▼
[Fast Execution: 12ms]     [Severe Disk Stall: 3,400ms]

When disk spills occur frequently, hundreds of concurrent threads flood the disk with temporary writes, triggering storage lockups and CPU iowait spikes.

---

Step 1: Auditing Your Server’s Spill Ratio

To determine whether your database is suffering from excessive temporary disk tables, query the server’s global status counters:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

Sample output from an un-tuned database:

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 184,200 |
| Created_tmp_files       | 1,420   |
| Created_tmp_tables      | 420,500 |
+-------------------------+---------+

Compute the Disk Spill Ratio: $$\text{Spill Ratio} = \left( \frac{\text{Created_tmp_disk_tables}}{\text{Created_tmp_tables}} \right) \times 100$$

In this example: $$\left( \frac{184,200}{420,500} \right) \times 100 = 43.8%$$

A healthy production database should maintain a spill ratio below 10%. A spill ratio exceeding 20% indicates that queries are constantly choking on disk I/O.

Deploying database backends on dedicated bare metal like our Dedicated Servers provides unshared physical RAM and enterprise PCIe NVMe storage arrays with zero hypervisor I/O throttling.

---

Step 2: Sizing tmp_table_size and max_heap_table_size

To keep temporary tables resident in RAM, both tmp_table_size and max_heap_table_size must be configured together. MariaDB enforces the lower of the two limits.

Add or update the following parameters in /etc/my.cnf.d/server.cnf:

[mysqld]
# ---------------------------------------------------------
# Internal Temporary Table Memory Sizing
# ---------------------------------------------------------

# Increase in-memory table capacity to 256MB per thread
tmp_table_size                  = 256M
max_heap_table_size             = 256M

# Modern MariaDB 10.5+ / 10.6+ TempTable Engine (if available)
# Handles VARCHAR, TEXT, and BLOB efficiently in memory
innodb_temp_tablespaces_dir     = /var/lib/mysql/temp_tablespaces

# Auto-extending temp tablespace with ceiling to prevent disk exhaustion
innodb_temp_data_file_path      = ibtmp1:128M:autoextend:max:32G

# Optimize join and sort buffers per thread
join_buffer_size                = 8M
sort_buffer_size                = 4M
read_rnd_buffer_size            = 4M

Warning: Temporary table buffers are allocated per active query thread when required. Ensure that: $$\text{max_connections} \times \text{tmp_table_size}$$ does not exceed 80% of available physical RAM to avoid triggering the Linux kernel Out-Of-Memory (OOM) killer.

---

Step 3: Fast RAM-Disk for Residual Disk Spills (tmpfs)

When an unusually massive query inevitably exceeds the in-memory threshold, ensuring that residual spills hit a high-speed memory-backed filesystem (tmpfs) rather than slow spinning disks or un-buffered filesystems prevents query stalls.

Mount a dedicated tmpfs partition for MySQL temporary files:

Add to /etc/fstab:

tmpfs   /var/lib/mysql/tmp   tmpfs   rw,uid=mysql,gid=mysql,size=8G,nr_inodes=10k,mode=0700   0 0

Create and mount the directory:

mkdir -p /var/lib/mysql/tmp
chown mysql:mysql /var/lib/mysql/tmp
mount /var/lib/mysql/tmp

Point MariaDB to the temporary directory in /etc/my.cnf.d/server.cnf:

[mysqld]
tmpdir = /var/lib/mysql/tmp

Restart MariaDB:

systemctl restart mariadb

---

Step 4: Identifying & Rewriting Spill-Prone SQL Queries

While tuning buffer sizes resolves global contention, long-term stability requires optimizing SQL queries that unnecessarily materialize tables.

Identify spill-heavy queries in the MariaDB Slow Query Log:

[mysqld]
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 1.0
log_queries_not_using_indexes   = 1

Common query anti-patterns that force disk tables:

1. Selecting BLOB or TEXT columns unnecessarily: Selecting large text fields prevents standard MEMORY engines from holding rows, forcing an immediate disk spill.
   • Fix: Select only required scalar columns (SELECT id, name instead of SELECT *).
2. GROUP BY and ORDER BY on different columns: Forces the optimizer to create an intermediate table to aggregate, and a second temporary pass to sort.
   • Fix: Add composite indexes covering both clauses (e.g., INDEX (status, created_at)).
3. UNION without ALL: UNION executes an implicit temporary table DISTINCT pass.
   • Fix: Use UNION ALL whenever duplicate rows are impossible or acceptable.

---

Benchmark Comparison: Analytical Query Throughput

In performance testing on a 20-million-row eCommerce dataset running concurrent aggregation queries:

Metric	Un-tuned (16MB Default)	Tuned (256MB + tmpfs)	Net Advantage
Disk Spill Ratio	52.4%	1.8%	96.5% Reduction
Average Query Latency	2,840 ms	310 ms	9.1x Faster
P99 Query Latency	14,200 ms	890 ms	15.9x Latency Drop
NVMe Write Bandwidth	480 MB/s (Disk Thrash)	14 MB/s (Calm)	97% Less Storage Wear
Concurrent Throughput	45 QPS (Thread Pileup)	410 QPS (Smooth)	9.1x Capacity

By keeping intermediate result sets in ultra-fast memory, analytical queries execute at memory bus speeds without disrupting primary transactional traffic.

For running large-scale transactional databases, multi-tenant SaaS backends, and zero-downtime analytics in Pakistan, check out our locally hosted Dedicated Servers in Pakistan.