How MySQL Allocates Sort Buffer Memory

This article explains how MySQL allocates memory for sort buffers during complex query execution. It covers the role of the sort_buffer_size system variable, how memory is allocated per-session and per-query, and the transition from in-memory sorting to disk-based sorting when memory limits are exceeded.

Per-Session Allocation

Unlike global buffers (such as the InnoDB Buffer Pool) which are shared across all connections, the sort buffer is a session-level allocation. When a query requires a sort operation—such as an ORDER BY or GROUP BY that cannot be resolved using an index—MySQL allocates a dedicated sort buffer for that specific client connection thread.

The Role of sort_buffer_size

The maximum amount of memory allocated for each sort operation is controlled by the sort_buffer_size system variable. * Full vs. Incremental Allocation: Historically, MySQL allocated the entire sort_buffer_size amount of memory upfront whenever a sort was initiated. In modern MySQL versions and depending on the operating system’s memory allocator (like glibc, jemalloc, or tcmalloc), memory may be allocated incrementally as needed, up to the limit defined by sort_buffer_size. * Multi-Buffer Allocation: In complex queries involving multiple joins, subqueries, or merge joins, MySQL may require multiple sort operations simultaneously. In these scenarios, a single query session can allocate multiple sort buffers at the same time, quickly multiplying the memory footprint of a single connection.

In-Memory vs. Disk Sorting (Filesort)

When MySQL needs to sort a result set, it attempts to perform the entire operation in-memory within the allocated sort buffer. 1. In-Memory Sort: If the dataset to be sorted fits entirely within the allocated sort buffer, MySQL performs a quicksort or merge sort entirely in RAM. 2. On-Disk Sort (Filesort): If the dataset is larger than the sort_buffer_size, MySQL falls back to a disk-based merge sort. It reads portions of the data into the sort buffer, sorts them, writes the sorted chunks to temporary disk files, and finally merges these sorted chunks to produce the final result.

Risks of High sort_buffer_size Settings

Because sort buffers are allocated per-thread (and potentially multiple times per-query), setting sort_buffer_size to an excessively high value can degrade performance. It can lead to rapid memory exhaustion and trigger the operating system’s Out-Of-Memory (OOM) killer, especially under high-concurrency workloads. For optimal performance, this variable should be kept at its default value and increased only for specific sessions executing complex, data-heavy reports.