300 млн. строк? Что это? Разве такое возможно? Да, и если кто-то не может представить себе такую ситуацию — поверьте, вполне! В моей практике в ИБ 1С, в регистре сведений, хранились все изменения всех-всех ключевых документов/справочников/регистров сведений сильно измененной, большой и сильно допиленной УПП — создания, изменения и т.д. За год там накопилось около 320 млн строк. И, как всегда это бывает, понадобилось добавить колонку, то есть новый реквизит в эту таблицу. Вот алгоритм действий, с помощью которого можно довольно быстро решить задачу:
Все описанное актуально для ИБ, развернутых в клиент-серверном варианте. Я тестировал на MS SQL, но думаю, на остальных СУБД — все тоже самое. Тесты проводились на типовой УПП 1.3.78.1. Платформа 8.3.5.1460, MS SQL EXPRESS.
1. Делаем резервную копию базы-источника средствами СУБД. Подчеркиваю, средствами СУБД, так как нам нужен полный зеркальный снимок нашей базы данных. Не используем выгрузку в dt, да собственно, раз Вы столкнулись с такими объемами — то навряд ли подумаете об этом.
2. Из резервной копии поднимаем точно такую же базу, регистрируем ее на сервере приложений 1С. Далее, в моем примере UPP1 — база-источник, UPP2 — ее точная свежая копия.
3. Для начала добавим в базе UPP2 в справочник Номенклатура строковый реквизит, к примеру назовем его СтрокаПокупатель, тип строка (50), неиндексируемое. После того, как мы подняли из резервной копии новую базу, подключаемся к ней средствам СУБД, например с помощью Management Studio. Теперь нам надо выяснить, какая же таблица БД соответствует имени метаданных «Справочник.Номенклатура». Я для этой цели использовал удобную обработку из состава мобильных Инструментов Разработчика, но у кого ее нет под рукой — легко вытащить всю нужную информацию с помощью функции ПолучитьСтруктуруХраненияБазыДанных. Итак, я выяснил, что в моей конфигурации это таблица _Reference95. Будем считать, что именно в этой таблице у меня миллионы строк. Произведем ее очистку скриптом:
TRUNCATE TABLE UPP2.dbo._Reference95
Данная инструкция — самый быстрый способ очистить таблицу от данных. Но, для миллионных объемов — придется подождать, конечно во много раз меньше, по сравнению с типовой реструктуризацией.
4. Запускаем конфигуратор для UPP2. Снимаем нужный объект с поддержки, добавляем реквизит — все как обычно. Обновляем конфигурацию базы данных, так как строки из таблицы мы удалили — реструктуризация и обновление пройдет быстро.
5. Теперь нам снова нужно выяснить, какая колонка в БД SQL была добавлена в нашу таблицу справочника «Номенклатура». У меня это колонка имеет название _Fld33662, тип nvarchar(50), NULL. Идем в базу UPP1 с помощью MenStudio. Находим таблицу_Reference95 и добавляем столбец. Как добавить столбец, выполнить произвольный запрос — будем считать, что это все умеют, а кто не умеет — разберется самостоятельно. Обращаю внимание, что добавлять столбцы в уже существующую и заполненную таблицу можно только установив флаг «разрешить Null», иначе инструкция ALTER TABLE, которая изменяет таблицу — не сработает.
6. Выгоняем пользователей из UPP1. Далее выполняем следующий скрипт:
BEGIN TRAN Tr1
DELETE FROM UPP1.dbo.Config
DELETE FROM UPP1.dbo.DBSchema
DELETE FROM UPP1.dbo.Files
DELETE FROM UPP1.dbo.Params
INSERT INTO UPP1.dbo.Config
SELECT *
FROM UPP2.dbo.Config
INSERT INTO UPP1.dbo.DBSchema
SELECT *
FROM UPP2.dbo.DBSchema
INSERT INTO UPP1.dbo.Files
SELECT *
FROM UPP2.dbo.Files
INSERT INTO UPP1.dbo.Params
SELECT *
FROM UPP2.dbo.Params
COMMIT TRAN Tr1
7. Открываем конфигуратор UPP1, видим, что наш реквизит добавился в справочник «Номенклатура». Запускаем предприятие и выясняем, что новому реквизиту действительно соответствует колонка _Fld33662. Пробуем открыть любой элемент справочника или выполнить запросов, прочитав новый реквизит. Если прочиталось — все получилось. Для очистки совести можно выполнить проверку ссылочной целостности конфигурации в меню тестирования и исправления. У меня проблем не возникло.
8. Обращаю внимание, что мы добавили в таблицу новое поле, значение которого во всех строках этой таблицы, существовавших ранее — Null. Для справочников и регистров сведений — я считаю, это допустимо. Особенно, если вновь добавленный реквизит примитивного типа.
Для более сложной ситуации, когда например нужно добавить измерение ссылочного типа в таблицу регистра накоплений — здесь уже нужно анализировать и принимать решение о целесообразности таких действий.
Алгоритм тот же самый, но теперь колонки в искомую базу из ее зеркальной копии мы будем добавлять не в одну таблицу, а в две — таблицу самих движений и таблицу итогов. И, если измерение индексируемое — выясняем, какой индекс добавился (добавится 1-н некластерный индекс) и как изменится кластерный индекс и вручную выполняем эти изменения в базе-источнике для обеих таблиц. Смотрим состав полей индекса в зеркале, переносим в источник. Так как в таблицах регистров накопления значения ссылочных полей не допускают типа Null, то в этой ситуации придется выполнить скрипт:
UPDATE UPP1.dbo._AccumRg17789
SET UPP1.dbo._AccumRg17789._Fld33665RRef = Convert(binary(16), 0)
FROM UPP1.dbo._AccumRg17789
UPDATE UPP1.dbo._AccumRgT17798
SET UPP1.dbo._AccumRgT17798._Fld33665RRef = Convert(binary(16), 0)
FROM UPP1.dbo._AccumRgT17798
Так как у нас там миллионы строк — неизвестно, насколько быстро это отработает. К сожалению, для тестов у меня не было таких объемов данных, поэтому однозначного ответа дать не могу. Но, если очень нужно — можно немного усложнить эти запросы, обновлять эти поля в таблицах порциями, например по 100 тыс. записей и повесить выполнение на «тайм-джоб» в самой СУБД.
Надеюсь, мой опыт кому-нибудь будет полезен.
Related Posts
Восстановление последовательности документов при закрытии месяца в Бухгалтерия 3.0 не завершается
Заполнение табличных частей
Формирование сводных актов выполненных работ
Ввод поступления в переработку на основании передачи сырья (между организациями)- Конспект по установке сервера 1С на linux
Получение имени компьютера и его IP локально и в терминале
чтож 1с на уровне движка не могла такой же алгоритм реализовать…
Закат солнца вручную 🙂
Наверное, нужно особо отметить, что никакие иные действия с конфигурацией в период работ проводиться не должны.
Пользователям в базе в это время тоже не место, т.к. различия конфигурации на сервере и клиенте, при условии, что механизм контроля демонического обновления платформы их не сможет обнаружить, могут привести к чему угодно и почти с гарантией — к необходимости чистить юзерам кэш.
А вообще — приобретайте поставвку КОРП, делайте фоновое обновление и будет вам счастье без хирургии на уровне СУБД.
(1) kauksi, похожий алгоритм, но обходящийся всего одной базой, реализован в поставке КОРП.
сервер уровня КОРП поддерживает:
фоновое обновление конфигурации базы данных;
(2) asved.ru, разумеется. Но именно с конфигурацией. Обратите внимание, в скрипте копирования нет переноса таблицы ConfigSave — это в ней хранится информация о динамическом обновлении. Данные могут продолжать вводиться, то есть пользователи могут продолжать спокойно работать, пока мы делаем зеркало, очищаем в нем нужные таблицы, устанавливаем связи «имя метаданного — имя колонки БД». А вот когда уже работаем с источником — завершаем все соединения с базой, кроме своего собственного.
(4) в ConfigSave хранится не динамическое обновление, а сохраненная основная конфигурация, еще не примененная к БД (не записанная в Config)
(5) asved.ru, это и имел ввиду.
Дополнительно к пункту 5 статьи. Инструкцию ALTER TABLE можно выполнить с хинтом DEFAULT, указав значение колонки по умолчанию. В этом случаем добавляемая колонка может иметь строгий тип и не содержать NULL, то есть к примеру nchar(20), not null. В таком виде скрипт вот такого вида
ALTER TABLE #tt1 ADD tekst2 VARCHAR(20) Not NULL DEFAULT ‘sdergf’
добавит новую колонку в таблицу, а в уже имеющихся ранее строках в этой колонке пропишет значение по дефолту. То есть, по сути, проапдейтит таблицу. Однако, неизвестно, насколько это будет быстро, учитывая, что у нас там миллионы строк, опять-таки (см. п. 8). Ну то есть в каждом конкретном случае надо решать отдельно.
Сам лично считаю, что в общем случае лучше добавить колонку с возможностью содержания Null, а затем проапдейтить таблицу самостоятельно, выбрав только те строки, которые нужны. К примеру, зачем мне данные старых, закрытых периодов. Валяются там в таких строках в этой колонке null, ну и ладно.
Было что-то похожее когда-то уже
А не пробовали изменять иерархию в справочнике с элементов на группы? Не смотрели что на уровне СУБД происходит?
(2) asved.ru, по поводу КОРП поставки — пробовали уже так делать? Нормально отрабатывает на больших таблицах, не приходится потом бэкапы ворочать?
(3) asved.ru, как-то фоново обновляли конфу — система взяла и удалила все элементы справочника Файлы и все задачи. Вот было весело потом все из бэкапа. НЕ ПОЛЬЗУЙТЕСЬ ФОНОВЫМ ОБНОВЛЕНИЕМ БЕЗ АКТУАЛЬНОГО БЭКАПА!!! !C в этом плане — тупая система безо всякой оптимизации.
Вообще, для быстрой реструктуризации я переименовывал таблицу, предварительно генеря скрипт создания таблицы. Потом этим скриптом создаю новую таблицу, потом реструктуризирую, потом грохаю новую таблицу, предварительно генеря скрипт создания с новыми полями и индексами. Потом переименовываю старую таблицу обратно и АЛЬТЕР КООЛОНКА/АЛЬТЕР ИНДЕКС создаю соответствующие колонки и индексы.
(7) necropunk, в точку! Приходится и еще как! Нам в свое время повезло — у нас был актуальный бэкап и мы прямо в SQL накалтили из базы бэкапа убитые 1С-кой таблицы.
Вообще, как только заметили, что таблицы сдохли — сразу стопанули эту «фоновую реструктуризацию» и уже приняли решения, что будем обновлять через переименовывание таблиц и последующее создание колонок и индексов. Сами колонки на той же таблице файлов создались мгновенно (2,9 млн файлов на тот момент было), для документов последующий абдейт занял около пяти минут (заполнялись новые колонки суммами из табличных частей документов — тоже около 3 миллионов документов всего — 4 вида). До этого пытались обновить — реструктуризация за 6 часов не успевала произвестись на весьма неслабом сервере.
(4) действительно, в конфигсэйве не хранятся данные динамического обновления — они хранятся в конфиге с соответствующими постфиксами в именах «файлов». Мы динамическое обновление делали просто накатывая изменения в конфигсэйве в конфиг. Даже перезапускать 1С не всегда после этого было нужно, чтобы получить новые версии объектов. Но, конечно, если что-то в кеше — то оно могло и не перегрузиться с сервера, но после очистки кеша все становилось на свои места. С другой стороны, и при динамическом обновлении происходит периодически такая проблема. При том при динамическом обновлении есть одна важная проблема, которая периодически проявляется — это затирание предыдущих динамически обновленных вариантов данных, если при последнем обновлении (нединамическом) они не были изменены. Так что лучше накатывать конфигсейв на конфиг, чем играть в рулетку при обновлении 1С — меньше шансов попасть в висок. Ну и бэкапы делать перед всякими подобными действиями весьма не помешает.
(7) necropunk, на тесте отработало нормально, но тест был без параллельной нагрузки пользователями. Плотнее не тестил.
(9) starik-2005, наличие актуального бэкапа перед обновлением — это не везение, а то, что пишут в регламентах кровью. Иногда кровью ответственных за обновление, которые этот бэкап не сделали.
(7) necropunk, там похожий алгоритм, да, только автор выполняет манипуляции внутри одной информационной базы, а я использую зеркальный ее снимок. Грубо говоря, отличия только в том, что я чисто эмпирически выяснил, какие служебные таблицы нужно скопировать из зеркала, чтобы в источнике получить новую структуру. А в приведенной публикации этим не заморачиваются, так как все внутри одной и той же базы.
По поводу изменения типа уже существующего реквизита — можно попробовать обойтись без копирования, выполнив инструкция наподобие
ALTER TABLE ##tt1 ALTER COLUMN tekst nvarchar(100) not null
Когда я явистам (Javaстам) рассказывал, как работает реструктуризация в 1С на больших таблицах, они удивились. Конечно тут есть что оптимизировать в платформе. И подобные статьи способствуют движению в этом направлении.
(12) надо попробовать такую штуку проделать с изменением иерархии, посмотреть что он там меняет и можно ли скриптом это как-то перенести… А то реструктуризация после такого изменения часов 20 идет, а у нас на все регламенты не больше шести.
(14) necropunk, имеется ввиду, что будет в СУБД, если изменить глубину иерархии справочника?
(8) starik-2005, а как Вы «генерируете» текст скрипта? я так понял, удалось как-то автоматизировать создание текста запроса»АЛЬТЕР КОЛОНКА» ?
(15) у нас просто есть справочник, в нем надо изменить иерархию с «Элементы» на «Группы». Проблема в том, что справочник огромный. Реструктуризация идет очень долго, за ночь точно не успевает.
(15)
Могу рассказать на своем примере: на разработку инструмента выполнения практически любой длительной реструктуризации ушло примерно 4 дня. Могу сказать, что альтер это не самое интересное:
PS. Инструмент писал чисто в ознакомительных целях и с целью прокачки познании SQL. Распространять и использовать его в бою не собираюсь ибо чту лицензионну политику.
(6) Колонки Nullable плохо влияют на производительность выполнения запросов
(17) theshadowco, хоть статью напишите, для тех у кого уже нет выхода — только нарушать политику… Про иерархическую структуру интересно.
(17) theshadowco, особо автоматизировать этот процесс смысла нет и писать какой-то универсальный T-SQL код. Так как, ИМХО, таблицы содержащие огромные объемы данных (от 1 млн и больше) — не должны подвергаться частому изменению структуры. Такая таблица после ввода системы в эксплуатацию должна как минимум какое-то длительное время работать спокойно и все. А если Вы туда постоянно добавляете колонки, при этом строк там уже 100500 млн — ну что сказать… проектировать надо сразу нормально. Создание/обновление индексов, изменение типа колонки — все это мы здесь уже обсудили, руками все прекрасно делается, ради такой операции раз в год можно и потерпеть. С остальным платформа справляется сама.
(19) протестирую, что происходит в иерархическом справочнике — отпишусь.
(20) С утверждением почти согласен — надо все делать правильно и не переделывать.
Но как показывает опыт идеальный мир в котором все таблицы спроектированы верно и не меняются никогда существует только в мечтах.
Изменения таблиц размером от 70 гб или от 100 млн записей происходят более часто, чем хотелось бы.
ошибочно
(15) вариантов масса, но однозначно сказать, какой номер филда будет следующим, сложновато, поэтому скрипт создания генерил с помощью механизма скул студио, а потом из нового скрипта создания выдирал новые филды и вставлял в АЛЬТЕР КОЛОНКА и ИНДЕКС.
Данные операции по большому счету требуется лишь тем компаниям, где доступность сервисов стремятся сделать 24/7, а 1С своей тупо реализованной реструктуризацией (именно тупо, и никак иначе) сводит данные потуги на нет. Поэтому или система перестает развиваться архитектурно и к ней начинают лепить миллионы строк когда внешних обработок, компонент и прочего хлама, чрезвычайно затрудняющего поддержку, либо при изменении архитектуры приходится посылать на три буквы лицензионное соглашение с идиотами, тупо написавшими (именно тупо, и никак иначе) механизм реструктуризации и делать все прямыми руками.
(0) Dach, спасибо за статью.
Но она претендует на универсальность, при этом ничего не сказано, что данные действия нарушают лицензионное соглашение, кто то сделает (не совсем осознавая рисков), получит косяк и задаст вопрос в 1С, «я тут скрипт выполнил, теперь какие то глюки, ПАМАХИТЕ…». Ну и колонки с типом «содержит NULL» пока кроме как в иерархических справочниках не видел. Использовать этот тип в других объектах не советую т.к. может привести к снижению производительности.
(24) Sergey.Noskov, покажите мне того, кто обращался в 1С с «ПАМАХИТЕ» и получил ответ? Мы в свое время обращались к ним постоянно, посылали дампы в гигабайты, отправляли всю доступную информацию, а результат простой — да, есть проблема, пофиксим в следующем релизе. Остальное на тему «у нас сломалась база» в 1С даже не стоит отправлять — в ответ не будет ничего совершенно. Это я Вам как тот, кто в 1С обращался, заявляю. А уж если кто-то что-то в скуле не дай Бог поправил — тут в ответ, конечно, есть риск получить кучу г-на от 1С. Но это здесь — единственный риск, при том хоть какая-то реакция уже лучше, чем вообще никакой.
(24) Sergey.Noskov, так как методика основана на в первую очередь создании бэкапа — надеюсь, что воплей «памахите, хулюганы зрения лишают!!!» не будет )))))
Ну понятно, что скорость запросов может упасть. Каждый конкретный случай надо рассматривать отдельно. Кому-то критично не допустить простоя в 30 часов на обновление, кому то критично чтобы запросы не тормозили. В конце концов, ничего не мешает сделать в таблице колонку с возможностью содержания NULL, дать возможность пользователям работать. Затем создать новую таблицу, скопировав структуру таблицы-источника и убрав там NULL в новой колонке. И потихоньку перекачивать данные из одной таблицы в другую. Когда полностью перекачаются — изменить название на название исходной таблицу и вуаля. Подход рабочий, проверено в публикации, указанной в (7)
откройте для себя ddl триггеры. напишите покет методов обновления исходной таблицы новыми полями из *NG, подмены исходных таблиц «однострочными пыстышками» для уменьшения времени копирования в NG таблицы и отмены удаления. и никакой длительной реструктуризации . и «деревенских» способов. возможно еще потребуется заполнение полей значениями по умолчанию. «однострочные пустышки» нужны, чтобы создавались *NG таблицы .
(27) МихаилМ, какое событие DDL Вы предлагаете использовать в качестве инициализатора триггера? Вы знаете как выполняется типовая реструктуризация 1С? Создается новая таблица с новой структурой (добавленными вновь колонками) и данные копируются из старой таблицы в новую.
Так какое событие назначить триггеру? CREATE TABLE? А что, если это просто добавление таблицы нового объекта метаданных? Да, и как узнать внутри триггера — какая таблица — источник, где производить «обновление полей» из ng table?
Вы бы лучше написали свою статью, с блекджеком и балеринами, а мы почитаем и поставим Вам лайки-звездочки. А пока что звучит не конструктивно, уж извините!
по конечно для события creat table и анализ суфикса . как проходит реструктуризация — знаю. по событию delete table подменяем обратно таблицу пустышку. узнать — анализируя имя таблицы *ng.
(29) МихаилМ, как узнать, что create table — это create table в результате реструктуризации, а не в результате, что разработчик добавил метаданное в конфигурацию? И аналогично с delete — это реструктуризация удаляет или разработчик? Опять-таки, что такого особенного будет делать триггер, в отличие от того, что изложено в статье и в комментах? По моему, ему придется делать все тоже самое: очистка, добавление колонки, переименование. Ну и возвращаемся к вопросу, надо ли автоматизировать этот процесс. Кому надо — тот напишет себе процедуру или триггер, как товарищ из (17), кому не надо — тот один раз и руками все сделает.
(30) анализ имени таблицы на присутствие суффикса «ng» укызвает о реструктуризации. кому надо тот сделает. мой пост — провокация для пытливых умов, коими индустрия 1с небогата.
(25) starik-2005, так в том то и дело, что не помогут, да еще и вы им скажите «мы тут эмулятор ключа поставили, чет он сломался» (были и такие случаи)
PS А по поводу помощи от 1С — помогают, да долго, да не всем, но помогают. Хотите работающую техническую поддержку от 1С — заключайте договор ЦКТП.
(32) Sergey.Noskov, ну или договор, или свои специалисты, которые могут разрулить подобные проблемы. Все зависит от стоимости решения и требований к SLA поддержки. 1С вообще для 100% проектов, в которых SLA — час на решение проблем неработоспособности базы, не подходит. ибо они однозначно за час даже отреагировать не успеют. Они делают ресурсоемкий низкопроизводительный продукт, который содержит в себе достаточно много ошибок, но при этом стоимость разработчика для 1С ниже большинства других проектов, а количество каркасов решений, с которых начать творить свое — несоизмеримо больше, чем у конкурентов. Вот на этом они и живут, и жить будут. Просто если взялась контора за 24/7, объявив для своих клиентов данный уровень обслуживания, то она должна обеспечить уровень поддержки своими силами и не уповать на 1С в принципе. При таком подходе лицензионным соглашением в плане невлияния на данные путем прямых запросов к SQL можно подтереться, ибо все-равно 1С НЕСПОСОБНА предложить необходимый уровень обслуживания. Поэтому гарантии — это наличие бэкапа и специалистов. способных в самом худшем случае развернуть его в течение часа.
Вообще, лицензионное соглашение только обещает рассмотреть баги при отсутствии влияния на таблицы внешних по отношению к 1С факторов. То есть они лишь рассмотрят баги и воткнут их в багтреккер для последующего фикса — и не более. Если вы что-то делали в SQL и сообщаете в 1С о баге, то такой баг в багтреккер 1С не попадет. Вот и вся разница. А то, что придет мальчик из 1С и починит вам базу — это каким же надо быть идиотом, чтобы в это поверить. Мальчик не придет, и девочка тоже, никто вам ничего не будет чинить, ибо они 200% скажут, что у них ваша ошибка не повторяется, поэтому проблема в: железе, стороннем ПО, положении звезд и т.д. (но только если докажете, что не в железе и не в стороннем ПО, что в принципе невозможно доказать).
(33) starik-2005,
вот этим мы и занимаемся.
С аргументами согласен — проблемы есть и с поддержкой и с продуктами. Но глобально это общая проблема рынка — хочешь быстро решать вопросы — нанимай своих спецов, а не рассчитывай на поддержку. Кто из вендоров обеспечивает SLA в течении часа и не косячит в продуктах? Проблемы есть у всех, мы когда по результатам 2х дневнего анализа проблемы с высокой утилизацией процов, получили от MS ответ «в вашей базе очень много запросов», мы тож долго смеялись и такая поддержка стоит не соизмеримо больше подписки на диск ИТС.
Вообще мы отвлеклись от сути статьи… Про проблемы, особенно наболевшие, можно долго дискутировать))
(2) asved.ru,
в точку!
По реструктуризации не посдкажете, 1с таблицы NG поочередно записывает или ждет пока все создаст? Если у меня реструктуризация не может завершиться, ошибок по базе нет, можно ее обратно откатить просто удалив таблицы и накатив старую конфу или не получится уже если она по половине документов прошлась? Виснет на регистрах у меня…
(8) какая версия платформы использовалась?
(37)
8.3.6, если память не изменяет. Может быть и более новая. Но сдается мне, что ничего глобально не поправили, т.к. также создаются таблицы «new generation», в которые льется это все в процессе «фонового обновления», после чего происходит этакое переименовывание «новой генерации» в «старую» и сервер приложений получает инфу, что все, надо юзать только старые таблицы. Предположу, что если таблица достаточно большая, то сервер приложений рано или поздно «забывает» о том, что есть какое-то полуновое состояние у этой таблички (а если еще и связанные таблички есть — вообще мрак). Ну или если rphost валится, что часто бывает на высоконагруженных проектах, ибо то памяти лимит тогойт, то внешняя компонента в двух потоках одновременно инициализируется, то еще какая неприятность.