IE 2017

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

Программирование - Инструментарий

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

Итак. Программисты 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

См. также

Комментарии
1. DERL (DERL) 13.09.13 12:17 Сейчас в теме
Спасибо! Взято на заметку... Статья сохранена в справочном каталоге :)
2. Олег (OLEG4120) 111 13.09.13 13:44 Сейчас в теме
3. Владислав Рожевский (vvr908) 365 14.09.13 12:14 Сейчас в теме
Интересная статья, спасибо.
Но что было бы еще интереснее узнать, так это актуальна ли информация для MS SQL (понятно, в общих чертах, без деталей)? Как в реальности проходит та же реструктуризация на MS SQL?

И что будет, если меняется тип сложного поля, к примеру ссылочного (добавляется новый тип, скажем)?
4. Олег (OLEG4120) 111 14.09.13 14:17 Сейчас в теме
(3) vvr908, На всех БД алгоритм одинако, при руструктуризации документа или другого объекта, состоящие, например, из 3 табличных частей, будет создано 4 таблицы с имена как у исходных таблиц и суффикса ng. Затем в эти новые таблици копируются данные по 1000 записей, после исходные таблици удаляются, а новые переименовываются. Т.е. изменив реквивит в одной из тч, будут копироваться все равно все 4 таблици. Вообще вся эта процедура упирается в производительность дисковой системы, процессор и память почти не задейтвованы. Отсюда такие низкие скорости
5. Олег (OLEG4120) 111 14.09.13 14:25 Сейчас в теме
(3) vvr908, При ситуации из последнего, нужно рассматривать вторую часть статьи, она более универсальная, но скорость ниже
6. BAZIL BAZIL (wbazil) 106 16.09.13 09:10 Сейчас в теме
спасибо, довольно простое и оригинальное решение
7. Алексей (ADirks) 177 16.09.13 09:49 Сейчас в теме
Что-то слишком долго 18 часов. Может, индексы снести перед копированием?
Чисто для интересу, на MS SQL закопировал табличку из ~5М строк, 21 колонка - 13 сек.
8. Сергей Борисов (juntatalor) 56 16.09.13 10:31 Сейчас в теме
Плюсанул, но разберитесь с оформлением статьи - опечаток много.

А постгресс у вас из коробки так работает (48ч на 25М записей)? Или есть какой-то "тюнинг"? Потому что это действительно ОЧЕНЬ долго.
9. Олег (OLEG4120) 111 16.09.13 11:08 Сейчас в теме
(8) Это долго, Posgres настроен в соответствии с рекомендациями 1с, по-моему были такие на ИТС. В целом, скорость работы в предприятии, очень неплохо, 50 пользователей, 1000 документов день.

Почему так долго - из-за дисковой подсистемы сервера.
На Оракле, на нармальном сервере та же процедура несколько секунд, но Оракл - очень дорого, Майкрософт SQL - просто дорого.
10. Олег (OLEG4120) 111 16.09.13 11:10 Сейчас в теме
(7) при копировании, получается таблица вообще без индексов, если вы про "SELECT * INTO ..."
11. Алексей (ADirks) 177 16.09.13 12:25 Сейчас в теме
(10) нет, я имел в виду
INSERT INTO _document39_vt431(
_document39_idrref, _keyfield, _lineno432, _fld433rref)
select * from _document39_vt431_copy

На таблицах _document39_vt... полюбому же PK с контролем уникальности, да ещё и кластерный поди, соответственно вставка будет притормаживать.
Помнится, когда в журнал документов (семёрошный) что-то добавляли, приходилось индексы отключать, и потом создавать по новой, на готовой таблице.
12. Алексей (ADirks) 177 16.09.13 12:35 Сейчас в теме
Точняк, кластерный, да ещё и по GUID. Бедненький сервер :)

в принципе, можно попробовать так:
INSERT INTO _document39_vt431(
_document39_idrref, _keyfield, _lineno432, _fld433rref)
select * from _document39_vt431_copy 
order by _document39_idrref, _keyfield

возможно будет легче
13. Евгений Пономаренко (Evgen.Ponomarenko) 510 16.09.13 13:05 Сейчас в теме
(9) OLEG4120,
На Оракле, на нармальном сервере та же процедура несколько секунд

Олег, Если на Оракле, операция занимает несколько секунд, а на PosgresSQL несколько часов - это явно ляп в настройках PosgresSQL, общие рекомендации это одно, а реальный тюнинг это другое. Смотрите на монитор производительности, ищите узкие места и устраняйте. Чаще всего нужно добавить возможность использовать больше памяти, и следите за свопом. Скульный сервер со свопом - куча дорогого металлолома.
uncle_Vasya; +1 Ответить 1
14. Олег (OLEG4120) 111 16.09.13 14:04 Сейчас в теме
(11) ADirks, Да, возможно Вы правы)
15. Олег (OLEG4120) 111 17.09.13 08:06 Сейчас в теме
(13) Evgen.Ponomarenko, Скорее все Вы правы, но это не уменьшает значимости статьи, т.к. реструктуризация платформой - долгий процесс
16. Алексей (ADirks) 177 17.09.13 19:54 Сейчас в теме
(14) А есть возможность попробовать (сортировку по PK при вставке)? Интересно же. Интерес конечно достаточно праздный, но всё же...
17. Алексей (ADirks) 177 17.09.13 20:04 Сейчас в теме
И перед такой операцией наверное имеет смысл проиндексировать соотв. образом таблицу копии. Время на построение индекса приплюсовать к времени копирования.
OLEG4120; +1 Ответить
18. Алексей Опарихин (Al-X) 18.09.13 11:34 Сейчас в теме
Спасибо ! Взял на заметку.
19. Олег (OLEG4120) 111 18.09.13 14:47 Сейчас в теме
(16) ADirks, +100 к Вашему скилу :)
время загрузки 2 часа. С sort в селекте и индексами.
20. Алексей (ADirks) 177 18.09.13 16:38 Сейчас в теме
(19) Это -100 к разработчикам из 1С, за идиотские кластерные индексы. Мало того, что бессмысленные, так ещё и вредные.

Рекомендую читануть на досуге: http://www.gotdotnet.ru/blogs/bezzus/1178/
21. Андрей Овсянкин (Evil Beaver) 4246 02.12.13 11:17 Сейчас в теме
(20) ADirks, не думаете же вы, что разработчики платформы не знают что такое кластерный индекс и недостатки GUID полей?
Есть определенное архитектурное решение. Мне кажется, что есть определенные причины, почему было сделано так, а не иначе. Вот вы как думаете, зачем в платформе сделано именно так?
22. Алексей (ADirks) 177 02.12.13 17:25 Сейчас в теме
(21) Я думаю, что кластерные индексы они делают потому что существует мнение, что у всякой таблички обязательно д.б. кластерный индекс. Ну как бы и ладно, лепят и лепят. Собственно, кластерный индекс действительно весьма желателен. Но штука то в том, что GUID - случайное число, по определению. И кластерный индекс по такому полю приводит к неоправданному увеличению времени вставки, что самое печальное - чем больше табличка, тем больше времени уходит на вставку одной записи. Это конечно архитектурное решение, но именно что "определенное".
С другой стороны, понятно, что в общем случае кластерный индекс возможно слепить только по GUID. Ну так дайте возможность лепить свои индексы, по ситуации. Так нет же, развеж можно...
starik-2005; +1 Ответить 1
23. Андрей Овсянкин (Evil Beaver) 4246 02.12.13 18:20 Сейчас в теме
(22) ADirks, про собственные индексы согласен.
24. Артур Аюханов (artbear) 879 18.06.15 18:04 Сейчас в теме
(20) Алексей, что за статья? ссылка http://www.gotdotnet.ru/blogs/bezzus/1178/ недоступна сейчас
25. Михаил Максимов (МихаилМ) 18.06.15 19:10 Сейчас в теме
осталось самое чуть-чуть:

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

+ появляются такие полюшки как: замена таблиц представлениями (например 1 кладр на все базы), свои индексы, секционирование,
классическая репликация, файловые группы.
starik-2005; Sley; утюгчеловек; +3 Ответить
26. Ридван (утюгчеловек) 27.07.15 10:04 Сейчас в теме
осталось самое чуть-чуть:
предложенный подход реализовать в виде ddl триггера(все субд поддерживают) и забыть о реструктуризации
+ появляются такие полюшки как: замена таблиц представлениями (например 1 кладр на все базы)


Это из собственного опыта? На эту тему хорошо бы отдельную статью сделать...
27. Сергей necropunk (necropunk) 5 06.07.16 09:24 Сейчас в теме
А при изменении иерархии справочника с элементов на группы как быть?
28. Игорь Иванов (chukawata) 9 15.03.17 05:50 Сейчас в теме
Реструктуризация - ресурсоёмкая процедура. Log-файл (в SQL-варианте) растёт на десятки гигабайт и может занять всё свободное место на диске с БД, что закончится ошибкой Runtime Errore.
Прилагаю иллюстрацию - диаграмму падения свободного места и батники для слежения за свободным местом и за объёмом файла.
Прикрепленные файлы:
diskfree.pdf
diskfree.bat
tempdb.bat
29. Алексей Чухланцев (AvalonE2008) 92 20.07.17 17:46 Сейчас в теме
30. Xer shi (Xershi) 277 06.08.17 12:55 Сейчас в теме
По мотивам статьи сделал реструктуризацию на своей базе. Переименовал таблицу с 3 миллионами записей. По статистике обновление заняло бы 74 дня. После переименования оно заняло 15 минут. Считайте ускорение во сколько раз?) Это был переход с 8.3.6 на 8.3.8.
uncle_Vasya; +1 Ответить
Оставьте свое сообщение