PostgreSQL: обходим подводные камни при миграции

Привет! Меня зовут Антон Казачков, я специалист вендорской поддержки СУБД Platform V Pangolin. Это реляционная СУБД уровня enterprise, которая разработана в СберТехе на основе PostgreSQL и доработана до корпоративного уровня надёжности и производительности. Сегодня Pangolin — целевая СУБД Сбера, основа для миграции всех существующих приложений и разработки новых.

Расскажу о ситуациях, с которыми сталкиваются команды на последних этапах миграции СУБД и в начале производственного цикла. На Хабре часто обсуждаются нюансы переезда СУБД из разных проприетарных версий на PostgreSQL. А вот информации о том, как вводить базы в эксплуатацию и выявлять производственные «болячки», на мой взгляд, не так много.

Миграция на финишной прямой — что может пойти не так?

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

Предположим, миграция данных состоялась, система запущена в рабочем профиле нагрузки, но что-то идёт не так. Как понять, что именно? С одной стороны, есть мониторинг, по которому можно собрать анамнез: время и симптомы проблемы. С другой стороны, в системах мониторинга, как правило, отсутствуют запросы к БД и затраты на их исполнение. Поэтому одного мониторинга для решения проблемы недостаточно.

Какие варианты решения существуют:

• оперативный анализ статистических представлений через, например, pgcenter Алексея Лесовского;

• включение auto_explain и анализ логов;

• инструментарий ОС: perf record, atop, strace, ltrace и пр.

Как видите, большинство инструментов либо представляют собой средства оперативного вмешательства, либо требуют специальных знаний. Исключением являются логи БД, но в них может находиться чувствительная информация. Кроме того, включение логирования запросов — не бесплатная операция, она приводит к перерасходу места в ОС и некоторым затратам СУБД на формирование лога. А анализ статистических представлений и вовсе зависит от работы СУБД, не имеет временных отметок и не применим для postmortem.

Решение: создаём performance insight и pg_profile, инструменты для выявления проблем производительности

До последнего времени анализ проблем производительности у нас основывался на изучении логов СУБД, применении explain к подозрительным запросам и подборе параметров для explain, при которых проявлялась проблема. Трудности начинались при вычислении затрат на исполнение запроса и конкурентных блокировок.

В итоге мы пришли к выводу, что текущего инструментария нам не хватает, и нужно написать что-то своё по мотивам проприетарных решений. Какие задачи должен решать инструмент:

1. Семплировать статистические метрики из pg_stat_activity и pg_locks c заданной периодичностью по всем БД экземпляра.

2. Сохранять определённое количество семплов в памяти, периодически сбрасывая их на диск. Глубина хранения на диске должна быть ограничена.

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

4. При старте постмастера считывать определённое количество последних семплов в память.

Что ещё было важно:

5. Прозрачная работа для СУБД с минимальным влиянием на систему.

6. Способность работать на всех нодах кластера, включая ведомые при физической репликации.

7. Работа инструмента не должна менять профиль нагрузки БД (включая блокировки).

8. Независимость от сетевой доступности.

9. Пользовательские интерфейсы — SQL (пользователи могут не иметь доступа к консоли ОС).

10. Умение строить красивые отчеты в HTML ?

Уже существующие аналогичные инструменты не подошли по ряду причин, среди которых — несовместимость лицензий, или, скажем, отсутствие сохранения состояния на момент аварийного завершения сервиса в том же pgsentinel.

Инструмент решили назвать performance insight. Мы сделали его в формате фонового процесса, с хранением данных в отдельном каталоге $PGDATA.

Интерфейсы:

Объект	Описание
FUNCTION pg_stat_get_activity_history	Снимок pg_stat_activity с отметками времени семплирования
FUNCTION pg_stat_get_activity_history_last	Последний снятый снимок pg_stat_activity
FUNCTION pg_lock_status_history	Снимок pg_locks с отметками времени семплирования
FUNCTION pg_stat_get_activity_and_lock_status_history	Снимок pg_stat_activity и pg_locks с отметками времени семплирования
FUNCTION pg_stat_activity_history_reset	Сброс статистики
FUNCTION pg_stat_activity_and_lock_status_history_report	Отчёт в HTML за заданный период

В отчёте можно увидеть:

• топ ожидаемых событий;

• топ ожидаемых блокировок;

• топ баз данных, к которым были подключены активные сессии;

• топ выполняемых запросов, находящихся в процессе ожидания событий, с топом ожидаемых событий;

• тексты запросов;

• топ сессий, ожидающих завершения некоторого события;

• топ сессий, ожидающих снятия блокировки;

• топ событий ожидания и блокировок, с разбивкой по интервалам времени.

Пример отчёта (заголовок и топ ожидаемых событий):

Инструмент получился весьма требовательным к памяти, поэтому идентификаторы объектов из представлений pg_locks и pg_stat_activity мы решили не расшифровывать. Пессимистичный бюджет RAM на один семпл занимает около 9Мб.

В чём это помогло:

1. Поиск взаимоблокировок.

2. Оценка блокировок по временным интервалам.

3. Суммарные блокировки, вызванные запросом.

4. Оценка длительности операций обслуживания СУБД в периоды минимальной нагрузки.

В чём это не помогло:

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

Не блокировками едиными

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

К сожалению, часто бывает, что при нагрузочном тестировании графики выявляют проблему, а причина её непонятна. К нам нередко обращаются за помощью в проведении нагрузочного тестирования, чтобы выявить узкие места или настроить параметры СУБД. Обычно в такой ситуации мы запрашивали:

• логи СУБД с включённым auto_explain;

• параметры СУБД и роли (\drds);

• DDL БД;

• выгрузки статистики из pg_stat_%.

Включить auto_explain, собрать гигабайты ценной телеметрии и, подхватив ещё двоих коллег, несколько часов смотреть в htop и pgcenter — всё это отнимало время и ресурсы. И мы стали искать решение, чтобы упростить этот процесс.

Хорошим выходом из ситуации могла бы стать передача инструментов профилирования в руки владельцев сервиса. Но к этой работе пришлось бы привлечь не только администраторов ОС или DBA, но и разработчиков, которые, в нашем случае, не имеют удалённого доступа к сервису.

Задача решилась довольно просто. Мы взяли за основу pg_profile Андрея Зубкова и доработали решение:

1. Послойно ввели свои метрики расчёта размера отношений. Вакуум не всегда успевал обновлять статистику, тем более по отношениям, перестраиваемых pg_repack и pg_squeeze.

2. Связали pg_stat_statements и pg_stat_kcache. Доработка связана с тем, что pg_profile сбрасывает pg_stat_statements, но не pg_stat_kcache.

3. Дополнили dblink собственными методами аутентификации без хранения пароля в открытом виде.

В обозримом будущем также планируем убедить pg_profile не сбрасывать pg_stat_statements, так как это плохо влияет на графики.

Итоги из нашей практики

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

2. Если у вас есть хранимая логика в БД — проводите тестирование более тщательно. Существующие инструменты не разворачивают планы выполнения внутри процедур и функций на plpgsql. Мы отлаживали процедуры и функции, используя самые простые методы, RAISE NOTICE и clock_timestamp()-current_timestamp.

3. Хорошо продумайте партицирование, особенно PARTITION BY HASH. Обосновывайте эффективность партицирования.

4. Настраивайте max_wal_size. Недостаточное значение может приводить к ненужным контрольным точкам.

5. Статистика для планировщика может собраться очень неудачно. Ещё недавно эта группа запросов могла работать отлично, а буквально сейчас она «устала» и не показывает объективную картину.

Автор: Антон Казачков (@ilway)