Секционирование таблиц и индексов в мире 1С

Публикация № 975144

Администрирование - Оптимизация БД (HighLoad)

1C 8 SQL Server индексы неплатформенные оптимизация производительности недокументированные возможности секционирование сегментирование обслуживание СУБД

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

Зачем все это

Файловые группы! Секционирование таблиц и индексов! Размещение таблицы на нескольких дисках! Собственные индексы для таблиц и другие замечательные возможности СУБД, которые платформа 1С не поддерживает "из коробки".

Все это применяется во многих других системах, но не у нас, ведь мы используем более продвинутые технологии:

  • Нет смысла разделять базу, таблицы или индексы на отдельные файлы для распределения по дискам, ведь в век SSD это пустая трата времени.

  • База стала большой, неповоротливой и с множеством ошибок в данных? Начнем жизнь с чистого листа (ну или почти с чистого) - свертка базы все решает!

  • Ускорение бэкапирования за счет отказа от сохранения исторических данных в базе - тоже не про нас. Ведь бэкапировать один файл базы удобнее.

  • Проблемы блокировок и неактуальных статистик вообще к нам не относятся, потому что платформа 1С сама все оптимизирует.

Уже сейчас на просторах нашей Родины все чаще можно встретить внедрения информационных систем на базе 1С с активным количество пользователей более 1000, а размером баз более 1 ТБ. Я думаю (или очень сильно надеюсь), что именно при подобных внедрениях работа с базами 1С меняется, а это привносит новые требования как к самой платформе, так и к необходимым компетенциям администраторов и разработчиков. Одним из таких требований является иной подход к обслуживанию базы данных, который нельзя сделать стандартными средствами платформы 1С. Как вы могли догадаться, речь идет о секционировании таблиц и индексов.

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

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

Все, что будет ниже, относится к клиент-серверному режиму работы и рассматривается в контексте Microsoft SQL Server. Но, фактически, может быть использовано и на PostgreSQL. 

 
 Вопросы лицензирования

Принцип работы

Что же такое секционирование и для чего оно используется? В общих чертах, секционирование - это разбиение таблиц и индексов на некоторые блоки, в качестве которых может выступать файловая группа (логическое разделение) или файл (физическое разбиение). Блоки могут быть разных размеров, находиться на разных дисках и иметь различные специфичные для них настройки. Как обычно, вся самая подробная информация о секционировании SQL Server находится в официальной документации, мы же рассмотрим несколько примеров его использования с описанием плюсов и минусов этого подхода.  

Разделяй и властвуй

 

Для SQL Server создание секций выполняется в несколько этапов. Опустим этап проектирования и рассмотрим по шагам простой пример.  У нас есть информационная база 1С "Partitioning", структура метаданных которой состоит из 2 документов, 4 регистров накопления и 4 справочников.

Схема метаданных

Структура метаданных дана просто для информации, все примеры будут на 1 или 2 таблицах. Как можно догадаться, примеры с секционированием будут выполнены на регистрах "Продажи_Секции" и "ТоварыНаСкладах_Секции". На стороне SQL Server эти объекты представлены несколькими таблицами. Нас интересуют только физические таблицы для упрощения примеров. Таблицы итогов и служебные таблицы секционировать не будем.

Метаданные Поле 1С Поле SQL
Имя таблицы
РегистрНакопления.ТоварыНаСкладах_Секции Период _Period
_AccumRg84 Регистратор _RecorderTRef
  Регистратор _RecorderRRef
НомерСтроки _LineNo
Активность _Active
ВидДвижения _RecordKind
Склад _Fld85RRef
Номенклатура _Fld86RRef
Количество _Fld87
РегистрНакопления.Продажи_Секции Период _Period
_AccumRg69 Регистратор _RecorderRRef
  НомерСтроки _LineNo
Активность _Active
Подразделение _Fld70RRef
Контрагент _Fld71RRef
Сумма _Fld72

Все таблицы базы содержат данные с 2010 до 2019 года, чтобы наглядно продемонстрировать действия секционирования.

Создание файловых групп

Для начала создадим логические блоки базы данных - файловые группы. Сделать это можно как с помощью SQL-скрипта, так и с помощью графического интерфейса в SQL Managment Studio (SSMS).

USE [master]
GO
ALTER DATABASE [Partitioning] ADD FILEGROUP [FG1]
GO
ALTER DATABASE [Partitioning] ADD FILEGROUP [FG2]
GO
ALTER DATABASE [Partitioning] ADD FILEGROUP [FG3]
GO

В результате, кроме основной файловой группы PRIMARY имеем три дополнительных: FG1, FG2, FG3.

Созданные файловые группы

Файловые группы необходимы для распределения данных по ним с помощью секционирования. За файловой группой может стоять как отдельный файл или группа файлов.

Добавление файлов

Файловые группы есть, но они нигде не используются. Добавим отдельные файлы для каждой файловой группы.

USE [master]
GO
ALTER DATABASE [Partitioning] ADD FILE ( 
	-- Настройки размещения и автоувеличение файла
	NAME = N'Partitioning_FG1', 
	FILENAME = N'D:\DBs\Partitioning_FG1.ndf' , 
	SIZE = 1024KB , 
	FILEGROWTH = 10%) 
	-- Принадлежность файла к файловой группе
	TO FILEGROUP [FG1]
GO
ALTER DATABASE [Partitioning] ADD FILE ( 
	NAME = N'Partitioning_FG2', 
	FILENAME = N'D:\DBs\Partitioning_FG2.ndf', SIZE = 1024KB, FILEGROWTH = 10%) 
	TO FILEGROUP [FG2]
GO
ALTER DATABASE [Partitioning] ADD FILE ( 
	NAME = N'Partitioning_FG3', 
	FILENAME = N'D:\DBs\Partitioning_FG3.ndf', SIZE = 1024KB, FILEGROWTH = 10%) 
	TO FILEGROUP [FG3]
GO

Теперь каждая файловая группа ассоциирована с отдельным физическим файлом. Эти файлы также сразу же доступны в файловой системе.

Определение функции и схемы секционирования

Тут начинается самое интересное. Нам необходимо определить как данные в таблице или индексах будут распределяться между секциями. Для этого используются функции секционирования. Как упоминалось выше, таблицы содержат данные с 2010 по 2019 год. Допустим, нам нужно распределить данные по годам между секциями по такому принципу:

Файловая группа Фильтр данных
FG1 до 2010 года включительно
FG2 с 2011 по 2014 год включительно
FG3  с 2015 по 2018 год включительно
PRIMARY с 2019 года по текущий момент

Создать функцию секционирования можно только с помощью SQL-скрипта. В нашем случае он будет выглядеть так.

USE [Partitioning]
GO

CREATE PARTITION FUNCTION [ByDatePartitionFunction] 
	-- Тип колонки исходной таблицы, по которой
	-- будет выполняться секционирование
	(datetime2(0))
-- Указание к какой области интервала значений
-- принадлежит аргумент в части "FOR VALUES"
AS RANGE LEFT 
-- Платформа 1С хранит даты с некоторым смещением,
-- которое обычно установлено в 2000 лет, чтобы
-- иметь возможность хранить пустую дату "01.01.0001"
-- из 1С в виде "01.01.2001" на стороне SQL Server.
-- Поэтому здесь все даты в 4-ом тысячелетии :)
FOR VALUES (
	N'4010-12-31T23:59:59.000', 
	N'4014-12-31T23:59:59.000', 
	N'4018-12-31T23:59:59.000'
)
GO

Тип колонки секционирования соответствует типы поля "_Period" в таблице регистра. Через SSMS можно увидеть новый объект в разделе "Хранилище".

Функция секционирования

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

USE [Partitioning]
GO

CREATE PARTITION SCHEME [ByDatePartitionScheme] 
-- Используемая функция секционирования
AS PARTITION [ByDatePartitionFunction] 
-- Файловые группы указаны в том порядке,
-- в котором указаны значения фильтров
-- при создании функции секционирования
TO ([FG1], [FG2], [FG3], [PRIMARY])
GO

В списке объектов базы созданную схему можно также заменить в разделе "Хранилище".

Схема секционирования

И так, функция и схема секционирования готовы, осталось применить их на таблицах / индексах.

Применяем секционирование

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

  • "ТоварыНаСкладах_Секции" (таблица "_AccumRg84")
  • "Продажи_Секции" (таблица "_AccumRg69")

Обе таблицы имеют кластерный индекс, поэтому будет достаточно применить схему секционирования к нему и всем некластеризованным индексам (которых у каждой таблицы по 1 для полей "Регистратор" + "НомерСтроки"). Для этого необходимо пересоздать индексы с явным указанием схемы секционирования. Вот полный скрипт для таблицы "_AccumRg84". Для "_AccumRg69" скрипт будет аналогичным, только имя таблицы и индексов нужно поменять.

USE [Partitioning]
GO

CREATE UNIQUE CLUSTERED INDEX [_AccumRg84_1] ON [dbo].[_AccumRg84]
(
	[_Period] ASC,
	[_RecorderTRef] ASC,
	[_RecorderRRef] ASC,
	[_LineNo] ASC
)WITH (
	-- Пересоздать индекс заново, если существует
	DROP_EXISTING = ON, 
	-- Включить инкрементальную статистику
	-- Об этом в статье далее
	STATISTICS_INCREMENTAL = ON)
-- Указываем схему секционирования и колонку таблицы,
-- к которой эта схема применяется
ON [ByDatePartitionScheme](_Period)
GO

CREATE UNIQUE NONCLUSTERED INDEX [_AccumRg84_2] ON [dbo].[_AccumRg84]
(
	[_RecorderTRef] ASC,
	[_RecorderRRef] ASC,
	[_LineNo] ASC
	-- Для секционирования в индексе должен присутствовать столбец секционирования
	-- поэтому стандартный платформенный индекс приходится изменять
	[_Period] ASC
)WITH (
	DROP_EXISTING = ON, 
	STATISTICS_INCREMENTAL = ON)
ON [ByDatePartitionScheme](_Period)
GO

Для упрощения составления скрипта можно использовать возможности SSMS по генерации DDL-команд для существующих объектов (таблицы и индексы). Сформированные автоматически скрипты можно использовать как шаблоны. Результатом скрипта будет разбиение таблиц и ее индексов на секции. Проверим результат для таблицы "_AccumRg84" и ее кластерного индекса с помощью этого скрипта.

Номер секции Количество строк в секции
1 (FG1) 4111890
2 (FG2) 1059512
3 (FG3) 82034
4 (PRIMARY) 536

Итог: основные таблицы регистров разбиты на секции с учетом файловых групп базы данных. Но для чего мы все это делали и что делать дальше?

 
 Примечание! Какое бывает секционирование и что такое сегментирование

Итак, поехали!

Какие проблемы решает

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

Гибкое управление данными

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

Поскольку к этим данным выполняется редкое обращение, то можно использовать сжатие PAGE для таблицы и индексов на этой секции. Сэкономим место на архивных данных, при этом сохраним уровень производительности при работе с остальными секциями (использование сжатия требует доп. ресурсов CPU).

ALTER INDEX _AccumRg84_1 
-- При указании секции для сжатия обязательно
-- указывать перестроение всех секций (REBUILD PARTITION=ALL )
ON _AccumRg84 REBUILD PARTITION=ALL 
-- При сжатии указываем номер секции
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1)) 

Проверим результат с помощью этого скрипта.

Таблица Объект Номер секции Сжатие
_AccumRg84 _AccumRg84_1 1 PAGE
_AccumRg69 _AccumRg69_1 1 PAGE

Кроме сжатия, для отдельных секций доступны:

  • Перенос данных, что может быть актуальным при переносе данных из OLTP в OLAP
  • Операции обслуживания
  • Операции бэкапирования
  • И др.

Тема обширная и рассмотреть ее в одной публикации невозможно, поэтому если Вам это интересно, то рекомендую почитать MSDN. Точкой старта может быть официальная документация, которую можно прочитать здесь.

Повышение эффективности дисковой подсистемы

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

Например, есть две файловые группы FG1 и FG2, которые используют два отдельных файла. У нас простой пример и все файлы находятся в одном каталоге, на одном диске. Но никто не мешает распределить файлы по разным дискам, тем самым ускорив операции ввода-вывода с ними. Подобный подход разбиения базы по дисковой подсистеме может дать значительный прирост производительности в зависимости от назначения системы и выполняемых в ней SQL-запросов.

Часто даются рекомендации по переносу базы tempdb на отдельный диск для улучшения производительности, т.к. это позволяет снизить конкуренцию за дисковые ресурсы между основной базой данных и tempdb. Представьте какие возможности у Вас появятся для оптимизации операций ввода-вывода, если основную базу можно будет расположить на нескольких дисках.

Оптимизация стратегии бэкапирования

В этом случае все сводится к простому правилу - бэкапировать нужно лишь то, что меняется. Если файловая группа FG1 не меняется уже 6 лет, то зачем делать ее регулярный бэкап?

Бэкап всех данных!

 

Вместо этого можно оптимизировать стратегию бэкапирования, делая резервную копию только "свежих" данных. В нашем случае для файловой группы FG1 можно установить режим "Только для чтения", чтобы в ней никто не смог поменять данные, в т.ч. и через 1С.

USE [Partitioning]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) 
FROM sysfilegroups WHERE groupname=N'FG1'
if(@readonly=0)
	ALTER DATABASE [Partitioning] MODIFY FILEGROUP [FG1] READONLY
GO

Теперь при попытке изменить данные в старом периоде через 1С появится ошибка на уровне СУБД. Это необходимо учитывать и делать проверки на уровне решения 1С.

 

Ошибка для файловых групп только для чтения

 

Вернемся к формированию бэкапа. Допустим, изначально для базы использовалась полная модель бэкапирования, ежедневно ночью был настроен бэкап полный и лога транзакции каждые 30 минут. Появилась проблема, что из-за большого объема базы полный бэкап выполняется длительное время и мешает работе пользователей и регламентных заданий. Примерный скрипт для формирования полного бэкапа может быть таким.

BACKUP DATABASE [Partitioning] 
TO  DISK = N'D:\DBs\Backup\Partitioning.bak' 
WITH NOFORMAT, NOINIT,  
NAME = N'Partitioning-Полная База данных Резервное копирование', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10, CHECKSUM
GO

Вместо этого сделаем резервное копирование только тех данных, что могут меняться, а файловую группу FG1 в режиме "Только для чтения" исключим из резервной копии. Предполагается, что резервная копия файловой группы FG1 уже есть и ее повторное создание не имеет смысла.

BACKUP DATABASE [Partitioning] 
	-- Перечисляем файловые группы для создания резервной копии
	FILEGROUP = N'PRIMARY',  
	FILEGROUP = N'FG2',  
	FILEGROUP = N'FG3' 
TO  DISK = N'D:\DBs\Backup\Partitioning.bak' WITH NOFORMAT, 
NOINIT,  
NAME = N'Partitioning-Полная База данных Резервное копирование', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10, CHECKSUM
GO

Конечно, работать с такими бэкапами нужно немного иначе, но особых проблем с восстановлением данных не будет. Например, если была повреждена архивная файловая группа FG1, то ее восстановить проще всего, т.к. установлен режим только для чтения.

RESTORE DATABASE [Partitioning] 
FILE = N'Partitioning_FG1' 
FROM  DISK = N'D:\DBs\Backup\FG1.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

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

USE [master]

-- Создаем резервную копию заключительного фрагмента журнала транзакции
-- и устанавливаем состояние базы в "NORECOVERY"
BACKUP LOG [Partitioning] TO  DISK = N'D:\DBs\Backup\Last_LogBackup.bak' 
	WITH NOFORMAT, NOINIT, NAME = N'Last_LogBackup', 
	NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5

-- Восстанавливаем состояние базы на указанный момент времени (параметр STOPAT)
RESTORE DATABASE [Partitioning] FROM  DISK = N'D:\DBs\Backup\WeeklyBackup.bak' 
	WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Partitioning] FROM  DISK = N'D:\DBs\Backup\LogBackup1.trn' 
	WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Partitioning] FROM  DISK = N'D:\DBs\Backup\LogBackup2.trn' 
	WITH  FILE = 1,  NOUNLOAD,  STATS = 5,  
STOPAT = N'2019-02-08T16:15:13' -- Момент времени для восстановления
GO

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

Что может быть лучше, чем быстрый бэкап :)

Улучшение процедур обслуживания

Для ускорения процедур обслуживания индексов и статистик можно выполнять операции на отдельных секциях.

Например, у нас есть 4 секции, причем одна из них вообще в режиме "Только для чтения". Чтобы уменьшить время обслуживания можно применить скрипт только к последней, "горячей" секции.

USE [Partitioning]
GO
ALTER INDEX [_AccumRg69_1] ON [dbo].[_AccumRg69]
-- Указание конкретной секции для перестроения
-- В обычных ситуациях выполняется перестроение всех
-- секций, что аналогично указанию "REBUILD PARTITION = ALL"
REBUILD PARTITION = 4
GO

Окей, с индексами все понятно, но как же статистика? Иногда обслуживание всех статистик может занимать даже больше времени, чем обслуживание индексов. При этом гистограмма распределения значений по таблице / индексу, чем в принципе и является статистика, не рассчитывается для каждой отдельной секции. Но решение все же есть. Начиная с версии SQL Server 2014 появилась так называемая инкрементальная статистика, которая может пересчитываться по секциям.

По умолчанию объекты базы данных не поддерживают инкрементальную статистику, потому что платформа 1С не включает их явно. Включить данный вид статистики для индекса можно при создании / перестроении.

CREATE UNIQUE CLUSTERED INDEX [_AccumRg69_1] ON [dbo].[_AccumRg69]
(
	[_Period] ASC,
	[_RecorderRRef] ASC,
	[_LineNo] ASC
-- Включение инкрементальной статистики для индекса
-- Кстати, мы это уже делали в одном из предыдущих скриптов :)
)WITH (DROP_EXISTING = ON, STATISTICS_INCREMENTAL = ON)
ON [ByDatePartitionScheme](_Period)
GO

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

UPDATE STATISTICS [dbo].[_AccumRg69]([_AccumRg69_1])
-- Указываем конкретную секцию для обновления статистики
WITH RESAMPLE ON PARTITIONS(4);

Для подробной информации о работе инкрементальной статистики и ее "внутренней кухне" рекомендую изучить статью "SQL Server 2014 : New incremental statistics", а также на MSDN. В них есть подробное описание как работает инкрементальная статистика, в каких случаях ее стоит использовать, ограничения и др. Если у Вас в базе огромные таблицы, то инкрементальная статистика может быть настоящим спасением при оптимизации обслуживания.

Подобные подходы актуальны для очень больших таблиц. На сколько это ускорит обслуживание? Ответ на этот вопрос можете дать только Вы, проанализировав объем данных и возможности своей инфраструктуры. К сожалению, инкрементальная статистика не решает проблему снижения эффективности статистики при росте таблиц, но это уже другая история (если интересно, то можно написать в других статьях).

Проблемы блокировок

С тех пор, как платформа использует свой "костыль" в виде менеджера управляемых блокировок и режим изоляции транзакций Read Commited Snapshot Isolation (RCSI), то проблемы блокировок на уровне SQL Server стало значительно меньше. Однако проблема эскалации блокировок все еще актуальна, т.к. она не решается использованием управляемых блокировок.

Проблема заключается в том, что при модификации большого объема данных таблицы в рамках одной транзакции SQL Server для оптимизации использования памяти может укрупнить область блокировки до уровня секции таблицы или всей таблицы.

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

Отличное описание есть на сайте Вячеслава Гилева, за что ему большое спасибо.

Плюсы и минусы

Все имеет свои плюсы и минусы, и секционирование тут не исключение.

Плюсы:

  • Гибкое управление данными, за счет действий над отдельными секциями (сжатие, перенос на отдельный диск, перенос данных на другие инстансы, бэкапирование и др.)
  • Ускорение операций обслуживания (перестроение индексов и обновление статистик по секциям).
  • Повышение производительности запросов для некоторых ситуаций. Эту ситуацию мы не рассматривали, но происходит это за счет:
    • Исключение обращений к секциям, которые не соответствую фильтрам запроса.
    • За счет разнесения секций на отдельные диски. 

Минусы:

  • Сложность администрирования и поддержки, т.к. требуются дополнительные компетенции.
  • Сложность при разработке баз данных, т.к. секционирование должно учитываться при модификации базы.
  • Как ни странно, секционирование может вызвать проблемы производительности в некоторых запросах. Например, из-за дополнительной операции соединения наборов данных из разных секций. Это стоит учитывать при планировании инфраструктуры и написания SQL-запросов.

Мы не будем отдельно останавливаться на каждом пункте, т.к. тогда статья станет очень большой и превратиться в книгу.. Более подробную информацию Вы всегда можете узнать на MSDN. Главное что нужно понять, что секционирование не является простым решением, поэтому перед его использованием нужно взвесить все плюсы и минусы. Особенно это важно в контексте платформы 1С, где нет полной власти над базой данных (она как бы есть, но ее как бы нет :)).

Проблемы в мире 1С

В контексте платформы 1С секционирование имеет свои особенности и подводные камни, а именно:

  • Лицензионное соглашение фирмы "1С" запрещает использовать недокументированные возможности. Только Вы ответственны за то, что делаете. Сам факт нарушения соглашения может как минимум вылиться в отказ в технической поддержки.
  • Проблемы при обновлении конфигурации, а именно реструктуризации таблиц.
    • Поскольку платформа 1С ничего не знает о секциях, то при реструктуризации все настройки таблиц и индексов будут сброшены на стандартные и секции будут "затерты".
    • При обновлении платформы 1С на новую версию или отказ от совместимости в конфигурации может привести к значительным изменениям на уровне базы, что может противоречить сделанными Вами изменениям. Например, ранее платформа хранила тип "Хранилище значений" с помощью SQL-типа "IMAGE". В одной из версий платформы этот тип был заменен на "VARBINARY". Если такие ситуации не обнаружить, то в лучшем случае реструктуризация прервется с ошибкой, а в худшем случится потеря данных.
  • Архитектура таблиц метаданных в большинстве решений противоречит основным требованиям секционирования.
    • Типовые конфигурации в большинстве таблиц имеют разделитель данных с типом "numeric", который включен во все индексы. Если Вы используете разделитель, то может понадобиться секционировать не просто по периоду, а по периоду с учетом разделителя. Проблема в том, что SQL Server поддерживает только указание одного поля секционирования. Решение тут - создавать виртуальное поле, о котором 1С ничего знать не будет, но этот подход мы сейчас не будем описывать. Если кому-то интересно - пишите в комментариях.
    • Не все типовые индексы можно просто так взять и секционировать, потому что не все они содержат поле секционирования, а это обязательное условие. Выше был пример, когда для включения секционирования пришлось добавлять поле "Период" в индекс по регистратору.
    • И многие другие специфические проблемы, с которыми можно столкнуться.
  • Топорное построение SQL-запросов платформой "1С" сводит на нет выигрыш в производительности для запросов по большим таблицам. Например, выше выполнено секционирование таблицы "_AccumRg84". Обслуживание ускорили, архивные данные сжимаем и поставили только для чтения, а бэкапы теперь выполняются гораздо быстрее. Но вот исключение обращений к архивным секциям в запросах не работает. Выполняя такой запрос из 1С мы ожидали, что будет прочитана только секция в файловой группе "PRIMARY". Вот текст запроса и план его выполнения.
exec sp_executesql N'
SELECT
CAST(COUNT_BIG(T1._RecorderRRef) AS NUMERIC(12))
FROM dbo._AccumRg84 T1
WHERE ((T1._Period >= @P1) AND (T1._Period <= @P2))
'
-- Все даты преобразуются к типу datetime2(3),
-- фактически период хранится с типом datetime2(0)
,N'@P1 datetime2(3),@P2 datetime2(3)'
,'4019-01-01 00:00:00','4019-01-31 23:59:59'

План запроса платформы 1С

Обратите внимание, что запрос секционированный и фактически обработано 4 секции, что не правильно. Все дело в том, что платформа по неведомой причине преобразовывает все параметры дат в SQL-запросах к типу "datetime(3)", хотя в таблицах даты хранятся с типом "datetime(0)". Для SQL Server это важно, т.к. происходит неявное преобразование типов и СУБД не может использовать секции. Если убрать преобразование дат и сразу поставить нужный тип "datetime(0)", то ситуация кардинально изменяется.

exec sp_executesql N'
SELECT
CAST(COUNT_BIG(T1._RecorderRRef) AS NUMERIC(12))
FROM dbo._AccumRg84 T1
WHERE ((T1._Period >= @P1) AND (T1._Period <= @P2))
'
-- Убираем преобразование типов к datetime2(3)
,N'@P1 datetime2(0),@P2 datetime2(0)'
,'4019-01-01 00:00:00','4019-01-31 23:59:59'

Исправленный запрос платформы 1С

Как можно заметить, запрос остается секционированным, но прочитана всего 1 секция. Это может быть критическим для больших операций чтения, например для Table Scan. Ведь прочитать одну секцию вместо 4 все же лучше.

 
 Крик души

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

Костыли и палки

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

Этот же подход подойдет и для сохранения настроек секционирования, но с некоторыми особенностями.

CREATE TRIGGER [CustomSettingsMaintenance_OnIndexCreate]
ON ALL SERVER 
AFTER CREATE_INDEX
AS

BEGIN
	SET NOCOUNT ON;

	-- В случае возникновения ошибок продолжаем работу
	SET XACT_ABORT OFF;

	DECLARE @SchemaName SYSNAME,
		@TableName SYSNAME,
		@DatabaseName SYSNAME,
		@IndexName SYSNAME;

    SELECT @TableName = EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]','SYSNAME')
    SELECT @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','SYSNAME')
	SELECT @IndexName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')
	SELECT @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME');

	-- Здесь запускаем скрипт перестроения индекса с учетом схемы секционирования 
	-- Для индексов, которые не содержат поле секционирования, также выполняем
	-- их перестроение с добавлением этого поля. Если необходимо, то
	-- включаем инкрементальную статистику.
	-- Дополнительно можно учитывать файловые группы только для чтения и отключать
	-- эту настройку на время реструктуризации.

	-- Возвращаем значение по умолчанию для ситуаций с ошибками в транзакции
  	SET XACT_ABORT ON;

END

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

Это конец

Вот и все. На самом деле ничего сложного, если понимать для чего это нужно.

Нужно ли это использовать на практике? Решать только Вам, но если хоть один из пунктов к Вам относится, то секционирование точно не для Вас:

  • Используется файловый режим работы информационной базы
  • Нет никаких проблем производительности и стабильности информационной системы
  • Считаете большой ошибкой выход за пределы экосистемы платформы 1С
  • Вы сотрудник фирмы “1С” 

В случае если у Вас высоконагруженная база, то рассмотреть возможность секционирования стоит, но делать это должен либо эксперт, либо архитектор 1С вместе с DBA.

Другие ссылки

Тема секционирования не новая, на Инфостарт она уже рассматривалась и было бы правильно добавить ссылки на эти материалы.

P.S. Некоторый полезный материал Вы можете найти здесь. Если есть что добавить / исправить - пишите в комментариях или делайте Issue / Pull Request в репозитории. Подобный опыт всегда интересен.

P.P.S. Весь материал только для ознакомления, Вся ответственность только на Вас!

122

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. Gilev.Vyacheslav 1817 11.02.19 00:36 Сейчас в теме
Одну важную вещь я бы выделил: секционирование надо делать до того как база сильно распухнет, задуматься стоит на пороге 30-50 миллионов строк в таблице, когда таблица будет терабайтовая ни какого технологического окна на продуктиве не хватит.
А таблицы второстепенной важности типа "версии" вообще в другую базу на другой сервер стараться выносить.
Yakud3a; stsasha87; A_Max; YPermitin; +4 Ответить
3. YPermitin 1632 11.02.19 08:01 Сейчас в теме
(1) согласен. Лучше раньше, чем поздно. И лучше поздно, чем никогда :)
10. nicxxx 206 11.02.19 13:02 Сейчас в теме
Поменять тип колонки _period на datetime2(3)?
11. YPermitin 1632 11.02.19 13:09 Сейчас в теме
(10)
а datet


Видимо немного не в ту ветку.

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

Но вообще решений несколько.
2. Dream_kz 89 11.02.19 07:45 Сейчас в теме
Если Вам интересно как исправить запросы платформы 1С или архитектуру таблиц метаданных на стороне БД пишите в комментариях, может это будет стимул для новой статьи.

Пишите еще, технические статьи очень интересны

Маленький вопрос, а с разделением документов по секциям все будет не так "просто" как с регистрами?
YPermitin; +1 Ответить
4. YPermitin 1632 11.02.19 08:05 Сейчас в теме
(2) спасибо. Больших различий нет, т.к. в них тоже есть период (Дата документа). Но если задача построить секции по более сложному принципу, то нюансы могут появится.

Но это не гарантировано, надо по конкретной задаче смотреть.
5. a.m.minakov 11.02.19 09:35 Сейчас в теме
Получается, что после каждого обновления, необходимо настраивать секционирование заново?
YPermitin; +1 Ответить
6. YPermitin 1632 11.02.19 09:40 Сейчас в теме
(5) Да, но:
1. Только при тех обновлениях, которые приводят к реструктуризации секционированной таблицы.
2. И только если не позаботиться о скриптах обслуживания, которые могут все сделать автоматом на стадии реструктуризации (см. в конце статьи описание принципа).

То есть да, усложнение сопровождения конечно будет. Но при должном подходе это не создаст проблемы, главное чтобы был специалист, который в этом разбирается.
7. capitan 1154 11.02.19 10:02 Сейчас в теме
8. nicxxx 206 11.02.19 12:30 Сейчас в теме
Ключевой момент- преобразование datetime. Если эту проблему не решить, то запросы так и продолжат тормозить. Но ее решение подразумевает вмешательство в работу платформы, что не каждому под силу. Давайте дружно попросим Орефкова заняться этим вопросом?))
9. YPermitin 1632 11.02.19 12:52 Сейчас в теме
(8) Решение на самом деле есть даже без дизасемблирования. Я хотел о нем написать, но боюсь тогда статья стала бы на столько большой, что к концу читатели бы впали в кому.

Может быть в следующий раз :)

Ну и плюс секционирование не обязательно через DateTime. Может у вас есть поле numeric(10,0), по которому нужно секционировать таблицу. В этом случае все будет работать как надо.
Aleskey_K; support; +2 Ответить
16. Magov 22.02.19 17:34 Сейчас в теме
(9)
еле есть даже без дизасемблирования. Я хотел о нем написать, но боюсь тогда статья стала бы на столько большой, что к концу читатели бы впали в ком

Намекните пожалуйста, хоть в какую сторону посмотреть. Интересует именно DateTime.
12. nicxxx 206 11.02.19 13:10 Сейчас в теме
Тогда пишите статью:)
torbeev; A_Max; +2 Ответить
13. YPermitin 1632 11.02.19 13:15 Сейчас в теме
14. Andreynikus 1177 12.02.19 23:50 Сейчас в теме
Отличная статья, спасибо!
YPermitin; +1 Ответить
15. YPermitin 1632 13.02.19 07:09 Сейчас в теме
17. GreenDragon 25.02.19 13:52 Сейчас в теме
Дайте угадаю... Enterprise?
YPermitin; +1 Ответить
18. YPermitin 1632 25.02.19 14:08 Сейчас в теме
(17) он самый, кровавый и беспощадный энтерпрайз.
19. GreenDragon 25.02.19 15:21 Сейчас в теме
(18) В тегах что ли укажите, а то каждый раз разочарование. В 10-й раз смотрю один и тот же фильм с надеждой на другую концовку...
YPermitin; +1 Ответить
20. YPermitin 1632 25.02.19 15:23 Сейчас в теме
(19) не хотел никого расстроить :)

Про лицензирование специально не писал, т.к. это сложная тема на самом деле. Но сделал ремарку в начале.
21. nvv1970 03.03.19 01:26 Сейчас в теме
Автор проверял секционирование в продакшене на SQL2016+ ?
Я наверно сам проверял, но уже не помню результат.))) (Не в 1С - активно применяю секционирование, были таблицы в пару миллиардов)

Ссылку на проблему оставлю это здесь: https://partners.v8.1c.ru/forum/topic/1748333
В частности, внешний источник 1с не в состоянии правильно работать с select запросами секционированных таблиц из-за специфической типизации параметра функции секционирования. Совместимость внешней базы "2014 и ниже" - спасает.
Проблема связана с изменениями в типе datatime2 c 2016 версии. Ссылки на партнерке приведены. В документации в BOL/MSDN про это кажется не написано, а "суслик есть".

Секционирование, сжатие данных (тема деградации записи не раскрыта) - это мощнейший инструмент.
Но нужно помнить, что используя его в 1С вы обрекаете владельца базы на то, что в какой-то момент они будут вынуждены выгружать ДТ и заливать его в чистую базу, т.к. без вас никто ни в чем не разберется. Поэтому "до террабайта - и так сойдет, а там будем резать, обменами переливать" и т.п. )))
Если вы не локальный DBA, а специалист со стороны, то никого эти прекрасные технологии не интересуют. К сожалению (

Есть еще более простые способы деления таблиц - это view. Часть таблицы выносится во внешнюю базу. Триггерами решается вопрос изменения данных. Все несложно, пока нет реструктуризации таблицы. Но и реструктуризация тоже решается.
YPermitin; +1 Ответить
22. YPermitin 1632 03.03.19 08:17 Сейчас в теме
(21)
Не в 1С - активно применяю секционирование, были таблицы в пару миллиардов

Спасибо за содержательный комментарий!

Автор проверял секционирование в продакшене на SQL2016+ ?

Проверял секционирование на 2016/2017 редакции. Основными проблемами с датами остается CAST'инг, но это все же особенность 1С. Можно ухитриться и обойти, но проблемы сопровождения станут актуальными. Поэтому секционирование для баз 1С все же пока работает как "костыль", который требует особого ухода :)

Изменения в 2016 в части datetime2 (https://docs.microsoft.com/ru-ru/sql/database-engine/breaking-changes-to-database-engine-features-in-sql-server-2016?view=sql-server-2017) действительно могут привести к неработоспособности запросов к секционированным таблицам, но эта проблема также решаем, но через "особые подходы в разработке". Печаль, но что делать.

Секционирование, сжатие данных (тема деградации записи не раскрыта) - это мощнейший инструмент.

Это все привело бы к слишком большой статье. Есть мнение что она уже такая :) Но проблема раскрыта в других источниках.
Пока что да, если нет спеца по БД, то делать все это опасно, поэтому если это не кровавый энтерпрайз, то усложнять сопровождение я бы не стал.

Есть еще более простые способы деления таблиц - это view.

Классика :) Этот способ простой и пока что единственный способ повысить эффективность работы статистики, которая может снижаться из-за ограничения в 200 шагов в гистограмме распределения.
Использовал на практике несколько раз, эффективность доказана. А реструктуризации можно решить, в самых сложных случая в "ручном режиме".

Вообще, если Вы имеете дело с НЕ 1Сной базой, то большинство тех сложностей, что появляются при использовании 1С, просто отпадают, т.к. настройки сделать проще и сопровождать тоже. Но появляются другие вопросы :)
Не завидую всем DBA, которые обслуживают большие базы 1С :) Но держитесь! :)
23. nvv1970 03.03.19 11:57 Сейчас в теме
(22) у меня есть религиозное убеждение, что dba 1c не существуют, но есть программисты отличное разбирающиеся в администрировании бд, но им этим некогда заниматься. Просто это всегда только лишь хобби. И за это никто не платит. Мы призраки.
24. YPermitin 1632 03.03.19 12:47 Сейчас в теме
(23) все, о чем я писал здесь, используется. Оплачивается или нет, возможно, зависит от бизнеса.

Никакой мифологии и призраков. Это реалии нагруженных БД. Знание того что и как работает как-раз отличает инженера от не инженера.
28. VVi3ard 42 10.04.19 17:25 Сейчас в теме
(23) Они не существуют там где они не нужны.
И вполне себе существуют и оплачиваются там где они нужны.

Если у вас ларек с шаурмой то DBA вам не нужен.
Если вы крупная торговая сеть с 4000 филиалов то DBA у вас есть (и не один).
YPermitin; +1 Ответить
29. YPermitin 1632 10.04.19 18:09 Сейчас в теме
(28)
ни не существуют


Никто не спорит, думаю многие с вами согласятся.
Да и в статье нет этому противоречий.
25. VVi3ard 42 10.04.19 11:06 Сейчас в теме
Не до конца понял пример с
"FROM dbo._AccumRg84 T1
WHERE ((T1._Period >= @P1) AND (T1._Period <= @P2))"

У вас в примере просмотр индекса, а вы сравниваете с сканированием таблицы.

Если в плане будет сканирование таблицы то оно будет всегда по всем секциям на то оно и сканирование.

В вашем примере просмотр индекса, он должен одинаково работать и на одной таблице и на секционированной у вас даже в примере видно 2896 чтений строк в обоих случаях.

Если опустить обслуживание и ReadOnly секции, а так же не лезть в дебри эскалации (потому что эскалация это жопа не зависимо от секционирования) то секционирование нужно только в одном случае:
У вас есть диски не объеденные в массив под СХД (Система Хранения Данных).


Любая высоконагруженная система по любому крутится на СХД где под контролером спрятано 30-40 дисков часть из которых ССД.
И она сама прекрасно распараллеливает нагрузку по всем дискам.

Если забрать у СХД 20 дисков, налепить из них 5 массивов и замапить на секции то эффективность системы будет хуже. Т.к. большую часть времени большая часть дисков под секциями которые меняются и читаются редко будет простаивать.


В общем думаю в статье было бы неплохо подробнее раскрыть тему:
"Нет смысла разделять базу, таблицы или индексы на отдельные файлы для распределения по дискам, ведь в век SSD это пустая трата времени."

Только не в век SSD а в век интеллектуальных СХД.

Секционирование это удел не hiload а скорее временный костыль для средних объемов, когда на хорошее железо быстрые диски и контроллеры денег еще нет, а данных уже много и нагрузка большая.
YPermitin; +1 Ответить
26. YPermitin 1632 10.04.19 12:09 Сейчас в теме
(25) спасибо за столь содержательный комментарий!

Пока отвечу кратко, но если нужно будет, то и подробнее отпишусь.

Пример был для того, чтобы показать, что из-за излишних преобразований типов платформой 1С в параметрах SQL Server не может эффективно использовать секции. Ему приходится обрабатывать все секции, а не только ту, которая попадает под условие фильтра. Запросы в приемере одинаковые, за исключением преобразования типов, поэтому и количество строк одно и то же. Как это обойти - тема отдельная.

По поводу сканирования вы правы. Тут я допустил неточность. Я хотел сказать, что при сканировании кластерного индекса могут быть затронуты не все секции. Тут дальше нужно описывать в каких случаях оптимизатор может это применять и т.д.

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

А про век SSD в начале статьи - так это сарказм был :) Троллинг так сказать.

В целом я согласен, что секционирование не для всех. Но что это костыль - громко сказано.

Про интелектуальные СХД тема очень хорошая, но она секционирование не исключает.

P.S. Если не сложно, можете написать что вы из технологий СХД используйте.
27. VVi3ard 42 10.04.19 17:23 Сейчас в теме
Пример был для того, чтобы показать, что из-за излишних преобразований типов платформой 1С в параметрах SQL Server не может эффективно использовать секции


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

Я возможно ошибаюсь и действительно имеет смысл разбивать единый массив дисков на много мелких под секции?

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


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


По части обслуживания мне дополнить нечего, по ней я согласен с вами.
Но в одних моментах мы упрощаем эксплуатацию в других усложняем (это про то как с 1С работает).


Про интелектуальные СХД тема очень хорошая, но она секционирование не исключает.


А какой смысл раскидывать по дискам если все эти диски "виртуальные"? Выделять на СХД под виртуальные диски массивы из реальных дисков не выгодно.
Да, выиграем на обслуживании, но при этом ухудшим работу СХД.


P.S. Если не сложно, можете написать что вы из технологий СХД используйте.


Мы не используем, используют наши клиенты, у них что то серьезное от Oracle используется за много млн. на SSD.

Но даже если брать дешевые СХД (естественно SAN) в которых 10-20 HDD + 16 GB RAM кэша + 10 SSD то уже там выгоднее доверить распределение нагрузки контроллеру.

Да даже банальный рейд 10 из 6 дисков выгоднее чем просто разбить на 2 raid 5 по 3 диска. При том что конфигурация 2 raid 5 позволит создать только 2 независимые секции.

Но что это костыль - громко сказано.

Обосную почему костыль.
Всегда лучше отдать все диски контроллеру. Чем забирать их у контроллера и самому по ним раскидывать данные.

Мне сложно представить ситуацию когда 4 секции (1 горячая + 3 холодных разной степени) в каждой из которых по 6 дисков будут работать быстрее чем 18 дисков под один диск.

Единственный вариант это когда мы уже уперлись в пропускную способность СХД, т..е условно больше 48 дисков СХД не держит и тогда мы устанавливаем вторую СХД и секции выносим на нее.

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

Со второй частью про производительность мне согласится сложно. Использовать секционирование для ускорения, идея крайне сомнительная, и раскрыта в статье не достаточно, пример с CAST интересный и познавательный, но практический смысл не понятен.
(еще стоит упомянуть что параллельное использование секций(ускорение работы) доступно только в Enterprise редакции)
YPermitin; +1 Ответить
30. YPermitin 1632 10.04.19 18:18 Сейчас в теме
(27)
еще стоит упомянуть что параллельное использование секций(ускорение работы) доступно только в Enterprise редакц


Не вижу никаких противоречий всего вышесказанного и статьи.

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

Еще больше дополнять статью смысла нет, ведь тогда это будет уже не статья, а документация. А для этого лучше идти на MSDN. Возможно более глубокие эксперименты с секциями и их влиянием на планы запросов можно оформить в виде статьи на Хабр, но тут это вряд ли будет интересно.

В любом случае, спасибо Вам за полезную информацию. Возможно в будущем подробнее опишу работу SQL Server с секциями, можно и для разных конфигураций дисковой подсистемы.

Или, Вы можете написать :)
Оставьте свое сообщение