🚨 MySQL Error Guide: 'ERROR 1038 (HY001)' Out of Sort Memory, Consider Increasing Server Sort Buffer Size

Exact Error Message

ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size

It appears when a query with ORDER BY or GROUP BY cannot complete a sort within the memory MySQL allocated for it.

What the Error Means

When a query must sort rows (an ORDER BY not satisfied by an index, a GROUP BY requiring sorting, a DISTINCT, or a UNION), MySQL performs a “filesort” using a per-sort buffer sized by sort_buffer_size. If the data to sort does not fit and MySQL cannot allocate the memory it needs, it raises ERROR 1038 with SQLSTATE HY001 (memory-allocation failure).

Counterintuitively, this is sometimes caused by sort_buffer_size being set too large, not too small. MySQL may allocate the full buffer per sort, and with many concurrent sorts the server runs out of memory and fails to allocate. So the message’s advice to “increase” the buffer is not always correct — the real fix is often to reduce the data being sorted or to size the buffer sanely.

Common Causes

• A query sorting a very large result set that no index can satisfy (full filesort).
• sort_buffer_size set excessively high, so each concurrent sort reserves huge memory and the OS refuses further allocation.
• Wide rows or many sort columns inflating the per-row sort size.
• High concurrency: many sessions each running a sort at once, multiplying total sort memory.
• A 32-bit build or container memory cgroup limit capping addressable memory.
• Sorting on expressions or BLOB/TEXT columns that bloat sort records.

How to Reproduce the Error

Set an unreasonably large sort_buffer_size and run a wide sort under memory pressure:

mysql -e "SET SESSION sort_buffer_size = 2147483647;"

SELECT * FROM big_table ORDER BY non_indexed_column;

ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size

The 2 GB per-sort allocation cannot be satisfied, so the sort fails immediately.

Diagnostic Commands

Read the current sort buffer and related sort settings (read-only):

mysql -e "SHOW VARIABLES LIKE 'sort_buffer_size';"
mysql -e "SHOW VARIABLES LIKE 'max_sort_length';"

+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| sort_buffer_size | 268435456 |
+------------------+-----------+

A 256 MB per-session sort buffer is far larger than recommended. Check how often sorts spill or merge:

mysql -e "SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Sort_scan';"

Identify which query is doing the filesort:

mysql -e "EXPLAIN SELECT * FROM big_table ORDER BY non_indexed_column\G" | grep -i "Extra"

Using filesort in the Extra column confirms the sort is not index-assisted. Confirm host memory headroom:

free -m
journalctl -u mysql --since "30 min ago" --no-pager | grep -i sort

Step-by-Step Resolution

1. Check sort_buffer_size first. If it is set to hundreds of MB or higher, lower it to a sane value (commonly 256 KB to 2 MB globally). Large global sort buffers are a classic misconfiguration that causes 1038.
2. Add an index that satisfies the ORDER BY/GROUP BY so MySQL avoids filesort entirely. Verify with EXPLAIN that Using filesort disappears.
3. Reduce the data sorted: replace SELECT * with only needed columns, add a WHERE filter, and add a LIMIT so MySQL can use a bounded sort.
4. If a single legitimate large sort genuinely needs more memory, raise sort_buffer_size only for that session with SET SESSION sort_buffer_size = ..., never globally to an extreme value.
5. Ensure host memory headroom (free -m) and check container cgroup limits; raise them if the server is memory-starved.
6. Re-run the query and confirm it completes and that Sort_merge_passes is low.

Prevention and Best Practices

• Keep the global sort_buffer_size small (KB to low single-digit MB); raise it per-session only for specific known-large sorts.
• Index the columns used in ORDER BY/GROUP BY so the optimizer avoids filesort, which is faster and uses less memory.
• Avoid SELECT * for sorted queries; fewer/narrower columns mean smaller sort records.
• Add LIMIT to paginated queries so MySQL bounds the sort.
• Monitor Sort_merge_passes — a rising value means sorts are spilling to disk and queries need indexes or smaller result sets.
• Account for concurrency: total sort memory is roughly sort_buffer_size times concurrent sorting sessions; size accordingly.

Related Errors

• Out of memory (Needed N bytes) in the error log — broader server memory exhaustion, sometimes triggered alongside large sort/join buffers.
• ERROR 1041 (HY000): Out of memory — the server cannot allocate memory for a core operation.
• Using temporary; Using filesort in EXPLAIN — not an error, but the warning sign that a query is doing expensive sorting.
• ERROR 3 (HY000): Error writing file '/tmp/...' — a filesort spilling to a /tmp that ran out of disk space.

Frequently Asked Questions

Should I increase sort_buffer_size as the message says? Not blindly. If it is already large, the error is more likely caused by over-allocation. Lower it and add an index instead.

Why does a bigger buffer cause an out-of-memory error? MySQL can allocate up to the full sort_buffer_size per concurrent sort. With many sessions, a huge per-sort buffer multiplies into more memory than the host has.

Will an index always eliminate the filesort? An index matching the ORDER BY columns and direction lets MySQL read rows pre-sorted, avoiding filesort. Composite or expression sorts may still need one.

Is this an InnoDB-specific error? No. Sorting happens in the SQL layer regardless of storage engine, so 1038 can occur with any engine.

How do I find the offending query? Run EXPLAIN on suspect queries and look for Using filesort, or capture the slow query log during the failure window. For deeper triage, see more MySQL guides.