Этой статьей мы начинаем цикл, посвященный различным настройкам по оперативной памяти в PostgreSQL. Тема непростая, даже сложная. Понятной информации по ней крайне мало (по состоянию на октябрь 2024). Поэтому будем разбираться, шаг за шагом, вдумчиво и, как принято у нас в блоге, подкреплять все выводы исследованиями и картиной из программы мониторинга PERFEXPERT (версия для PG).

Почему так сложно и непонятно

Здесь стоит обратиться для сравнения к MS SQL Server, как одной из наиболее распространенной у нас СУБД для крупных бизнес-систем.

Какие в MS есть настройки в части памяти? В общем‑то их немного — есть min server memory, есть max server memory, min memory per query и, пожалуй, из основных всё. Есть ещё настройки базы tempDB, но, по факту, они относятся больше к работе с диском, т.к. все временные таблицы SQL Server хранит в отдельных файлах [tempDB], которые никак не кэшируются. В остальном SQL Server представляет собой некий черный ящик, который выделяет запросам динамически ту или иную область памяти из buffer pool, что‑то кэширует, что‑то читает с диска и т. д. по какой‑то логике, известной лишь ему и небольшой прослойке специалистов. То есть, что там под капотом мало кто знает и влиять на эти процессы не может. Не могу сказать, что это хорошо или плохо, но за десятки лет админы научились неплохо контролировать ситуацию с памятью через счетчики производительности типа Page Life Expectancy или Buffer cache hit ratio, а также других метрик, указывающих на проблемы с памятью (например, анализ трасс запросов может многое рассказать и дополнить счетчики производительности).

Главное различие в управлении памятью между MS SQL и PostgreSQL заключается в том, что MS SQL автоматически и самостоятельно перераспределяет память между потребителями (Buffer pool, Memory objects и т.д.). При этом пользователь устанавливает только общий предел потребления ресурсов, не влияя на перераспределение памяти внутри.

А вот в PostgreSQL всё по‑другому. Во‑первых, настроек СУБД, связанных с оперативной памятью гораздо больше, и в отличии от MS SQL необходимо заранее планировать распределение памяти для различных сценариев нагрузки, поскольку отдельные виды памяти (shared_buffers, maintenance_work_mem и т.д.) могут динамически расширяться и перекрываться друг с другом, не имея фиксированного верхнего предела. А во‑вторых, средств мониторинга, которые бы указывали на проблемы с памятью практически нет. Точнее не со всей памятью, а как раз по тем видам памяти, по которым существуют настройки. Есть расширения pg_stat_statement, pg_buffercache и подобные, но они все предъявляют к потребителю большие требования к квалификации, что невозможно достичь всем клиентам, даже крупным.

Сразу скажу, что здесь вы не найдете перечня настроек PG со значениями, которые вы заберете себе и у вас сразу все полетит аки ракета. Нет никакой серебряной пули. Но я покажу причинно‑следственные связи, ход мысли, с претензией на методику. За любой цифрой в ваших настройках должен стоять расчет, базирующийся на вашей же статистике.

В рамках одной статьи этот материал не поместится, поэтому будет цикл статей. И возможно, в конце он оформится в полноценную методику. А пока это больше изыскания.

Начнём. Крупными мазками.

Использование оперативной памяти процессами PostgreSQL разделим укрупненно на четыре части и представим в виде круговой диаграммы. Далее будем называть ее «пирог памяти». Весь пирог – это тот объем памяти, который отдан одному инстансу PostgreSQL, а его куски – shared_buffers, maintenance_work_mem, temp_buffers и work_mem и есть основные потребители этой памяти. На картинке ниже условно изображен наш пирог. Цифр на нем нет. Специально, т.к., во-первых, некоторые параметры задаются на общий объем, а некоторые параметры задаются на сессию – объем динамически меняется. А, во-вторых, всё нужно считать, чем мы и будем заниматься на протяжении всего цикла статей.

Вспомним, что есть что.

shared_buffers — параметр, который устанавливает, сколько выделенной памяти будет использоваться для кэширования данных. Обычно под shared_buffers отдают львиную часть всей памяти — от 25 до 50%. Если сравнивать с MS SQL Server, то это близкий аналог buffer pool, размер которого ограничивается параметром max server memory.

temp_buffers — параметр, который задает максимальный объем памяти, выделяемый для временных таблиц (буферов) в каждом сеансе. т. е. временные таблицы, в отличии от MS SQL, могут кэшироваться в памяти, что позволяет избегать чтения с диска при работе с ними. Особенно это актуально для 1С:Предприятия, где очень интенсивно используются временные таблицы. При этом каждая таблица всегда имеет копию на диске, не важно хватило ли ей памяти в temp_buffers или нет.

maintenance_work_mem — параметр, который устанавливает объём оперативной памяти, выделенный для выполнения операций обслуживания базы данных: VACUUM, CREATE INDEX и ALTER TABLE, ADD FOREIGN KEY. Для расчета нужно понимать количество одновременных потоков (задач) обслуживания.

autovacuum_work_mem — параметр, который задает максимальный объём памяти, который будет использовать каждый рабочий процесс автоочистки. Входит в тот же кусок пирога, что и maintenance_work_mem и по умолчанию равен «-1», т.е. объём определяется значением maintenance_work_mem.

work_mem — параметр, который задаёт базовый максимальный объём памяти, который будет использоваться в рамках одного рабочего процесса (сессии) во внутренних операциях типа соединений, сортировки, группировки и т. п. при обработке запросов, прежде чем будут задействованы временные файлы на диске.

Поскольку мы за системный подход, то настоятельно рекомендуем администратору, насколько это возможно, оценивать диапазон потребления памяти в каждом куске указанного пирога. Без грамотной оценки есть большая вероятность столкнуться с недостатком памяти в определенные моменты (хотя общего объема памяти может быть с избытком), свопированием и ошибками в логе PostgreSQL. 

Контроль за работой памяти на сервере PostgreSQL. Общие сведения

Счетчиков, которые бы показали, что у вас все хорошо или всё плохо готовых нет. Поэтому эти знания нужно собирать по крупицам, анализируя профиль нагрузки системы в целом.

Профиль нагрузки — это собирательный образ, будем понимать под ним некую комбинацию статистических данных из:

1. Оценки популярных групп запросов с помощью трассировщика по

   — популярности;
   — использованию ресурсов памяти/диска (логическим и физическим чтениям);
   — использованию ресурсов CPU.

2. Системных и дополнительных счетчиков в мониторинге Perfexpert. Например, таких как Очереди к дискам, Cache hit ratio, Temp data total size, Available Physical Memory, Max temp table size, Swap total и т. д.

Шаг 1. Установка начальных значений параметров памяти

Начинать с чего‑то надо и тут, пожалуй, можно воспользоваться многочисленными рекомендациями в интернетах. А дальше наблюдаем за системой и меняем значения. Но вдумчиво! Статья, конечно, нацелена на проблемы высоконагруженных систем, обладающих немалыми аппаратными ресурсами. Но логика применима абсолютно для любых.

shared_buffers — обычно рекомендуют устанавливать 30% от общего объема. Как точка старта вполне подходяще.

maintenance_work_mem — по умолчанию равен 64 Мб. Поскольку эта память используется для служебных операций типа обновления статистики, создания индексов, VACUUM, то 1С рекомендует выделять под эти операции объем памяти, соразмерным с физическим размером самого большого индекса. Для старта хорошо.

autovacuum_work_mem — дополняет предыдущий параметр. Задаёт максимальный объём памяти, который будет использовать каждый рабочий процесс автоочистки. По умолчанию равен «-1» — объём определяется значением maintenance_work_mem. Но на практике его нужно менять.

temp_buffers — по умолчанию равен 8 Мб. Для 1С‑систем, которые очень активно работают с временными таблицами, этого будет, скорее всего, мало. Хотя все зависит от общего объема пямяти и общего количества сессий. Надо смотреть. Для больших ИТ‑систем (500+ пользователей), где обычно все хорошо с ресурсами, можно смело начинать со 128 Мб или даже с 256 Мб. Но, повторюсь, всё индивидуально — нужно отслеживать потребление. Об этом будет ниже.

work_mem — по умолчанию равен 4 Мб. Для первой итерации(!) можно воспользоваться формулой от вендора 1С: разделить объём доступной памяти (физическая память минус объём, занятый под другие программы и под совместно используемые страницы shared_buffers) на максимальное число одновременно используемых активных соединений.

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

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

Шаг 2. Выбор shared_buffers

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

Для этого анализируем профиль нагрузки в части использования дисковой подсистемы для выполнения запросов. Если эта доля не велика, то значит бОльшая часть данных «влезает» в отведенный кэш и PG забирает их из кэша, а не с диска. То есть все хорошо. В противном случае будет происходить много чтений с диска, что говорит о том, что серверу PostgreSQL часто не хватает выделенного объёма оперативной памяти (shared_buffers), при выполнении запросов он вынужден обращаться к более медленной (по сравнению с ОЗУ) дисковой подсистеме, тем самым провоцируя дополнительную нагрузку на диски и оказывая негативное влияние на длительность выполнения операций пользователями.

Логика вроде простая. Теперь как это понять?

Обратимся к мониторингу Perfexpert. В первую очередь смотрим на график счетчика Cache hit ratio. Счётчик, показывающий вероятностное попадание данных в кэш. Это фактически соотношение между количеством данных, считанных из памяти с количеством данных, считанных с физического носителя. В идеале график этого счетчика должен представлять условно горизонтальную линию со значением близким к 100% в течение рабочего дня. Это значит, что все данные находятся в кэше и система почти не обращается к диску. Если же есть провалы, значит кэша (памяти) не хватает, происходит выдавливание из него данных, что, в свою очередь, приводит к чтению данных с диска. Возьмем в качестве примера один календарный день в системе:

Зеленый график и есть Cache hit ratio. Видно, что он далеко не всегда стремится к 100%, есть глубокие провалы. То есть признак нехватки памяти на лицо.

Дополнительно имеет смысл проанализировать трассу READS с тяжелыми запросами за выбранный период. Подробнее о трассировке в PostgreSQL см. в другой нашей статье Мониторинг PostgreSQL. Новые возможности анализа производительности 1С и других систем. Часть 2: Трассировка. Под тяжелыми запросами понимаются те, которые выполняли более 50 тыс. логических чтений. Так можно выявить группы запросов, которые чаще других «выгрызают» кэш, а также группы запросов, которые чаще других обращаются к диску – делают физические чтения. Их оптимизация может улучшить картину с потреблением памяти.

Если отсортировать запросы по доле физических чтений, то можно выделить группу ТОП5-10 запросов, которые больше остальных обращаются к диску. Причем в другие дни ситуация будет схожая и останутся буквально 3-4 группы запросов, которые постоянно обращаются именно к жесткому диску.

На рисунке выше статистика приведена за 1 день, но если поток запросов большой, то достаточно пары часов.

Например, за два часа суммарно по запросам с диска считано 6 648 372 блоков (блок = 8192 байт). Это могут быть либо блоки данных, либо блоки временных таблиц.

Таким образом, дисковая система за 2 часа (если в среднем посчитать) потратила на чтения:

6 648 372 * 8 192 = 50,72 Гб

Или средний поток будет: 50,72 Гб/120 минут/60 секунд = 7,2 Мб/с

В Linux, конечно же, есть механизм дискового кэширования, в котором ОС использует ту часть оперативной памяти, которая не нужна в данный момент приложениям. Но даже в этом случае, когда PG обращается за данными не к самому диску, а к кэшу Linux, значительная часть этого потока ложится на диски.

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

Итого в контексте нашей первоначальной задачи. Если счетчик Cache hit ratio имеет тенденцию в течении рабочего дня часто опускаться ниже 80%, то значит памяти не хватает и нужно увеличить shared_buffers. Параллельно оптимизируем тяжелые запросы в ТОПе по физическим чтения с диска. Особенно это актуально для тех систем, где ресурсы памяти ограничены и двигаться в сторону увеличения нет возможности.

Общий объем памяти на указанном сервере СУБД: 256 Гб

Текущее значение shared_buffers: 40 Гб.

Учитывая вышеописанную ситуацию, имеет смысл увеличить его на треть, до 52 Гб и понаблюдать за изменениями в счетчике Cache hit ratio и трассах. Таких итераций может быть несколько.

Шаг 3. Выбор maintenance_work_mem и autovacuum_work_mem

Рекомендация 1С — это установить maintenance_work_mem в размер самого большого индекса. С одной стороны — это хорошо. Индекс обычно перестраивается в одном потоке и если весь индекс помещается в память, то он завершит перестроение гораздо быстрее — ему не придется работать с данными порциями с диска. Но чем больше база данных, тем больше в ней таблицы и индексы. Бывают случаи, что некоторые индексы занимают десятки и сотни гигабайт и вообще могут не поместиться в память. Поэтому здесь нужно быть осторожным, чтобы не уйти в своп и не положить систему.

В мониторинге можно быстро оценить размеры таблиц и индексов и принять решение о необходимости для maintenance_work_mem в одной сессии отдать весь этот объем.

Обычно та же реструктуризация таблиц происходит в одном потоке, плюс в регламентное окно (забываем пока про динамическое обновление 1С), то есть другой нагрузки на систему и нет. Допустим, указываем maintenance_work_mem  = 10 Гб, как на картинке выше. Вероятность того, что одновременно несколько потоков будет отгрызать по 10 Гб не так велика, т.к. таких потоков вроде и нет. Но есть несколько «но», о которых необходимо помнить:

1. Все настройки производятся на инстанс. Если на сервере развернуто несколько инстансов, то нужно делить и распределять «пироги» между собой. Задача усложняется.

2. Очень часто на инстансе развернуто несколько баз данных, которые не относятся к основной — вплоть до тестовых. То есть может возникнуть ситуация, когда в двух и более базах наложатся по времени процессы по перестроению индексов. А значит ситуация с maintenance_work_mem может выйти из‑под контроля.

3. Многие администраторы используют принудительную многопоточность при регламентном пересчете индексов и статистик. Нужно помнить, что наши 10 Гб будут в пределе помножаться на это количество потоков. Это следует контролировать.

Но наиболее частой обслуживающей операцией в течение дня является все же
autovacuum: VACUUM или autovacuum: VACUUM ANALYZE.

Для автовакуумов есть отдельная настройка autovacuum_work_mem, чтобы их потребление памяти не оказывало влияние на остальные служебные процессы. И более того, для сбора идентификаторов мёртвых кортежей VACUUM может использовать не более 1GB памяти.

Для этой операции потоков может быть несколько (по умолчанию 5). В PERFEXPERT очень удобно смотреть их кол-во и потребление памяти в любой момент времени и сразу принимать решение:

Рассмотрим теперь потребление памяти автовакуумом на протяжении недели, с разными значениями autovacuum_work_mem

Неделя 1

maintance_work_mem = 65 536 блоков = 512 Мб

autovacuum_work_mem = -1

Потребление памяти каждого из пяти автовакуумов на протяжении недели очень редко превышает 70 Мб, обычно ниже). Вот срезы по трем произвольно выбранным дням:

Поскольку autovacuum_work_mem = -1, то он может брать в пределе памяти себе столько, сколько указано в maintance _work_mem = 65 536 блоков = 512 Мб. Но судя по значениям потребления физической памяти у него как будто есть какая-то граница, через которую он не может перешагнуть.

Было решено увеличить память для автовакуума, а maintance_work_mem не трогать:

Неделя 2

autovacuum_work_mem = 524288 блоков = 4 Гб

Смотрим как изменилась картина по потреблению памяти:

: 

Появились автовакуумы, которые могут отъедать до 500 Мб. Причем, чаще всего это автовакуум служебной таблицы pg_attribute:

autovacuum: VACUUM ANALYZE pg_catalog.pg_attribute

Но встречаются и другие. Если не брать автовакуум служебных таблиц, а взять какую-нибудь большую и часто изменяемую таблицу, включая индексы (для примера _inforg29785), то будет видно как перераспределилась длительность автовакуумов по ней.

На второй неделе пиков стало меньше, т.е. пересчет в среднем стал происходить быстрее.

Неверные настройки параметра autovacuum_work_mem могут привести к нескольким негативным последствиям в работе PostgreSQL:

1. Замедление выполнения операций автovacuum.

2. Блокировки: Долгие операции автovacuum могут вызвать блокировки на таблицах, которые они обрабатывают. Это может повлиять на производительность других запросов, ожидающих доступа к этим таблицам.

3. Накопление мертвых кортежей. Если автovacuum не может эффективно очищать мертвые кортежи из-за недостатка памяти, это может привести к их накоплению, что увеличивает использование дискового пространства и ухудшает производительность запросов.

4. Проблемы со статистикой. Излишне долгое выполнение операций ANALYZE может привести к устаревшей статистике, что затруднит оптимизатору выбор наилучших планов выполнения запросов.

5. Влияние на XID (идентификатор транзакции). Может возникнуть еще один эффект, скорее косвенный. Если операции автovacuum не могут эффективно очищать мертвые кортежи, то происходит накопление идентификатора транзакций, и существует совсем не нулевая вероятность (уже встречали у наших заказчиков) достижения предела XID (2^32). Когда это происходит, база данных может столкнуться с проблемами, связанными с wraparound, что требует выполнения более интенсивных операций очистки, таких как VACUUM FREEZE. Это может вызвать дополнительные нагрузки на систему и увеличить время простоя.

Резюме по первой части

PostgreSQL имеет гораздо больше настроек по управлению памятью, чем тот же MS SQL. А значит вероятность сделать что-то неправильно повышается в разы, а может и на порядки. Пирог памяти – это живое существо, за которым нужно следить и вовремя принимать меры.

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

Еще раз кратко по каждой разобранной настройке.

Shared_buffers. После выбора первоначального значения оцениваем вероятностное попадание данных в кэш с помощью счетчика Cache hit ratio. Увеличиваем память, если показания счетчика часто опускаются ниже 80%. Дополнительно оцениваем в трассах тяжелых запросов те, которые больше всего «выгрызают» данных с диска и пытаемся их оптимизировать. В пределе, по нашему опыту, shared_buffers может стремиться к 50% от общего объема ОЗУ.

Maintenance_work_mem. В первую очередь рассматриваем возможность установить его в значение максимально большого индекса. Обязательно сопоставлять эту настройку с общим объемом доступной памяти. При выполнении регламентного обслуживания помним о количестве потоков, чтобы в пределе случайно не перебрать с памятью и не уйти в своп. Опять же, на время регламентов можно задавать индивидуальные настройки, увеличивая maintenance_work_mem даже в разы, и потом возвращать их.

Autovacuum_work_mem. По умолчанию использует настройки памяти, указанные в maintenance_work_mem. И если maintenance_work_mem выбран недостаточно большим, то автовакуум будет скорее всего упираться в какие-то пороговые значения, что говорит о нехватке памяти для него. Пороговые значения можно оценить по показателю
[Physical memory – Shared memory]. Если для автовакуума памяти не будет хватать, то операции будут затягиваться и, как следствие, увеличиваются риски неполного пересчета статистик, накопления мертвых кортежей и даже достижения предела идентификатора транзакций XID.

В следующей части статьи: разбор настройки параметров temp_buffers и work_mem, а также их роль на весь «пирог памяти».