Per-Connection Buffers, Sort/Join Buffers, Memory Multipliers, and OOM Risk

Learning outcomes

After sizing the global cache, ServiceHub still suffers memory spikes when many reporting sessions run at once. A common reaction is to make sort_buffer_size and join_buffer_size huge globally because one query got faster in a test session. This lesson shows why configured per-session maxima become dangerous when multiplied by concurrency—and why MySQL does not necessarily allocate every configured byte to every idle connection.

Memory multiplication: configured maximum is not resident memory

MySQL sessions allocate memory as work requires it. For example, a session that performs a sort allocates sort workspace up to the configured sort_buffer_size; the optimizer can allocate less and grow toward the limit. A join that cannot use an index can need join buffers, and a complex join can require more than one. Therefore, multiplying every buffer maximum by max_connections usually overstates steady memory—but ignoring multiplication entirely understates peak risk.

Inspect variables and actual memory instrumentation

SELECT @@GLOBAL.sort_buffer_size AS global_sort,       @@SESSION.sort_buffer_size AS session_sort,       @@GLOBAL.join_buffer_size AS global_join,       @@SESSION.join_buffer_size AS session_join,       @@SESSION.read_buffer_size AS read_buffer,       @@SESSION.read_rnd_buffer_size AS read_rnd_buffer;SELECT VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCEFROM performance_schema.variables_infoWHERE VARIABLE_NAME IN ('sort_buffer_size','join_buffer_size',                        'read_buffer_size','read_rnd_buffer_size')ORDER BY VARIABLE_NAME;

SELECT THREAD_ID,       EVENT_NAME,       CURRENT_NUMBER_OF_BYTES_USED,       HIGH_NUMBER_OF_BYTES_USEDFROM performance_schema.memory_summary_by_thread_by_event_nameWHERE CURRENT_NUMBER_OF_BYTES_USED > 0ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESCLIMIT 30;

Memory event names vary by subsystem and version, so diagnose from the rows your server actually exposes instead of hard-coding one instrument name into monitoring. The summary reports observed allocations; it does not predict a future concurrency spike.

Create a sort-plus-join workload and read the plan first

EXPLAIN ANALYZESELECT w.work_order_id,       w.region,       w.parts_cost * r.multiplier AS adjusted_partsFROM servicehub_perf_lab.work_orders AS wJOIN servicehub_perf_lab.region_rules AS r  ON r.region_name = w.regionWHERE w.status='open'ORDER BY adjusted_parts DESCLIMIT 5000;

The exact algorithm is cost-based; current MySQL can choose a hash join when applicable, and an expression-based order still needs sort work. The evidence to capture is the chosen operators, estimated versus actual rows, execution time, and session sort counters—not an assumption that a specific buffer must be the bottleneck.

SHOW SESSION STATUS WHERE Variable_name IN ( 'Sort_rows','Sort_scan','Sort_range','Sort_merge_passes');SELECT w.work_order_id,       w.region,       w.parts_cost * r.multiplier AS adjusted_partsFROM servicehub_perf_lab.work_orders AS wJOIN servicehub_perf_lab.region_rules AS r  ON r.region_name=w.regionWHERE w.status='open'ORDER BY adjusted_parts DESCLIMIT 5000;SHOW SESSION STATUS WHERE Variable_name IN ( 'Sort_rows','Sort_scan','Sort_range','Sort_merge_passes');

A budget model that respects concurrency

Do not create a “worst case” by multiplying every maximum by max_connections and call that a forecast. Build workload classes from observed active sessions. For example, classify ordinary API sessions, reporting sessions, ETL sessions, and administrative work; record realistic concurrent peaks and the memory-heavy operations each class can perform.

Keep an explicit uncertainty margin. Memory high-water values from one run are not proof of the future maximum, but they are much stronger evidence than assuming every connection allocates every configured buffer.

Wrong approach: make all buffers huge globally

-- A tempting but dangerous reaction to one slow report:-- SET GLOBAL sort_buffer_size = 64 * 1024 * 1024;-- SET GLOBAL join_buffer_size = 64 * 1024 * 1024;-- Global changes become defaults for relevant sessions/new connections;-- concurrent operations can multiply memory demand dramatically.

Repair: fix access paths, then scope memory experiments

For the ServiceHub join, the missing access path on work_orders.region is more fundamental than a global join buffer. Add an index only after verifying the workload benefits and write cost are acceptable.

CREATE INDEX idx_region_statusON servicehub_perf_lab.work_orders(region,status);ANALYZE TABLE servicehub_perf_lab.work_orders;EXPLAIN ANALYZESELECT w.work_order_id,       w.region,       w.parts_cost * r.multiplier AS adjusted_partsFROM servicehub_perf_lab.work_orders AS wJOIN servicehub_perf_lab.region_rules AS r  ON r.region_name=w.regionWHERE w.status='open'ORDER BY adjusted_parts DESCLIMIT 5000;

The sort may remain because the ordering is on a computed value, but the join/filter access path can improve. If a large unavoidable sort still shows meaningful merge-pass pressure, test a slightly larger buffer only in the target session or statement.

SELECT /*+ SET_VAR(sort_buffer_size=1048576) */       w.work_order_id,       w.region,       w.parts_cost * r.multiplier AS adjusted_partsFROM servicehub_perf_lab.work_orders AS wJOIN servicehub_perf_lab.region_rules AS r  ON r.region_name=w.regionWHERE w.status='open'ORDER BY adjusted_parts DESCLIMIT 5000;

Compare the same dataset, same concurrency, same cache state, plan, merge-pass delta, latency distribution, and host memory. A faster single run is not sufficient evidence for a production default.

Production judgment

Per-session buffers are economic multipliers: their cost depends on the number of simultaneous operations that need them. Start with query shape and indexing. Change session-scoped values only after the plan and counters show the relevant operation remains expensive. Global changes require a concurrency budget and a staged load test that watches both mysqld resident memory and host paging.

Lesson 3 moves to another concurrency multiplier: internal temporary tables, which draw from TempTable resources and may spill to disk when memory limits are reached.

Authoritative references

• MySQL 8.4 Reference Manual
• MySQL Community Server 8.4 downloads
• Server System Variables — sort_buffer_size and join_buffer_size
• Performance Schema Memory Summary Tables
• Hash Join Optimization
• EXPLAIN ANALYZE