gifts2017

Сжатие баз данных 1С:Предприятие в MS SQL Server

Опубликовал Алексей Бочков (Aleksey.Bochkov) в раздел Администрирование - Системное

Тема сжатия баз данных 1С в настоящий момент довольно часто обсуждается. Достоинства сжатия известны – уменьшение размера базы данных, уменьшение нагрузки на дисковую подсистему и некоторое ускорение выполнения тяжелых операций чтения/записи. Из недостатков – небольшое увеличение нагрузки на процессоры сервера СУБД за счет расхода ресурсов на компрессию/декомпрессию данных. Но при использовании в качестве MSSQL и DB2 (за Oracle и PostgreSQL не скажу, т.к. не знаю) есть один «подводный камень» - при выполнении реструктуризации происходит декомпрессия новых таблиц и индексов. Происходить это может как при выполнении обновления конфигурации с изменением структуры метаданных, так и при выполнении тестирования и исправления ИБ (реиндексация пересоздает только индексы, а реструктуризация – и таблицы, и индексы). «Проблема» кроется в том, что признак сжатия устанавливается индивидуально для каждой таблицы и индекса.

Решение проблемы для MS SQL Server.

В течение долгого времени сжатие было доступно только в Enterprise версиях MS SQL Server, но начиная с SQL2016 Service Pack 1 эта функциональность была включена во всех редакциях, в том числе, бесплатной Express (релиз от 16.11.2016 - https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/).
Поймать момент создания новой таблицы или индекса возможно с помощью DDL-триггера, который вызывается сразу после выполнения CREATE TABLE/INDEX, но до начала переноса данных платформой 1С. Триггер данного типа можно создать как для конкретной ИБ, так и для всего сервера. Создание триггера для ИБ не вписывается в лицензионное соглашение 1С, поэтому лучше создавать для всего сервера, тем более что это позволит обслуживать сразу все базы на данном сервере.


Создание DDL-триггера для операции создания таблицы:


CREATE TRIGGER [data_compression]
ON ALL SERVER
AFTER CREATE_TABLE
AS
--Текст триггера


Создание DDL-триггера для операции создания индекса:


CREATE TRIGGER [index_compression]
ON ALL SERVER
AFTER CREATE_INDEX
AS
--Текст триггера


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


ALTER TABLE [имя базы].[имя схемы].[имя таблицы] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

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


ALTER INDEX [имя индекса] ON [имя базы].[имя схемы].[имя таблицы] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

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


Ниже приведу решение в том виде, как оно используется у нас. Это позволило автоматически применить сжатие для всех баз данных на сервере.


Создана служебная база CompressionSetting с двумя таблицами:


1)    Databases - для хранения списка баз, которые сжимать НЕ требуется.

CREATE TABLE [dbo].[Databases](
    [name] [nvarchar](100) NULL,
    [active] [int] NULL
) ON [PRIMARY]


Из ~200 баз на сервере я внес в эту таблицу только одну – tempdb:


2)    Trace – для мониторинга работы DDL-триггеров

CREATE TABLE [dbo].[trace](
    [text] [nvarchar](max) NULL,
    [DatabaseName] [nvarchar](max) NULL,
    [DateTime] [datetime] NULL
) ON [PRIMARY]


 
Далее создал 2 DDL-триггера:

1)    Для таблиц

CREATE TRIGGER [data_compression] 

ON ALL SERVER 
AFTER CREATE_TABLE 
AS 
DECLARE @SchemaName nvarchar(150),

@ObjectName nvarchar(150),
@DatabaseName nvarchar(150),
@cmd nvarchar(500)

--Получим имя схемы из выполняемой команды CREATE TABLE 
SET @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(150)')
--Получим имя таблицы
SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(150)')

--Получим имя базы
SET @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(150)')
--Сформируем из полученных данных требуемую команду на установку признака сжатия для таблицы
set @cmd = 'ALTER TABLE [' + @DatabaseName + '].[' + @SchemaName + '].[' + @ObjectName + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'

--Теперь проверяем настройки – если базы нет в таблице CompressionSetting.dbo.Databases с признаком Active = 1, то выполняем команду, иначе игнорируем
IF NOT EXISTS (SELECT  1 AS Expr1
			FROM CompressionSetting.dbo.Databases AS T
			WHERE (name = @DatabaseName) AND Active = 1) 

BEGIN 
	INSERT INTO CompressionSetting.dbo.trace (text, DatabaseName, DateTime) SELECT @cmd, @DatabaseName, GETDATE()

	EXEC (@cmd) 
END
ELSE 
BEGIN 
	PRINT 'TEST'

END

2)    Для индексов

CREATE TRIGGER [index_compression]
ON ALL SERVER
AFTER CREATE_INDEX

AS
DECLARE @SchemaName nvarchar(150),
@ObjectName nvarchar(150),
@TargetObjectName nvarchar(150),

@DatabaseName nvarchar(150),
@cmd nvarchar(500)
--Получим имя схемы из выполняемой команды CREATE INDEX
SET @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(150)')

--Получим имя индекса
SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(150)')
--Получим имя таблицы
SET @TargetObjectName = EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(150)')

--Получим имя базы
SET @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(150)')
--Сформируем из полученных данных требуемую команду на установку признака сжатия для индекса
set @cmd = 'ALTER INDEX [' + @ObjectName + '] ON [' + @DatabaseName + '].[' + @SchemaName + '].[' + @TargetObjectName + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'

--Теперь проверяем настройки – если базы нет в таблице CompressionSetting.dbo.Databases с признаком Active = 1, то выполняем команду, иначе игнорируем
IF NOT EXISTS (SELECT  1 AS Expr1
            FROM CompressionSetting.dbo.Databases AS T
            WHERE (name = @DatabaseName) AND Active = 1)

BEGIN
    INSERT INTO CompressionSetting.dbo.trace (text, DatabaseName, DateTime) SELECT @cmd, @DatabaseName, GETDATE()

    EXEC (@cmd)
END
ELSE
BEGIN
    PRINT 'TEST'

END


Сразу после создания триггеров никаких изменений в размере баз, конечно, не произойдет. Для сжатия уже имеющихся баз можно воспользоваться следующими методами:
1)    Написать скрипт для поочередного перебора таблиц и индексов и установки для них признака сжатия. Мне этот вариант не понравился, т.к. на сжатие больших таблиц требуется очень много времени, база при этом раздувается, а потом очень долго уменьшается через SHRINK DATABASE.
2)    Выполнить полную реструктуризацию через «Тестирование и исправление» . По времени быстрее, но база также раздувается и ее потом придется уменьшать через SHRINK DATABASE.
3)    Самый оптимальный вариант, на мой взгляд – пересоздать базу через dt-файл. При этом готовая база изначально будет минимального размера, а время загрузки базы со сжатием мало отличается от загрузки в обычном режиме.


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

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

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

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Александр Капустин (kapustinag) 29.01.12 21:19
Пытались на своих базах оценить, какой получался выигрыш в размере БД, и какова была цена - то есть проигрыш в производительности?
2. albochkov (Aleksey.Bochkov) 29.01.12 21:27
(1) БД уменьшались в размерах на 40-80% в зависимости от характера данных. Значимого увеличения нагрузки на процессоры не зафиксировали.
3. Модератор раздела Артур Аюханов (artbear) 30.01.12 12:13
(0) База размера 40-50 (и более) Гигов как долго сохраняется в формат dt через 1С ?
4. albochkov (Aleksey.Bochkov) 30.01.12 12:47
(3) Это сильно зависит от типа конфигурации и оборудования.
У меня на сервере разработки, например, выгрузка базы УТ 10.3 размером 60-70 Гб в dt занимает около часа.
В тоже время, Консолидация объемом ~250Гб выгружается около 2-х часов.
5. Имя Фамилия (a.ivanov) 24.09.12 12:19
Нафига весь этот огород с триггерами, если можно повесить регламент который будет включать сжатие. И выполнять его с нужной периодичностью. Например раз в три дня или неделю. Зависит от интенсивности изменений рабочей базы.
6. albochkov (Aleksey.Bochkov) 24.09.12 12:26
(5) a.ivanov, Вы, видимо, не совсем внимательно прочитали. Регламент не спасет от декомпрессии базы при очередном обновлении, а триггеры позволят изначально создавать таблицы сжатыми.
7. Алексей Бочков (Aleksey.Bochkov) 26.03.15 05:45
В качестве апдейта - прошло 3 года, на разных серверах мы использовали данную методику для MS SQL 2008/2012/2014 - везде полет нормальный.
Один из наиболее нагруженных серверов: >300 сжатых баз разработчиков, несколько дисковых массивов суммарной емкостью 4Тб заполнены на 80%.
Без сжатия аналогичное количество информации занимало бы >10Тб. ИМХО, неплохая оптимизация :).
8. Алексей Лустин (lustin) 31.03.15 13:31
В дополнение - сводный коэффициент увеличения производительности 1.9 раза по сравнению с несжатыми базами. сводное уменьшение объема 2.3 раза. Результат получен в течении нескольких лет на 134 базах находящихся под мониторингом и проверкой качества.

P.S. Последние 2 года все базы вначале сжимаются - будь то MSSQL или Oracle. На такую же технологию перевели и PostgreSQL
Bronislav; kardanovir; new_user; DimaP; Aletar; Aleksey.Bochkov; +6 Ответить
9. Михаил Максимов (МихаилМ) 31.03.15 14:06
использую возможности ddl триггеров для отмены пересоздания таблиц при реструктуризации.

эта возможность дает:

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

те набор стандартных действий по управлению производительностью субд.


ddl триггеры поддерживаются всеми субд работающими с 1с.

подобную тему не создавал тк явно противоречит лицензии.
10. Алексей Захаров (almas) 19.09.15 09:14
Статья супер. Пробуем воспользоваться, посмотрим, что получится...
11. Алексей Захаров (almas) 19.09.15 09:47
К данной статье считаю, что просто необходимо привести еще одну ссылку:
забудем о свертке

а так, же прилагаю скриптик для сжатия баз

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (DATA_COMPRESSION = PAGE)'
GO

EXEC sp_MSforeachtable 'ALTER TABLE ? REBUILD WITH (DATA_COMPRESSION = PAGE)'
GO

И еще на что из статьи обратил внимание:
Важное замечание - функция сжатия таблиц доступна только для обладателей версии Enterprise SQL Server
Silenser; Perk0n; Aule2; Anzhey; +4 Ответить
12. Александр Тарасюк (Aletar) 12.11.15 10:12
Применили сжатие для некоторых баз. Кроме того, что места стало вагон, так и производительность прилично возросла, так как исчезли ожидания PAGELACTH.
Так что спасибо Вам огромное.
13. Дмитрий К (SuhoffGV) 12.01.16 16:23
Вопрос к знатокам:

Дано:
1. 1c82, postgresql, база с большим количеством файлов в реквизитах типа хранилище_значений (сканы, pdf и т.п.). Объем базы в PGAdmin ~60Gb.
2. Обработкой вычищаю из базы файлов ~ на 17Gb. Объем базы в PGAdmin не поменялся.
3. Делаю выгрузку/загрузку в/из dt. Объем базы в PGAdmin 30Gb.

Вопросы:
1. Как уменьшить размер базы (сжать базу) БЕЗ загрузки/выгрузки в dt?

14. Алексей Бочков (Aleksey.Bochkov) 17.01.16 01:53
15. Валентин Будкин (vabue) 25.01.16 17:30
Для MS SQL 2012 подход аналогичный?
16. Алексей Бочков (Aleksey.Bochkov) 27.01.16 07:25
(15) vabue,
Да, этот подход работает во всех последующих версиях - 2012/2014/2016
17. Vova Petrov (bdsmka) 31.03.16 07:19
Статья супер, огромное спасибо. SQL server 2014 ent УТ 10.3 с 96гиг ужалась до 8.5гиг!!!!!(10х))) Комплексная 2.0 с 180гиг до 60гиг.(3х)
18. Максим Кравченко (inomaratadeath) 31.05.16 12:57
поясните нубу, сжатие базы - описанное в этом топике - это опция в свойствах базы >параметры>автоматическое сжатие>true? Или это что-то другое? И автошринкует ли это файл лога транзакций, или я совсем не в ту степь ушёл?
19. Алексей Бочков (Aleksey.Bochkov) 10.06.16 08:13
(18) inomaratadeath,
нет, к автоматическому сокращению размера БД (shrink database / auto shrink) это отношения не имеет.
Тут речь идет именно о сжатии данные в таблицах.
20. Серебряная Пуля Команда (Silverbulleters) 04.09.16 23:51
думаю стоит поднять тему, для тех кто прочитает эту статью в будущем

на самом деле на данный момент существует некая противоречивая ситуация

* если вы напишите в компанию 1С на официальный адрес поддержки - вам ответят что данный подход является нарушением лицензионного соглашения.

оно и понятно - конструкции ALTER TABLE, ALTER INDEX (также кстати как и CREATE INDEX) является вмешательством в структуру БД (изменением схемы данных), что запрещено лицензионным соглашением.

* однако во многих конечных компаниях при владении MSSQL Enterprise руководствуются другими документами, а именно

https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

для ленивых, не желающих ходить по ссылке процитирую


What can you compress? Anything in the following list that meets the above criteria…
A table with no organization (“heap” structure);
A table organized as a clustered index;
A nonclustered index;
An indexed view (remember, these do persist on disk);
Partitioned tables and indexes (each partition can be configured differently);


Как все помнят нас интересует


A table organized as a clustered index;
A nonclustered index;


так как это основные типы наших табличек в мире 1С.

позволю себе еще процитировать


Data compression provides multiple benefits. It saves disk space, and it can help improve the performance of certain workloads. The benefits of data compression come at the cost of higher CPU usage for compressing and decompressing the data. Therefore, it is important to understand the workload characteristics on a table before deciding on a compression strategy. Data compression provides flexibility in terms of levels of compression (row or page) and the objects you can compress (table, index, partition). This enables fine-tuning the compression based on the characteristics of data and the workload.
Another important advantage of data compression is that it works transparently to the application, and it works well with other SQL Server features, such as TDE and backup compression.
The results shown in this white paper are based on the data and the hardware used in our tests. Your results will vary based on your own data, workload and hardware. Perform thorough testing when deciding what tables and indexes to compress.


очень важен пункт


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


то есть рисков никаких... казалось бы, однако...


Implementing compression is a multi-step process:

Figure out what objects you should compress
Plan to handle all of your environments (dev, QA, production)
Compress them during a low-activity window
Regularly patrol your environments checking for added objects that weren’t compressed
Keep your environments in sync


Обратите внимание на рекомендации PFE инженеров от Microsoft

* планируйте точно какие объекты вы ДОЛЖНЫ сжимать
* применяйте сжатие на всех своих контурах приложения, а не только продуктивном
* сжатие применяйте в периоды наименьшей активности
* регулярно проверяйте какие объекты у вас не сжаты
* синхронизируйте окружения

для тех кто не в курсе - существует такая хитрая версия как SQL Server Developer Edition, эта версия полнофункциональная и предназначена для проверки разработчиком функциональности вплоть до Enterprise, на проверочном контур (не продуктивном)

Но вернемся к противоречию - как мы видим использование сжатие, требует наличия внутреннего инженера по SQL серверу и определённого менеджмента это самого процесса владения SQL сервером.

И теперь становится понятным почему на партнерской конференции был получен ответ


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


как мы видим вверху он в целом соответствует рекомендациям компании Microsoft - буздумно и без поддержки включать сжатие просто не соответствует Best Practics от Microsoft.


А теперь маленькое замечание - если вы ИСПОЛЬЗУЕТЕ сжатие советую ознакомиться с еще одной интересной штукой

https://www.brentozar.com/sql/tempdb-performance-and-configuration/

Грамотный DBA, сжатие и история с tempdb позволяет ускорить ЗУП в 4.5 раза ;-) как минимум.
корум; Berckk; pbazeliuk; pumbaE; lustin; kardanovir; JohnyDeath; Bronislav; nixel; new_user; +10 Ответить
21. Наталия Мастербатова (zzz_natali) 08.12.16 05:50
(16)
Несколько вопросов, если позволите:
1. Если после запуска вашего скрипта что-то пошло через ж..., то какими командами всё вернуть в статус-кво?
2. Я правильно поняла, что эта фишка работает только на Enterprise версиях?
3. Если БД расположены на твердотельных накопителях(SSD), то все телодвижения по сжатию таблиц теряют смысловую нагрузку?
Спасибо.
22. Алексей Бочков (Aleksey.Bochkov) 09.12.16 09:28
(21)
1 - триггер работает в рамках транзакции 1С. Если что-то пойдет не так, то 1С откатит транзакцию и сообщит об ошибке, повреждений в базе при этом не будет. Для отката изменений необходимо отключить триггер и выполнить полную реструктуризацию или выгрузку/загрузки через dt-файл.

2 - это верно для SQL2005-2014. Начиная с SQL2014 SP1 (выпущена 16 ноября 2016) сжатие доступно во всех версиях, в том числе бесплатной Express редакции.
https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/
(в публикации также скорректировал это)

3 - в общем случае, сжатие позволяет снизить нагрузку на сеть/хранилище вне зависимости от типа хранилища.
Если процессор станет узким местом, то вместо увеличения производительности можно получить обратный эффект.
Исходя из своей практики - есть смысл сжимать большие таблицы даже для очень быстрых сетевых хранилищ с 100-500k IOPS.
23. Канат Джумадылов (Fox-trot) 09.12.16 10:37
судя по задаваемым вопросам скоро и бухгалтера начнут заниматься администрированием sql-серверов
24. Антонио (Fragster) 09.12.16 12:45
повесил триггер на тестовую базу, изменил текст таким образом:

CREATE TRIGGER [data_compression]
ON DATABASE
AFTER CREATE_TABLE
AS
DECLARE
@SchemaName nvarchar(150),
@ObjectName nvarchar(150),
@DatabaseName nvarchar(150),
@cmd nvarchar(500)

--Получим имя схемы из выполняемой команды CRE ATE TABLE
SET @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(150)')
--Получим имя таблицы
SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(150)')

--Получим имя базы
SET @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(150)')
--Сформируем из полученных данных требуемую команду на установку признака сжатия для таблицы
set @cmd = 'ALT ER TABLE [' + @DatabaseName + '].[' + @SchemaName + '].[' + @ObjectName + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'

--Теперь проверяем настройки – если базы нет в таблице CompressionSetting.dbo.Databases с признаком Active = 1, то выполняем команду, иначе игнорируем
INS ERT INTO CompressionSetting.dbo.trace (text, DatabaseName, DateTime) SELE CT @cmd, @DatabaseName, GETDATE()

EXEC (@cmd)

получил ошибку при реструктуризации:

В процессе обновления информационной базы произошла критическая ошибка
по причине:
Ошибка СУБД:
Microsoft SQL Server Native Client 11.0: Cannot find the object "dbo._Node6782NG" because it does not exist or you do not have permissions.
HRESULT=80040E37, SQLSrvr: SQLSTATE=42S02, state=C, Severity=10, native=1088, line=1