sort_spill_mem

The sort_spill_mem system variable sets the memory threshold (in bytes) for sort operations. When the memory consumed by a sort operation exceeds this value, MatrixOne spills intermediate sort data to disk to avoid out-of-memory conditions. A value of 0 disables sort spilling, keeping all sort data in memory. Increasing this value allows larger sort operations to run without spilling, at the cost of higher memory consumption.

The sort_spill_mem system variable controls the memory limit for sort spill-to-disk behavior during query execution. When a query sorts a large dataset that exceeds available memory, setting sort_spill_mem to a positive value enables intermediate data to be written to temporary disk storage, preventing the query from failing due to insufficient memory.

Syntax

SET GLOBAL sort_spill_mem = value; SET SESSION sort_spill_mem = value;

Query the current value:

SHOW VARIABLES LIKE ‘sort_spill_mem’; SELECT @@sort_spill_mem; SELECT @@global.sort_spill_mem;

Arguments

Property	Value
Variable Type	int
Scope	Both
Dynamic	Yes
Default Value	0
Optional Value	0 to 1099511627776 (1 TiB)

Examples

DROP DATABASE IF EXISTS sort_spill_demo;
CREATE DATABASE sort_spill_demo;
USE sort_spill_demo;

CREATE TABLE t1(id INT, grp INT);
INSERT INTO t1 VALUES (1, 40), (2, 10), (3, 20), (4, 10), (5, 30), (6, 20);

SHOW VARIABLES LIKE 'sort_spill_mem';

SET @@sort_spill_mem = 1;
SHOW VARIABLES LIKE 'sort_spill_mem';

SET @@max_dop = 1;

SELECT id, grp FROM t1 WHERE id <= 6 ORDER BY grp, id;

SET @@sort_spill_mem = 0;
SHOW VARIABLES LIKE 'sort_spill_mem';

DROP TABLE t1;
DROP DATABASE sort_spill_demo;

Constraints

• sort_spill_mem is set in bytes. The value 0 (default) disables spill-to-disk; sort operations will fail with an out-of-memory error if they exceed available memory.

• Spilling incurs I/O overhead; queries may run slower when spill is active compared to in-memory-only execution.

• The effective spill behavior also depends on the max_dop setting and the available temporary storage space.