Это третья и заключительная часть цикла статей по настройке памяти в PostgreSQL. Полагаю, она получилось уже не такой заумной, как предыдущие две, и представляет из себя некий сухой остаток с собирательным примером, в котором показано как выбирать параметры по настройке оперативной памяти в PG. Если же хочется погрузиться в руду, то милости просим в Часть 1 и Часть 2. Тем не менее, цепочка логических рассуждений сохранена — как делаем, зачем и почему.

Подход не претендует на последнюю инстанцию и появился в результате приобретенного опыта во время наблюдения и настройки за несколькими высоконагруженными системами PostgreSQL на платформе 1С. Этим и делимся. Для наблюдения и принятия решений, как обычно, использовалась система мониторинга Perfexpert.

Повторюсь, для тех, кто начал читать сразу с третьей части про куски оперативной памяти, за которыми присматриваем и меняем настройки по необходимости. Использование оперативной памяти процессами PostgreSQL разделим укрупненно на четыре части и представим в виде круговой диаграммы. Далее будем называть ее «пирог памяти». Весь пирог — это тот объем памяти, который отдан одному инстансу PostgreSQL, а его куски — shared_buffers, maintenance_work_mem, temp_buffers и work_mem и есть основные потребители этой памяти.

Пример настройки параметров оперативной памяти

Итак, на сервере есть один инстанс PostgreSQL, на котором крутится средней загруженности система с 500+ пользователями. Если инстансов несколько, то нужно все нижесказанное проделывать для каждого, с оценкой его вклада в общий профиль нагрузки.

Общий объем памяти 256 Гб. Объем базы здесь не так важен, но точно не менее общего объема оперативки.

Что там с памятью в целом?

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

Свободно в пике ~170 Гб. Есть краткие периоды, когда потребление возрастает, но складывается стойкое ощущение, что система «задушена» в части потребления памяти. Даже если память не задействована (аналогично как и если не задействован процессор) – это вовсе не указывает на то, что все работает эффективно, а скорее наоборот, появляется подозрение, что ИТ система (PostgeSQL в частности) не использует ресурсы.

Переходим к параметрам.

shared_buffers (SHB)

В настройках SHB отдано 77 Гб. Это 30% от общего объема. Много это или мало? Или нормально?

Учитывая, что SHB — эт о постоянная величина, которая резервируется за PostgreSQL сразу после загрузки, то ее эффективность можно оценить через статистику чтений страниц из памяти относительно диска. В Perfexpert для этого есть отдельный счетчик Cache Hit Ratio, который как раз показывает вероятность попадания данных в кеш (подробно см. в Часть 1). Но можно использовать pg_stat_statements и получить эти данные через соотношение
shared_blks_hit /shared_blks_read , где

shared_blks_hit– общее число попаданий разделяемых блоков в кеш для данного оператора

shared_blks_read– общее число разделяемых блоков, прочитанных данным оператором

Если памяти достаточно, то при выполнении запросов почти все данные будут браться из кэша. В противном случае идет обращение к диску и чтение выполняется на порядки медленнее. При идеальном раскладе график должен стремиться к горизонтальной 100% линии. Ту же видно, что Cache Hit Ratio падает даже не до 70-80%, а почти в ноль. То есть памяти явно не хватает.

На сколько увеличить?

Если совсем основательно подходить к вопросу, то нужно анализировать трассу тяжелых запросов, которые выгрызают память, производя логические и физические чтения. Далее оценивать возможность оптимизации вопросов и оценивать сколько памяти не хватает, чтобы они по максимуму считывали данные из памяти и по минимуму обращались к диску (подробно см. Часть 1).

Но такая возможность не всегда есть, т.к. трассировки штатной в PG нет.

Возвращаясь к нашему примеру, память вроде как свободная есть, поэтому увеличиваем shared_buffers до 50%, т.е. до 128 Гб. И далее смотрим как изменился график Cache Hit Ratio. Эффект 100% будет – запросы станут заметно реже читать данные с диска. Если же свободной оперативной памяти у вас в системе мало (график Available Physical Memory), то здесь только настройками уже не обойтись и это первый признак к увеличению объема памяти на сервере.

Итак, поскольку shared_buffers — это константа, то после увеличения SHB у нас в пироге памяти остается:

[256 Гб] – [128 Гб (SHB)] — [20 Гб (резерв под файловый кеш и нужды ОС)] = 108 Гб

maintenance_work_mem (MWM) и autovacuum_work_mem (AWM)

Настраиваем, исходя из свободного остатка в пироге памяти: 108 Гб.

maintenance_work_mem — тоже условная константа, которая устанавливает объём оперативной памяти, выделяемый для выполнения операций обслуживания базы данных (vacuum/avtovacuum, пересчет индексов и статистик, создание индексов) и определяется не столько профилем нагрузки ИТ-системы 1С и запросами, сколько количеством служебных процессов.

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

В данном случае можно смело увеличить maintenance_work_mem до 3 Гб. Индексов, которые бы занимали десятки и сотни гигабайт тут нет.

Пересчет индексов в несколько потоков делают не часто, но надо помнить, что в пике вам может потребоваться памяти до [3Гб*N потоков]. Но опять же риск не велик, т.к. регламентные операции типа вакуума и пересчета статистик с индексами проводятся обычно в нерабочее время.

Есть еще настройка с автовакуумом. Она идет в паре с MWM и ее стоит рассмотреть отдельно.

autovacuum_work_mem (AWM)

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

Фактически для сбора идентификаторов мёртвых кортежей VACUUM может использовать не более 1GB памяти. Поэтому устанавливать AWM более 1Гб смысла нет.

По умолчанию количество потоков автовакуума в системе — 3 (autovacuum_max_workers=3). Для нашего примера выберем 10 потоков и параметр autovacuum_work_mem = 1 Гб.

Поскольку автовакуумы — это практически непрерывные процессы, то в пределе в нашем куске пирога они могут съесть до 10 Гб. Фактически же будет гораздо меньше.

Итого, считаем остаток свободной памяти:

[108 Гб (после выделения SHB)] – [3 Гб* 3 потока (MWM)] – [1 Гб * 10 потоков (AWM)] = 89 Гб

На самом деле останется несколько больше, т. к. в рабочее время регламентное обслуживание редко запускают, или не запускают вообще, а автовакуум для большинства таблиц потребляет гораздо меньше 1 Гб. Но в уме будем держать число 89 Гб.

temp_buffers (TMB)

Коварный параметр. Задает максимальный объем памяти, выделяемый для временных таблиц (буферов) в каждом сеансе. При этом каждая временная таблица всегда имеет копию на диске, не важно хватило ли ей памяти в temp_buffers или нет.

По умолчанию temp_buffers равен 8 Мб и для 1С систем, которые очень активно работают с временными таблицами этого, конечно, мало.

Сколько ставить? Подробно см. в Часть 2, а в сухом остатке: анализируем размер и количество временных таблиц за какой-то более-менее релевантный интервал в несколько дней и определяем сколько можем выдать под это дело памяти. В Perfexpert для этого есть счетчики:

• Количество сессий, в которых создана хотя бы одна временная таблица;

• Максимальный размер временных таблиц в одной сессии, Мб;

• Средний размер временных таблиц в сессиях, Мб.

Из графиков понятно, что есть пики с размером одной временной таблицы выше 10 Гб. Но это именно пики и закладываться сразу на это не нужно. Если посмотреть средний размер таблиц, то он уже колеблется в гораздо более разумных пределах (0,5 – 3 Гб). Опять же не берем максимум, а возьмем что-то среднее – 1 Гб и дальше смотрим на:

1. Счетчик свободной оперативной памяти (график Available Physical Memory) – остаток памяти не должен приближаться к границе свопирования. Об этом чуть ниже.

2. Трассы тяжелых запросов с временными таблицами – физических чтений должно стать меньше, а значит обращений к диску меньше.

Что касается нашего пирога, то получается, что за неделю максимальное количество сессий с временными таблицами составило почти 30, а размер TMB мы выбрали 1 Гб, то есть кусок temp_buffers в максимуме будет порядка 30 Гб. И у нас остается:

[89 Гб (ОС, SHB, MWM, AWM)] — [30 Гб (TMB)] = 59 Гб

Еще момент, про temp_buffers, на котором раньше не сильно акцентировали внимание и, пожалуй, стоит развернуть поподробнее.

Платформа 1С автоматически чистит пулы с сессиями примерно раз в полчаса (просто эмпирическое наблюдение). То есть временные таблицы не удаляются из буфера сразу как только использовались, а продолжают оставаться какое-то время в памяти. Вот как это может выглядеть на счетчиках:

Во-первых, хорошо видна корреляция между временными таблицами (кол-во, размер) и свободной оперативной памятью. То есть «просадка» по памяти вызвана именно временными таблицами. Пилообразный характер потребления памяти (верхний розовый график) указывает, что память очищается с частотой примерно раз в полчаса. То есть, 1С удаляет старые сессии и создает новые. А до этого момента временные таблицы живут в буфере, хотя уже удалены приложением 1С.

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

Во-вторых, по поводу границы свопирования. По умолчанию в Linux значение vm.swappiness равно 60%. Чем выше это значение, тем выше вероятность использования swap раздела. То есть, как только уровень свободной физической памяти опускается менее 60% могут начаться процессы свопирования и Linux начнет выгружать страницы памяти на диск. Обратите внимание на нижний график с использованием swap. Активное свопирование началось тогда, когда еще свободной памяти оставалось больше половины. А зачем оно нам надо? Поэтому мы рекомендуем настройку Linux vm.swappiness снизить с 60% до 5–10%. Ее также можно менять в режиме реального времени, но не забыть сделать настройку в конфиг-файле, иначе после перезагрузки все вернется обратно.

work_mem

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

У нас осталось после расчетов 59 Гб под work_mem. Много или мало? И какую настройку выставить, чтобы не переступить эту границу? Подробно см в Часть2, а ниже постараюсь дать сухую выжимку.

В случае нехватки памяти для внутризапросовых операций типа JOIN, GROUP BY, ORDER и т.п. запрос записывает данные на диск в темповые данные и работает с диском. Поэтому оценивать нехватку work_mem нужно по двум критериям — использованию темповых данных (Temp data) и использованию физических чтений во время выполнения тяжелых запросов. Если эти критерии совпадают по времени, то значит размер work_mem недостаточен.

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

Среднее и максимальное значения тут совпадают и достигает 4,5 Гб. Это опять же пиковые значения. Т.е. для начала можно взять что-то медианное или просто среднее. Скажем, 2 Гб и наблюдать в течении тех же 1–2 недель за потреблением оперативной памяти — график Available Physical Memory. Нужно помнить, что суммарно для work_mem мы запланировали 51 Гб и стараться не выйти за эти рамки.

Выводы

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

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

1)  Система работает неудовлетворительно, пользователи жалуются на падение производительности. Хотя аппаратных ресурсов достаточно или даже с избытком, но они не задействованы в полной мере. В нашем примере — это память (но и с CPU очень часто бывает схожая ситуация). И эта ситуация даже неприятнее, чем нехватка ресурсов, так как при нехватке всё на поверхности — увеличивай ресурсы и будет лучше. Может не хорошо, но точно лучше. А здесь нужно разобраться и вычислить то бутылочное горлышко, которое не позволяет системе задышать, что не всегда очевидно.

Что скажет администратор? «У меня все показатели в норме:CPU — 30%, памяти свободной полно. Проблема на стороне приложения, разбирайтесь!». Знакомо?

2) Система работает вроде бы удовлетворительно, жалоб нет или мало. Но есть сомнения, что не все аппаратные ресурсы задействованы на 100%, т.к. опять же процессор не задействован даже на половину и/или памяти свободной много. Вроде все нормально, но что‑то не то.

Описанный пример как раз посвящен ситуации с памятью. И, надеюсь, был вам полезен. 

P.S. Возможно, остался еще один вопрос, который стоит проговорить.

Нужно ли увеличивать объем оперативной памяти и задействовать ее для улучшения работы PG?

Если вы все рассчитали: shared_buffers, temp_buffers, maintenance_work_mem, work_mem, и в результате пришли к выводу, что размер получившегося пирога выходит за границы имеющегося объема памяти, то вы получили предпосылки и, главное, аргументированное подтверждение того, что ваш профиль нагрузки требует увеличения оперативной памяти на сервере СУБД, чтобы ИТ-система работала устойчивее и быстрее. 

 Ссылки на все части «Особенности работы с оперативной памятью в PostgreSQL:

1. PostgreSQL — особенности работы с памятью для 1С-систем. Часть 1

2. PostgreSQL — особенности работы с памятью для 1С-систем. Часть 2

3. PostgreSQL — особенности работы с памятью для 1С-систем. Часть 3