work_mem и спилл сортировки: quicksort, external merge и лимит на узел плана

В плане стоит Sort Method: external merge Disk: 190912kB: сортировка не поместилась в work_mem, разбилась на отсортированные серии, сбросила их во временный файл и слила обратно. Лимит работает на каждый узел плана отдельно, а не на запрос, поэтому большой work_mem на сотне соединений опаснее, чем кажется. Как читать сам план, описано в статье про EXPLAIN ANALYZE BUFFERS; здесь разбираем, что происходит под строкой Sort.

DBA / 01koddo

Одна сортировка при трёх work_mem

Опыт 1: один запрос при трёх значениях work_mem

Таблица на 5 млн строк, 288 МБ. Параллельные воркеры выключены, чтобы в плане был один узел Sort:

docker run -d --name dba-work-mem-sort-spill -e POSTGRES_PASSWORD=pg postgres:18
docker exec -it dba-work-mem-sort-spill psql -U postgres

SELECT setseed(0.42);
CREATE TABLE events AS
SELECT g AS id, (random()*1e9)::int AS k, repeat('x', 20) AS pad
FROM generate_series(1, 5000000) g;
VACUUM ANALYZE events;
SET max_parallel_workers_per_gather = 0;

Один запрос при work_mem = 4MB, 64MB и 400MB (в psql перед каждым прогоном SET work_mem = '...'):

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT * FROM events ORDER BY k;

-- work_mem = 4MB
  Sort Method: external merge  Disk: 190912kB
  Buffers: shared hit=3905 read=32898, temp read=47719 written=47808
Execution Time: 1575.523 ms
-- work_mem = 64MB
  Sort Method: external merge  Disk: 190824kB
  Buffers: shared hit=3999 read=32804, temp read=23853 written=23858
Execution Time: 1357.765 ms
-- work_mem = 400MB
  Sort Method: quicksort  Memory: 370909kB
Execution Time: 1301.312 ms

Три повтора каждого значения дали ту же очерёдность: 4 МБ медленнее всех, 64 МБ ближе к памяти. На диске лежат 190 МБ, но им хватает кэша ОС, поэтому разница во времени небольшая: холодный диск другого сервера даст другие цифры, их здесь не измеряли. А вот блоки не врут: written при 4 МБ ровно вдвое больше, чем при 64 МБ (47 808 против 23 858), хотя метод у обоих external merge. Чтобы сортировка осталась в памяти, нужно work_mem больше, чем размер данных на диске: при 256 МБ запрос ещё писал в файл (Disk: 190816kB), при 360 МБ уже шёл quicksort (Memory: 357255kB). В памяти строки занимают примерно вдвое больше, чем в потоке на диске.

Планировщик знает о лимите заранее и закладывает спилл в цену. EXPLAIN без ANALYZE для того же запроса даёт Sort (cost=882443.15..894943.77 ...) при 4 МБ и Sort (cost=643169.15..655669.77 ...) при 400 МБ; стоимость Seq Scan под ним одна и та же, 86802.47. Откуда берутся числа cost=, разобрано в статье про модель стоимости.

Опыт 2: почему при 4 МБ данные пишутся дважды

Переход от памяти к диску происходит в puttuple_common: пока кортежи помещаются в work_mem, они лежат в массиве; как только места нет, включаются ленты.

			if (state->memtupcount < state->memtupsize && !LACKMEM(state))
			{
				MemoryContextSwitchTo(oldcontext);
				return;
			}

			/*
			 * Nope; time to switch to tape-based operation.
			 */
			inittapes(state, true);

			/*
			 * Dump all tuples.
			 */
			dumptuples(state, false);

Источник: tuplesort.c, строки 1257–1271, тег REL_18_6. inittapes создаёт набор логических лент, а dumptuples сортирует накопленное в памяти и пишет его на ленту как одну серию. Сколько серий слить за один проход, решает размер памяти:

mOrder = allowedMem /
	(2 * TAPE_BUFFER_OVERHEAD + MERGE_BUFFER_SIZE);

Источник: tuplesort.c, строки 1803–1804; константы там же, строки 176–179: TAPE_BUFFER_OVERHEAD равно BLCKSZ (8192 байта), MERGE_BUFFER_SIZE равно BLCKSZ * 32, порядок ограничен снизу 6 и сверху 500. Подставляем: знаменатель 2 × 8192 + 32 × 8192 = 278 528 байт. Для 4 МБ получается 4 194 304 / 278 528 = 15,06, то есть 15 лент, для 64 МБ 67 108 864 / 278 528 = 240,9, то есть 240. Проверим по журналу сортировки: trace_sort выводит строки LOG на клиент при client_min_messages = log.

SET work_mem = '4MB';  -- затем '64MB'
SET client_min_messages = log;
SET trace_sort = on;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM events ORDER BY k;

Запрос выполнен при 4 МБ и при 64 МБ. В выводе оставлены строки про ленты и слияние, без времени CPU; число серий (90 и 6) посчитано по строкам finished writing run:

-- work_mem = 4MB
LOG:  worker -1 switching to external sort with 15 tapes
LOG:  starting merge pass of 90 input runs on 15 tapes, 260 KB of memory for each input tape
LOG:  starting merge pass of 6 input runs on 6 tapes, 658 KB of memory for each input tape
LOG:  performsort of worker -1 done (except 6-way final merge)
LOG:  external sort of worker -1 ended, 23864 disk blocks used
-- work_mem = 64MB
LOG:  worker -1 switching to external sort with 240 tapes
LOG:  starting merge pass of 6 input runs on 6 tapes, 10600 KB of memory for each input tape
LOG:  performsort of worker -1 done (except 6-way final merge)
LOG:  external sort of worker -1 ended, 23853 disk blocks used

Картина совпадает с формулой. При 4 МБ на 15 лент приходится 90 серий, одним слиянием их не прочитать, и первый проход (90 input runs) сливает их в 6 новых серий и записывает заново; затем финальное 6-way слияние отдаёт строки наверх без записи. Отсюда written=47808 против written=23858: каждый байт записан дважды. При 64 МБ серий всего 6, лент 240, и хватает одного финального слияния. disk blocks used у обоих почти равно размеру данных (23 864 и 23 853 блока по 8 КБ), потому что logtape.c переиспользует место прочитанных серий внутри одного файла:

 * We can work around this problem by recognizing that any one tape
 * dataset (with the possible exception of the final output) is written
 * and read exactly once in a perfectly sequential manner.  Therefore,
 * a datum once read will not be required again, and we can recycle its
 * space for use by the new tape dataset(s) being generated.  In this way,
 * the total space usage is essentially just the actual data volume, plus
 * insignificant bookkeeping and start/stop overhead.

Источник: logtape.c, строки 14–20. Поэтому размер временного файла (Disk: 190912kB) не показывает, сколько раз данные ходили на диск: это видно только по temp written в BUFFERS.

Опыт 3: как узнать масштаб спилла и почему лимит на узел

Счётчики базы копят временные файлы всех запросов. Сброс статистики, один спилл при 4 МБ, чтение:

SELECT pg_stat_reset();
SET work_mem = '4MB';
SET log_temp_files = 0;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF, SUMMARY OFF) SELECT * FROM events ORDER BY k;
SELECT temp_files, temp_bytes FROM pg_stat_database WHERE datname = 'postgres';

 temp_files | temp_bytes
------------+------------
          1 |  195493888

В журнале сервера при log_temp_files = 0 появилась строка с тем же числом: temporary file: path "base/pgsql_tmp/pgsql_tmp200.0", size 195493888. Это 190 912 КБ, то же значение, что в Disk: плана. В production значение log_temp_files задают порогом в килобайтах (например 10MB), чтобы в журнал попадали только крупные спиллы.

Теперь о лимите. Два окна с разной сортировкой в одном запросе дают два узла Sort:

SET work_mem = '400MB';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF, SUMMARY OFF)
SELECT sum(a), sum(b)
FROM (SELECT row_number() OVER (ORDER BY k) a, rank() OVER (ORDER BY id DESC) b FROM events) s;

 Aggregate (actual rows=1.00 loops=1)
   ->  WindowAgg (actual rows=5000000.00 loops=1)
         ->  Sort (actual rows=5000000.00 loops=1)
               Sort Key: events.k
               Sort Method: quicksort  Memory: 331793kB
               ->  WindowAgg (actual rows=5000000.00 loops=1)
                     ->  Sort (actual rows=5000000.00 loops=1)
                           Sort Key: events.id DESC
                           Sort Method: quicksort  Memory: 313796kB
                           ->  Seq Scan on events (actual rows=5000000.00 loops=1)

В выводе опущены строки Window и Storage. Каждой сортировке разрешено по 400 МБ, и обе остались в памяти: 331 793 + 313 796 = 645 589 кБ, около 630 МБ одним запросом одного соединения. Если на 200 соединениях одновременно идут такие запросы, получается около 123 ГБ, хотя в конфиге написано только work_mem = 400MB. Документация об этом предупреждает: сортируют ORDER BY, DISTINCT и merge join, а хеш-узлы получают work_mem × hash_mem_multiplier, по умолчанию 2.0 (SHOW hash_mem_multiplier в контейнере вернул 2).

Узел с границей может вообще не сортировать всё. Тот же ORDER BY k с LIMIT 10 при work_mem = 4MB показал Sort Method: top-N heapsort Memory: 26kB: в куче держатся только десять лучших строк, спилла нет.

Что из этого следует

• Лимит считают на узел и на соединение. Безопасный work_mem = доступная память / (соединения × число сортировок и хешей в типичном запросе), а не доступная память / соединения.
• Больше памяти полезно ступенями. От 4 МБ до 64 МБ число проходов упало с двух до одного, а от 64 МБ до 400 МБ время почти не изменилось. Смотри, когда исчезает второй проход, а не «чем больше, тем лучше».
• Размер Disk: не равен объёму записи. Реальную запись показывает temp written в EXPLAIN (ANALYZE, BUFFERS).
• Точечная настройка надёжнее глобальной. Тяжёлому отчёту SET LOCAL work_mem = '256MB' внутри транзакции, остальным соединениям общее значение по умолчанию.
• Спилл виден в мониторинге. Рост temp_bytes в pg_stat_database и строки log_temp_files показывают, какие запросы не помещаются, ещё до жалоб на скорость.

Чек-лист

1. Для медленного ORDER BY, DISTINCT или merge join прочитай EXPLAIN (ANALYZE, BUFFERS): Sort Method: external merge и temp written означают спилл.
2. Включи log_temp_files с порогом (например 10MB) и следи за temp_files и temp_bytes в pg_stat_database.
3. Прежде чем поднимать work_mem в postgresql.conf, умножь его на число соединений и число сортировок в запросе; для отчёта используй SET LOCAL.
4. Если спилл идёт в хеш-узле, смотри hash_mem_multiplier, а не только work_mem; как хеш делится на партии, разобрано в статье про алгоритмы соединения.
5. Контейнер опыта убирается командой docker rm -f -v dba-work-mem-sort-spill.