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

16.09.13

База данных - HighLoad оптимизация

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

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

реструктуризация

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    2959    spyke    26    

42

Быстродействие типовой 1С

HighLoad оптимизация Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    5094    vasilev2015    19    

37

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7624    158    ZAOSTG    67    

96

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5956    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8845    ivanov660    6    

76

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

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

15.11.2023    5095    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

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

11.10.2023    16163    skovpin_sa    14    

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

И что будет, если меняется тип сложного поля, к примеру ссылочного (добавляется новый тип, скажем)?
4. OLEG4120 162 14.09.13 14:17 Сейчас в теме
(3) vvr908, На всех БД алгоритм одинако, при руструктуризации документа или другого объекта, состоящие, например, из 3 табличных частей, будет создано 4 таблицы с имена как у исходных таблиц и суффикса ng. Затем в эти новые таблици копируются данные по 1000 записей, после исходные таблици удаляются, а новые переименовываются. Т.е. изменив реквивит в одной из тч, будут копироваться все равно все 4 таблици. Вообще вся эта процедура упирается в производительность дисковой системы, процессор и память почти не задейтвованы. Отсюда такие низкие скорости
5. OLEG4120 162 14.09.13 14:25 Сейчас в теме
(3) vvr908, При ситуации из последнего, нужно рассматривать вторую часть статьи, она более универсальная, но скорость ниже
31. MariusUrsus 22.03.18 16:16 Сейчас в теме
(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", приложение "Хранение данных".
Прикрепленные файлы:
6. wbazil 138 16.09.13 09:10 Сейчас в теме
спасибо, довольно простое и оригинальное решение
7. ADirks 186 16.09.13 09:49 Сейчас в теме
Что-то слишком долго 18 часов. Может, индексы снести перед копированием?
Чисто для интересу, на MS SQL закопировал табличку из ~5М строк, 21 колонка - 13 сек.
10. OLEG4120 162 16.09.13 11:10 Сейчас в теме
(7) при копировании, получается таблица вообще без индексов, если вы про "SELECT * INTO ..."
11. ADirks 186 16.09.13 12:25 Сейчас в теме
(10) нет, я имел в виду
INSERT INTO _document39_vt431(
_document39_idrref, _keyfield, _lineno432, _fld433rref)
select * from _document39_vt431_copy

На таблицах _document39_vt... полюбому же PK с контролем уникальности, да ещё и кластерный поди, соответственно вставка будет притормаживать.
Помнится, когда в журнал документов (семёрошный) что-то добавляли, приходилось индексы отключать, и потом создавать по новой, на готовой таблице.
14. OLEG4120 162 16.09.13 14:04 Сейчас в теме
(11) ADirks, Да, возможно Вы правы)
16. ADirks 186 17.09.13 19:54 Сейчас в теме
(14) А есть возможность попробовать (сортировку по PK при вставке)? Интересно же. Интерес конечно достаточно праздный, но всё же...
19. OLEG4120 162 18.09.13 14:47 Сейчас в теме
(16) ADirks, +100 к Вашему скилу :)
время загрузки 2 часа. С sort в селекте и индексами.
20. ADirks 186 18.09.13 16:38 Сейчас в теме
(19) Это -100 к разработчикам из 1С, за идиотские кластерные индексы. Мало того, что бессмысленные, так ещё и вредные.

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

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

Почему так долго - из-за дисковой подсистемы сервера.
На Оракле, на нармальном сервере та же процедура несколько секунд, но Оракл - очень дорого, Майкрософт SQL - просто дорого.
13. Evgen.Ponomarenko 567 16.09.13 13:05 Сейчас в теме
(9)
На Оракле, на нармальном сервере та же процедура несколько секунд

Олег, Если на Оракле, операция занимает несколько секунд, а на PosgresSQL несколько часов - это явно ляп в настройках PosgresSQL, общие рекомендации это одно, а реальный тюнинг это другое. Смотрите на монитор производительности, ищите узкие места и устраняйте. Чаще всего нужно добавить возможность использовать больше памяти, и следите за свопом. Скульный сервер со свопом - куча дорогого металлолома.
uncle_Vasya; +1 Ответить
15. OLEG4120 162 17.09.13 08:06 Сейчас в теме
(13) Evgen.Ponomarenko, Скорее все Вы правы, но это не уменьшает значимости статьи, т.к. реструктуризация платформой - долгий процесс
12. ADirks 186 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

возможно будет легче
17. ADirks 186 17.09.13 20:04 Сейчас в теме
И перед такой операцией наверное имеет смысл проиндексировать соотв. образом таблицу копии. Время на построение индекса приплюсовать к времени копирования.
OLEG4120; +1 Ответить
18. Al-X 18.09.13 11:34 Сейчас в теме
Спасибо ! Взял на заметку.
25. МихаилМ 18.06.15 19:10 Сейчас в теме
осталось самое чуть-чуть:

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

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


Это из собственного опыта? На эту тему хорошо бы отдельную статью сделать...
27. necropunk 9 06.07.16 09:24 Сейчас в теме
А при изменении иерархии справочника с элементов на группы как быть?
28. chukawata 10 15.03.17 05:50 Сейчас в теме
Реструктуризация - ресурсоёмкая процедура. Log-файл (в SQL-варианте) растёт на десятки гигабайт и может занять всё свободное место на диске с БД, что закончится ошибкой Runtime Errore.
Прилагаю иллюстрацию - диаграмму падения свободного места и батники для слежения за свободным местом и за объёмом файла.
Прикрепленные файлы:
diskfree.pdf
diskfree.bat
tempdb.bat
29. AvalonE2008 100 20.07.17 17:46 Сейчас в теме
30. Xershi 1474 06.08.17 12:55 Сейчас в теме
По мотивам статьи сделал реструктуризацию на своей базе. Переименовал таблицу с 3 миллионами записей. По статистике обновление заняло бы 74 дня. После переименования оно заняло 15 минут. Считайте ускорение во сколько раз?) Это был переход с 8.3.6 на 8.3.8.
uncle_Vasya; +1 Ответить
32. sokir 2 22.10.18 13:04 Сейчас в теме
В 8.3.11.2867 это уже сделано и даже ещё намного более оптимизированнее.
https://wonderland.v8.1c.ru/blog/optimizatsiya-restrukturizatsii-bazy-dannykh/

Так что уже не актуально.
Оставьте свое сообщение