Ускорение реструктуризации таблиц

16.05.2017 32 Comments

Инструментарий разработчика, Разработка





    документ
    Duplicate Object
    Rename
    colomn
    Колонка _fld1097

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

Итак. Программисты 80lvl скорее всего знают все и даже больше, чем описано в статье, поэтому эта публикация будет ориентирована в первую очередь на новичков. А так как у новичка скорее всего ни репутации и стартмани — все скрипты я не буду прикреплять, а выложу в статье.

Поехали. Предположим в вашей конфигурации есть некий документ, с 5 табличными частями. В СУБД (в нашем примере PosgreSQL, но все ниже сказанное справедливо и других СУБД) такой документ предстанет в виде 6 таблиц

Предположим вам необходимо добавить реквизит в табличную часть _document39_vt415, узнать какая именно табличная часть можно либо специальными обработками, либо просто посмотрев несколько записей из таблицы в самой СУБД. Что произойдет далее, точнее что сделает платформа 1С, она создаст копии всех 6 (!) таблиц документа и начнет копирование в них данных из старых таблиц — начнется реструктуризация. Процесс этот, мягко говоря, не быстрый. Почему я вообще пишу эту статью, потому что в моем случаи: количество документов (записей в _document39 было 1М) и записей в табличных частях 25М, процесс реструктуризации документа средствами 1С занял 48 часов. Так вот мы попытаемся обмануть платформу.

Продолжаем, добавляем реквизит в табличную часть в конфигураторе, у меня это число длинной 10, точность 0 (во время всех манипуляций его можно не закрывать), сохраняем, но не обновляем. Переименовываем все таблицы документа в pgAdmin или чем вы там пользуетесь (у меня это пара pgAdmin и EMS SQL Manager PostgreSQL), например _document39 в _document39_src

И создаем копии наших переименованных таблиц (пустые) с первоначальными именами, в нашем примере делаем пустую копию _document39_src с именем _document39.

Копии я создавал в EMS SQL Manager лишь потому, что в нем это проще, но можно и в  pgAdmin. В нем надо в контекстном меню таблицы выбрать Скрипты — CREATE и в окне SQL редактора изменить имя таблицы на новое.

Если посмотреть в предприятии, у нас нет ни одного документа.

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

Смотрим какое имя получила новая колонка таблицы, которая соответствует новому реквизиту.

У меня это _fld1097. Возвращаемся к нашей исходной таблице, которую мы переименовали в _document39_src, добавляем новую колонку в нее

Ставим значение по умолчанию, здесь 0 и жмем ОК. Весь процесс занял около 1 часа (в 48 раз быстрее). После того как колонка создана, стираем значение по умолчанию и переименовываем таблицу обратно (у нас в _document39)

Запускаем предприятие и проверяем. Радуемся или плачем.

 

Итак, это мы добавили реквизит, рассмотрим теперь случай, если нам надо изменить тип реквизита, например, было число (5, 2), надо число (10, 4), или добавить индексов.

Тут есть два варианта.

Вариант первый. Создаем копии таблиц и заливаем в них данные из основной таблицы

SELECT * INTO _document39_copy FROM _document39;

SELECT * INTO _document39_vt415_copy FROM _document39_vt415;

SELECT * INTO _document39_vt431_copy FROM _document39_vt431;

SELECT * INTO _document39_vt434_copy FROM _document39_vt434;

SELECT * INTO _document39_vt437_copy FROM _document39_vt437;

SELECT * INTO _document39_vt444_copy FROM _document39_vt444;


После этого очищаем исходные таблицы, т.е. доходим до момента, когда 1С думает, что у нас нет записей в таблицах документ. Делаем все необходимые изменения в конфигураторе и обновляем. Теперь нам надо вернуть данные назад

NSERT INTO _document39(
_idrref, _version, _marked, _date_time, _numberprefix, _number,
_posted, _fld556, _fld392rref, _fld393rref, _fld394, _fld395,
_fld579, _fld396, _fld397, _fld398rref, _fld399, _fld400, _fld401rref,
_fld1018rref, _fld403, _fld402rref, _fld404rref, _fld405, _fld538rref,
_fld406, _fld407, _fld408rref, _fld409rref, _fld410rref, _fld411rref,
_fld412rref, _fld413, _fld414)
select * from _document39_copy; -- ~60min (1.5 M records)

INSERT INTO _document39_vt431(
_document39_idrref, _keyfield, _lineno432, _fld433rref)
select * from _document39_vt431_copy;

INSERT INTO _document39_vt434(
_document39_idrref, _keyfield, _lineno435, _fld436rref)
select * from _document39_vt434_copy;

INSERT INTO _document39_vt437(
_document39_idrref, _keyfield, _lineno438, _fld439rref, _fld440rref,
_fld441, _fld442rref)
select * from _document39_vt437_copy;

INSERT INTO _document39_vt444(
_document39_idrref, _keyfield, _lineno445, _fld446rref)
select * from _document39_vt444_copy; --3 min

INSERT INTO _document39_vt415(
_document39_idrref, _keyfield, _lineno416, _fld426rref, _fld423,
_fld419rref, _fld421, _fld420, _fld536, _fld425, _fld418, _fld422,
_fld428rref, _fld427rref, _fld417rref, _fld429, _fld424)
select * from _document39_vt415_copy; --16588297 строк, 18.5 h

Запускаем предприятие и проверяем. Радуемся или плачем.

Вариант второй. Кто-то считает, что INSERT INTO работает медленно, поэтому можно использовать следующие скрипты, работающие не с копиями таблицы а с файлами на диске

COPY BINARY _document39
TO 'e:/_document39';

COPY BINARY _document39_vt431
TO 'e:/_document39_vt431';

COPY BINARY _document39_vt434
TO 'e:/_document39_vt434';

COPY BINARY _document39_vt437
TO 'e:/_document39_vt437';

COPY BINARY _document39_vt444
TO 'e:/_document39_vt444';

COPY BINARY _document39_vt415
TO 'e:/_document39_vt415';

где ‘e:/_document39’ это файл в корне диска е.

Скрипт загружающий данные обратно

COPY BINARY _document39
FROM 'e:/_document39';

COPY BINARY _document39_vt431
FROM 'e:/_document39_vt431';

COPY BINARY _document39_vt434
FROM 'e:/_document39_vt434';

COPY BINARY _document39_vt437
FROM 'e:/_document39_vt437';

COPY BINARY _document39_vt444
FROM 'e:/_document39_vt444';

COPY BINARY _document39_vt415
FROM 'e:/_document39_vt415';

На этом, пожалуй все.

Как видно, процесс это все равно долгий (около 18 часов у меня). Что мы получили, около 19 часов против 48 при изменении типа реквизита и добавлении индексов, и около 1 часа против 48 часов при добавлении реквизита.

PS. У меня есть подозрение, что на других СУБД реструктуризация средствами платформы будет быстрей. К тому же у меня стоял старый PosgresSQL, еще 8.2.4-3.1

Related Posts

32 Comments

  1. DERL

    Спасибо! Взято на заметку… Статья сохранена в справочном каталоге 🙂

    Reply ↓
  2. OLEG4120

    ) рад помочь

    Reply ↓
  3. vvr908

    Интересная статья, спасибо.

    Но что было бы еще интереснее узнать, так это актуальна ли информация для MS SQL (понятно, в общих чертах, без деталей)? Как в реальности проходит та же реструктуризация на MS SQL?

    И что будет, если меняется тип сложного поля, к примеру ссылочного (добавляется новый тип, скажем)?

    Reply ↓
  4. OLEG4120

    (3) vvr908, На всех БД алгоритм одинако, при руструктуризации документа или другого объекта, состоящие, например, из 3 табличных частей, будет создано 4 таблицы с имена как у исходных таблиц и суффикса ng. Затем в эти новые таблици копируются данные по 1000 записей, после исходные таблици удаляются, а новые переименовываются. Т.е. изменив реквивит в одной из тч, будут копироваться все равно все 4 таблици. Вообще вся эта процедура упирается в производительность дисковой системы, процессор и память почти не задейтвованы. Отсюда такие низкие скорости

    Reply ↓
  5. OLEG4120

    (3) vvr908, При ситуации из последнего, нужно рассматривать вторую часть статьи, она более универсальная, но скорость ниже

    Reply ↓
  6. wbazil

    спасибо, довольно простое и оригинальное решение

    Reply ↓
  7. ADirks

    Что-то слишком долго 18 часов. Может, индексы снести перед копированием?

    Чисто для интересу, на MS SQL закопировал табличку из ~5М строк, 21 колонка — 13 сек.

    Reply ↓
  8. juntatalor

    Плюсанул, но разберитесь с оформлением статьи — опечаток много.

    А постгресс у вас из коробки так работает (48ч на 25М записей)? Или есть какой-то «тюнинг»? Потому что это действительно ОЧЕНЬ долго.

    Reply ↓
  9. OLEG4120

    (8) Это долго, Posgres настроен в соответствии с рекомендациями 1с, по-моему были такие на ИТС. В целом, скорость работы в предприятии, очень неплохо, 50 пользователей, 1000 документов день.

    Почему так долго — из-за дисковой подсистемы сервера.

    На Оракле, на нармальном сервере та же процедура несколько секунд, но Оракл — очень дорого, Майкрософт SQL — просто дорого.

    Reply ↓
  10. OLEG4120

    (7) при копировании, получается таблица вообще без индексов, если вы про «SELECT * INTO …»

    Reply ↓
  11. ADirks

    (10) нет, я имел в виду

    INSERT INTO _document39_vt431(

    _document39_idrref, _keyfield, _lineno432, _fld433rref)

    select * from _document39_vt431_copy

    На таблицах _document39_vt… полюбому же PK с контролем уникальности, да ещё и кластерный поди, соответственно вставка будет притормаживать.

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

    Reply ↓
  12. ADirks

    Точняк, кластерный, да ещё и по GUID. Бедненький сервер 🙂

    в принципе, можно попробовать так:

    INSERT INTO _document39_vt431(
_document39_idrref, _keyfield, _lineno432, _fld433rref)
select * from _document39_vt431_copy
order by _document39_idrref, _keyfield

    возможно будет легче

    Reply ↓
  13. Evgen.Ponomarenko

    (9)

    На Оракле, на нармальном сервере та же процедура несколько секунд

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

    Reply ↓
  14. OLEG4120

    (11) ADirks, Да, возможно Вы правы)

    Reply ↓
  15. OLEG4120

    (13) Evgen.Ponomarenko, Скорее все Вы правы, но это не уменьшает значимости статьи, т.к. реструктуризация платформой — долгий процесс

    Reply ↓
  16. ADirks

    (14) А есть возможность попробовать (сортировку по PK при вставке)? Интересно же. Интерес конечно достаточно праздный, но всё же…

    Reply ↓
  17. ADirks

    И перед такой операцией наверное имеет смысл проиндексировать соотв. образом таблицу копии. Время на построение индекса приплюсовать к времени копирования.

    Reply ↓
  18. Al-X

    Спасибо ! Взял на заметку.

    Reply ↓
  19. OLEG4120

    (16) ADirks, +100 к Вашему скилу 🙂

    время загрузки 2 часа. С sort в селекте и индексами.

    Reply ↓
  20. ADirks

    (19) Это -100 к разработчикам из 1С, за идиотские кластерные индексы. Мало того, что бессмысленные, так ещё и вредные.

    Рекомендую читануть на досуге: http://www.gotdotnet.ru/blogs/bezzus/1178/

    Reply ↓
  21. Evil Beaver

    (20) ADirks, не думаете же вы, что разработчики платформы не знают что такое кластерный индекс и недостатки GUID полей?

    Есть определенное архитектурное решение. Мне кажется, что есть определенные причины, почему было сделано так, а не иначе. Вот вы как думаете, зачем в платформе сделано именно так?

    Reply ↓
  22. ADirks

    (21) Я думаю, что кластерные индексы они делают потому что существует мнение, что у всякой таблички обязательно д.б. кластерный индекс. Ну как бы и ладно, лепят и лепят. Собственно, кластерный индекс действительно весьма желателен. Но штука то в том, что GUID — случайное число, по определению. И кластерный индекс по такому полю приводит к неоправданному увеличению времени вставки, что самое печальное — чем больше табличка, тем больше времени уходит на вставку одной записи. Это конечно архитектурное решение, но именно что «определенное».

    С другой стороны, понятно, что в общем случае кластерный индекс возможно слепить только по GUID. Ну так дайте возможность лепить свои индексы, по ситуации. Так нет же, развеж можно…

    Reply ↓
  23. Evil Beaver

    (22) ADirks, про собственные индексы согласен.

    Reply ↓
  24. artbear

    (20) Алексей, что за статья? ссылка http://www.gotdotnet.ru/blogs/bezzus/1178/ недоступна сейчас

    Reply ↓
  25. МихаилМ

    осталось самое чуть-чуть:

    предложенный подход реализовать в виде ddl триггера(все субд поддерживают) и забыть о реструктуризации

    + появляются такие полюшки как: замена таблиц представлениями (например 1 кладр на все базы), свои индексы, секционирование,

    классическая репликация, файловые группы.

    Reply ↓
  26. утюгчеловек
    осталось самое чуть-чуть:

    предложенный подход реализовать в виде ddl триггера(все субд поддерживают) и забыть о реструктуризации

    + появляются такие полюшки как: замена таблиц представлениями (например 1 кладр на все базы)

    Это из собственного опыта? На эту тему хорошо бы отдельную статью сделать…

    Reply ↓
  27. necropunk

    А при изменении иерархии справочника с элементов на группы как быть?

    Reply ↓
  28. chukawata

    Реструктуризация — ресурсоёмкая процедура. Log-файл (в SQL-варианте) растёт на десятки гигабайт и может занять всё свободное место на диске с БД, что закончится ошибкой Runtime Errore.

    Прилагаю иллюстрацию — диаграмму падения свободного места и батники для слежения за свободным местом и за объёмом файла.

    Reply ↓
  29. AvalonE2008

    Спасибо за статью!

    Reply ↓
  30. Xershi

    По мотивам статьи сделал реструктуризацию на своей базе. Переименовал таблицу с 3 миллионами записей. По статистике обновление заняло бы 74 дня. После переименования оно заняло 15 минут. Считайте ускорение во сколько раз?) Это был переход с 8.3.6 на 8.3.8.

    Reply ↓
  31. MariusUrsus

    (3) Для ссылочного поля механизм тот же самый. изменятся тип — 

    bytea

    (уникальный идентификатор ссылки) и шаблон наименования поля — добавится 

    <sql_name>rref.

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

    <sql_name>_type|<sql_name>_n|<sql_name>_s|<sql_name>_l|<sql_name>_d|<sql_name>_rtref|<sql_name>_rrref,

    т.е. идентификатор типа, поля примитивных типов, указатель (тип) ссылки, ссылка.

    Подробнее и с картинками см. в книге «Профессиональная разработка в системе 1С:Предприятие 8. Издание 2. Том 2», приложение «Хранение данных».

    Reply ↓
  32. sokir

    В 8.3.11.2867 это уже сделано и даже ещё намного более оптимизированнее.

    https://wonderland.v8.1c.ru/blog/optimizatsiya-restrukturizatsii-bazy-dannykh/

    Так что уже не актуально.

    Reply ↓

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *