Всем привет!

В своей первой статье, посвященной группам доступности, я уже писал о системе электронного документооборота ДОМ.РФ «СДУ Приоритет» и о том, как Always On Availability Groups помогли нам значительно сократить требуемое технологическое окно за счёт оптимальной процедуры отката со стороны БД. В этой части речь пойдет о том, как мы провели дедубликацию файлов в СЭД на уровне БД и сократили объем БД на 8Тб без потери информации, и как нам помогли в этом группы доступности.

Удаляем дубликаты

Не известно, это проблема платформы или «Приоритета», но суть ее в том, что при запуске маршрута согласования на каждого согласующего создается свой собственный дубликат каждого приложенного файла. Наш рекордсмен – пакет документов с кучей приложений весом чуть менее 500мб, который прогнали через 10 циклов согласований с общим количеством участников около 60. Да, все верно, суммарный объем данных в БД по данному документу – 30Гб.

В недавнем (мы установили его в январе) масштабном обновлении «Приоритета» и платформы эту проблему наконец-то исправили. Теперь вместо дубликата создается только ссылка на исходный бинарник, а отдельный бинарник появляется только в случае внесения правок в файл. Слава разработчикам! Но что делать с теми дубликатами, которые уже созданы?

Одно из предложений – вытеснить файлы из БД на файловое хранилище с дедубликацией и не заморачиваться. Предложение хорошее, но немедленной реализации мешают 3 момента: вопрос контроля целостности вытесненных файлов, вопрос обеспечения высокой доступности и отсутствие результатов нагрузочного тестирования. Ну и само собой вопрос: а что мешает сначала убрать мусор, а потом уже провести вытеснение? Когда-нибудь мы вернемся к вытеснению, а пока пошли своим путем.

Первым делом разберемся, как мы будем сравнивать наши varbinary(max). Функция CHECKSUM отлично работает в тестовых целях для оценки объемов, т.к. имеет хорошую скорость, но получаемый хэш слишком короткий для полной уверенности в отсутствии коллизий. Для большей точности мы будем использовать HASHBYTES c 32 байтным SHA2-256. У нас - SQL Server 2016. Это важно, т.к. в более ранних версиях существует ограничение на входной объем HASHBYTES – 8000 байт.

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

В отличие от простого апдейта или создания вычисляемой колонки курсор не заклинивает таблицу и не оказывает какого-то заметного влияния на производительность СЭД. Все потребные для работы колонки мы создаем и считаем заранее. В моем случае я исключил из обработки бинарники, созданные в 2021 году (помним, что в новой версии проблема исправлена).

Курсор подсчета хэша

SET NOCOUNT ON
GO
Declare @BinId uniqueidentifier
Declare BinIdCursor CURSOR FOR
Select ID
FROM dbo.dvsys_binaries
where NeedUpdate = 1 and Updated IS NULL
FOR UPDATE
OPEN BinIdCursor
FETCH NEXT FROM BinIdCursor into @BinId
WHILE @@FETCH_STATUS = 0 
BEGIN 
UPDATE BNR
SET BNR.HashSha2256 = Hashbytes('SHA2_256', BNR.Data)
,BNR.Updated = 1
FROM dvsys_binaries as BNR
where BNR.ID = @BinId
  FETCH NEXT FROM BinIdCursor into @BinId
  END
  CLOSE BinIdCursor 
DEALLOCATE BinIdCursor

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

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

Триггер для контроля

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Hashim] 
ON [dbo].[dvsys_binaries] 
AFTER UPDATE  
AS
DECLARE @BinID uniqueidentifier
DECLARE @NeedUpdateCheck tinyint
SELECT @BinID = ins.ID FROM INSERTED ins;
SELECT @NeedUpdateCheck = ins.NeedUpdate FROM INSERTED ins;
IF ( UPDATE (Data) and @NeedUpdateCheck = 1 ) 
BEGIN 
UPDATE BNR
SET BNR.TriggerHash = 1
FROM dvsys_binaries as BNR
where BNR.ID = @BinID
END

Под денормализацией я подразумеваю запись в отдельную колонку данных, до которых надо тянуться через 5 джойнов, в нашем случае – это guid родителькой карточки, а также порядок в согласовании. Этот нехитрый прием многократно ускорит работу нашей основной процедуры.

Пример денормализации

UPDATE [Database_Name].[dbo].[dvsys_binaries]
 SET [RCOrder] = RCAP.[Order],RCInstId = RCAP.InstanceID
FROM Database_Name].[dbo].[dvsys_binaries] as BNR -- Binaries
LEFT JOIN [Database_Name].[dbo].[dvsys_files] as FILES -- Files
on FILES.BinaryID = BNR.ID
LEFT JOIN [Database_Name].[dbo].[dvtable_{F831372E-8A76-4ABC-AF15-D86DC5FFBE12}] as FLVV -- FilListVersion version
  on FLVV.FileID = FILES.FileID
  LEFT JOIN [Database_Name].[dbo].[dvtable_{B4562DF8-AF19-4D0F-85CA-53A311354D39}] as CFMI -- CardFile main info
  on CFMI.FileID = FLVV.InstanceID
   LEFT JOIN [Database_Name].[dbo].[dvtable_{521e077c-a39c-42e3-a4f6-c124b3325740}] as RCSF
   on RCSF.VersionFile = CFMI.InstanceID
   LEFT JOIN [Database_Name].[dbo].[dvtable_{d045c254-e38e-4a0f-b7b0-40bcb5fb8c87}] as RCAP
   on RCAP.RowID = RCSF.Approver

Итак, останавливаем сервера приложений СЭД, и - вперед: нам осталось пересчитать хэш измененных файлов, которые отследил триггер и создать необходимые нам индексы по подсказкам оптимизатора.

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

Основной курсор

Declare @InstId uniqueidentifier
Declare InstIdCursor CURSOR FOR

Select InstanceID
FROM dbo.dvsys_instances

where CardTypeID = 'E9964235-366B-45BF-94AF-B11E840BBC4C' and Optimized IS NULL
FOR UPDATE
OPEN InstIdCursor
FETCH NEXT FROM InstIdCursor into @InstId
WHILE @@FETCH_STATUS = 0 

BEGIN 

UPDATE BNR
SET BNR.ToDel = 0
FROM dvsys_binaries as BNR
INNER JOIN

(SELECT MIN(RCOrder) as MINRCORD,CheckSumData
  FROM [Database_Name].[dbo].[dvsys_binaries]
  where RCInstId = @InstId
  Group by CheckSumData) as MINBNR
  ON BNR.CheckSumData = MINBNR.CheckSumData and BNR.RCOrder = MINBNR.MINRCORD

  UPDATE FLS

SET FLS.NewBinID = FLS1.BinaryID, FLS.MainFile = 0
  FROM [Database_Name].[dbo].[dvsys_binaries] as BNR
  LEFT JOIN [Database_Name].[dbo].[dvsys_binaries] as BNR1

  ON BNR.CheckSumData = BNR1.CheckSumData and BNR1.ToDel = 0
  LEFT JOIN dvsys_files as FLS
  ON FLS.BinaryID = BNR.ID
    LEFT JOIN dvsys_files as FLS1
  ON FLS1.BinaryID = BNR1.ID

  where BNR.RCInstId = @InstId and BNR.ToDel IS NULL
  UPDATE dbo.dvsys_instances
  SET Optimized = 1 WHERE InstanceID = @InstId
  FETCH NEXT FROM InstIdCursor into @InstId
  END
  CLOSE InstIdCursor 
DEALLOCATE InstIdCursor

Мы 2 недели готовились, считали хэши 200 часов, делали денормализацию. Количество файлов к сравнению около 12 миллионов, объем – 16 Тб. И это все ради 2-х часов работы основной процедуры. Кстати, при использовании коротких хэшей CHECKSUM основная процедура отрабатывает всего за 30 минут.

Теперь мы сохраняем в отдельную колонку старые ссылки на бинарники, а потом переписываем их теми, что получены в результате работы основной процедуры. Файлы перенацелены, есть возможность откатить все за 5 минут. И вот, спустя неделю тестирования на кошечках мы наконец-то переходим к необратимым операциям – удалению бинарников и освобождению места.

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

Лёгким движением руки место освобождается… Освобождается… Освобождается…

Перечислю варианты, которые рассматривались для освобождения места:

•   DELETE FROM, SHRINK

• INSERT INTO, RENAME

• SELECT INTO, RENAME

• CURSOR INSERT INTO, RENAME

Не трогайте кнопку шринк! Остановитесь, перестаньте делать шринк! Стоп! Прекратите!

Это слова матерых DBA, и они правы. Но в нашем случае SHRINK выглядел весьма привлекательно, мы даже можем запустить DELETE через курсор и, контролируя рост лога транзакций бэкапами, успешно завершить удаление дней за 5 без критичных блокировок. Вся проблема в том, что после такого удаления мы получим файлы с хаотично раскиданными страницами и наш SHRINKFILE, пытаясь разложить страницы, наглухо заклинит БД на непредсказуемый период времени (один из тестов показал 4 дня). Один раз на тесте удалось добиться удаления со структурированием страниц и тогда работал SHRINKFILE TRUNCATEONLY (т.е. освобождение свободного места в конце файла, выполняется почти мгновенно), но для такого DELETE нужен TABLOCK, определенное сочетание индексов (и звезд), а также большой запас времени.

Резюмирую свои попытки задействовать шринк: «Не трогайте кнопку шринк!!!»

Попытка №1 INSERT INTO

Итак, начинаем работы на продуктивной среде. Дано:

Таблица dvsys_binary объемом в 18ТБ. 10ТБ необходимо сохранить, 8ТБ в мусорную корзину. Для проведения работ запрошен временный диск объемом 11ТБ под размещение новой файловой группы.

План работ прост – согласовываем неработоспособность СЭД, отключаем сервера в ночь с пятницы на субботу, отключаем репликацию на вторую ноду, выставляем Recovery Model – Simple и делаем INSERT INTO WITH (TABLOCK), а днем переименовываем таблицу и завершаем работы.

Ближе к утру на почту пришли оповещения о стремительно заканчивающемся месте на диске, а так как я просил группу дежурных администраторов игнорировать сработавшие триггеры, то и узнал о проблеме уже утром. Главная моя ошибка - я чрезмерно уверился в том, что модель восстановления Simple и TABLOCK гарантируют минимальное логирование, поэтому не проверил реальное использование лога транзакций.

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

Попытка №2 SELECT INTO on Filegroup

Обычный SELECT INTO для нас не подходит, т.к. не хотелось бы заливать в стандартную файловую группу такой объем. SELECT INTO с указанием файловой группы изначально позиционировался как новый функционал SQL Server 2017, т.е. пользователям Server 2016 оставалось только облизываться. Какова же была моя радость, когда я вчитался во все это повнимательнее и увидел, что это работает и в 2016sp2. Красота, будет быстро и надежно, думал я. Зря.

План аналогичный, согласовываем, отключаем СЭД, репликацию. Процесс идет, скорость в норме, лог не растет, красота. И где-то на 50% процесс останавливается по причине SLEEP_TASK wait.

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

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

Попытка №3 CURSOR INSERT INTO.

Концепция работ по дедубликации изначально строилась на принципе разбиения крупных задач на мелкие. Но видимая простота задачи сподвигла на выбор простых решений, ведь казалось, что может быть проще, чем скопировать строки из таблицы А в таблицу Б?

Настало время перейти к плану БВГ – курсору. Запуск на тесте показал, что курсор будет работать около 70 часов, ожидаемый рост лога транзакций около 150 ГБ/час (на тесте роста не было, на продуктивной будет из-за репликации), влияние на быстродействие не критичное.

Курсор

SET NOCOUNT ON;
Declare @BinID uniqueidentifier
Declare BinIDCursor CURSOR FOR
Select ID
FROM dbo.dvsys_binaries
where NeedUpdate = 1 and (Updated != 1 or Updated is NULL)
FOR UPDATE
OPEN BinIDCursor
FETCH NEXT FROM BinIDCursor into @BinID
WHILE @@FETCH_STATUS = 0 
BEGIN 
INSERT INTO dbo.dvsys_dedup_binaries with (TABLOCK)  ( [Data]
      ,[ID]
      ,[Type]
      ,[StreamData]
      ,[FullTextTimeStamp]
      ,[PartNum]
      ,[StorageID]
      ,[Path]
      ,[Size]
      ,[Hash]
      ,[HaveFileLink]
      ,[HashSha2256]
      ,[Updated]
      ,[NeedUpdate]
      ,[TriggerHash]
      ,[ToDel]
      ,[RCInstId]
      ,[RCOrder])

SELECT  BNR.[Data]

      ,BNR.[ID]
      ,BNR.[Type]
      ,BNR.[StreamData]
      ,null
      ,BNR.[PartNum]
      ,BNR.[StorageID]
      ,BNR.[Path]
      ,BNR.[Size]
      ,BNR.[Hash]
      ,BNR.[HaveFileLink]
      ,BNR.[HashSha2256]
      ,BNR.[Updated]
      ,BNR.[NeedUpdate]
      ,BNR.[TriggerHash]
         ,BNR.[ToDel]
      ,BNR.[RCInstId]
      ,BNR.[RCOrder]
  FROM [Database_Name].[dbo].[dvsys_binaries] as BNR
  where bnr.ID = @BinID and NeedUpdate = 1
UPDATE BNR
SET BNR.Updated = 1
FROM dvsys_binaries as BNR
where BNR.ID = @BinID
  FETCH NEXT FROM BinIDCursor into @BinID
  END
  CLOSE InstIdCursor 
DEALLOCATE InstIdCursor

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

Работы согласованы, подготовка завершена, утро понедельника – ключ на старт. Продуктивная среда показывает чуть более высокую скорость работы – рост логов около 200 ГБ/час, ожидаемое время чуть больше 60 часов. С 6 до 18 часов каждые 3 часа происходит бэкап лога транзакций, который не дает нашему логу разрастись.

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

Джоб и View

INSERT INTO Anik_logmonitoring ( [Database Name]
      ,[Log Size (MB)]
      ,[Log Space Used (%)]
      ,[Status])
EXEC('DBCC SQLPERF(LOGSPACE);');
Update Anik_logmonitoring
set Date = GetDate()
where Date is Null;
 CREATE view [dbo].[anik_Monitoring_MainLogSize]
as
SELECT TOP (1)
      [Log Size (MB)] as LogSize
      ,[Log Space Used (%)] as LogSizeUsedPercent
  FROM [Database_Name].[dbo].[Anik_logmonitoring]
  where DATEDIFF(MINUTE,GETDATE(), Date) < 10
  and [Database Name] = 'Database_Name'
  order by Date desc
GO

Я попросил коллег повесить триггер с критичностью Disaster (круглосуточная реакция) на утилизацию лога более 85% и через 2 часа (в 8 вечера!!!) получил ответ о готовности. Респект коллегам за оперативность.

Мониторинг есть, значит настало время его испытать. Я не стал выключать курсор и сел ждать. Последний бэкап был в 18:00, значит лог с ростом около 200 ГБ/час дойдет до предельной планки 1200 ГБ ближе к полуночи.  

Я три ночи не спал,

Я устал.

Мне бы заснуть,

Отдохнуть…

Но только я лег —

Звонок!

— Кто говорит?

— Носорог ГДА.

— Что такое?

— Беда! Беда!

Позовите скорей DBA!

— В чем дело?

— Спасите!

— Кого?

— Наш лог!

Наш лог ушел за порог…

— Ушел за порог?

— Да!

И ни туда, ни сюда!

Мониторинг работает, прекрасно. Дальнейшие действия прошли без неожиданностей: перелили остатки, создали индексы и constraints, переименовали, протестировали и, наконец, удалили старую файловую группу. Единственный нюанс, на который хотел бы обратить внимание – при создании кластерного индекса необходимо убедиться, что он создается в правильной файловой группе, иначе sql начнет перетягивать таблицу в другую файловую группу.

Заключение

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

#СЭД #DocsVision, #AlwaysOn, #AvailabilityGroups, #MSSQL