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

Продолжаем исследовать и настраивать память в PostgreSQL. Начало см. здесь.

Будет ещё и третья — заключительная часть, где я постараюсь максимально доступным языком рассказать уже методику выбора настроек. А пока предлагаю набраться терпения и ознакомиться со следующей порцией исследования по выбору настроек оперативной памяти PostgreSQL. Предупреждаю, будет не просто и, наверняка, не каждый доберется до конца.

В первой части были рассмотрены параметры shared_buffers, maintenance_work_mem, autovacuum_work_mem. А сегодня на повестке параметры temp_buffers и work_mem.

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

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

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

Термин «Пирог памяти»

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

Выбор temp_buffers

Если бы MSSQL умел кэшировать временные таблицы из базы [tempDB], то там обязательно существовала бы аналогичная настройка. Правда надо отметить, что в последних версиях MS SQL можно использовать режимы работы с метаданными в памяти. Тем не менее, управлять размерами памяти для временных таблиц MS SQL не позволяет. А PosgreSQL умеет. За это однозначно +1 в карму.

Размер этого кэша определяется как раз настройкой temp_buffers, но для одного сеанса. Это важно, т.к. общий размер кэша — величина суммарная и динамическая.

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

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

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

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

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

Разберем пример. На рисунке ниже показан один рабочий день достаточно высоконагруженной системы (до 28 000 запросов в секунду) и маленький пятиминутный фрагмент этого же дня — выделен желтым.

Нас интересуют синие графики с размером временных таблиц — максимальным, средним и суммарным.

Обратите внимание, что максимальный и суммарный размеры почти совпадают. То есть какая-то одна сессия, а может даже и одна временная таблица, вносит основной вклад в общий размер. В пике на этом маленьком участке суммарный размер временных таблиц достигает ~6Гб. И если значение temp_buffers маленькое, то при чтении данных из большой таблицы будет активно задействован диск.

В течение всего дня размер временных таблиц доходит до 8 Гб. Но расклад тот же. Временные таблицы в одной сессии занимают 90–95% места от всех временных таблиц.

Поэтому в этой ситуации нужно рассмотреть возможность поднять temp_buffers с 500 Мб до 6Гб

Но при этом следует обязательно отслеживать общее состояние потребления памяти. Не должно случиться так, что temp_buffers, после его увеличения в настройках, будет пытаться потребить памяти больше, чем ее есть на самом деле. В данном примере причина роста темповых таблиц достаточна прозрачна и вызвана всегда одной сессией, поэтому риски увеличения параметра temp_buffer до 6 и даже 8 Гб минимальны. Но гораздо чаще объем временных таблиц размазан по разным сессиям. Поэтому в любом случае повышение temp_buffers следует проводить постепенно (особенно, если оперативной памяти не много) и на каждом шаге отслеживать ситуации нехватки оперативной памяти, чтобы не допускать свопирования и падения системы.

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

За исследуемый пятиминутный интервал, где произошел рост использования временных таблиц, в трассе виден один характерный запрос, вставляющий 41 млн. строк. По времени запрос наполнения временной таблицы pg_temp.tt163 как раз попадает на участок графика с началом роста размера временных таблиц (выделен желтым).

Если отфильтровать трассу только по SPID 715 892 (которому принадлежит этот запрос), то в хронологии можно увидеть как раз все точки роста размера временных таблиц, в точности совпадающих с графиком. Все запросы относятся к типу INSERT INTO pg_temp.tt##. Понятно, что это данные только по одной сессии и на общий размер влияют и другие. Но их доля мала, если не сказать ничтожна, т.к. последний запрос в трассе по SPID 715 892 заканчивается как раз в 13:51, что совпадает с падением графика.

Итого в нашем примере одной сессии как раз пригодились бы 6 Гб оперативной памяти в куске пирога temp_buffers. Это позволит сократить нагрузку на диск, дабы избежать избыточных чтений (на графике виден пик красного цвета, предшествующий очередной порции считываемых данных сессией SPID 715 892) и даже ускорить выполнение запросов, т.к. все чтения будут из оперативной памяти, а не с диска. Надо помнить еще одно обстоятельство, что при удалении временных таблиц память temp_buffers не освобождается мгновенно и продолжает использоваться до удаления сессии. Поэтому надо делать запас памяти для куска пирога под temp_buffers.

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

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

Сразу видно как возросло потребление оперативной памяти каждым процессом. На рисунке ниже представлен срез для двух настроек temp_buffers и линейка времени для обоих случаев установлена в пиках объема временных таблиц, а на вкладке «ТОП Процессов» виден размер каждого из процессов в этот момент времени.

Видно, что потребление памяти значительно возросло. Это неплохо. Можно посмотреть теперь в трассы и оценить помещаются ли таблицы в буфер.

На рисунке выше приведены характеристики одного и того же запроса, который записывает 40+ млн. строк. Во втором случае он уже почти целиком работает с оперативной памятью и данные для него будут гарантировано считаны из нее. Тут речь не про ускорение. Длительность не изменилась почти (с поправкой 48 млн. строк), т.к., вероятно, линукс закешировал эти данные. Но может случиться так, что этого и не будет и наша цель зарезервировать себе гарантированное место в пироге памяти.

Важный момент. Объем памяти, который съела временная таблица в temp_buffers не возвращается до тех пор, пока сессия жива. То есть таблица может быть очищена (truncate), удалена (drop), а память так и остается занятой. Поэтому очень хорошо, что 1С:Предприятие постоянно создает новые сессии и подчищает старые. Иначе может случиться неприятность, когда вся свободная память уйдет в кусок пирога temp_buffers и там и останется…

Выбор work_mem

Настройка work_mem, наверное, самая важная в настройке всего «пирога памяти», т.к. трудно контролируемая. И выбирается в большинстве случаев «на ощупь» и по «рекомендациям из интернета».

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

Неважно какое значение вы выбрали или когда-то выбрали за вас. Важно сейчас оценить достаточное/избыточное оно или нет.

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

Переходим в Perfexpert и включаем отображение счетчиков с темповыми данными (Temp data). Это совсем не то же самое, что Temp tables!

• Temp data files count — количество файлов с темповыми данными;

• Temp data total size, Mb — общий размер темповых данных в Мб;

• Temp data max size, Mb — максимальный размер темповых данных в Мб для одной сессии;

• Temp data avg size, Mb — средний размер темповых данных в сессиях в Мб;

Картина по темповым данным может быть, к примеру, такая:

Файлы темповых данных используются активно, но их общий объем маленький — всего 20–25 Мегабайт.

И в трассе READS видна соответствующая раскладка по тяжелым запросам.

Если группы запросов во вкладке «Статистика» отсортировать по использованию CPU (или количеству запросов, или по доле логических чтений), то мы получим несколько групп тяжелых запросов, которые и создают основную нагрузку в исследуемом периоде.

В этом примере каждый из запросов (на рисунке выше показаны запросы одной группы — выделены красной рамкой) выполняет минимальное количество записей в темповые данные (колонка Temp blks written) и физических чтений. То есть обращения к диску при выполнении запроса минимальны, всё хорошо.

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

Если посмотреть трассу запросов READS за этот интервал времени (см. рисунок ниже), то там можно увидеть достаточно большое кол-во записей в темповые данные (колонка Temp blks_written) и физических чтений, причем для совершенно разных запросов.

Тем не менее, поступим также как и предыдущем примере — получим ТОП самых тяжелых по потреблению CPU запросов (нижняя панель «Статистика — READS»). Почти все перечисленные группы запросов присутствуют в верхней панели и у всех у них выполняются большие записи в файлы с темповыми данными и миллионные физические чтения.

Если в трассе значение физических чтений совпадает со значением Temp blks written, то это говорит о том, что физические чтения с диска обусловлены исключительно нехваткой work_mem. Но даже если они отличны, но при этом есть хронологическое совпадение с графиками использования темповых данных, то опять же это сигнал к увеличению work_mem.

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

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

Так для нашего примера самый максимальный размер темповых блоков в трассе тяжелых запросов составил 242 528 блоков * 8192 байт= 1894,75 Мб. То есть именно этого размера не хватило запросу, чтобы выполниться целиком в памяти и не обращаться к дисковой подсистеме. Поэтому можно увеличить размер work_mem как раз на почти 2 Гб и мониторить далее систему. Work_mem потребит ровно столько, сколько ему нужно в запросе, лишнего не возьмет, а после выполнения запроса отдаст обратно.

И еще момент. Если, изменяя параметр shared_buffers или temp_buffers вы в большей степени влияете на чтение с диска (уменьшаете количество физических чтений), то в случае с work_mem вы влияете как на запись, так и на чтение с диска.

Заключение

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

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

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

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

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