Tune MariaDB Temporary Tables and Sort Operations Safely

Effective MariaDB temporary table tuning is not a contest to make Created_tmp_disk_tables reach zero. Temporary tables and filesorts are normal execution tools for aggregation, deduplication, ordering, derived results, and some UNION queries. The operational problem begins when avoidable work spills to slow or undersized storage, many sessions allocate private buffers at once, or a reporting query fills tmpdir and harms unrelated traffic.

The tempting fix is to raise tmp_table_size, max_heap_table_size, and sort_buffer_size globally. That can make one benchmark faster while increasing the production server's worst-case memory exposure. It also hides missing indexes, excessive row width, and queries that sort far more data than they return.

This guide builds a measured workflow. It distinguishes explicit and internal temporary tables, captures interval-based counters, identifies responsible statements, improves access paths, budgets per-session memory, plans temporary storage, applies version-aware limits, and defines verification and rollback. Commands are designed for modern MariaDB releases, but every production change should first be checked against the exact version returned by SELECT VERSION().

Understand the four different resources

Several mechanisms are casually called “temporary space,” but they have different controls and failure modes.

Internal temporary tables

MariaDB can create an internal temporary table while executing a statement. Common triggers include grouping, duplicate elimination, materialized derived tables, and result processing for some ORDER BY, DISTINCT, and UNION plans. The optimizer decides whether one is needed; the application does not issue CREATE TEMPORARY TABLE.

An eligible internal table can begin in memory. The smaller value of tmp_table_size and max_heap_table_size limits an internal in-memory table. If it grows beyond that effective ceiling, MariaDB can convert it to an on-disk internal table. When aria_used_for_temp_tables is ON, as it normally is in standard builds, Aria handles applicable on-disk internal temporary tables.

Some result shapes cannot use the in-memory representation and may go directly to disk. Therefore a disk temporary table is not proof that the memory limit is too low. It may be a consequence of data types, row width, plan shape, or the server release.

Explicit temporary tables

An application or administrator creates these deliberately:

CREATE TEMPORARY TABLE session_work (
  id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MEMORY;

The table is private to the connection and disappears when that connection closes. A user-created MEMORY table is governed by max_heap_table_size. When it reaches the limit, it returns a table-full error; it is not automatically converted to Aria like an internal temporary table. Use InnoDB explicitly when the working set can be large, needs variable-length types unsupported by MEMORY, or must not consume unpredictable RAM.

Filesort workspace

“Using filesort” in EXPLAIN means MariaDB uses its sorting algorithm instead of returning rows in index order. It does not mean the operation necessarily writes a file. Sorting uses session-private workspace controlled in part by sort_buffer_size; larger results may require merge passes and temporary files.

Filesort can be entirely reasonable. Sorting 50 qualifying rows is different from scanning and sorting 50 million rows to return a page of 50. Inspect rows read, filtering, indexes, and limit behavior before treating the label as a defect.

Other temporary files

Binary log caches, online DDL, InnoDB operations, and other server components can create temporary files too. Created_tmp_disk_tables does not describe every byte written under tmpdir. Database counters, filesystem capacity, filesystem latency, and process-level I/O must be observed together.

Establish a production baseline

Start with identity and configuration, not assumptions:

SELECT VERSION(), @@version_comment;

SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'tmp_table_size',
  'max_heap_table_size',
  'sort_buffer_size',
  'tmpdir',
  'aria_used_for_temp_tables'
);

Record values in bytes exactly. Convert them only for reporting:

SELECT @@GLOBAL.tmp_table_size / 1024 / 1024 AS tmp_table_size_mib,
       @@GLOBAL.max_heap_table_size / 1024 / 1024 AS max_heap_table_size_mib,
       @@GLOBAL.sort_buffer_size / 1024 / 1024 AS sort_buffer_size_mib;

Check relevant cumulative counters:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Sort_merge_passes',
  'Sort_range',
  'Sort_rows',
  'Sort_scan',
  'Threads_connected',
  'Threads_running',
  'Uptime'
);

These values accumulate from server start or the last status reset. A raw ratio from a server running for months does not describe the current incident. Capture two samples over a representative interval and calculate deltas.

Example collection without resetting global counters:

install -d -m 0750 /var/log/mariadb-observation

mariadb --batch --skip-column-names -e "
SELECT NOW(6), VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME IN
('CREATED_TMP_TABLES','CREATED_TMP_DISK_TABLES','SORT_MERGE_PASSES',
 'SORT_RANGE','SORT_ROWS','SORT_SCAN','THREADS_RUNNING');
" > /var/log/mariadb-observation/temp-sort-before.tsv

sleep 300

mariadb --batch --skip-column-names -e "
SELECT NOW(6), VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME IN
('CREATED_TMP_TABLES','CREATED_TMP_DISK_TABLES','SORT_MERGE_PASSES',
 'SORT_RANGE','SORT_ROWS','SORT_SCAN','THREADS_RUNNING');
" > /var/log/mariadb-observation/temp-sort-after.tsv

Use a monitoring system for continuous rate calculations. Avoid FLUSH STATUS on a shared production server merely to simplify arithmetic because it resets global status information that other operators and alerts may rely on.

For an interval, calculate:

disk_temp_fraction = delta(Created_tmp_disk_tables)
                     / delta(Created_tmp_tables)

Treat the result as a diagnostic signal, not a universal service-level objective. A low-volume reporting host may legitimately have a high fraction. A transaction host can have a modest fraction but unacceptable latency because one recurring statement writes enormous intermediate results.

Correlate spills with workload symptoms

Before changing settings, align counter rates with:

Query latency percentiles and timeouts.
Threads_running, CPU saturation, and load average.
Temporary filesystem utilization, IOPS, latency, and queue depth.
Free memory, swap activity, and cgroup memory pressure.
Deployment, batch, analytics, backup, and schema-change schedules.
Replica lag if heavy reads also run on replicas.

On Linux, inspect the configured path and the filesystem that actually backs it:

mariadb --batch --skip-column-names -e "SELECT @@GLOBAL.tmpdir;"
findmnt -T /tmp
df -hT /tmp
df -i /tmp

Replace /tmp with the returned path. A filesystem can fail because it has no free blocks or no free inodes. Capacity alone is insufficient; high storage latency can turn a spill into a user-visible pause.

Sample current device behavior with installed operating-system tools:

vmstat 1 10
iostat -xz 1 10

Do not infer MariaDB causality from these host-wide tools alone. Another process may be producing the I/O. Correlate timestamps and, where permitted, use pidstat -d or your container monitoring to attribute writes.

Find the statements creating the work

Global counters tell you that work exists. They do not identify the SQL responsible.

Use the slow query log for a bounded investigation

Review the current state first:

SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'slow_query_log',
  'slow_query_log_file',
  'long_query_time',
  'log_slow_filter',
  'log_slow_verbosity'
);

MariaDB's slow-log options vary by release. Confirm supported values on the running version before changing them. For a controlled window, enable only the detail needed, protect the log from unbounded growth, and restore the previous settings afterward. Do not set long_query_time=0 globally on a busy production server without estimating the write volume.

Analyze a copied or rotated log rather than repeatedly scanning the active file. The bundled mariadb-dumpslow utility can aggregate similar statements:

mariadb-dumpslow -s t -t 20 /path/to/copied-slow.log
mariadb-dumpslow -s r -t 20 /path/to/copied-slow.log

The first view ranks by query time; the second emphasizes rows sent or examined according to utility support. Validate options with mariadb-dumpslow --help on the host.

Use Performance Schema statement data

When Performance Schema statement consumers are enabled, event tables expose per-statement fields such as CREATED_TMP_TABLES, CREATED_TMP_DISK_TABLES, SORT_MERGE_PASSES, SORT_ROWS, and ROWS_EXAMINED.

Inspect availability instead of assuming it:

SELECT @@performance_schema;

SELECT NAME, ENABLED
FROM performance_schema.setup_consumers
WHERE NAME LIKE 'events_statements%';

DESCRIBE performance_schema.events_statements_history_long;

If collection is already enabled, find recent expensive events:

SELECT THREAD_ID,
       EVENT_ID,
       ROUND(TIMER_WAIT / 1000000000000, 6) AS seconds,
       ROWS_EXAMINED,
       ROWS_SENT,
       CREATED_TMP_TABLES,
       CREATED_TMP_DISK_TABLES,
       SORT_ROWS,
       SORT_MERGE_PASSES,
       LEFT(SQL_TEXT, 500) AS sql_text
FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT IS NOT NULL
  AND (CREATED_TMP_DISK_TABLES > 0 OR SORT_MERGE_PASSES > 0)
ORDER BY TIMER_WAIT DESC
LIMIT 30;

History is finite and may already have overwritten the event. Enabling consumers adds overhead and changing instrumentation is an operational decision. Test it, document the previous state, and do not casually truncate Performance Schema tables that another diagnostic session uses.

Digest aggregation is more useful for recurring query shapes. First inspect the columns supported by the installed release:

DESCRIBE performance_schema.events_statements_summary_by_digest;

Then select available temporary-table, sort, latency, execution-count, and row counters for the top digests. Column names evolve, so production automation should feature-detect the schema rather than paste a query written for another major version.

Read the execution plan correctly

Run EXPLAIN with the exact SQL shape and realistic parameter selectivity:

EXPLAIN
SELECT customer_id, SUM(total_amount) AS revenue
FROM sales.orders
WHERE created_at >= '2026-08-01 00:00:00'
  AND created_at <  '2026-09-01 00:00:00'
GROUP BY customer_id
ORDER BY revenue DESC
LIMIT 50;

Using temporary and Using filesort are clues. Also inspect:

Join order and access type.
Candidate and selected keys.
Estimated rows and filtering.
Expressions wrapped around indexed columns.
Whether the query reads wide payload columns before aggregation.
Whether LIMIT applies only after a large grouping or sort.

Use ANALYZE FORMAT=JSON only when executing the statement is safe. Unlike plain EXPLAIN, analysis runs the query and reports observed work. Never test an unbounded or mutating statement on production merely to obtain a plan.

ANALYZE FORMAT=JSON
SELECT customer_id, SUM(total_amount) AS revenue
FROM sales.orders
WHERE created_at >= '2026-08-01 00:00:00'
  AND created_at <  '2026-09-01 00:00:00'
GROUP BY customer_id
ORDER BY revenue DESC
LIMIT 50;

Capture the plan before and after a change. Latency without rows examined and plan evidence can improve temporarily because of cache warmth, not because the design improved.

Fix query shape before increasing memory

The best temporary table is often the one the optimizer no longer needs, or one made dramatically smaller before materialization.

Preserve sargable predicates

This predicate commonly blocks a normal index range scan:

WHERE DATE(created_at) = '2026-08-25'

Use a half-open range:

WHERE created_at >= '2026-08-25 00:00:00'
  AND created_at <  '2026-08-26 00:00:00'

This preserves boundary correctness and lets an index beginning with created_at support the range. Confirm time-zone semantics before rewriting temporal predicates.

Filter and project early

Avoid carrying large text or JSON payloads into a grouping or sort when the output does not need them. Select identifiers and aggregation inputs first, then join the small result back to fetch display columns.

Bad working-set shape:

SELECT customer_id, customer_name, notes, SUM(total_amount)
FROM sales.order_report_source
WHERE created_at >= '2026-08-01'
GROUP BY customer_id, customer_name, notes;

A narrower approach:

SELECT c.id, c.name, x.revenue
FROM (
  SELECT customer_id, SUM(total_amount) AS revenue
  FROM sales.orders
  WHERE created_at >= '2026-08-01 00:00:00'
    AND created_at <  '2026-09-01 00:00:00'
  GROUP BY customer_id
) AS x
JOIN crm.customers AS c ON c.id = x.customer_id
ORDER BY x.revenue DESC
LIMIT 50;

This is not automatically faster; the derived result may itself materialize. Its advantage is reducing row width and postponing payload access. Verify with plans and measurements.

Remove unnecessary ordering

Do not add ORDER BY for deterministic appearance when the consumer does not require order. Conversely, never remove ordering when pagination or business semantics depend on it.

For stable keyset pagination, prefer a deterministic indexed boundary over large OFFSET values:

SELECT id, created_at, total_amount
FROM sales.orders
WHERE customer_id = 481
  AND (created_at, id) < ('2026-08-25 12:00:00', 9000000)
ORDER BY created_at DESC, id DESC
LIMIT 100;

A supporting index could be:

CREATE INDEX ix_orders_customer_created_id
ON sales.orders (customer_id, created_at, id);

Test index creation duration, disk demand, locking behavior, and replication impact on production-sized staging data. Redundant indexes also increase write cost and storage, so inspect existing indexes first:

SHOW INDEX FROM sales.orders;

Match index order to equality, range, and ordering

For:

SELECT id, created_at, total_amount
FROM sales.orders
WHERE customer_id = 481
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 100;

an index beginning (customer_id, status, created_at, id) may allow MariaDB to filter equalities and read rows in the requested order. Whether it is appropriate depends on selectivity, supported descending behavior, projected columns, and other workload. Use EXPLAIN, test both common and skewed values, and watch write amplification.

No single index can optimize every combination of dynamic filters and sort choices. Product design may need a restricted set of supported orderings rather than an index for every UI option.

Size internal in-memory limits safely

Inspect both global and session values:

SELECT @@GLOBAL.tmp_table_size,
       @@SESSION.tmp_table_size,
       @@GLOBAL.max_heap_table_size,
       @@SESSION.max_heap_table_size;

The effective internal in-memory ceiling is the lower of the two session values. Raising only one may change nothing:

SELECT LEAST(@@SESSION.tmp_table_size,
             @@SESSION.max_heap_table_size) AS effective_internal_limit_bytes;

These are limits, not necessarily memory reserved at connection creation. Still, temporary and sort workspaces are private allocations that can multiply under concurrency. A simplistic worst-case estimate is deliberately conservative:

potential_private_working_memory
  = concurrent_memory_heavy_statements
    * (effective_temp_limit + sort_buffer_size + other_per-thread_buffers)

One statement may perform multiple operations, and implementation details differ by plan and release, so this is not an exact allocator model. It is a warning against multiplying a large global setting by max_connections and assuming the host can absorb it.

Build a memory budget that includes:

InnoDB buffer pool and other global caches.
MariaDB process overhead and connection/thread structures.
Sort, join, read, binlog, and temporary workspaces.
Galera, backup, audit, or plugin memory where applicable.
Operating-system page cache and kernel needs.
Sidecars or other containers under the same node limit.
A safety margin for workload bursts.

Tune from observed concurrent heavy statements, not only Threads_connected. Idle pooled connections are different from 100 simultaneous report queries.

Test a session-scoped increase first

For a known report connection, test both limits together without changing every workload:

SET SESSION tmp_table_size = 64 * 1024 * 1024;
SET SESSION max_heap_table_size = 64 * 1024 * 1024;

Run the controlled statement, compare latency, rows examined, per-session temporary counters, storage writes, and memory pressure. The chosen 64 MiB is an example, not a recommendation.

If the query still creates a disk table, do not repeatedly double memory. The result may be ineligible for in-memory storage, or the intermediate result may simply be larger than expected.

Apply a global change in an owned configuration file

After load testing and budget review:

[mariadb]
tmp_table_size = 64M
max_heap_table_size = 64M

Verify how the daemon parses configuration before restart:

mariadbd --print-defaults
my_print_defaults mariadbd server mysqld

Dynamic global changes affect defaults for new sessions; existing sessions retain their session values. Persisted configuration and a controlled pool recycle are therefore part of a complete rollout.

Tune sort behavior without creating a memory incident

Inspect settings and interval deltas:

SELECT @@GLOBAL.sort_buffer_size,
       @@SESSION.sort_buffer_size;

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Sort_merge_passes',
  'Sort_range',
  'Sort_rows',
  'Sort_scan'
);

Sort_scan counts sorts associated with full scans, while Sort_range counts sorts fed by range access. Sort_rows indicates rows sorted, and Sort_merge_passes counts merge passes. A rising merge-pass rate can justify investigation, but first reduce input rows and improve indexes.

Test a scoped change only for the affected session:

SET SESSION sort_buffer_size = 4 * 1024 * 1024;

Compare the same query under representative concurrency. A larger sort buffer can consume more private memory and may not improve small sorts. Avoid copying oversized values from a dedicated analytics server to an OLTP host.

When ORDER BY uses an expression, an index on the base column usually cannot directly provide expression order:

ORDER BY LOWER(customer_name)

Consider a schema-supported generated column and index only after validating semantics, collation, DDL cost, and version support. Sometimes accepting a bounded filesort is simpler and safer.

Engineer tmpdir for predictable failure behavior

Query the active path:

SELECT @@GLOBAL.tmpdir;

On Unix, tmpdir can contain a colon-separated list used in round-robin fashion. On Windows the separator is a semicolon. This distributes file creation; it is not replication, capacity pooling, or failover. One full or failed path can still break statements.

A production temporary filesystem should have:

Enough capacity and inodes for measured peaks plus margin.
Predictable latency under concurrent spill load.
Correct ownership and restrictive permissions.
Monitoring for capacity, inode exhaustion, latency, and I/O errors.
An explicit mount available before MariaDB starts.
A documented response when space approaches the threshold.

Create a dedicated local path carefully:

install -d -o mysql -g mysql -m 0750 /var/lib/mariadb-tmp
findmnt -T /var/lib/mariadb-tmp
df -hT /var/lib/mariadb-tmp
df -i /var/lib/mariadb-tmp

Configure it:

[mariadb]
tmpdir = /var/lib/mariadb-tmp

tmpdir is a global, non-dynamic setting, so changing it requires a planned restart. Confirm mandatory access control too. On systems using systemd hardening, AppArmor, or SELinux, Unix ownership alone may not authorize the daemon.

Do not place latency-sensitive temporary work on an unreliable network filesystem. Do not assume a RAM-backed tmpfs is free: its pages consume memory and can count against a container or cgroup limit. A large spill can then cause an out-of-memory kill instead of a clean disk-full error.

Replica workloads need additional care. MariaDB documentation warns that replica load operations can depend on slave_load_tmpdir; a directory cleared during restart can disrupt replication when the override is not configured. Audit the exact replication mode and release before changing paths.

Never delete unfamiliar files from an active MariaDB temporary directory to recover space. A live statement may own them. Identify and stop the responsible work through supported database controls, then let the server clean up its files.

Use MariaDB 11.5+ temporary-space limits deliberately

MariaDB 11.5 introduced controls and status for temporary disk consumption. Confirm presence rather than assuming the major version:

SHOW GLOBAL VARIABLES LIKE 'max_tmp%space_usage';
SHOW GLOBAL STATUS LIKE 'Max_tmp_space_used';

max_tmp_session_space_usage limits total temporary file and temporary table usage for an individual session. A value of zero disables the limit. The variable was named max_tmp_space_usage in MariaDB 11.5.0 only, so automation must detect the installed name.

There is also a global temporary-space control in supported releases. Consult the exact server documentation and test failure behavior before relying on it. Limits protect shared capacity by failing work; they do not optimize a query. Set them above legitimate measured peaks, below the point where the filesystem or server becomes unsafe, and ensure applications handle the resulting error.

Test with a non-production workload and alert before the hard boundary. A hard limit with no early warning merely converts a host-wide outage into repeated application errors.

Handle explicit temporary tables safely

When application SQL uses CREATE TEMPORARY TABLE, name the engine and understand its lifecycle:

CREATE TEMPORARY TABLE report_ids (
  id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

InnoDB is often safer for an uncertain or large working set. MEMORY can be fast for small fixed-purpose data, but its rows count against memory and its size is capped by the session's max_heap_table_size.

Connection pooling creates a subtle lifecycle risk. An explicit temporary table remains until dropped or until the physical connection closes. Returning a connection to the pool does not necessarily close it. A later request can encounter an old table name or retain memory longer than expected.

Use defensive cleanup:

DROP TEMPORARY TABLE IF EXISTS report_ids;

Wrap creation and cleanup in the application's connection lifecycle, including error paths. Remember that temporary table DDL and transaction behavior can be version and statement dependent; test the exact sequence rather than assuming ordinary table semantics.

Troubleshoot common failure patterns

Created_tmp_disk_tables rises but latency is normal

Do not tune a harmless counter. Establish interval volume, statement identity, bytes written, and service impact. Reporting queries may legitimately spill. Preserve headroom and alert on change from baseline.

Raising tmp_table_size changed nothing

Check max_heap_table_size; the lower value remains effective. Confirm session values on the real application connection. The query may create an ineligible or oversized result. Recheck plan, data types, row width, and predicates.

ERROR 1114: table is full

First determine whether the table is explicit or internal and whether the failure is memory, disk capacity, inode exhaustion, or a configured 11.5+ temporary-space limit.

SHOW FULL PROCESSLIST;
SHOW WARNINGS;
SELECT @@SESSION.max_heap_table_size,
       @@SESSION.tmp_table_size,
       @@GLOBAL.tmpdir;

df -hT /path/from/tmpdir
df -i /path/from/tmpdir
journalctl -u mariadb --since '-15 minutes' --no-pager

Do not blindly increase a MEMORY table limit or remove files. Stop or fix the responsible query, restore safe capacity, and choose an engine appropriate to the working set.

No space left on device

Block new batch/report work, identify active expensive statements, and protect core traffic. If a statement must be terminated, prefer killing only the query:

KILL QUERY connection_id;

Confirm the correct connection ID from SHOW FULL PROCESSLIST; killing the wrong query can interrupt a transaction. KILL CONNECTION also closes the session and may roll back its transaction, which can take time and create more I/O.

After the immediate incident, fix capacity alerts, workload controls, query design, and space limits. Moving tmpdir requires restart and should not be improvised during an outage without validating mounts, permissions, and security policy.

Sorting remains slow after adding an index

Check whether the optimizer chose it, whether earlier range conditions prevent using later key parts for ordering, whether collations and directions match, and whether the selected columns require many table lookups. Test representative values because data skew can change the best plan.

Refresh optimizer statistics only with an understood maintenance procedure:

ANALYZE TABLE sales.orders;

This command affects optimizer statistics and can consume resources. Schedule and validate it; do not use it reflexively during peak load.

A configuration change appears inactive

Check global and session scopes. A global dynamic change sets the default for new sessions, while existing pooled connections may keep old values. If a restart was used, confirm the loaded option files and startup logs:

mariadbd --print-defaults
journalctl -u mariadb -b --no-pager

SELECT @@GLOBAL.tmp_table_size,
       @@SESSION.tmp_table_size,
       @@GLOBAL.max_heap_table_size,
       @@SESSION.max_heap_table_size,
       @@GLOBAL.sort_buffer_size,
       @@SESSION.sort_buffer_size;

Roll out changes with evidence and rollback

A production-ready sequence is:

Record version, uptime, configuration origin, interval counters, workload, filesystem metrics, and query plans.
Identify one or more responsible query digests.
Fix predicates, row width, joins, aggregation, pagination, or indexes in staging.
Replay representative concurrency, not one serial query.
Test session-scoped memory changes if query improvements are insufficient.
Define memory and temporary-storage guardrails.
Roll out to a small workload segment or reporting pool.
Compare latency, errors, rows examined, spill rate, merge passes, RAM, and storage latency.
Persist approved values and recycle connections deliberately.
Keep a timed rollback criterion and an owner watching the deployment.

Before editing a configuration file, preserve its content and document ownership in source control or configuration management. A rollback for memory settings is normally restoring the old values, validating parsed options, restarting if required, and recycling affected connections. An index rollback may require a separate online DDL window; do not promise instant reversal.

Useful rollback triggers include:

Resident memory or cgroup usage exceeds the tested envelope.
Swap activity begins or the kernel invokes the OOM killer.
Temporary storage latency or saturation worsens.
P95 or P99 latency regresses for core transactions.
Error rate, replica lag, or connection churn rises.
The target query improves but total host throughput declines.

Monitor rates, capacity, and query ownership

Build dashboards around rates and service impact:

rate(Created_tmp_tables) and rate(Created_tmp_disk_tables).
Disk-temp fraction with a minimum event-volume condition.
rate(Sort_merge_passes), rate(Sort_rows), rate(Sort_scan), and rate(Sort_range).
Query latency and rows examined by digest.
Threads_running, connection count, CPU, RSS, and swap.
tmpdir free bytes, free inodes, latency, IOPS, and errors.
Temporary-space-limit errors on MariaDB 11.5+.
Replica lag and batch/report concurrency.

Alert on deviation from the host's own baseline and on exhaustion forecasts. A fixed “disk temp ratio above 25%” alert without workload context is noisy. Pair a fraction with absolute rate, duration, latency, or remaining capacity.

Keep labels bounded. Exporting full SQL text or unnormalized identifiers as monitoring labels can overload the monitoring platform and expose sensitive values. Use normalized digests and a secure diagnostic workflow to retrieve statement text.

Best practices checklist

Measure counter deltas over representative intervals.
Separate internal tables, explicit temporary tables, filesort, and other temp files.
Find query digests before changing server-wide buffers.
Preserve sargable predicates and reduce rows early.
Keep aggregation and sorting rows narrow.
Design indexes for real equality, range, and ordering patterns.
Set tmp_table_size and max_heap_table_size together when both should change.
Budget per-session buffers under realistic concurrency.
Test larger buffers at session scope first.
Monitor tmpdir capacity, inodes, latency, and mount availability.
Treat tmpfs as memory, especially in containers.
Version-gate MariaDB 11.5+ temporary-space limits.
Clean up explicit temporary tables in pooled connections.
Preserve baseline, rollback values, and before/after plans.
Accept bounded, measured disk spills when they are the safest design.

FAQ

Is Created_tmp_disk_tables supposed to be zero?

No. Some query plans and result types legitimately require on-disk internal tables. Investigate interval rate, query ownership, bytes written, latency, and capacity rather than optimizing the counter to zero.

Should tmp_table_size and max_heap_table_size match?

For internal in-memory tables, the lower session value is the effective ceiling. Keeping them aligned avoids accidental clipping, but max_heap_table_size also governs user-created MEMORY tables, so assess that consequence.

Does Using filesort always mean disk I/O?

No. It identifies MariaDB's sort operation instead of index-ordered retrieval. The sort may remain in memory. Rows sorted, merge passes, latency, and storage metrics reveal its actual cost.

Why does a temporary table still spill after I raise memory?

The result may exceed the new limit, be ineligible for the memory representation, use a session that retained old values, or still be constrained by the other lower limit. Inspect the actual connection and plan.

Is a tmpfs a good MariaDB tmpdir?

Only with a strict memory budget and workload guardrails. tmpfs consumes memory and can contribute to cgroup or host OOM conditions. Fast local persistent storage often fails more predictably.

Can MariaDB limit temporary disk use per session?

MariaDB 11.5+ provides max_tmp_session_space_usage; it was named max_tmp_space_usage in 11.5.0 only. Detect support and test application error handling before enabling a restrictive value.

Should reporting queries run on a replica?

A replica can isolate primary CPU and I/O, but heavy temp work may increase replica lag and exhaust its own storage. Define freshness requirements, resource limits, and lag-based admission controls.

Can I delete files from tmpdir when it is full?

Do not delete unknown files while MariaDB is running. Identify and stop the owning statement through supported controls. Manual deletion can corrupt active work or complicate recovery.

Conclusion

Reliable MariaDB temporary table tuning begins with workload evidence. Measure interval deltas, map work to statements, inspect plans, reduce rows and row width, and build the right indexes before expanding private buffers. When memory changes are justified, test them per session and calculate concurrency exposure rather than tuning a single query in isolation.

Temporary storage is part of the database's production architecture. Give tmpdir monitored capacity, predictable latency, correct permissions, startup ordering, and failure limits appropriate to the installed MariaDB version. The goal is not zero spills. It is bounded work, stable latency, controlled memory, and a failure mode that protects the rest of the service.