Общие сведения, исходные данные

Продолжаем публиковать некоторые детали проектов по миграции больших баз данных 1С с MS SQL Server на PostgreSQL.

В предыдущем примере речь шла о миграции одной большой 10+ Тб базы данных 1С с MS SQL на PostgreSQL. Сегодня речь пойдет о проекте миграции сразу сорока с лишним баз данных 1С. Но это не 40 разных ИС. Это четыре группы распределенных информационных систем, схожих по конфигурации, но, тем не менее, разных, с базами размером от 50 Гб до 2 Тб каждая.

Что еще из начальных условий?

Система обмена данными – DB Replication (не 1C РИБ). DB Replication – репликация данных на уровне самих баз данных, минуя приложение 1С, и используя собственный транспорт и систему дистрибуции пакетов.

Базы сильно отличаются по степени нагрузки, в некоторых из них нагрузка периодически достаточно высокая (ЦП под 100%, потребление ОЗУ) – интенсивное перепроведение документов, тяжелые отчеты, тяжелые регламентные операции и т.п. А также в периоды регламентных операций (типа закрытия периода) трафик обмена данными между БД становится очень интенсивным, создавая серьезную нагрузку на подсистему обмена.

Технологическое окно имеет максимальный размер двое суток (выходные). Вся инфраструктура развернута на Windows + MS SQL Server 2014 + 1C 8.3.14

Топология системы обмена данными каждой из четырех систем – «звезда». Одна центральная база и множество региональных.

Центральная база не только аккумулирует региональные данные, но в ней активно работают пользователи и изменяют данные, региональные базы также обмениваются некоторой информацией между собой. Поэтому DB Replication поддерживает свою систему правил с настраиваемой логикой фильтрации обмена на уровне записей (маршрут пакету назначается в зависимости от содержимого – например, от значений реквизитов объектов 1С – «Организация», «Склад» и проч.)  Каждый узел (база данных) – это свой географический регион пользователей. При этом все серверы физически находятся в одной локации.

Требуется:

• Перевести работу серверов СУБД на связку Linux + PostgreSQL, а серверов 1С – на связку Linux + 1C.

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

Теперь пройдемся по рискам.

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

Перечислю основные риски, на которые точно следует обращать внимание и заложить в план миграции эти работы по адаптации ИС.

1. Риск отказа функциональности и интеграционных механизмов.

   1. В связи с обновлением СУБД заказчик также обновил платформу 1С до одной из последних версий (с сохранением версии совместимости) по причине эволюции взаимодействия 1С и PostgreSQL, а также в связи с возможностями конвертации данных MS SQL → PostgreSQL. Естественно, с полноценным тестированием разного функционала. Сама по себе смена платформы 1С тоже может быть очень нетривиальной задачей. Об этом у нас есть отдельная статья.

   2. Отсутствие поддержки внешних компонент (COM, ActiveX, DLL) на Linux.
      Например, будет нарушена интеграционная работа с Word/Excel/Outlook через COM-объект, с CRM API, токенами и средствами криптографии, с внешними драйверами кассовых аппаратов, сканерами ШК, СМС-шлюзами и пр.

   3. Запуск внешних программ/скриптов, завязанных на Windows. Не работают конструкции вида: ЗапуститьПриложение("powershell -file ...").

   4. Файловые операции: разделители пути «\» и «/»; регистрозависимость файловой системы Linux (Отчет.XLSX ≠ отчет.xlsx ≠ ОТЧЕТ.xlsx); специальные системные пути типа «C:\Temp\», «C:\Program Files».

   5. NULL и пустые значения: MS SQL и PostgreSQL могут по-разному обработать NULL.

   6. Веб-сервисы и HTTP-вызовы: технически работают, но возможны проблемы с кодировками. Windows по умолчанию отдаёт CP1251, Linux ждёт UTF-8. Соответственно, в обменах русские буквы превращаются в кракозябры.

2. Деградация производительности (отклика от баз).
   Тут следует разделить проблему на три разных по своей природе:

   1. Первый блок назовем «обычные дни». В силу того, что один и тот же 1С-запрос может совершенно по-разному отрабатывать на двух СУБД, то часто случаются ситуации, когда оптимально работающий на MS запрос начинает отвратительно работать на PG и приходится переписывать запросы. Особенно это заметно на запросах к виртуальным таблицам типа СрезПоследних на больших таблицах. Тут почти гарантированно можно получить задержку, измеряемую даже десятками минут. Об этом несколько месяцев назад публиковали отдельную статью. Кто не видел, рекомендую ознакомиться.

      То есть в системе, наверняка, будут участки кода и запросы, которые придется переписывать и адаптировать под реалии PostgreSQL.

   2. Второй блок проблем – это просадка производительности из-за многопользовательской нагрузки. Которая дополнительно усугубляется во время выполнения каких-то тяжелых операций типа закрытия периода.

      Ситуация отличается от предыдущей тем, что по отдельности на стенде все работает быстро и предсказуемо, но как только в систему зашла критическая масса пользователей от былой шустрости не осталось и следа. Хотя на MS всё работало бодро и проблем на этом функциональном участке не было. Это ситуация наиболее неприятная, т.к. смоделировать ее на стенде практически не возможно – ни одно нагрузочное тестирование не гарантирует и не защищает вас от неё (оставим за скобками сложность самого тестирования). Поэтому здесь важно иметь инструментарий и компетенции для локализации проблемных участков и принятия правильных решений для их купирования.

   3. И последний третий блок проблем – это возможная просадка производительности в скорости обмена данными между узлами распределительной ИС.

Методика перехода

Сразу скажу, что сценарий единомоментного перевода всех баз на PostgreSQL был отброшен как слишком рискованный, ибо получить одновременно в сорока базах воз проблем – это такое себе развлечение. Поэтому перевод планировался постепенным – по одной базе или группами, плавно конвертируя и подключая к контуру обмена всё новые и новые базы PG, не отключая при этом базы MS! То есть пользователи работают либо с базой MS, либо с PG, а сами базы при этом поддерживаются в синхронном состоянии. Это важный аспект на проекте.

За онлайн синхронизацию всех баз вне зависимости от СУБД (MS SQL или PostgreSQL) отвечала DB Replication. Чтобы было наглядно, вот так выглядит топология распределенной системы с одновременной работой MS SQL и PostgreSQL на период самой миграции и еще несколько месяцев после:

Да, серверов стало больше. Каждая база PG – это отдельный инстанс (экземпляр) PostgreSQL. Но оно того стоило – об этом чуть ниже.

Первичная конвертация из MS в PG проводилась штатной утилитой ibcmd. Далее DB Replication синхронизирует оперативные изменения данных. Ранее достаточно подробно описывал этот этап миграции. Суть его, если совсем кратко, заключается в том, что конвертация MS → PG может происходить долго и очень долго, вплоть до нескольких дней. И ни в какое технологическое окно, естественно, влезть не удастся. Поэтому конвертация идёт столько, сколько идёт, с возможными повторами, исправлением ошибок в данных и т.п. А репликация в это время накапливает все оперативные изменения в очередь. И как только целевая база PG будет готова, очередь репликации начнет прокачиваться с четкой последовательностью всех транзакций.

Этапы проекта

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

Укрупненная схема этапов:

Синим цветом отмечены задачи, которые выполняет заказчик, а зелеными – Софтпоинт. Если посмотреть, то всё, что связано с функциональной частью ИС: тестирование, анализ и адаптация кода, запросов 1С – это зона ответственности заказчика и/или подрядчиков, которые поддерживают ИС в части 1С. Зона ответственности Софтпоинт – это сама миграция данных, обеспечение их целостности и консистентности, контроль за производительностью системы, во время и после миграции, обеспечение обратного моста на случай нерешаемых проблем.

1. Обновление платформы.
   Исходная версия платформы была 8.3.14 в режиме совместимости 8.2.16, заказчик самостоятельно обновился до 8.3.25 с сохранением режима совместимости. Эта часть проекта была вне нашей зоны ответственности, поэтому деталей тут не будет. Длительность этапа на все информационные системы составила ~4 месяца.

2. Развертывание тестовой среды
   Для одной ИС на стенде были развернуты несколько распределенных баз, чтобы отрабатывать различные варианты логики обмена между ними (не просто «всё-всем»). Конфигурация стенда, естественно, обговаривается заранее: количество серверов, их характеристики, ПО, права и т.д., поскольку это тот контур, на котором будет проводиться конвертация, функциональное и нагрузочное тестирование, отрабатываться сценарии переключения пользователей и т.п.

   

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

   Поделюсь ошибкой, с которой столкнулись при конвертации некоторых баз. Ее суть: невозможно вставить значение NULL в поле, в котором запрещено значение NULL. Дело в том, что в PG в самых разных таблицах очень много полей приобрели свойство ISNULLABLE = FALSE (т.е. не допускают NULL), хотя в MS эти же поля были ISNULLABLE = TRUE (т.е. допускают значение NULL). И в MS некоторые из этих полей действительно содержали NULL. Откуда там взялись такие значения – определить сейчас уже затруднительно, т.к. ИС имеет многолетнюю богатую историю.

   Решение проблемы:

   1)  Сконвертировать сначала пустую конфигурацию без данных, чтобы ошибка конвертации не возникала, и найти все таблицы и поля, поменявшие свойство ISNULLABLE с TRUE на FALSE.

   2)  Воспользоваться скриптами поиска во всех исходных базах MS таблиц и строк с проблемными данными. Найденные NULL-ы устранить либо путем удаления строк, либо преобразованием значения.

   Под катом выложил краткий алгоритм со скриптами, которые помогут вам выявить «проблемные» таблицы и колонки, если вы сталкивались с этой ситуацией. Еще раз акцентирую внимание, что подобная проблема может появится в БД с большей историей развития и наполнения данными, особенно с большим количеством разных интеграций. Скорее всего, в моно-базе таких ошибок не будет. Вопрос «почему оно появилось?» не стоит. Разбираться в первопричинах нет абсолютно никакого смысла, важно обойти ситуацию.

   Алгоритм и скрипты поиска полей со свойством NOT NULL

   1. В пустой сконвертированной базе выгружаем список полей со свойством не Null в sql-файл для возможности создания таблицы с этим же данными в MSSQL:

   SELECT table_name, column_name
   FROM information_schema.columns
   WHERE table_schema = 'public'  
     AND is_nullable = 'NO'
     and column_name like '_fld%'
     order by 1;

   Имя целевой таблицы (target table) для экспорта - rst_not_nullable_field.

   2. В исходной базе(ах) MS:

   a)  Создать пустую таблицу rst_not_nullable_field

   create table rst_not_nullable_field(
   table_name nvarchar(max),
   column_name nvarchar(max))

   b)  Выполнить скрипт из файла из п.1 – заполнить таблицу rst_not_nullable_field данными.

   c) Выполнить скрипт ниже для получения списка таблиц и колонок, в которых присутствует значение NULL. Результат будет в таблице rst_is_nullable_field.

   select t.name as sqlname, c.name as fieldname into rst_is_nullable_field
   from sys.columns as c
   inner join sys.tables as t
   on c.object_id = t.object_id
   inner join rst_not_nullable_field as n
   on c.name = n.column_name
   and  t.name = n.table_name
   where system_type_id in (34, 99)
   and c.is_nullable = 1
   order by 1
   
   create table rst_is_nullable_count(
   null_count int,
   sqlname nvarchar(max),
   fieldname nvarchar(max))
   
   declare @cmd nvarchar(max)
   
   DECLARE  _cursor CURSOR LOCAL FOR
   
   select '
   insert into rst_is_nullable_count
   
   select count(*), '''+ sqlname +''', ''' + fieldname + ''' from ' 
   + sqlname + ' where '+ fieldname + ' is NULL' from rst_is_nullable_field
   
   OPEN _cursor
   FETCH NEXT FROM _cursor	INTO @cmd 
   WHILE @@FETCH_STATUS=0
   BEGIN
   	exec sp_executesql @cmd
   FETCH NEXT FROM _cursor	INTO @cmd 
   END
   CLOSE _cursor
   DEALLOCATE _cursor
   
   select * from rst_is_nullable_count where null_count <>0
   

4. Адаптация ИС под новую СУБД

   Здесь подрядчик, отвечающий за 1С, выполнил все работы, убирающие риски отказа функциональности и интеграционных механизмов, описанные выше: оптимизация пользовательских запросов, переписывание некоторых блоков кода 1С. Чем больше у вас внешних взаимодействий, тем больше времени нужно закладывать на тестирование функционала.

   Одной из сложностей стал расчет себестоимости. Деградация части sql-запросов на одном из этапов расчета была связана с неверным/нестабильным планом построения запроса в PG. Нужно было переписать тяжелые запросы на стороне 1С, чтобы платформа генерировала другие sql-запросы и платформа 1С. О проблеме со СрезПоследних на PG писал в одной из прошлых статей. На данном проекте заказчик выбрал сценарий переписать код на стороне 1С, а не пользоваться подсказками (хинтами) на стороне sql.

   Роль Софтпоинт – первоначальный анализ причин замедления, выявление проблемных запросов с помощью системы мониторинга Perfexpert.

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

   1. На данном этапе возникла ситуация, когда один из отчетов сильно деградировал в скорости. Пока подрядчик переписывал его на стороне 1С, пользователи его выполняли в базе данных на MS. То есть основная оперативная работа шла в базе PG, а несколько отчетов (да, их оказалось по факту более одного) получали некоторое время в базе MS, пока разработчики их не допилили. Причины преимущественно всё те же – СрезПоследних по разным регистрам сведений.

   2. Еще один момент, на который стоит обратить внимание – это актуальные статистики (=вовремя пересчитанные) в PG для больших таблиц. Чем больше строк, тем дольше не будет срабатывать AUTOVACUUM ANALYZE. По умолчанию порог срабатывания в PG выставлен в 20%. Т.е. для таблицы со 100 000 строк нужно, чтобы изменения (insert/update/delete) произошли как минимум с 20 тыс. строками. А неверная статистика – это неправильный план построения запросов, приводящий часто к очень серьезному замедлению запроса. Подробно об этом с примерами писал в статье Записки оптимизатора 1С (ч.14.3). Отличия в обслуживании статистик в MS SQL и в PostgreSQL. Настоятельно рекомендую к прочтению.

   3. И, пожалуй, последнее, на что необходимо обращать внимание и за чем следить – это правильные настройки выделения оперативной памяти серверу PostgreSQL. Их много, и с наскока не всегда можно понять, какая настройка за что отвечает. Для этого у нас есть отдельный цикл статей про память в PostgreSQL. Также рекомендую ознакомиться, кто не видел.

6. Отключение баз MS от контура обмена.
   Базы MS сосуществовали с базами PG несколько месяцев, пока не прошли все циклы бизнес-процессов и не удостоверились, что, во-первых, весь функционал работает, а, во-вторых, работает со скоростью не хуже, чем в MS. Соответственно, постепенно базы MS отключались от контура репликации и серверные мощности высвобождались.

7. Перевод серверов 1С с Windows на Linux
   Это задача не была в зоне ответственности Softpoint. И весь проект серверы 1С прекрасно работали на Windows. Обычно основные сложности здесь – это заставить правильно работать периферию: сканеры, принтеры и т.д. В целом проект показал, что задачи можно распараллелить – есть ветка перевода СУБД на PG, а есть ветка по переводу серверов 1С на Linux. И ставить одно в зависимость от другого не обязательно.

Заключение

Спасибо, что дочитали до конца. Возможно, получилось несколько сумбурно. Но, надеюсь, основную идею миграции MS SQL на PostgreSQL больших высоконагруженных и распределенных ИС донести удалось.

Переход лучше делать порционным – по одной/несколько баз данных. Это позволит минимизировать риски просадки производительности, локализовав их в отдельной базе данных или группе баз данных, не допуская деградации производительности на всём предприятии.
Такую топологию, с одновременно работающими экземплярами баз данных и на MS, и на PG, можно получить, в принципе, не только с помощью DB Replication. Есть разные системы обмена данными. Но ее настройка тянет на отдельный подпроект и нужно обязательно закладывать ресурсы на него. Кроме того, технология двустороннего обмена данными между MS и PG (в нашем случае это DB Replication) позволила организовать обратный мост, благодаря которому пользователи смогли выполнять свои основные функции в системе на PostgreSQL, а часть функционала (некоторые отчёты) параллельно делали в базе на MS SQL до тех пор, пока разработчики не закончили его адаптацию под PostgreSQL. Обратный мост мог даже позволить вернуть полностью всех пользователей на MS без потери данных, если бы произошел сбой/просадка производительности, не позволяющий дальше выполнять пользователям их функции в PG. Но, к счастью, этого не потребовалось.

По нашему опыту такая страховка нужна, и она уже не раз выручала наших заказчиков в разных проектах миграции (не обязательно только MS → PG). А если у вас в контуре обмена работают десятки нагруженных баз данных с хорошим информационным потоком, то для вас это действительно актуально, т.к. для ИТ-службы эта ситуация может быть тем самым блок-фактором, из-за которого она боится даже начинать этот проект.

Другие наши статьи по миграции данных, а также некоторые другие, важные при запуске системы 1С на PostgreSQL:

1. Гетерогенная распределенная система как способ миграции больших и не очень баз данных с MSSQL Server на PostgreSQL.

2. Миграция с MSSQL Server на PostgreSQL. Предпосылки.

3. Миграция терабайтной базы 1С: УПП с платформы 1C 8.1 на 8.3.

4. Как мигрировать большую 10+ Тб базу 1С из MS SQL в PostgreSQL и уложиться в трехчасовое окно.

5. Записки оптимизатора 1С (ч.12).  СрезПоследних в 1C: Предприятие на PostgreSQL. Почему же так долго?

6. Цикл статей по настройке работы PostgreSQL с оперативной памятью.