Rivalry2

HTML Diff

0 added 0 removed

Original 2026-01-01

Modified 2026-02-19

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

2 Иван Чувашов

3 DBA Southbridge

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

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

6 Иван Чувашов

7 Ведущий инженер Okko

8 У нас была ситуация, когда появились idle in transaction, и приложение начало тормозить - пул подключений в базе данных был забит. Они блокировали ресурсы, и нам нужно было срочно принимать какое-то решение, мы пошли самым простым путём, который напрашивается и предлагается вашему вниманию.

9 Первый вариант, который может быть - это выполнение команд 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 = 'название_БД';

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

11 Иван Чувашов

12 Сертифицированный администратор PostgreSQL: PostgresPro, 10 уровень "Эксперт"

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

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

15 Другой вариант - перезагрузка сервера PostgreSQL

16 Иван Чувашов

17 Спикер курса по PostgeSQL

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

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

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

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

22 Ещё вариант - перезагрузка бэкендов

23 Иван Чувашов

24 13 лет работает с базами данных

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

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

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

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

29 <ul><li>установка pgbouncer на паузу -<a>команда pause</a>;</li>

30 <li>перезагрузка сервера PostgreSQL;</li>

31 <li>снятие pgbouncer с паузы - команда resume.</li>

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

33 ЕЩЁ

34 В августе 2021 провели митап с Иваном по нюансам работы с PostgreSQL.

35 Иван Чувашов

36 Больше 6 лет работает архитектором БД и DBA

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

38 Давайте воспроизведем проблему сбоя дисковой подсистемы, затем покажем её решение. У нас есть PostgreSQL - тринадцатая версия. Создадим базу и инициализируем<a>pgbench</a>. Она существует, чтобы нагружать сервер, снимать метрики производительности. Но нам она нужна для других целей.

39 Рассмотрим таблицу pgbench_branches, в ней есть три поля и одна запись.

40 Найдем, где находится физически эта таблица на диске: psql -p5432 -d test -c "select pg_relation_filepath('pgbench_branches')";

41 Остановим кластер PostgreSQL. Откроем файл base/16839/16853 и допишем любой текст в середине файла. Сохраним его.

42 Можно воспользоваться просто командой:sed -i 's/@/@123@/' ~/13/main/base/16839/16853

43 Запустим PostgreSQL. Попробуем прочитать данные из таблицы.

44 В таблице также одна запись, но теперь в ней просто пустые строки.

45 Мы знаем, что файл у нас поврежден, но при этом PostgreSQL об этом не знает, и это достаточно серьезная проблема, с которой можно встретится в PostgreSQL.

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

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

48 Иван Чувашов

49 Поддерживает техническую инфраструктуру компании Окко (dev, preprod, prod) в части баз данных

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

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

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

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

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

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

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

57 Иван Чувашов

58 Строил отказоустойчивые кластеры на базе СУБД PostgreSQL и GreenPlum 6x;

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

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

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

62 <ul><li>что-то с картой видимостью,</li>

63 <li>что-то с индексами на этой таблице,</li>

64 <li>что-то с данными в самой таблице.</li>

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

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

67 ЕЩЁ

68 23-25 сентября 2021 года Иван проводит второй поток обучения продвинутого курса по PostgreSQL.

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

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

71 Иван Чувашов

72 Регулярно выступает на IT-конференциях

73 Первая команда у нас создаст эксклюзивную блокировку, которая дропнет constraint. Достаточно быстрая операция. Вторая - по изменению типа, он относится к одному виду типов, поэтому быстрее заменится, проблем с этим не будет. Далее меняем тип на связные таблицах и пытаемся создать constraint.

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

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

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

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

78 Ситуация номер два, из жизни

79 Иван Чувашов

80 Спикер курса по PostgeSQL

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

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

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

84 <ul><li>отдельно рекурсивное cte, ограничив его по уровню вложенности;</li>

85 <li>отдельно маппинг результата cte с данными.</li>

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

87 Ситуация три - немного о другом

88 Иван Чувашов

89 Ведущий инженер Okko

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

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

92 Пик в районе трёх часов не относится к данной ситуации

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

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

95 Иван Чувашов

96 DBA Southbridge

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

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

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

100 Надеемся, что кейсы были полезны.

101 А вот, что ждёт инженеров, которые пройдут продвинутый курс Ивана по PostgreSQL:- Научитесь оценивать состояние кластера в критический момент, принимать быстрые и эффективные решения по обеспечению работоспособности кластера.

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

103 - Поймете, как действовать в случае критических ситуаций с базой данных PostgreSQL и будете знать, где и с помощью какого алгоритма искать проблему.Это всё тоже написано на странице курса, а ещё там есть программа и немного о формате обучения:<a>https://slurm.club/3zk7PGq</a>.

104