Per-Session Memory, Sort/Join Buffers, Temp Tables, and Memory Explosion Risks

Learning outcomes

After increasing the buffer pool, ServiceHub still experiences memory spikes only during reporting bursts. The server has thousands of connected sessions, but only a fraction execute complex sorts and joins at once. The critical distinction is that many MariaDB buffers are per session or per operation when needed, not one fixed allocation per server and not necessarily one allocation permanently reserved for every idle connection.

1. Where server memory comes from

MariaDB’s memory-allocation documentation explicitly warns that very large query-execution buffers become dangerous when many simultaneous users invoke them. The goal is therefore not “set large buffers globally,” but “make the common path cheap and use scoped exceptions for proven expensive work.”

2. Create a query that can sort and materialize

DROP DATABASE IF EXISTS servicehub18;CREATE DATABASE servicehub18 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub18;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status ENUM('open','waiting','closed') NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  updated_at DATETIME(6) NOT NULL,  summary VARCHAR(240) NOT NULL,  INDEX ix_status_opened(status, opened_at),  INDEX ix_customer_updated(customer_id, updated_at)) ENGINE=InnoDB;INSERT INTO tickets(customer_id,status,priority,opened_at,updated_at,summary)WITH RECURSIVE seq AS (  SELECT 1 AS n  UNION ALL SELECT n+1 FROM seq WHERE n < 1000)SELECT MOD(n,125)+1,       ELT(MOD(n,3)+1,'open','waiting','closed'),       MOD(n,5)+1,       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL n MINUTE,       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL n MINUTE,       CONCAT('ServiceHub ticket ',n)FROM seq;SELECT VERSION() AS server_version, @@version_comment AS build_comment,       @@innodb_buffer_pool_size AS buffer_pool_bytes;SELECT COUNT(*) AS seeded_rows FROM tickets;

SHOW SESSION VARIABLES WHERE Variable_name IN ( 'sort_buffer_size','join_buffer_size','join_buffer_space_limit', 'read_buffer_size','read_rnd_buffer_size','tmp_table_size', 'max_heap_table_size','max_session_mem_used');SHOW SESSION STATUS WHERE Variable_name IN ( 'Created_tmp_tables','Created_tmp_disk_tables','Sort_merge_passes', 'Sort_rows','Sort_scan','Memory_used');SELECT customer_id, status, COUNT(*) AS tickets,       MAX(updated_at) AS newestFROM servicehub18.ticketsGROUP BY customer_id, statusORDER BY tickets DESC, newest DESC;SHOW SESSION STATUS WHERE Variable_name IN ( 'Created_tmp_tables','Created_tmp_disk_tables','Sort_merge_passes', 'Sort_rows','Sort_scan','Memory_used');

The before/after delta shows what this session did during the observation window. Created_tmp_disk_tables indicates conversion to on-disk internal temporary tables, but it does not by itself tell you that raising tmp_table_size is the best fix. First inspect the query plan, indexes, grouping shape, result width, and concurrency.

3. Understand each knob before multiplying it

sort_buffer_size is allocated by a session performing a sort. MariaDB’s documentation recommends improving indexes first when sort merge passes are high and specifically suggests session-scoped increases when needed. join_buffer_size caps join-buffer behavior for block-based joins, while join_buffer_space_limit constrains total join-buffer memory for a query. tmp_table_size limits internal in-memory temporary tables, with the smaller of it and max_heap_table_size applying to those internal tables; user-created MEMORY tables have their own max_heap_table_size behavior.

SELECT @@GLOBAL.max_connections AS max_connections, @@GLOBAL.sort_buffer_size AS sort_buffer_bytes, @@GLOBAL.join_buffer_size AS join_buffer_bytes, @@GLOBAL.tmp_table_size AS tmp_table_bytes, @@GLOBAL.max_heap_table_size AS max_heap_bytes, @@GLOBAL.innodb_buffer_pool_size AS buffer_pool_bytes;SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Threads_connected','Threads_running','Max_used_connections', 'Created_tmp_tables','Created_tmp_disk_tables');

For a planning scenario, suppose 40 active analytical sessions can each need one 8 MiB sort, one 8 MiB join buffer, and a temporary table approaching 64 MiB. That scenario is roughly 40 × (8+8+64) MiB = 3.125 GiB before thread/session structures and any additional operators. It is not a prediction that MariaDB will allocate exactly that amount; it demonstrates why modest-looking per-operation settings become material at concurrency.

4. The wrong approach: solve one disk spill by raising global limits for everyone

A common change is to observe one reporting query creating a disk temporary table and then globally set tmp_table_size, max_heap_table_size, sort_buffer_size, and join_buffer_size to hundreds of megabytes. Under concurrent reporting, the process can suddenly exceed its memory envelope.

-- Preserve the session values first.SET @old_sort := @@SESSION.sort_buffer_size;SET @old_tmp  := @@SESSION.tmp_table_size;SET @old_heap := @@SESSION.max_heap_table_size;-- Example scoped experiment. Choose values from your lab budget, not folklore.SET SESSION sort_buffer_size = 4*1024*1024;SET SESSION tmp_table_size = 32*1024*1024;SET SESSION max_heap_table_size = 32*1024*1024;-- Run exactly the same query and compare status deltas + elapsed time.SELECT customer_id, status, COUNT(*) AS tickets, MAX(updated_at) AS newestFROM servicehub18.ticketsGROUP BY customer_id, statusORDER BY tickets DESC, newest DESC;-- Roll back the experiment for this session.SET SESSION sort_buffer_size = @old_sort;SET SESSION tmp_table_size = @old_tmp;SET SESSION max_heap_table_size = @old_heap;

A session-scoped experiment limits blast radius. If the query improves, you still need to compare memory consumption and concurrency risk. Often the better production correction is an index, reduced result width, a rewritten query, workload isolation, or an application/reporting pool with bounded concurrency.

5. Observe temp-table behavior without confusing cause and symptom

SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Created_tmp_tables','Created_tmp_disk_tables','Created_tmp_files');SHOW SESSION STATUS WHERE Variable_name IN ( 'Created_tmp_tables','Created_tmp_disk_tables','Created_tmp_files');EXPLAINSELECT customer_id, status, COUNT(*)FROM servicehub18.ticketsGROUP BY customer_id, statusORDER BY COUNT(*) DESC;

Global counters tell you the server-wide trend; session counters isolate your test. A high disk-temp ratio can come from complex query shapes, data types or result sizes that cannot remain in memory, not merely from a low byte limit. Correlate with statement digests and plans from Chapter 17/10 before changing server defaults.

6. Reproducible lab: create a concurrency-safe memory budget

Prerequisites: one free local MariaDB Community server and permission to read variables/status. Host memory visibility is strongly recommended. No Enterprise feature is required.

1. Record physical/container memory limit, MariaDB RSS, buffer-pool size, Threads_connected, Threads_running, and relevant session defaults.
2. Run the grouped query and capture session temp/sort counters.
3. Use a realistic peak active analytical concurrency—not max_connections alone—to build a scenario budget.
4. Make one session-scoped buffer/temp change, rerun the exact query, and record elapsed time, temp-table deltas, and process/container memory.
5. Decide whether the evidence supports a scoped setting, query/index change, workload isolation, or no change.
6. Restore session values and drop the disposable database.

Production judgment and bridge

Per-session tuning is appropriate for measured statements with known concurrency and a bounded memory budget. Global increases are dangerous when they silently multiply across workload bursts. Monitor process/container memory, swap/OOM, active concurrency, temp-table conversions, sort passes, statement latency, and pool/queue behavior. The next lesson turns from memory multiplication to CPU scheduling: how connection count becomes runnable concurrency and when MariaDB’s thread pool helps.

Authoritative references

Use the target-version tab or release notes when a variable or default differs from the course baseline. These lessons intentionally avoid treating old tuning folklore as current MariaDB behavior.

• MariaDB Memory Allocation
• Server system variables
• Block-based join algorithms and join buffers