Особенности разработки больших и высоконагруженных баз 1С + Postgresql

Сейчас уже никого не удивишь базой размером более терабайта – такие объемы стали обыденностью. И высоконагруженные базы на 1С на PostgreSQL тоже уже не редкость.

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

Глава 1. Всё только начинается

Разработка крупной высоконагруженной системы “с нуля” – амбициозная задача для любого программиста, не только специалиста по 1С. С такой задачей столкнулась и одна из наших команд.

Кратко опишу саму задачу:

• Требовалось создать базу, агрегирующую данные из нескольких источников.

• Основным источником для этой базы выступала шина данных.

• Немаловажная особенность: данные должны были дополняться по мере их поступления: приходит новый пакет – предыдущие данные обновляются, в них вносятся дополнения и так далее.

• Нужно было предусмотреть обязательное хранение пакетов данных для анализа – чтобы в принципе понимать, из чего этот пакет в итоге собрался.

• Система должна обрабатывать около 4 миллионов первичных данных в месяц – при этом нагрузка распределялась по часам неравномерно, и основное поступление приходилось на рабочее время.

• Активных пользователей – до 1000.

• Общий объем данных за год – примерно 10 терабайт.

Команда успешно разработала систему, провела тестирование и приняла решение переводить ее на этап опытно-промышленной эксплуатации, но по частям: сначала подключить только 30% источников и пока работать без пользователей – использовать только фоновую загрузку данных.

Глава 2. Первый запуск. Первые проблемы

После перехода к опытно-промышленной эксплуатации начали проявляться первые проблемы.

Основная сложность, с которой столкнулась команда в самом начале – это недостаточность показателей производительности. Когда в системе работают пользователи, относительно понятно, как контролировать эффективность: можно просто настроить APDEX и отслеживать его динамику. Но когда пользователей нет, а вся нагрузка формируется только за счет фоновых заданий, возникают вопросы – что именно контролировать? Доходило до того, что приходилось смотреть загрузку процессора: например, если в рабочем режиме она составляла 80%, а потом вдруг падала до 40%, значит нужно идти разбираться. Поэтому мы решили разработать отдельные показатели производительности:

• Например, если основным источником данных является шина, можно контролировать размер очереди шины и наблюдать за динамикой. Если очередь растет – значит, возникли проблемы. Если убавляется – все хорошо.

• Второй показатель, который можно контролировать – скорость обработки самого пакета данных. Но здесь важно учитывать, что фиксация времени обработки каждого пакета вызывает дополнительную нагрузку, поэтому разумнее объединять данные в группы: по 100 или по 1000 пакетов, либо отслеживать каждый сотый, хотя это будет и не совсем корректно.

После внедрения показателей производительности стало очевидно, что база у нас не справляется – скорость обработки данных недостаточная. Здесь мы увидели два возможных выхода:

1. Оптимизация кода и запросов – самый логичный и прямой выход.

2. Распараллеливание обработки – разделение данных на пакеты и потоки. При этом важно помнить: запускать сотню потоков одновременно не стоит, иначе производительность резко просядет. Нужно предусмотреть паузы и распределение нагрузки, особенно если в будущем к системе подключатся реальные пользователи.

После того как скорость обработки удалось стабилизировать, примерно через месяц возникла новая проблема, на которую уже было принято решение подключить экспертов (хотя, возможно, это нужно было сделать и раньше) – база за месяц выросла до 5 терабайт при том, что предварительно планировалось, что ее прирост будет составлять примерно 10 терабайт за год.

Причем основная таблица у нас занимала около 3 ТБ, что на тот момент составляло больше половины от объема всей базы.

Простые подсчеты показывали: в таком темпе годовой объем превысит 50 терабайт – подобный рост размера был для нас неприемлем, и мы должны были это предотвратить.

Глава 3. Wraparound

Когда эксперты начали смотреть СУБД, оказалось, что там уже запустился autovacuum: to prevent wraparound – autovacuum с защитой от зацикливания. Сразу скажу – это не катастрофа, а всего лишь предупреждение, что в регламентных процедурах что-то не так, и на них нужно обратить внимание.

• Первым делом мы добавили себе некоторый срок для решения проблемы – увеличили параметр autovacuum_freeze_max_age, запускающий autovacuum: to prevent wraparound, до 500 миллионов (более чем в два раза от исходного значения 200 миллионов). Причем на больших базах его рекомендуют делать вплоть до двух миллиардов – половину от общего количества. Но нам хватило 500 миллионов, и остался задел на будущее.

• Начали разбираться в причинах дальше и увидели, что autovacuum заходит в таблицу редко, потому что значение autovacuum_vacuum_scale_factor у нас установлено в 0.2 (т.е. 20%). Это значит, что для таблицы в миллиард записей autovacuum придет тогда, когда там изменится 200 миллионов. При этом он будет выполняться очень долго и когда закончит, то опять придется начинать заново – у нас как раз сложилась такая ситуация. Поэтому мы уменьшили значение до 1%. Сразу сделаю оговорку, что у нас на тот момент использовалась PostgreSQL 10 – это нам пригодится в будущем.

• Когда мы начали анализировать, почему же autovacuum не справляется, то обнаружили, что за это отвечают две настройки – autovacuum_vacuum_cost_delay и autovacuum_vacuum_cost_limit: autovacuum_vacuum_cost_limit определяет количество условных операций, которые выполняет autovacuum в одном пакете, затем процесс делает паузу autovacuum_vacuum_cost_delay и продолжает работу. Такой «щадящий» режим в нашем случае оказался слишком медленным. Мы решили его сделать более агрессивным: снизили autovacuum_vacuum_cost_delay практически до нуля – оставили на всякий случай одну миллисекунду. После чего autovacuum обработал наши объемы буквально за несколько часов. Однако мы заметили побочный эффект: при старте autovacuum нагрузка резко возрастала, а производительность системы падала. Пока пользователей в системе нет – это вроде не критично, но с их появлением возникли бы серьезные проблемы. Мы немного поиграли с этими двумя настройками и вывели для себя следующие значения:

  • задержку между порциями обработки autovacuum_vacuum_cost_delay решили оставить прежней – 20 миллисекунд.

  • а размер порции autovacuum_vacuum_cost_limit увеличили в пять раз – до 1000.

Итого, мы выставили следующие настройки:

• Для агрессивного autovacuum:

  • autovacuum_vacuum_cost_delay = 20 (default 20)

  • autovacuum_vacuum_cost_limit = 1000 (default 200)

  • autovacuum_vacuum_scale_factor = 0.01 (default 0.2)

• Для регулярного vacuum freeze

  • autovacuum_freeze_max_age = 500кк (default 200кк)

  • vacuum_freeze_min_age = 50кк

  • vacuum_freeze_table_age = 150кк

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

Глава 4. Архитектура

Основная таблица в 3 ТБ как раз содержала пакеты данных. Когда мы начали ее анализировать, то заметили, что она не совсем оптимально разработана. И после серии мозговых штурмов мы сформировали ряд шагов для оптимизации хранения пакетов обмена:

• В исходном варианте пакеты сохранялись в текстовом виде сырых данных, по которым при необходимости мог производиться поиск по подстроке через LIKE (СОДЕРЖИТ). Но, разумеется, такой подход крайне неэффективен, потому что каждый такой запрос – это скан таблицы.

• Мы приняли решение выделить теги поиска. Эти теги отдельно формировались при загрузке пакета, при этом сам пакет при поиске больше не затрагивался. Это дало дополнительное преимущество – появилась возможность собирать статистику по тегам.

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

• Следующим этапом стало разделение пакетов и тегов. У нас PostgreSQL, а у PostgreSQL, в отличие от MS SQL при изменении строки не происходит ее перезапись – происходит добавление новой строки, а старая помечается как неиспользуемая и хранится так до тех пор, пока в эту таблицу не придет autovacuum. А так как autovacuum обрабатывал долго и долго не приходил, у нас на каждую живую строку скапливалось по 2-3 неживых – размер таблицы был искусственно завышен практически в 3 раза.

• Следующий немаловажный шаг – вынесение всех пакетов в отдельное хранилище. Без него, к сожалению, никак. Диски у нас не резиновые. А быстрые диски – дорогие. Поэтому задумайтесь: если у вас в базе есть дополнительные тяжелые данные, выносите их в отдельное хранилище.

Глава 5. Немного о нормализации

Сделаю небольшое отступление. К сожалению, в своей работе я уже сталкивался со справочниками, имеющими >100 реквизитов. На первый взгляд это кажется удобным: все доступно в одном месте. Но на практике возникают серьезные проблемы:

• В результате получаются слишком большие формы.

• Для открытия такой формы нужно получить объект, обработать и заполнить все поля.

• При изменении этот объект нужно перезаписать.

• При обращении к справочнику с таким большим количеством реквизитов возникают проблемы с производительностью запросов.

• Скорее всего, там есть еще и другие сложности, с которыми я просто еще не сталкивался.

Здесь я хочу напомнить о нормализации таблиц и требованиях третьей нормальной формы:

• Нет дублей строк – это условие, как правило, везде соблюдается, здесь все хорошо.

• Неключевые поля зависят только от полного ключа – в случае справочника это условие тоже соблюдается.

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

Глава 6. Запросы

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

• Применение консоли запросов на рабочей базе – самая большая и одновременно проще всего устранимая боль.
  Программисты обычно знают, что за запрос они пишут, но далеко не всегда понимают, как этот запрос повлияет на работу «боевой» системы. Поэтому тестировать разрабатываемый запрос нужно на своей отдельной базе или, если необходимы реальные данные – на копии продуктивной системы либо тестовой базе с аналогичным наполнением. Но использовать консоль запросов на проде – категорически нельзя. Тем более отлаживать код на рабочей базе – это даже не обсуждается.

• Сортировки и отборы в динамических списках – другая боль, с которой справиться гораздо сложнее.
  Пользователи часто хотят искать по любой части строки либо сортировать по произвольным полям – например, по полю «Примечание». Понятно, что индекс по примечанию никто делать не будет, но донести до пользователя, что такой поиск снижает производительность системы, очень сложно. Приходится искать обходные решения – например, использовать полнотекстовый поиск. Но у него есть и другие проблемы. Например, если вы реализовали свой полнотекстовый поиск на ElasticSearch, вам потребуется отдельная команда для его поддержки. А встроенный полнотекстовый поиск 1С страдает отсутствием отказоустойчивости: если индекс поврежден, его перестроение займет сутки, и все это время пользователи будут ждать. По-другому с ним, увы, никак.

• Временные таблицы.
  С одной стороны, возможность их использования сильно упрощает работу разработчика. Но с другой стороны – с ними нужно быть очень аккуратными. Например, если вы попытаетесь из большой таблицы отобрать по пользователю или его подчиненным все непроведенные документы, может получиться огромное количество данных. Для их обработки может не хватить оперативной памяти, и тогда система положит их на диск, что сильно замедлит их дальнейший анализ. Но в целом для PostgreSQL гораздо удобнее проработать 50 запросов в пакете, чем просчитать оптимальный план запроса для 50 соединений.

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

• Механизм ключей аналитики.
  В целом все знают, что такое ключи аналитики, и как с ними работать. Но этот механизм можно использовать немного иначе. Например, у вас есть регистр сведений из четырех реквизитов: первый, второй, третий, четвертый. Если вам часто нужно искать по определенным сочетаниям реквизитов – например, по второму и четвертому – чтобы не строить запросы, которые явно не будут оптимально работать, вы можете вынести сочетание этих реквизитов в отдельный справочник аналитик и искать уже его значению.

Глава 7. Индексы СУБД

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

В МS SQL есть удобная встроенная вьюшка для анализа индексов, но в PostgreSQL такого нет, поэтому мы пользуемся статистикой по индексам и по таблицам:

• Отслеживаем количество сканирований, используя расширения pg_stat_all_indexes, pg_stat_statements и pg_stat_kcache, чтобы понять, какие конкретно запросы читают с диска.

• Эту связку хорошо дополняет auto_explain, чтобы иметь и их планы выполнения под рукой.

• Собранные данные выгружаются в ElasticSearch для агрегации.

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

• Для контроля и поддержания актуальности индексов у нас разработан специальный внутренний инструмент. В том числе он позволяет получить статистику использования “ручных” индексов.

Глава 8. Реструктуризация v2

Что касается механизма реструктуризации версии 2.0 – если кто-то с ним не знаком, рекомендую доклад Рината Юлчурина, в котором подробно и качественно раскрыта тема.

Мы тоже столкнулись с одним из нюансов в новой версии реструктуризации: если в базе используются разделители (а они есть в большинстве типовых конфигураций), у вас с некоторой долей вероятности реструктуризация версии 2.0 работать не будет. Это особенность работы платформы при использовании СУБД PostgreSQL – она связана с тем, что если в конфигурациях используются разделители, их столбцы всегда идут последними.

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

Глава 9. Обновление PostgreSQL 10,11 > 14,15

Обновление версии СУБД – тема сложная и болезненная, но крайне важная.

Итак, нам нужно было обновить PostgreSQL с 10 до 15 версии, потому что по утверждениям экспертов PostgreSQL 15 быстрее.

• Сначала мы оценили задачу на уровне «вроде изян» – у нас все готово, достаточно только обновиться.

• Но перед любыми серьезными изменениями – выпуск тяжелого релиза, обновление платформы или версии СУБД – обязательно проводим нагрузочное тестирование.

• Провели тестирование и обратили внимание, что общая производительность системы по APDEX упала с почти 1 до 0.4.

• Первый подозреваемый – статистика. VACUUM FULL, ANALYZE, все обновили, но ситуация не изменилась – CPU и диски были перегружены.

• «Шеф, все пропало». У нас уже опускались руки. Как последний способ обратились в РКЛ за советом.

• Приходит ответ: «Попробуйте увеличить значение параметра конфигурации default_statistics_target, т.о. увеличить объем статистики, собираемой для отдельных столбцов, воспользовавшись командой
  ALTER TABLE… ALTER COLUMN… SET STATISTICS 10000
  – установить по отдельному реквизиту максимальный параметр статистики диапазонов 10000». И действительно – после того как мы так сделали, запрос начал работать заметно быстрее.

• Но это же только один запрос, а как быть с остальными – для каждого увеличивать количество диапазонов по реквизитам? Их очень много. И почему в pgAdmin этот запрос на PostgreSQL 10 и 15 выполняется одинаково, а в реальной базе план строится неоптимально? Решили копнуть чуть глубже и проверить сами запросы 1С. И действительно – оказалось, что многие из них можно оптимизировать: где-то убрать «ИЛИ», где-то – «В», где-то соединение поправить, где-то просто поменять порядок полей. Все эти действия привели к успеху – планы стали идентичны.

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

Комментарий из 2025г.
В PostgreSQL 17 эта проблема (и попутно несколько других) устранена.
Также PostgreSQL 17 получил много улучшений производительности.

Выводы

Какие из этого можно сделать выводы?

• Размер базы имеет значение! Чем меньше данных в рабочей базе, тем проще с ней работать: обновлять и проводить регламенты по обслуживанию.

• Из этого вывод – стараемся хранить только полезные данные.

• Исторические и технические данные – версии строк, архивные пакеты и прочую тяжелую информацию – стараемся хранить отдельно. Эти данные редко нужны в ежедневной работе, а место на больших и быстрых дисках дорогое.

• Следим за состоянием СУБД. Важно отслеживать не только объем диска или уровень нагрузки, но и состояние вакуумов, а также общее состояние системы – как она работает.

• Нужно обязательно иметь вменяемые метрики показательной производительности системы – особенно там, где отсутствует пользовательская нагрузка, либо в большом объеме присутствует фоновая нагрузка.

• Обязательно проводим нагрузочное тестирование перед выпуском в прод, перед обновлением платформы или СУБД. Представляете, что бы мы получили, если бы мы просто обновили версию СУБД без тестирования? Мы бы получили большую головную боль, а откатиться было бы довольно сложно. Да, я, наверное, выгляжу как безумец с плакатом: «Проводите нагрузочное тестирование!» – но всегда об этом говорю и не перестану говорить.

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

• Откуда черпать информацию для решения проблем? В первую очередь, смотрим доклады о PostgreSQL – с конференций Инфостарта и других (Highload, PgConf и т.д.). Что-то из этого к 1С не применимо, но что-то применимо, поэтому смотрим, а что можем, то используем для себя. Я для себя, например, очень часто беру какие-то фишки и стараюсь реализовать их у нас.

• Читаем статьи и книги. Например, на сайте Postgres Pro есть великолепная книга Егора Рогова «PostgreSQL 16 изнутри» – очень рекомендую, как минимум для начала.

Глава бонусная. 20 000 000 пакетов в сутки

Бонусом расскажу об интересном челлендже.

К нам пришла задача – загружать 20 миллионов пакетов в сутки. Источник – шина данных (rest), внутри бинарный файл и описание.

Вопрос звучал так: «Справится ли с этим 1С?» Как говорил известный персонаж: «Challenge accepted». Мы приняли вызов и решили проверить.

Оказалось, что 1С с такой нагрузкой вполне справляется. Опишу решение:

• Первым шагом разделяем поступающие данные на потоки.

• Далее отделяем файлы от самого пакета, чтобы в пакете указывалась ссылка на скачивание бинарного файла отдельно. Пусть он хранится в отдельной базе, хранилище S3 или просто на диске – а нам только ссылка на него нужна.

• Дальше обрабатываем этот пакет без промежуточной записи на диск – JSON позволяет это делать. Диск – всегда узкое место, поэтому максимально задействуем память и процессор.

• Считаем, что если данные к нам пришли через шину, они уже валидированы. Если все-таки требуется проверить какие-то данные, мы безусловно грузим этот пакет, а все дополнительные проверки выполняем отдельно сбоку..

Вопросы и ответы

Как выполняется нагрузочное тестирование? И кто пишет тесты?

Нагрузочное тестирование выполняется через Тест-центр. Сценарии тестов в основном пишет функциональная команда, так как именно она лучше понимает работу системы и особенности ее разработки. Затем эксперты приводят эти тесты к правильному виду и запускают само тестирование.

Нагрузочные тесты встроены в процесс разработки – пишутся параллельно созданию новой функциональности? Или же формируются отдельно – команда садится на месяц-два и пишет эти нагрузочные тесты?

Хотелось бы прийти к первому варианту. Но пока чаще используется второй вариант: тесты пишутся ближе к релизу. При этом мы их можем многократно использовать и дорабатывать.

Используете ли вы для отказоустойчивости репликацию базы PostgreSQL – аналог Always On?

Да, у нас используется отказоустойчивый кластер на базе Patroni, там разве что переключение пока происходит в полуручном режиме – требуются ручные действия, чтобы вторая реплика начала работать полноценно. Со временем планируем написать скрипты, чтобы это автоматизировать.

*************

Статья написана по итогам доклада (видео), прочитанного на конференции INFOSTART TECH EVENT.