Нюансы работы с PostgreSQL в 3 кейсах от DBA

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

Разберем случаи из PostgreSQL: запросы в статусе idle in transaction, выключенные контрольные суммы данных, переполнение int4, убивающие базу временные файлы и загрузку CPU.

Кейс первый

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

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

У нас была ситуация, когда появились 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?

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

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

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

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

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

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

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

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

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

Еще есть другой вариант, не рассмотренный нами выше. Если вы используете инструмент управления пулом соединений, например pgbouncer, то ситуация решилась бы довольно просто:

– установка pgbouncer на паузу – команда pause;

– перезагрузка сервера PostgreSQL;

– снятие pgbouncer с паузы – команда resume.

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

Кейс второй

СУБД 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 обращается к данным, он рассчитывает контрольную сумму данных страницы, сравнивая её с сохраненной в заголовке страницы, и если она не соответствует, то он выдаёт ошибку.

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

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

Спросите про прострой? Она настолько производительна, что будет упираться в ваш диск. Когда мы переводили кластер полтора-терабайтный во включенную контрольную сумму данных, у нас это заняло сорок минут.

Проделаем ту же процедуру с повреждением данных, что и ранее.

И теперь если прочитаем данные из таблицы pgbench_branches увидим, что у нас появилась ошибка о несовпадении контрольных сумм.

Но если мы всё-таки хотим извлечь эти данные, то есть флаг ignore_checksum_failure. Когда мы его включаем, у нас выдаётся предупреждение, что контрольная сумма данных не совпадает, но запрос исполняется.

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

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

Пример из жизни. Запросы шли в базу и некоторые их них повисали. На сутки, двое, трое. Потом пул запросов стало большим и они начал забирать всю оперативную память. Приходил omm killer и убивал PostgreSQL.

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

Предполагали три варианта:

• что-то с картой видимостью,

• что-то с индексами на этой таблице,

• что-то с данными в самой таблице.

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

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

Кейс третий

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

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

Первая команда у нас создаст эксклюзивную блокировку, которая дропнет 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. Это можно делать всё в одной транзакции, и будет достаточно быстро.

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

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

Было выбрано решение – разделить запрос на два:

• отдельно рекурсивное cte, ограничив его по уровню вложенности;

• отдельно маппинг результата cte с данными.

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

Производительность базы данных сильно снизилась, что привело к деградации приложения. Анализ мониторинга железа не показывал явных проблем с производительность.

Загрузка CPU доходит до полтинника. Проблемы наблюдаются достаточно давно. Нагрузка 50% – это не критично. Нагрузка по диску каких-то 400 ops.

Смотрим логи, а там постоянно такие записи.

Ставка 62 секунды. Копаем дальше, смотрим различные метрики. И видим, что в таблице items отсутствуют первичный ключ. При вставке данных в таблицу history из-за внешнего ключа проверялось наличие записи в таблице items. Починили.

Проблему не решило. Копаем дальше, смотрим представление pg_statio_all_tables и раскрываем всю суть.

Запрос 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.

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