Антон Дорошкевич | маяк в мире 1С и СУБД

У многих сервисов был свой дом в Яндексе....
Тёплый, уютный, надёжный!
Желаю всем кто сейчас восстанавливает эти ит-дома и работает на пределе физических возможностей огромной удачи, чтобы всё получалось с первого раза!
Я уверен, что все ИТ-шники сейчас с вами!
❤️❤️❤️

https://max.ru/id7729445882_biz/AaEcCIPHOmk

Обновление минорной версии PostgreSQL – практически обязательно!

Обычно к обновлению минорной версии ПО (последняя цифра в номере версии) относятся как к необязательной процедуре вычитывая новшества и примеряя их на себя.
Но за последние несколько месяцев, и это по моему мнению пока самое полезное применение Больших Лингвистических Моделей (ИИ, хотя он ещё вообще не ИИ))) ) в ИТ, с помощью ИИ найдено очень большое количество уязвимостей в самом широком спектре ПО.

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

В последних минорных версиях PostgreSQL 18.6, 17.11, 16.15, 15.19 и даже в 14.24 исправили 32 CVE!

❗Обратной стороной ИИ в ИТ является использование найденных уязвимостей с генерацией сценариев использования с нечеловеческой скоростью, поэтому обновления, которые раньше можно было отложить, теперь откладывать нельзя!

Как понять хватает ли памяти серверу PostgreSQL 2.0

Ранее мы уже получили верхнеуровневую информацию о заполненности кэша СУБД
https://max.ru/explorer1c/AZ4g2XVbeoY

Но этой информации не всегда достаточно чтобы принять решение хватает ли нам уже выделенного объёма.

Тут нам на помощь приходит ещё один столбец из pg_buffercache_summary(), а именно usagecount_avg, который даёт информацию о среднем значении счётчика использования буферов в кэше.

select usagecount_avg from pg_buffercache_summary()

Сам процесс "нагревания" и "охлаждения" буферного кэша основан на алгоритме Clock-Sweep.

Краткий смысл алгоритма:
Процесс "нагревания" - при каждом обращении к странице в кэше счётчик её использования (usage_count) увеличивается на единицу.

Чтобы избежать зацикливания в бесконечном круге горячих страниц максимальное значение ограничено, например в postgresql, числом 5.

Процесс "охлаждения" - циклически обходим страницы в буферном кэше.
- Если usage_count > 0, то уменьшаем его значение на единицу.
- Если usage_count = 0, то эта страница может быть вымещена из кэша, а на её место загружена новая.

Таким образом эффективность кэша можно оценить значением usagecount_avg > 2,5.
Если же значение usagecount_avg часто оказывается меньше единицы, то буферного кэша недостаточно и страницы постоянно оттуда вымещаются.
Сильно высокое значение usagecount_avg (4-5) наоборот говорит о том, что у нас слишком большой буферный кэш и его можно уменьшить.

Более подробную раскладку, какое количество страниц с каким значением счётчика в кэше можно получить запросом:

select usage_count, buffers from pg_buffercache_usage_counts()

Получив, например такой расклад:
usage_count | buffers
0 | 441196
1 | 236729
2 | 81823
3 | 44989
4 | 214970
5 | 159941

При этом usagecount_avg = 1,86

❗Конечно и это ещё не вся информация для тончайшей настройки PostgreSQL, но надеюсь что она даст ещё больше понимания как работает кэш и что с ним можно делать и нужно ли это делать.

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

Можно ли сделать поведение PostgreSQL в отношении потребления памяти процессами более жестким чем "мягкий предел"?

Кратко - да, можно))

Проблематику расхода памяти процессами вкупе с работой пула соединений 1С затрагивал в https://max.ru/explorer1c/AZvZrNDcSXU

Но это же только часть проблемы, есть сценарии и похуже, когда мы не можем никак повлиять через idle таймаут поскольку запрос выполняется...
И вполне может произойти ситуация, когда на выполнение запроса не хватит оперативной памяти и опять придёт охранник ОС OOMKiller и прекратит это безобразие...

В версии PosrgresPro Enterprise есть параметр max_backend_memory - он как раз таки задаёт максимальный объём памяти на один рабочий процесс и при его превышении прекращает выполнение запроса.

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

Сейчас ведём работы совместно с командой PostgrePRO по следующей логике:

Раз мы как администраторы СУБД задали ограничение max_backend_memory, то значит когда мы прервали выполнение запроса, то будет логичным вообще завершить этот процесс аналогично механике idle_session_timeout, таким образом снижая вероятность перерасхода оперативной памяти в целом на сервере.

Сегодня ровно год первому сообщению в канале!
Огромное спасибо всем вам!
Честно - было очень страшно начинать и я надеюсь, что держу свои обещания публикуя только практические кейсы без рекламы и воды!

С годовщиной, "Маяк в мире 1С и СУБД" 🎉🎉🎉🎉🎉🎉🎉

Теперь на багборде можно легко и быстро сравнить версии платформы по изменениям!

Уверен что это сильно упростит ответ на самый популярный вопрос - "А на какую платформу переходить?")))

Начнём...)

Как собрать для анализа запроса все временные таблицы с их содержимым?

Все мы хорошо знаем, что количество временных таблиц, создаваемых и используемых во время работы 1С огромно.
Очень часто нам для оптимизации скорости работы 1С требуется не только текст запросы и его план, но и содержание временных таблиц.
А это огромная проблема – таблиц может быть много, записей в них могут быть и тысячи и миллионы…
Какие только инструменты для этого не пытались использовать, и ТехЖурнал с записью всех запросов к СУБД, и трассировку запросов на уровне СУБД. А потом сбор всех этих данных и формирование таблиц с содержимым на основании этих данных.
Было очень тяжело и всегда неохота этим заниматься…

Насколько мне известно при работе с MS SQL так ничего и не поменялось.
А вот в PostgreSQL появилось расширение auto_dump.
«На пальцах» что делает расширение:
Следит за текстом запросов dump_on_query_string и когда находит нужный (например UPDATE _AccRg), то делает бэкап всех временных таблиц и их содержимым (можно и физических, но по моему мнению это достаточно опасно, ниже опишу почему), собирает предполагаемый и фактический планы запроса, сохраняет текст самого запроса, складывает это всё в каталог указанный в параметре output_directory
Так же можно настроить чтобы собирались запросы только длительнее чем auto_dump.timeout
Или запросы у которых по нашему мнению плохой план из-за большой разницы в предполагаемых и фактических значениях bad_plan_count_threshold и/или bad_plan_percent_threshold

Чем опасен параметр dump_persistent_tables, который позволяет сразу дампить физические таблицы?
Тем что физические таблицы могут быть огромного объёма и в итоге мы получим и тормоза и забитый диск.
Поэтому включать этот параметр нужно только полностью понимая что вы делаете.

Как потом работать с полученной информацией уже запросами к СУБД напрямую:
1. Создаём новую пустую базу из 1С. Это делается для того чтобы были созданы функции и типы данных, которые использует 1С.
2. Делаем dump физических таблиц, которые есть в запросе.
3. Восстанавливаем из дампа таблицы в базу созданную в п.1
4. Выполняем скрипт create_temporary.sql по созданию временных таблиц, который нам создал auto_dump
5. Выполняем скрипт insert_temporary.sql по наполнению временных таблиц, который нам создал auto_dump
6. Выполняем запрос query.sql, который нам создал auto_dump, на уровне СУБД и пытаемся его оптимизировать настройками СУБД, добавлением индексов (понимая как потом мы их сможем добавить на уровне 1С), улучшать план запроса манипулируя параметрами сбора статистики, менять сам запрос на уровне СУБД опять же понимая как мы потом это на 1С напишем и т.д.

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

Небольшой анонс поездок и мероприятий с моим участием:

08-10/09 - Обучение по кластеру 1с и PostgreSQL, Москва
11/09 - Хакатон-ИнфоСофт, Новосибирск
18/09 - Жёлтая конфа, Москва
26-27/09 - Партнёрский семинар 1С, Москва
08-10/10 - Infostart Tech Event, Санкт-Петербург

Сентябрь беспощаден!)