1C: Распределение таблиц 1С базы данных SQL по разным дискам, используя файловые группы.

Опубликовано Опубликовано в рубрике 1C Программирование, MS SQL Server

Функционал SQL-сервера в части поддержки файловых групп, и схем секционирования применительно к 1С, мало кто использует. Сложно сказать почему. Возможно из-за недостаточной информированности программистов, возможно из-за того, что базы 1С никогда не вырастают до больших размеров. Ведь базы 1С редко когда содержат данные за период более чем 5 лет. Возможно, потому, что многие пользователи (как и программисты) видят в “свертке” (обрезке) базы панацею от всех бед и ошибок прошлого периода. Возможно потому, что 1C, как платформа, не способна поддерживать непротиворечивый и актуальный массив информации за длительный период (5-10 и более лет). Посему больших баз 1С попросту не бывает. Мы, например, не видели баз размером более чем 150 гигабайт.

Но между тем, файловые группы как раз и предназначены для использования в больших базах (больших по меркам SQL-сервера, а не 1С, естественно). Для базы данных мы можем определить несколько файловых групп (по умолчанию файловая группа одна – это файл “имя_базы.mdf”, и имеет тип PRIMARY), каждую из которых можем разместить на отдельном диске (файлы файловых групп, отличные от группы PRIMARY, имеют расширение ndf). Для каждой таблицы базы данных мы можем указать файловую группу, в которой физически будет хранится данная таблица.

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

Покажем как создать несколько файловых групп для базы данных (на примере SQL-сервера 2005). Обращаем внимание, что распределение таблиц по файловым группам можно было бы указывать при создании таблиц, но мы не можем воспользоваться таким способом, т.к. платформа 1С создает таблицы всегда в одной файловой группе (имеющей тип PRIMARY). Поэтому наш путь – переместить выбранные нами таблицы в нужную файловую группу (группы). 1С сможет использовать все таблицы, вне зависимости от размещения их в файловых группах. Необходимая “прозрачность” обеспечивается SQL-сервером, 1С попросту не знает из каких файловых групп SQL-сервер считывает данные.

Создадим новую файловую группу с логическим именем SECONDARY. При создании файловой группы есть возможность установить флажок “PRIMARY”. Этот флажок может быть установлен только у одной группы, и, определяет ту файловую группу, к которой будут создаваться таблицы по умолчанию.

рис 1. рис 2. рис 3.

Теперь переместим в нашу новую файловую группу некоторые таблицы. Для нашего примера мы решили переместить таблицы: ссылки на документы, журнал документов, итоги регистра остатков ТМЦ, движения регистра остатков ТМЦ, итоги регистра партий ТМЦ, движения регистра партий ТМЦ.

Запустим скрипт, выполняющий перемещение в файловую группу SECONDARY.

USE [Base1C2012]
GO

ALTER TABLE _1SCRDOC DROP CONSTRAINT PK__1SCRDOC WITH (MOVE TO SECONDARY)
GO
ALTER TABLE _1SCRDOC ADD CONSTRAINT PK__1SCRDOC PRIMARY KEY(ROW_ID)
GO

ALTER TABLE _1SJOURN DROP CONSTRAINT PK__1SJOURN WITH (MOVE TO SECONDARY)
GO
ALTER TABLE _1SJOURN ADD CONSTRAINT PK__1SJOURN PRIMARY KEY(ROW_ID)
GO

ALTER TABLE RA328 DROP CONSTRAINT PK_RA328 WITH (MOVE TO SECONDARY)
GO
ALTER TABLE RA328 ADD CONSTRAINT PK_RA328 PRIMARY KEY(IDDOC,LINENO_,ACTNO)
GO

ALTER TABLE RA405 DROP CONSTRAINT PK_RA405 WITH (MOVE TO SECONDARY)
GO
ALTER TABLE RA405 ADD CONSTRAINT PK_RA405 PRIMARY KEY(IDDOC,LINENO_,ACTNO)
GO

ALTER TABLE RG328 DROP CONSTRAINT PK_RG328 WITH (MOVE TO SECONDARY)
GO
ALTER TABLE RG328 ADD CONSTRAINT PK_RG328 PRIMARY KEY(PERIOD, SP331, SP330, SP4061, SP7404, SP340, SP1554, SP10225)
GO

ALTER TABLE RG405 DROP CONSTRAINT PK_RG405 WITH (MOVE TO SECONDARY)
GO
ALTER TABLE RG405 ADD CONSTRAINT PK_RG405 PRIMARY KEY(PERIOD, SP4062, SP408, SP418, SP3117)
GO

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

Теперь мы получили базу, таблицы которой разнесены по разным файловым группам. К слову, база данных, использованная в этом примере, имеет размер ~15 гигабайт, и показала по тестам прирост производительности около 5%. Такую величину вполне можно списать на погрешность тестов, и не принимать во внимание. Напомним, что по нашим предположениям, разнесение таблиц базы данных по разным файловым группам даст увеличение производительности при достаточно большом размере базы данных. Либо в том случае, когда размер базы данных (отдельных её таблиц) является большим именно для конкретной аппаратной конфигурации сервера. Иными словами, мы могли бы рассчитывать на увеличение производительности в том случае, если бы дисковая подсистема была “узким местом”.

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

Ни для кого не секрет, что в каждой типовой конфигурации 1С существуют несколько таблиц, размер которых составляет до 60-70% от всего размера базы данных. Для базы данных описанной выше это таблицы: таблица ссылок на документы, журнал документов, таблицу итогов регистра остатков ТМЦ, таблицу движений регистра остатков ТМЦ, таблицу итогов регистра партий ТМЦ, таблицу движений регистра партий ТМЦ. Совокупный размер этих таблиц составляет 70% от размера всей базы данных, т.е. всех её таблиц. Причем в этих таблицах содержатся данные текущего периода, и данные прошлых периодов, которые в текущей работе требуются редко (а именно только при построении отчетов за прошлые периоды). Было бы очень правильно с точки зрения производительности разместить часть такой таблицы с данными прошлых периодов в одной файловой группе, а часть с данными за текущий период (текущий год, например) в другой файловой группе. Такую возможность предоставляют нам схемы секционирования.

Схема секционирования позволяет разделять таблицу на две логические части (размещенные в разных файловых группах) по значению ключа секционирования. Ключ секционирования – это поле секционируемой таблицы.

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

Описание примера использования схемы секционирования ждите в следующей статье…