Ипатов Александр
Старший разработчик ГК Юзтех
Привет всем!
Я — Ипатов Александр, backend-разработчик в ГК Юзтех. Сегодня хочу поделиться своим опытом в интересном проекте, связанном с миграцией БД MsSQL на PostgreSQL в разрезе оптимизации хранимых процедур и функций (далее — хранимых процедур, так как процесс оптимизации не сильно завязан на том, что именно имеем на выходе).
Актуальность проектов, связанных с миграциями серверов и баз данных с зарубежных платных продуктов (Microsoft, Oracle) на аналогичные отечественные или зарубежные open-source решения (в разрезе статьи будем рассматривать Postgres) в 2024 году очень велика. Те решения, которые были реализованы и поддерживались на протяжении 5–10 лет, потребовалось практически в формате «пожара» переносить на аналогичные. А бизнес, который привык к уже полностью сформированным и отработанным рабочим процессам, не готов к потере эффективности и, как следствие, потере клиентов сервисов, заказов и бизнес-метрик.
В одном из таких проектов мне удалось поучаствовать. Из начальных условий: проект по переносу БД из MsSQL начался примерно 3 года назад.
На самом деле, проект был более обширный — перенос монолитного сервиса на микросервисы, в том числе, как один из элементов — перенос БД.
Хочется отметить, что перенос схем, таблиц, индексов и других элементов базы данных прошел относительно спокойно. Чего не скажешь о переносе хранимых процедур. Язык T-SQL, на котором пишутся хранимые процедуры в MsSQL, конечно же имеет отличия от PL/pgSQL, который используется в PostgreSQL. В связи с чем, непосредственно миграция хранимых процедур заняла много времени: точное число хранимых процедур я не назову, но порядок – около 800 штук (среди которых 500 стали работать хуже после миграции, их то и предстояло оптимизировать).
Из интересного: самая большая хранимая процедура занимала 12.000 строк кода, поэтому миграция производилась автоматизированными средствами. Как итог – да, хранимые процедуры перенесли с MsSQL, это было уже достижение. Но всё только начиналось…
Прошла отсечка и БД Postgres стала готова к нагрузочному тестированию, поскольку были перенесены все элементы с БД MsSQL.
Ожидание: показатели работы всей системы в целом будут на 20% ниже тех, что были на продакшн версии (MsSQL).
Реальность: показатели времени выполнения хранимых процедур хуже на сотни процентов.
Действие: дополняем проект командой разработчиков, которые будут заниматься оптимизацией хранимых процедур на PostgreSQL.
Подход: от массового к точечному (от общей настройки системы администраторами БД до работы непосредственно с каждой медленной хранимой процедурой).
Здесь и появилась команда разработчиков, состоящая из 4–5 человек, среди которых мне удалось принять участие в этом довольно интересном и нетривиальном проекте.
Принцип работы команды разработки был следующий:
Имеется тестовый стенд для разработчиков, который максимально идентичен будущему продакшн серверу БД PostgreSQL по характеристикам самой базы данных, но по «железу» сильно от него отстает. Связано, в первую очередь, с дороговизной реализации идентичного по «железу» тестового стенда.
Здесь хочу акцентировать внимание на важном моменте. Несмотря на то, что язык SQL считается декларативным языком, то есть таким, при котором с пользователя снимается нагрузка по определению и описанию порядка выполнения непосредственно действий с множествами (не видов соединения таблиц в базе данных, а способов соединения непосредственно наборов данных из этих таблиц, с учетом имеющихся ограничений, указанных в запросе) оптимизатор в БД Postgres сам определяет лучший «путь» выполнения запроса – строит план выполнения. Этот план выполнения строится исходя из нескольких моментов:
Актуальность «статистики» в системе на текущий момент, то есть то, насколько статистические данные о таблицах (как временных, так и обычных) являются достоверными. За данный пункт отвечает команда analyze (либо vacuum analyze – анализ вместе с предварительной очисткой пространства, занятого удаленными данными в таблицах).
Идентичность параметров физического сервера: мощность и количество процессоров, объем оперативной памяти и других.
Среди этого, наш тестовый стенд удовлетворял лишь пункту 1.1., тогда как по пункту 1.2. сильно отставал от будущего продакшна. В связи с этим, возникали ситуации, когда оптимизированная и ускоренная в разы хранимая процедура на тестовом стенде не давала актуальные результаты на будущем продакшене. Выход из данной ситуации – тестирование оптимизированной хранимой процедуры на будущем продакшн сервере БД, но с обязательным выполнением условия отсутствия DML-команд внутри (insert / update / delete). Если же хранимая процедура не могла выполнить данное требование, тогда руководитель команды разработки принимал решение – опубликовать изменение без предварительного тестирования на продакшн сервере, и на боевом запуске пользователями за период времени 2–3 дня анализировать результаты работы.
На тестовом стенде производится бэкап системы 1 раз в 2 недели (для того, чтобы очистить все тестовые хранимые процедуры, индексы и вью, не вошедшие в релиз, которые в результате лишь загрязняли систему и создавали «лишний шум»).
Оптимизация хранимых процедур заключалась в приведении характеристик по времени выполнения в БД Postgres к аналогичным результатам на текущем продакшн сервере БД MsSQL: среднее время выполнения, максимальное время выполнения, медианное время выполнения, время выполнения 80% запусков, также имело значение суммарное количество запусков за интервал времени. На основе перечисленных временных характеристик была сформирована обобщенная метрика, которая отвечала на вопрос: хранимая процедура оптимизирована и соответствует заявленным требованиям или нет. Причем, если в хранимой процедуре использовались «курсоры» на выходе, обязательно во времени выполнения учитывалось ещё и время извлечения данных из него с помощью FETCH.
После оптимизации хранимой процедуры разработчик обязательно проводит 5 – 10 тестовых запусков хранимой процедуры с различными параметрами для того, чтобы удостовериться в идентичности выводимых результатов оптимизированной процедуры с текущей версией. Причём, если хранимая процедура на выходе давала «курсор» — то обязательно нужно было сравнить результаты после его извлечения с помощью команды FETCH.
Релизы производятся в среднем 1 раз в 3–4 дня, по результатам релизов запускается нагрузочное тестирование и обновляется единая таблица со сводными данными по каждому релизу. Тогда можно корректно интерпретировать результаты оптимизации.
Это основные принципы работы, которые позволили системно подойти к решению поставленной в рамках проекта задачи. Хочу отметить – если бы не систематизация, то, возможно, проект не был бы реализован.
Не менее важным является то, какие именно методы позволили нам оптимизировать 500 хранимых процедур из общего числа 800 мигрированных с БД MsSQL на PostgreSQL:
Уход от большего числа временных таблиц процедурах в сторону CTE (обобщенное табличное выражение) / массивов.
При наличии предварительных расчетов уходили от временных таблиц и записывали предварительные данные в разного рода представления, аналогичные временным таблицам, но без записи в отдельную временную таблицу: иногда CTE строились каскадным методом, следуя одна за другой – читаемость кода упала в разы.
Эта методология была пробной и использовалась в рамках хранимых процедур, которые не содержали в себе DML-операторы (insert / update / delete) данных постоянных таблиц и использовались в качестве неких отчетов для пользователей по их запросам. Её причиной было то, что процессор был сильно нагружен за счёт большого количества операций с временными таблицами, хотелось снизить нагрузку на основную read-write ноду кластера БД – и перенести часть отчетных хранимых процедур на физическую read-only ноду. Однако, в последствии её использования, сильной разгрузки не произошло, но для разработчиков и аналитиков возникли большие проблемы по доработке и анализу отчетов. В связи с чем, данная методология была отвергнута и переродилась в следующий метод.
Уход от временных таблиц в сторону расширения pg_variables.
В данной статье я подробно описал основную суть и особенность применения этого расширения. Есть и плюсы, и минусы. Явный минус для тех, кто работает на Windows серверах – это расширение поддерживается лишь на Linux. Основной плюс, который и стал причиной использования этого расширения – оно может использоваться на read-only ноде кластера БД PostgreSQL. В результате, данное расширение pg_variables успешно разгрузило read-write ноду кластера БД от одной хранимой процедуры, которая вызывала 40 взаимосвязанных хранимых процедур внутри себя, в которой особенно часто и много использовались временные таблицы.
Переписывание мелких временных таблиц (с 1 столбцом) – на 1 массив.
Зачастую, в начале хранимых процедур использовались вспомогательные запросы, которые обычно содержали в себе некоторые удовлетворяющие определенным условиям идентификаторы (заказов, клиентов и прочее). Эти выборки писались в мелкие временные таблицы. На объеме в 500 хранимых процедур, с учетом больших запусков этих хранимых процедур (доходило до 5 тысяч запусков в сутки в некоторых хранимых процедурах) это значительная нагрузка на процессор, который кроме формирования выборки ещё и делал вставку этих данных во временные таблицы, не говоря уже о предварительном удалении и создании временной таблицы. Массив же формировался быстрее, и очень часто использование такого метода положительно сказывалось на общее время выполнения хранимых процедур на большом объеме запусков.
Переписывание временных таблиц, которые использовались внутри лишь одного запроса – на CTE у данного конкретного запроса на выборку.
Иногда от временных таблиц было сложно уйти, поскольку одна временная таблица использовалась в более чем одной последующей выборке данных. Но в случаях, когда временная таблица была создана и использовалась лишь в одном запросе – в 90% случаев использование CTE положительно сказывалось на время выполнения запроса.
Уход от MATERIALIZED VIEW в сторону временных таблиц и СТЕ.
Материализованное представление (MATERIALIZED VIEW) хранится в памяти и должно обновляться, если мы хотим получать корректные данные. Однако, зачем обновлять данные, если далее в выборках используются дополнительные ограничения? Во время оптимизации мы не обошли и этот момент, в результате мы ушли практически от всех MATERIALIZED VIEW и увидели хороший прирост по скорости выполнения.
Использование индексов у объемных временных таблиц.
К объемным временным таблицам, которые впоследствии используются в дальнейших запросах, также можно попробовать построить индекс. Если мы понимаем, что это позволит уйти от её полного последовательного сканирования. В результате, в некоторых случаях дальнейшего использования временных таблиц при высокой селективности запросов будет использоваться данный временный индекс, что позволит значительно ускорить выполнение запроса.
Классическое переписывание SQL-запросов, написанных не оптимально.
Разработчику, который пишет SQL-запросы, нужно иметь знания о том, как БД работает с этими запросами. Например, использование после обширного количества джоинов в запросе блока WHERE, в котором данные ограничиваются очень узким срезом (например, всего несколько строк на выходе). Это не касается, например, выборок лишь с LEFT JOIN, однако в конструкциях с JOIN (INNER JOIN) это первоочередная часть оптимизации запросов. Для этого, конечно, нужно уметь читать планы запросов, знать способы доступа к данным (последовательное сканирование, индексный доступ, сканирование по битовой карте) и способы соединений (вложенным циклом, хэшированием и слиянием).
Это основные методы, которые позволили подойти к решению поставленных в рамках проекта задач. Конечно, ещё есть вариант полного переписывания «с чистого листа», но это более долгий процесс, поскольку будет требовать совместной работы разработчика и аналитика, для понимания бизнес-сути работы хранимой процедуры.
Проект по оптимизации хранимых процедур на PostgreSQL успешно завершился и позволил достичь поставленных перед новой БД целей – система стала работать даже лучше, чем работала до перехода (что подтверждают и пользователи).
Заключение
На мой взгляд, сейчас вопросы, связанные с миграцией систем с подсанкционных сервисов на open-sourse решения, стоят очень остро. Говоря об этом в срезе миграций баз данных, всегда будут появляться вопросы, описанные в статье – вопросы ускорения процессов, которые годами отлаживались и настраивались под одну систему, с одним движком, на новую систему.
Поэтому, надеюсь, что статья поможет в реализации задач, связанных с оптимизацией хранимых процедур после миграций.