Lab: verify sorting, JOIN and spill¶
Run on an isolated v4.2.5 instance. This minimal result fixture does not establish spill activation.
DROP DATABASE IF EXISTS kernel_spill_518;
CREATE DATABASE kernel_spill_518;
USE kernel_spill_518;
CREATE TABLE t(id INT PRIMARY KEY, grp INT, payload VARCHAR(200));
INSERT INTO t VALUES(1,1,'a'),(2,1,'b'),(3,2,'c');
SET sort_spill_mem=1048576;
SELECT id FROM t ORDER BY payload DESC,id;
SELECT a.grp,COUNT(*) AS pairs FROM t a JOIN t b ON a.grp=b.grp GROUP BY a.grp ORDER BY a.grp;
SET sort_spill_mem=0;
DROP DATABASE kernel_spill_518;
Sorted IDs: 3,2,1; JOIN counts: 1→4, 2→1. The setting is restored.
Activation and acceptance¶
Generate larger data with a fixed seed, increasing rows/string widths while preserving result assertions. For sorting retain EXPLAIN ANALYZE, memory and temporary-file/spill observations. For JOIN retain physical join type, distributions and admission events. HashBuild limits belong to deployment/controller configuration; sort_spill_mem is not a JOIN switch.
Record spill success only after observing the relevant events/bytes and matching results. Preserve original disk/FD failures and check files, FDs and account terminal state after cancellation. Container RSS alone cannot establish exact accounting.
The SQL fixture supports single-node checks; actual spill/resource failures/remote cancellation need separate experiments. See execution and memory.
The minimal baseline uses a 1 MiB threshold. Extremely small values such as one byte are not guaranteed-success configurations; target builds can report spill disk-budget exhaustion. Preserve the error and check memory, disk and FD limits separately.