Что такое work_mem и для чего данный параметр используется в PostgreSQL?

work_mem - это важный параметр конфигурации в PostgreSQL, который определяет лимит, т.е. максимальный размер оперативной памяти (при нехватке может использоваться диск), выделенной для выполнения таких операций, как:

• сортировка, при выполнении таких операций, к примеру как: ORDER BY, DISTINCT, GROUP BY.
• объединения данных (JOINS) и хеширования для построения хеш-таблиц в оперативной памяти.
• операций над множествами, как: "объединение, пересечение и вычитание", т.е. речь про UNION, INTERSECT и EXCEPT.
• построение битовых карт, при bitmap методе сканирования, речь про exact/lossy heap blocks, подробнее про данный момент можете почитать мою другую статью: https://dzen.ru/a/ZoAs76w0DksN7LQg (секция: есть ли взаимосвязь work_mem с работой bitmap сканирования и может ли это негативно повлиять на работу самого процесса сканирования?)

Этот параметр влияет на эффективность выполнения запросов и общую производительность базы данных.

Важно подметить, что work_mem выделяется на каждую операцию, а не на каждую сессию PostgreSQL. Это важная деталь, т.к. один SQL-запрос может выполнять несколько операций сортировок или операций по объединению, каждая из которых будет потреблять свою долю памяти.

Что это именно так, можно убедиться при помощи следующего SQL-скрипта. Создадим тестовую таблицу и вставим в неё миллион записей:

к сожалению, Дзен перестал поддерживать функционал вставки кода, на момент написания статьи, поэтому приходится вставлять картинку, а снизу ссылку на код в GitHub Gist

Далее просто ради убежденности проверим, что таблица заполнилась:

И выполним такой SQL-запрос:

План выполнения покажет, сколько памяти было использовано каждой операцией сортировки:

вывод плана по исполненному SQL-запросу

Давайте разберем вывод плана и более детально проанализируем его, чтобы понять, что work_mem выделяется на каждую операцию.

План выполнения показывает две операции сортировки, каждая из которых использует метод "external merge" и запись временных результатов на диск (т.е. установленного значения work_mem не хватило). Давайте посмотрим на детали этих операций:

1. Основная сортировка по id:

Sort (cost=262750.23..264833.56 rows=833334 width=4) (actual time=1002.945..1061.759 rows=1000000 loops=1)

Sort Key: subquery.id

Sort Method: external merge Disk: 11768kB

Sort Method: external merge Disk: 11768kB: указывает, что операция сортировки использовала 11768kB дискового пространства, потому что выделенной памяти из work_mem было недостаточно для выполнения операции в памяти.

2. Сортировка в подзапросе по random_text:

Sort (cost=62839.48..63881.15 rows=416667 width=37) (actual time=378.818..509.315 rows=333333 loops=3)

Sort Key: test_table.random_text

Sort Method: external merge Disk: 14840kB

Sort Method: external merge Disk: 14840kB: вторая сортировка, выполненная внутри подзапроса, использовала 14840kB дискового пространства.

Что это означает? Подтверждение использования work_mem для каждой операции.

Каждая из этих сортировок использовала свою собственную память из work_mem. Это подтверждается следующим:

1. Разные размеры дискового пространства: операции сортировки используют разное количество дискового пространства (11768kB и 14840kB). Если бы work_mem выделялся на одну сессию, эти значения были бы одинаковыми или операция использовала бы общую память.
2. Разные временные файлы на диске: каждая операция сортировки создала "свои" временные файлы на диске, что подтверждает выделение памяти на каждую операцию - отдельно.

Итого план выполнения показывает, что каждая операция сортировки использует свое количество памяти, определенное значением work_mem. Таким образом, можно заключить что work_mem действительно выделяется на каждую операцию, а не на всю сессию в PostgreSQL.

А как сделать так, чтобы временные данные не сохранялись на диск, а всё происходило в ОЗУ (оперативной памяти)?

Для такого действия можно повысить значение work_mem. Попробуем сделать это ради теста на основании той же тестовой таблицы, созданной ранее.

Повысим значение work_mem до 128Мб (чисто ради теста, в реальной продуктивной среде так делать не стоит, о чем расскажу далее).

к сожалению, Дзен перестал поддерживать функционал вставки кода, на момент написания статьи, поэтому приходится вставлять картинку, а снизу ссылку на код в GitHub Gist

И посмотрим, как изменился план запроса:

новый вывод статистики по плану запроса, после повышения значения work_mem

Что поменялось?

Поменялся метод, теперь вместо external merge и использования диска, у нас выводится:

"Sort Method: quicksort Memory"

Это означает, что:

• данные отсортировались и поместились полностью в оперативную память, и сортировка полностью выполнилась именно в ней. Поскольку мы повысили work_mem до 128 Мб, то две операции сортировок уместились в утилизацию памяти (24577 Кб или ~25 Мб, и 87077 Кб или ~87 Мб, что дает в сумме: 25+87=112 Мб).
• был использован алгоритм быстрой сортировки (quick sort).

Теперь, вспомним тему почему нельзя менять так значение для параметра work_mem?

• во-первых, оперативная память используемая с work_mem не относится к типу "общей памяти", как shared_buffers к примеру.
• во-вторых, work_mem связан с параллельным выполнением запросов, которое взаимосвязано с количеством workers (фоновых процессов в PostgreSQL), управление которыми задаются через параметры: max_worker_processes, max_parallel_workers, max_parallel_workers_per_gather, max_parallel_maintenance_workers.
• в-третьих, неправильно рассчитав необходимый объем для work_mem можно попасть в ситуацию задержек по исполнению запросов с ростом объема данных на РСУБД в боевой среде, как и с ростом количества клиентских приложений, которые будут использовать данную РСУБД с измененным work_mem.

Правильное значение данного параметра зависит от многих факторов, к примеру как:

• выделенных технических мощностей под PostgreSQL (ЦПУ, ОЗУ), корректных настроек системных параметров.
• соблюдения пропорций между различными параметрами самого PostgreSQL (shared_buffers, сам work_mem, maintenance_work_mem и других).
• количества конкурентных и активных клиентских подключений к вашему PostgreSQL-сервису.
• частоты и сложности исполняемых SQL-запросов.

Поэтому, как видите достаточно много параметров необходимо учитывать для корректной настройки work_mem параметра. Приведенный пример с изменением work_mem, повторюсь, был показан сугубо для демонстрационных целей. Корректное же значение в реальной жизни зависит от ряда фактов.

Тем не менее можно выделить общую формулу, которую можно использовать, как некую "стартовую точку", если вам все же интересно знать, как подсчитать данное значение:

work_mem = ( (выдленное ОЗУ на PostgreSQL-хосте) * 0.8 - shared_buffers) / (среднее кол-во активных клиентских подключений к РСУБД)

Как один из методов оптимизаций выделю, что можно переопределять work_mem на уровне транзакции. Приведу пример для наглядности:

1. Сначала выодим значение work_mem.

SHOW work_mem;

2. Описываем анонимный plpgsql-блок, в рамках которого будет исполнена транзакция, и в рамках которого изменим значение для work_mem и выведем его через RAISE NOTICE:

к сожалению, Дзен перестал поддерживать функционал вставки кода, на момент написания статьи, поэтому приходится вставлять картинку, а снизу ссылку на код в GitHub Gist

3. Снова выведем значение work_mem, после выполнения анонимного plpgsql-блока:

SHOW work_mem;

Должен получиться следующий вывод, если вы не меняли значение по умолчанию для work_mem из основного конфигурационного файла PostgreSQL (оно, как раз, равно 4Мб):

• сначала: 4MB.
• потом, вывод RAISE NOTICE: "Новое значение для work_mem: 128MB".
• после исполнения анонимного plpgsql-блока: опять 4MB.

Итого, как выглядит логика работы PostgreSQL в отношении work_mem, на high-level уровне?

Оффтопик: казалось бы можно было бы с этого начать статью :) Но мне хотелось сразу дать какие-то "приземленные" примеры, чтобы заинтересовать, поэтому такой порядок повествования.

Если описать логику работы PostgreSQL, на high-level уровне, по отношению к work_mem параметру, то это выглядит так в "общих чертах":

1. Процесс-координатор (главный процесс PostgreSQL) определяет, сколько памяти требуется для текущей операции. Процесс-координатор оценивает количество памяти, необходимое для выполнения операции, на основе доступного значения work_mem и объема данных.
2. Workers (фоновые-дочерние процессы) запрашивают необходимый объем work_mem у координатора. В случае параллельных операций, дочерние процессы (workers) запрашивают свою отдельную долю памяти. Каждая операция дочерних процессов, просто, использует work_mem в пределах своего процесса.
3. Координатор распределяет доступную оперативную память для workers, учитывая лимиты параметров РСУБД. Здесь стоит отметить, что в многопоточном режиме, work_mem применяется к каждому рабочему процессу - индивидуально. Это означает, что суммарное потребление ресурсов может быть намного выше, чем для любого отдельного процесса.
4. Если work_mem - недостаточно, workers начинают использовать временные файлы на диске. Как было показано в примере с SQL-кодом (ранее с выводом плана запроса), то если для операции сортировки или хеширования - недостаточно оперативной памяти, начинается использование временных файлов на диске для выполнения всей операции.
5. По завершении операции, высвобождается память ранее используемая workers. Так как, work_mem не выделяется из общего пула, а является лимитом памяти для каждой операции, то по завершении операции - память просто высвобождается для использования другими операциями в PostgreSQL.

Постскриптум:

• при написании статьи использовалась следующая версия PostgreSQL v16.3
• к сожалению, Дзен перестал поддерживать функционал вставки исходного программного кода с syntax highlighting, на момент написания данной статьи. Про схожие проблемы рассказывают и другие Дзен-авторы: https://dzen.ru/a/XtUGaNtXXh9qY-gb поэтому вместо исходного кода, Вы видите комбинацию из картинки и ссылки на SQL-код в GitHub Gist. Прошу прощения за неудобства!