MariaDBBench Temp Table Guide: Symmetric tmp_table_size, max_heap_table_size & TEXT/BLOB Spills

MariaDBBench Diagnostic SQL & Configuration Snippet (tmp_table_size & Disk Spills)

Run this read-only inspection snippet in your mariadb client to verify live counters against official storage-engine thresholds before editing 50-server.cnf.

-- MariaDBBench check: Calculate ratio of internal temporary tables created on disk
SELECT
  SUM( IF(VARIABLE_NAME = 'Created_tmp_tables', VARIABLE_VALUE, 0) ) AS total_tmp_tables,
  SUM( IF(VARIABLE_NAME = 'Created_tmp_disk_tables', VARIABLE_VALUE, 0) ) AS disk_tmp_tables,
  ROUND(
    SUM( IF(VARIABLE_NAME = 'Created_tmp_disk_tables', VARIABLE_VALUE, 0) ) * 100.0 /
    GREATEST(1, SUM( IF(VARIABLE_NAME = 'Created_tmp_tables', VARIABLE_VALUE, 0) )), 2
  ) AS disk_spill_pct
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME IN ('Created_tmp_tables', 'Created_tmp_disk_tables');

The Symmetric MIN(tmp_table_size, max_heap_table_size) Rule in MariaDB & MySQL

Official MariaDB and MySQL documentation specifies that internal in-memory temporary tables are bounded by the smaller of tmp_table_size and max_heap_table_size. Raising tmp_table_size to 128M while leaving max_heap_table_size at its 16M default silently caps every in-memory temp table at 16 MB. That is why our MariaDBBench 50-server.cnf generator always emits identical values for both directives.

Why Including TEXT or BLOB Columns in SELECT * Bypasses In-Memory Temp Tables

MariaDB’s in-memory MEMORY storage engine for implicit temporary tables does not support BLOB or TEXT column types (such as WordPress post_content or wp_postmeta.meta_value). Whenever a GROUP BY, DISTINCT, or ORDER BY temp-table query projects a TEXT column, MariaDB skips RAM temp allocation immediately and writes an on-disk Aria temporary table even if the result has only 20 rows. Rewriting queries to group scalar IDs first and join TEXT columns afterward restores in-memory execution.

Keeping Per-Query Temp Buffers Safe Under High max_connections

Because temporary table limits apply per query execution rather than globally across the server, setting 1G or 2G invites instant OOM crashes during reporting bursts; we recommend keeping symmetric values between 64M and 192M and pointing tmpdir to local PCIe 4.0 NVMe.

MariaDBBench Compatible NVMe Host Tiers for tmp_table_size & Disk Spills (2026-11-12 Public Rates)

Published root 50-server.cnf access specs and introductory rates retrieved 2026-11-12; promotional and renewal prices may change, so confirm terms on vendor checkout.

Related MariaDBBench InnoDB Parameter Guides (GUIDE-08)