PostgreSQL: 3 кейса от DBA

Иван Чувашов, DBA Okko и Southbridge, разберёт жизненные кейсы с PostgreSQL, которые помогут решить ваши проблемы.

1

Иван Чувашов
DBA Southbridge
Ситуация с idle in transaction – приложение открыло транзакцию, отправило изменения в базу данных, а закрыть транзакцию забыло, транзакция висит.

Как решать инженеру такие ситуации? В Интернете можно найти много статей на тему: что такое idle in transaction, с чем оно связано и даже на практике посмотреть примеры реализации этой ситуации, но не получите самую главную информацию – как решать такие проблемы?

Иван Чувашов
Ведущий инженер Okko
У нас была ситуация, когда появились idle in transaction, и приложение начало тормозить – пул подключений в базе данных был забит. Они блокировали ресурсы, и нам нужно было срочно принимать какое-то решение, мы пошли самым простым путём, который напрашивается и предлагается вашему вниманию.
Первый вариант, который может быть – это выполнение команд
select pg_cancel_backend(pid) from pg_stat_activity where state = 'idle in transactions' and datname = 'название_БД';
или
select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle in transactions' and datname = 'название_БД';

Отличие данных функций друг от друга можно почитать тут: https://postgrespro.ru/docs/postgresql/13/functions-admin#FUNCTIONS-ADMIN-SIGNAL-TABLE.

Иван Чувашов
Сертифицированный администратор PostgreSQL: PostgresPro, 10 уровень «Эксперт»
А может ли упасть PostgreSQL?

Когда выполняете pg_terminate_backend – это не нулевая вероятность. Допустим, у вас есть процесс, который считывает данные и вы выполняете команду pg_terminate_backend, запрос подтягивает эти же данные в оперативную память PostgreSQL. Если в результате данные стали грязными, то их нельзя считывать другим процессам. Поэтому postmaster-у нужно очистить оперативную память, что он и делает, перезагружая себя.

Точечно отстреливать запросы, можно вызвать групповую команду по маске – очевидные плюсы. Но эти команды на самом деле не решают проблему с idle in transaction. Когда ситуация возникает на стороне бэкенда и мы выполнили команду, которая группой срубила запросы, то через некоторое время ситуация может повториться и тут же появятся от бэкенда запросы в статусе idle in transaction.
Другой вариант – перезагрузка сервера PostgreSQL

Иван Чувашов
Спикер курса по PostgeSQL
Какие в этом решении могут быть минусы? Конечно же, остановка сервера. К тому же часто бывает, что на одном кластере находится не одна база, а несколько или даже с десяток разных баз.

При остановке сервера PostgreSQL мы убиваем подключения к другим базам или подключения других сервисов. Все это усугубляется ситуацией, когда приложение автоматически не может переподключится к базе. Да бывает и такое! Что может привести к каскадному эффекту в перезагрузке сервисов.

Не знаю, если ли тут плюсы?

Небольшой лайхак. Чтобы быстрее перезапустить кластер, перед перезагрузкой выполните команду checkpoint.
Ещё вариант – перезагрузка бэкендов

Иван Чувашов
13 лет работает с базами данных
Через pg_stat_activity определяем IP проблемного сервиса, с которого произошло подключение. Начинаем их перезагружать. В мире IT микроархитектуры этот процесс не будет являться существенной проблемой.

В нашей ситуации получилось таким образом: увидели idle in transaction – начали пачками отключать запросы. Но количество соединений с базой данных не изменялось. Тут же появлялись новые в этом же статусе. Потом мы подумали: нужно перезагружать PostgreSQL.

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

  • установка pgbouncer на паузу – команда pause;
  • перезагрузка сервера PostgreSQL;
  • снятие pgbouncer с паузы – команда resume.

В другом известном инструменте управления пулом соединений Odyssey функционал, связанный с pause пока не реализован, что может ограничивать его использование в нагруженных проектах.
ЕЩЁ

В августе 2021 провели митап с Иваном по нюансам работы с PostgreSQL.
На канале Слёрм: https://youtu.be/Qx2NoGCHco8.

2

Иван Чувашов
Больше 6 лет работает архитектором БД и DBA
СУБД PostgreSQL работает с диском, оперативной памятью, процессором. Если выходит аппаратная часть оборудования, то идём и чиним. Но иногда бывают и скрытые проблемы, например сбой в дисковом массиве, который мы можем заметить не сразу, можем вообще не знать об этом.

Давайте воспроизведем проблему сбоя дисковой подсистемы, затем покажем её решение. У нас есть PostgreSQL – тринадцатая версия. Создадим базу и инициализируем pgbench. Она существует, чтобы нагружать сервер, снимать метрики производительности. Но нам она нужна для других целей.
Рассмотрим таблицу pgbench_branches, в ней есть три поля и одна запись.
Найдем, где находится физически эта таблица на диске:
psql -p5432 -d test -c "select pg_relation_filepath('pgbench_branches')";
Остановим кластер PostgreSQL. Откроем файл base/16839/16853 и допишем любой текст в середине файла. Сохраним его.

Можно воспользоваться просто командой:
sed -i 's/@/@123@/' ~/13/main/base/16839/16853
Запустим PostgreSQL. Попробуем прочитать данные из таблицы.
В таблице также одна запись, но теперь в ней просто пустые строки.

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

Насколько это просаживает перформанс? Есть статистика, что – от одного до трёх процентов, но при этом вы точно знаете, что данные у вас повреждены или не повреждены. Это стоит того, чтобы включать контрольную сумму данных у себя. В девелоперской базе неважно. В препроде на ваше усмотрение. А в продовской обязательно должно быть включено.

Иван Чувашов
Поддерживает техническую инфраструктуру компании Окко (dev, preprod, prod) в части баз данных
В двенадцатой версии появилась хорошая утилита pg_checksums. Если раньше до двенадцатой версии вам приходилось создавать новый кластер уже с включенной контрольной суммой данных и в него переносить данные, то с двенадцатой версии можно выключить текущий сервер PostgreSQL и запустить эту утилиту, и она просмотрит все страницы и запишет в заголовках контрольные суммы данных.

Спросите про прострой? Она настолько производительна, что будет упираться в ваш диск. Когда мы переводили кластер полтора-терабайтный во включенную контрольную сумму данных, у нас это заняло сорок минут.
Проделаем ту же процедуру с повреждением данных, что и ранее.
И теперь если прочитаем данные из таблицы pgbench_branches увидим, что у нас появилась ошибка о несовпадении контрольных сумм.
Но если мы всё-таки хотим извлечь эти данные, то есть флаг ignore_checksum_failure. Когда мы его включаем, у нас выдаётся предупреждение, что контрольная сумма данных не совпадает, но запрос исполняется.
Часто приложения используют только оперативные данные. PostgreSQL не обращается к старым страницам данных. И если в них есть повреждения, то мы можем узнать об этом слишком поздно, когда в резервных копиях тоже будут содержаться они.

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

Иван Чувашов
Строил отказоустойчивые кластеры на базе СУБД PostgreSQL и GreenPlum 6x;
Пример из жизни. Запросы шли в базу и некоторые их них повисали. На сутки, двое, трое. Потом пул запросов стало большим и они начал забирать всю оперативную память. Приходил omm killer и убивал PostgreSQL.

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

Предполагали три варианта:
  • что-то с картой видимостью,
  • что-то с индексами на этой таблице,
  • что-то с данными в самой таблице.

Решили удалить индексы и посмотреть, что будет – как только мы это сделали у нас приложение перестало работать. Это был фейл – приложение не работало три часа. Но нам стало сразу ясно, где проблема. Индексы ссылали на данные, которых нет в БД (страница данных нулевого размера).

Как вышли из ситуации? Создали новую пустую таблицу и по блокам перетаскивали данные со старой таблицы в новую. Потом били блоки на меньшего размера и так до тех пор пока не выявили семнадцать битых строк, для которых были ссылки в других таблицах, но в целевой отсутствовали.
ЕЩЁ

23–25 сентября 2021 года Иван проводит второй поток обучения продвинутого курса по PostgreSQL.
Посмотреть программу: https://slurm.club/3zk7PGq.

3
Кейс разбит на три ситуации и они о предотвращении проблем, а не исправлении.
Ситуация номер один

У нас есть три таблички: заказы, продукты и таблица, которая связывает многие ко многим. В какой-то момент времени бизнес решил, что нужно сравнивать значения в одно регистре (обратите внимание на тип у колонки id в таблице orders). Можно со стороны приложения переводить все данные к нижнему или верхнему регистру и делать сравнение в запросе. Но можно воспользоваться встроенным типом данных citext. Рассмотрим, как разработчики решили переходить на новый тип данных.

Иван Чувашов
Регулярно выступает на IT-конференциях
Первая команда у нас создаст эксклюзивную блокировку, которая дропнет constraint. Достаточно быстрая операция. Вторая – по изменению типа, он относится к одному виду типов, поэтому быстрее заменится, проблем с этим не будет. Далее меняем тип на связные таблицах и пытаемся создать constraint.

Что у нас получается – эксклюзивная блокировка на две таблицы product orders и orders, чтобы данные не изменялись. И это будет выполняться в одном потоке. Когда у нас 100-200 записей, то проблем нет – это доли секунды. Если записей стало больше, миллионы, тогда эти внешний ключ будет накатываться очень долго.

Разработчики выкатывают релиз, и у нас останавливается сервис. Моя была ошибка, что пропустил этот pull request. Разобрались, срубили запрос. Ночью мы остановили сервис бэкенда, накатили sql-скрипты.

Какие еще есть варианты решения? Можно воспользоваться конструкцией:
ADD CONSTRAINT ... NOT VALID
VALIDATE CONSTRAINT

В этом случае будут наложены более легкие блокировки.
Вариант 2, более специфичный, но рабочий. С десятой версии Postgres появилась логическая репликация product_orders в product_orders_replic, в которой уже есть внешний ключ product_orders_product_id_fkey. Когда мы скопировали все данные, мы взяли и поменяли таблички: product_orders -> product_orders_tmp, product_orders_replic -> product_orders. Это можно делать всё в одной транзакции, и будет достаточно быстро.
Ситуация номер два, из жизни

Иван Чувашов
Спикер курса по PostgeSQL
Представим, что есть таблички folders и folder_files. Мы хотим пробежаться по всем подпапкам и вытаскивать файлы, которые там есть. Когда у нас десятки-сотни тысяч записей, то проблем нет. Но когда появляются десятки миллионов записей, то тут нужно искать другие способы раскрутки дерева.

Но у нас был простой рекурсивный цикл. Что произошло? По каким-то причинам оптимизатор решил не использовать индекс, а делать полное сканирование таблицы folders. На каждом шаге создавать временные файлы большого размера на диске. Что привело к остановке базы данных из-за отсутствия места.
Было выбрано решение – разделить запрос на два:
  • отдельно рекурсивное cte, ограничив его по уровню вложенности;
  • отдельно маппинг результата cte с данными.
Тут можно предложить много способов оптимизации. Например, если дерево не меняется, то использовать не рекурсию, а вложенные интервалы. Но я не видел такой практической реализации.
Ситуация три – немного о другом

Иван Чувашов
Ведущий инженер Okko
Производительность базы данных сильно снизилась, что привело к деградации приложения. Анализ мониторинга железа не показывал явных проблем с производительность.

Загрузка CPU доходит до полтинника. Проблемы наблюдаются достаточно давно. Нагрузка 50% – это не критично. Нагрузка по диску каких-то 400 ops.
Пик в районе трёх часов не относится к данной ситуации
Смотрим логи, а там постоянно такие записи.
Ставка 62 секунды. Копаем дальше, смотрим различные метрики. И видим, что в таблице items отсутствуют первичный ключ. При вставке данных в таблицу history из-за внешнего ключа проверялось наличие записи в таблице items. Починили.
Проблему не решило. Копаем дальше, смотрим представление pg_statio_all_tables и раскрываем всю суть.

Иван Чувашов
DBA Southbridge
Запрос select * from pg_statio_all_tables показывает кто генерит большую нагрузку на диск. Первая строчка history_text_default. В дефолтовую секцию ничего не должно писаться, но именно она создает нагрузку на диск. А количество чтений с диска на три порядка больше, чем в позиции на втором месте. Дефолтовая секция, большое количество чтений с диска – и мы понимаем, что перестало работать секционирование таблицы history_text.

Мы использовали расширение pg_partman, пересобрали табличку history_text. По ссылке можно найти, как мы это сделали: https://github.com/Doctorbal/zabbix-postgres-partitioning#zabbiz-history-and-trends-tables.

Как только мы устранили проблему, деградация системы прекратилась и приложение заработало.
Надеемся, что кейсы были полезны.

А вот, что ждёт инженеров, которые пройдут продвинутый курс Ивана по PostgreSQL:

— Научитесь оценивать состояние кластера в критический момент, принимать быстрые и эффективные решения по обеспечению работоспособности кластера.

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

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

Это всё тоже написано на странице курса, а ещё там есть программа и немного о формате обучения: https://slurm.club/3zk7PGq.