PostgreSQL 9.6 параллельные запросы: 3-4x на аналитике и подводные камни настройки

PostgreSQL 9.6 вышел в сентябре 2016-го, и главной темой пресс-релиза были параллельные запросы. Мы несколько месяцев смотрели на это с интересом, но в продакшн DWH так просто не идут: сначала надо понять что именно работает, что нет, и где у новой механики края. Теперь можем рассказать.

Контекст: зачем нам это вообще

У нас есть DWH-проекты на сопровождении где PostgreSQL живёт не как OLTP-база, а как аналитическое хранилище - витрины данных, ETL-агрегаты, отчётность. Запросы там вполне честные: несколько таблиц на десятки миллионов строк, GROUP BY на широких колонках, JOIN двух крупных фактовых таблиц. Классика, которую PostgreSQL до 9.6 честно выполнял последовательно на одном ядре.

На машинах под этот класс задач обычно 8-16 ядер, из которых в пике грузится одно-два. Что происходит с остальными - вопрос риторический, и PostgreSQL 9.6 наконец начал на него отвечать.

Что появилось в 9.6

Параллельное выполнение строилось поэтапно. В 9.6 работает:

• Параллельный sequential scan. Несколько worker-процессов читают один heap параллельно, лидер-процесс собирает строки. Самый распространённый кейс для аналитических запросов.
• Hash join с параллельным scan. Если оптимизатор выбирает hash join, workers параллельно читают одну из сторон (inner scan), лидер строит хэш-таблицу и выполняет соединение. Параллельное построение хэш-таблицы обеими сторонами - не в 9.6.
• Параллельный nested loop. Тоже есть, но срабатывает реже - зависит от плана.

Что в 9.6 ещё не параллелится: сортировка (ORDER BY без индекса), GROUP BY с агрегацией в ряде случаев, и index scan. Это важно - к этому вернёмся.

Отдельно полезная вещь из того же релиза: synchronous_commit теперь можно выставлять на уровне отдельной транзакции (SET LOCAL synchronous_commit = off). Для ETL-загрузки где потеря пачки при сбое не критична - это даёт заметный прирост скорости записи без изменения глобальной настройки базы.

Настройка: параметры которые реально влияют

Три параметра определяют поведение параллельных запросов:

max_parallel_workers_per_gather - сколько worker-процессов запускается на одну Gather-ноду в плане. По умолчанию в 9.6 - 0, то есть параллелизм выключен. Это первое что надо поменять. Мы ставим 2-4 в зависимости от числа ядер и ожидаемой конкурентности.

max_parallel_workers - потолок суммарного числа workers на весь инстанс. Отсюда вытекает нетривиальная арифметика: если одновременно выполняются пять аналитических запросов и каждый хочет четыре worker'а - нужно 20 process slots. Лучше считать заранее, а не в момент когда отчётная система начинает тормозить.

parallel_tuple_cost и parallel_setup_cost - затраты на запуск параллелизма в модели оптимизатора. Значения по умолчанию (0.1 и 1000 соответственно) консервативные: оптимизатор избегает параллелизма на небольших запросах. Для DWH-нагрузки parallel_setup_cost = 500 и parallel_tuple_cost = 0.05 дают более агрессивное использование workers. Трогаем аккуратно - изменение влияет на все запросы в базе.

Что получилось на практике

На аналитическом запросе с full scan двух таблиц по 40 млн строк и hash join между ними - с max_parallel_workers_per_gather = 4 план переключился с Seq Scan -> Hash -> Hash Join (последовательно) на Gather -> Hash Join -> Parallel Seq Scan. Время выполнения упало примерно в 3-4 раза. Не на каждом запросе, но на тяжёлых - стабильно.

EXPLAIN ANALYZE с BUFFERS показывает в плане ноды Parallel Seq Scan и Gather - по ним видно сколько workers реально участвовали. Если workers: 0 - параллелизм не запустился, смотрим почему: таблица слишком маленькая, запрос содержит non-parallelizable функцию, или worker pool исчерпан.

Неочевидные ограничения

Несколько вещей которые выяснились не из документации, а в процессе:

Функции по умолчанию - PARALLEL UNSAFE. Любая пользовательская функция в PostgreSQL 9.6 маркирована PARALLEL UNSAFE, пока явно не указано иное. Это значит: запрос, вызывающий любую UDF, не будет параллелиться - даже если функция тривиальная. Пришлось пройтись по ETL-функциям и проставить PARALLEL SAFE там где это действительно безопасно.

Параллелизм и LIMIT. Если запрос содержит LIMIT, оптимизатор часто отказывается от параллельного плана - он считает что найти нужные строки быстрее без overhead на координацию workers. Для аналитики, где LIMIT часто добавляется «на всякий случай» в интерфейсе - это неочевидная причина пропавшего ускорения.

max_worker_processes - глобальный лимит. Параллельные workers берутся из max_worker_processes (default 8). На инстансе где уже работают pg_logical, autovacuum workers и background workers расширений - реальный pool меньше чем кажется. Смотрим pg_stat_activity и pg_stat_bgwriter прежде чем поднимать max_parallel_workers.

Параллелизм не помогает с bottleneck в I/O. Если bottleneck - диск (и это видно по blks_hit / blks_read в EXPLAIN BUFFERS), параллельный scan только ускоряет конкуренцию за I/O. Выигрыш будет скромнее ожидаемого. На наших проектах с SSD-хранилищем под WAL и рабочим set'ом в shared_buffers - эффект был хорошим. На SATA-ротации с горячими данными вне кеша - скромнее.

Где сейчас

9.6 в продакшне на DWH с декабря - держится стабильно. synchronous_commit = off на уровне ETL-загрузки тоже прижился: скорость записи выросла, а потеря данных при сбое в этом контексте не критична (следующий ETL-прогон восполнит).

Параллельные запросы - это не «включил и работает». Это плановая работа: аудит UDF на parallel safety, подбор parallel_setup_cost под конкретную нагрузку, мониторинг pool workers чтобы они не кончались в пиках. Но когда оно настроено правильно - на аналитических запросах разница заметна без бенчмарков, просто по времени отклика в BI-инструменте.