На связи Никита Скирдин, программист 1С компании «Белый код».
Одна из проблем механизма Истории данных в 1С – это значительное увеличение объема базы данных. При этом старые версии истории данных, даже если настроено хранение истории данных в течение 3 месяцев, могут оставаться в базе и занимать место. В статье рассказываю пошагово алгоритм действий.
При записи объекта, версии которого должны сохраняться в Истории данных, информация о значениях реквизитов и табличных частях объекта сохраняется в таблицу `_DataHistoryQueue0`. Из этой таблицы-очереди данные должны переноситься в таблицу `_DataHistoryVersions` по команде `ИсторияДанных.ОбновитьИсторию();` или сразу при включенной опции «Обновлять историю данных сразу после записи». Но что если регламентное обновление истории не производилось, а при обновлении сразу после записи база начинает подвисать из-за большого количества одновременных записей, а значит и большого количества фоновых заданий?
В Highload-системах размер очереди истории данных может достигать больших значений: при размере базы 500 Гб размер таблицы `_DataHistoryQueue0` на нашем примере достигал 60 Гб. На очистку очереди через обновление истории требуется слишком много времени, особенно когда обработки требует не один десяток миллионов записей в очереди. В нашем случае механизм должен был бы обработать 12 миллионов записей.
Очевидным выходом из такой ситуации может стать очистка очереди `_DataHistoryQueue0` через sql-скрипт, который может как запускаться администратором системы раз в какое-то время вручную, так и автоматически по регламенту. Есть лишь одна проблема: для создания такого скрипта нам необходима дата записи истории данных, но отдельной колонки для дат в очереди нет. Данная статья освещает способ извлечения даты записи из таблицы `_DataHistoryQueue0` исключительно средствами Microsoft SQL Server 2022 (на старых версиях MSSQL извлечение даты также возможно, но сделать это сложнее в связи с отсутствием битовых сдвигов).
В следующих частях статьи мы постепенно пройдем по пути разработчика: от изучения структуры таблицы-очереди и извлечения нужных нам байтов до перевода байт в дату и внесения финальных штрихов в скрипт, чтобы он успешно справлялся с поставленной задачей – удалением лишних записей из таблицы `_DataHistoryQueue0`.
Общая структура _DataHistoryQueue0
Таблица `_DataHistoryQueue0` содержит в себе следующие колонки:
Скрытый текст
`_MetadataId` – уникальный ключ метаданных, который может использоваться для сопоставления с таблицей `_DataHistoryMetadata`.
`_DataId` – уникальный идентификатор хранимого в истории данных объекта.
`_Position` – порядковая версия хранимого в истории данных объекта.
`_Content` – содержимое записи очереди истории данных.
А также `_Fld814` и `_Fld789`, назначение которых в рамках этой статьи не разбиралось
Каждую запись таблицы-очереди можно однозначно идентифицировать по трем колонкам: `_MetadataId`, `_DataId` и `_Position`. Если рассматривать в терминах 1С таблицу-очередь как Регистр сведений, то обозначенные выше три колонки – это измерения регистра, а свойство `_Content` – ресурс.
Возможность составить ключ записи из трех свойств пригодится нам в финальной части для оптимизации скрипта в вопросе удаления записей из `_DataHistoryQueue0`. Сейчас же подробно рассмотрим свойство `_Content`, поскольку именно в нем находится необходимая нам дата записи.
Поиск и извлечение содержащих дату байтов
Значение поля _Content представляет из себя набор байт в формате TLV (Tag-Length-Value). Опираясь на исследования из статьи, получаем, что нужная нам дата содержится сразу после тега `0x30`. Автор статьи не упоминает это прямо, но для свойств с типом `varint` (variable int), в отличие от других типов, между тегом и значением свойства нет размера. Пример свойства даты в составе `_Content`: `0x000030C0A2DCFBA0909101`, само значение даты будет содержаться в части `0xC0A2DCFBA0909101`.
Т. к. все свойства до даты имеют размер, то общая логика алгоритма поиска даты в составе `_Content` будет выглядеть следующим образом:
Пропускаем первый байт, поскольку он всегда указывает на то, что дальше идет Заголовок записи истории данных.
Пропускаем идущий за первым байтом `varint`.
Получаем байт типа данных и следующий за ним размер свойства в формате `varint`.
Если тип равен `0x30`, то перед нами на самом деле не размер свойства, а нужная нам дата. Соответственно, получаем из `varint` дату и выходим из алгоритма.
В противном случае пропускаем байт типа, байты размера в формате `varint` и то количество байт, которое было указано в размере.
Повторяем действия с п. 3, пока _Content не подойдет к концу или пока не найдем дату.
Практическая реализация данного алгоритма на языке MSSQL будет выглядеть следующим образом:
Скрытый текст
```
CREATE FUNCTION get_date_from_content (
@content AS VARBINARY(max)
)
RETURNS DATETIME
WITH EXECUTE AS CALLER
AS BEGIN
DECLARE @type AS VARBINARY(1)
DECLARE @length_bin AS VARBINARY(max)
DECLARE @length AS BIGINT
DECLARE @seconds AS BIGINT
SET @content = SUBSTRING(@content, 2, DATALENGTH(@content))
SET @content = dbo.get_content_after_varint(@content)
WHILE (DATALENGTH(@content) > 0) BEGIN
SET @type = SUBSTRING(@content, 1, 1)
SET @length_bin = SUBSTRING(@content, 2, DATALENGTH(@content))
SET @length = dbo.get_varint(@length_bin)
IF (@type = 0x30) BEGIN
SET @seconds = @length / 10000
RETURN dbo.get_date_from_bigint_s(@seconds)
END
SET @content = dbo.get_content_after_varint(@length_bin)
SET @content = SUBSTRING(@content, 1 + @length, DATALENGTH(@content))
END
RETURN NULL;
END;
GO
```
Как можно заметить, в функции `get_date_from_content` используется ряд вспомогательных функций. Одну из них, `get_date_from_bigint_s`, мы рассмотрим в следующем разделе. Сейчас сосредоточим внимание на разборе `varint`.
Перепишем приведенный в упомянутой статье алгоритм для получения числа из формата `varint` на язык MSSQL:
Скрытый текст
```
CREATE FUNCTION get_varint(
@content AS VARBINARY(max)
)
RETURNS BIGINT
WITH EXECUTE AS CALLER
AS BEGIN
DECLARE @index AS INT;
DECLARE @offset AS INT;
DECLARE @value AS BIGINT;
DECLARE @chunk AS BIGINT;
SET @index = 0;
SET @offset = 0;
SET @value = 0;
SET @chunk = 0;
WHILE (1 = 1) BEGIN
SET @index += 1;
SET @chunk = SUBSTRING(@content, @index, 1);
SET @value |= LEFT_SHIFT(@chunk & 0x7f, @offset);
SET @offset += 7;
IF NOT ((@chunk & 0x80) = 0x80) BREAK;
END
RETURN @value;
END;
GO
```
Также создадим вспомогательную функцию, которая позволит нам пропустить набор байт, входящих в varint, не получая при этом его числового значения:
Скрытый текст
```
CREATE FUNCTION get_content_after_varint(
@content AS VARBINARY(max)
)
RETURNS VARBINARY(max)
WITH EXECUTE AS CALLER
AS BEGIN
DECLARE @index AS INT;
DECLARE @offset AS INT;
DECLARE @chunk AS BIGINT;
SET @index = 0;
SET @chunk = 0;
WHILE (1 = 1) BEGIN
SET @index += 1;
SET @chunk = SUBSTRING(@content, @index, 1);
IF NOT ((@chunk & 0x80) = 0x80) BREAK;
END
RETURN SUBSTRING(@content, @index + 1, DATALENGTH(@content));
END;
GO
```
Таким образом, мы можем получать байты даты из свойства `_Content`. Следующим шагом преобразуем нашу дату из числа в тип `DATETIME` MSSQL.
Получение даты из байт
Дата в свойстве `_Content` хранится в виде единицы измерения времени, единица которой равна 10-4 секунд. Т. е. для получения секунд нам необходимо разделить полученное из `_Content` число на 10 000.
Поскольку количество секунд отмеряется от «пустой даты» 1С, т. е. от 01.01.0001, то нам придется несколько схитрить и сначала прибавить полученное количество секунд к 01.01.1900, а затем вычесть 1899 лет, чтобы получить дату от «пустой даты» 1С.
Но здесь мы столкнемся с еще одной проблемой: функция `DATEADD` принимает на вход аргумент `INT`, а не `BIGINT`. При попытке преобразовать количество секунд, которое не помещается в INT, мы получим переполнение и скрипт не выполнится. В связи с этим мы разделим секунды на количество суток, поделив на 86400 (количество секунд в сутках) с округлением вниз, и на остаток секунд в последних сутках, взяв остаток от деления секунд на 86400.
Итоговая функция для получения даты из секунд, прошедших с «пустой даты» 1С, выглядит следующим образом:
Скрытый текст
```
CREATE FUNCTION get_date_from_bigint_s(
@dt_s AS BIGINT
)
RETURNS DATETIME
WITH EXECUTE AS CALLER
AS BEGIN
RETURN DATEADD(YEAR, -1899, DATEADD(SECOND, @dt_s % 86400, DATEADD(DAY, @dt_s / 86400, '19000101')))
END;
GO
```
На этом этапе у нас готовы все основные функции, которые нам необходимы.
Исключение объектов с годовым сроком хранения истории
В рамках задачи мы будем удалять записи, срок хранения которых вышел. При этом в ходе предварительного анализа было выяснено, что в базе нет записей, хранящихся дольше года, поэтому нет необходимости обрабатывать те объекты, срок хранения которых 12 месяцев.
Скрипт для выбора записей, которые были созданы год и более назад, представлен ниже:
Скрытый текст
```
SELECT COUNT(*)
FROM [dbo].[_DataHistoryQueue0]
WHERE
dbo.get_date_from_content([_Content]) <= '20230731'
GO
```
Для того, чтобы выбрать `_MetadataId`, которые нам не нужно обрабатывать, придется воспользоваться механизмом трассировки запросов SQL. Не прибегая к трассировке, однозначную связь между `_MetadataId` и именем объекта метаданных можно установить только через разбор данных, содержащихся в столбце `_Content` таблицы `Config`, в которой находится конфигурация информационной базы, что крайне сложно сделать, поскольку данные закодированы в формате ZIP.
Профайлер мы будем запускать из SQL Server Management Studio по кнопке меню Сервис -> SQL Server Приложение Profiler. В открывшемся окне профайлера необходимо будет установить соединение с SQL сервером, а затем на вкладке “Events Selection” необходимо убрать все флажки и оставить только флажок на событии TSQL -> Exec Prepared SQL. Затем необходимо установить фильтр по названию базы данных по колонке `DatabaseName`.
Открывая в 1С по очереди историю изменений каждого объекта, который должен храниться в базе дольше 3 месяцев, и записывая идентификатор метаданных, получим список `_MetadataId`, которые необходимо исключить из удаления.
В итоге наш скрипт для удаления записей из таблицы-очереди будет выглядеть следующим образом:
Скрытый текст
```
DELETE
FROM [dbo].[_DataHistoryQueue0]
WHERE
[_MetadataId] NOT IN (
0x8A75BB6E3004590F45A06B581CEEF4A2,
0xBE4FE18B73CE726B4509F392B1DFE632,
0x8169B63ABBFD04EE4A4444917D549218,
0xBA20A37C9742EC794B670349C97305DD,
0xABB1DCE1CCA4EBD34CC879957B04EBC4,
0x90979FE67516B81D4522F56D69EC59C6,
0x873AA9FAFA7853994C34E4BE318716F3,
0x87D1CB8D023DD564481FFB5110CEFB2F,
0x816065A0C64D131848D9CC82319B18A1,
0x8D2F98E82AAE11944AFB72537B86CF07,
0xBCAE1B918895B132476FAB97DC549688,
0xBCC8B09974E023B34910004572B83C8F,
0xB337CDDDD2968BEA42B6E36C050B09A5,
0xBC0986B5B5A4A65B43EDD06059AFD471,
0x984A2A2DA8517F384FA3464C5E0D9DD9,
0xA3307B6F835E2DA7413F86B3E16B931A,
0xA64BCCACFCABC3FB4D7079A3EAE285FE,
0x85F9086B89E1764B4965ED8B8EAC9A31,
0x9C09C3A3CFCCF41446A93DC280B2615F
)
AND dbo.get_date_from_content([_Content]) < '20240431'
GO
```
Финальные штрихи
Последняя проблема – это переполнение лога транзакций при попытке удаления больше 7 миллионов строк одним запросом. Чтобы решить эту проблему, разобьем операцию удаления с помощью оператора `TOP`. Поместив запрос удаления в цикл, получим парциальное удаление данных, которое позволит избежать чрезмерного разрастания лога транзакций.
Поскольку при использовании запроса на удаление в цикле мы будем вынуждены каждый раз по новой запускать отбор по дате, который занимает значительное время, то в целях экономии времени имеет смысл создать временную таблицу, куда мы поместим записи, которые затем будем удалять.
В создании такой таблицы нам поможет знание того, что у `_DataHistoryQueue0` 3 ключевые колонки: `_MetadataId`, `_DataId` и `_Position`. Временную таблицу мы можем создать следующей командой:
Скрытый текст
```
CREATE TABLE dbo._DataHistoryQueue0_TmpForDeletion (
_MetadataId binary(16) NOT NULL,
_DataId binary(20) NOT NULL,
_Position numeric(9, 0) NOT NULL,
CONSTRAINT PK_Queue PRIMARY KEY (_MetadataId, _DataId, _Position)
)
GO
```
Переписав полученный в предыдущем разделе скрипт, получим:
Скрытый текст
```
-- Insert items in table for deletion.
INSERT INTO dbo._DataHistoryQueue0_TmpForDeletion
(_MetadataId, _DataId, _Position)
SELECT
_MetadataId,
_DataId,
_Position
FROM [dbo].[_DataHistoryQueue0]
WHERE
[_MetadataId] NOT IN (
0x8A75BB6E3004590F45A06B581CEEF4A2,
0xBE4FE18B73CE726B4509F392B1DFE632,
0x8169B63ABBFD04EE4A4444917D549218,
0xBA20A37C9742EC794B670349C97305DD,
0xABB1DCE1CCA4EBD34CC879957B04EBC4,
0x90979FE67516B81D4522F56D69EC59C6,
0x873AA9FAFA7853994C34E4BE318716F3,
0x87D1CB8D023DD564481FFB5110CEFB2F,
0x816065A0C64D131848D9CC82319B18A1,
0x8D2F98E82AAE11944AFB72537B86CF07,
0xBCAE1B918895B132476FAB97DC549688,
0xBCC8B09974E023B34910004572B83C8F,
0xB337CDDDD2968BEA42B6E36C050B09A5,
0xBC0986B5B5A4A65B43EDD06059AFD471,
0x984A2A2DA8517F384FA3464C5E0D9DD9,
0xA3307B6F835E2DA7413F86B3E16B931A,
0xA64BCCACFCABC3FB4D7079A3EAE285FE,
0x85F9086B89E1764B4965ED8B8EAC9A31,
0x9C09C3A3CFCCF41446A93DC280B2615F
)
AND dbo.get_date_from_content([_Content]) < '20240431'
GO
-- Delete items.
WHILE 1 = 1 BEGIN
DELETE TOP (100000) dhq
FROM dbo._DataHistoryQueue0 AS dhq
INNER JOIN dbo._DataHistoryQueue0_TmpForDeletion AS tmp
ON dhq._MetadataId = tmp._MetadataId
AND dhq._DataId = tmp._DataId
AND dhq._Position = tmp._Position
IF @@rowcount < 100000
BREAK;
END
GO
```
Нам осталось лишь добавить несколько качественных улучшений: удаление создаваемых функций и временной таблицы, а также подсчет попавших во временную таблицу записей, чтобы легче было узнать, сколько строк удалено из таблицы-очереди. Итоговая версия скрипта представлена в Приложении 1.
Итоги
В результате применения данного скрипта удалось очистить из таблицы `_DataHistoryQueue0` 2 из 12 миллионов строк. Размер таблицы сократился с 61 Гб до 55 Гб.
Приложение 1. Финальная версия скрипта
Скрытый текст
```
IF object_id('dbo._DataHistoryQueue0_TmpForDeletion', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo._DataHistoryQueue0_TmpForDeletion
END
IF object_id('dbo.get_varint', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].get_varint
END
IF object_id('dbo.get_content_after_varint', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].get_content_after_varint
END
IF object_id('dbo.get_date_from_bigint_s', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].get_date_from_bigint_s
END
IF object_id('dbo.get_date_from_content', 'FN') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].get_date_from_content
END
GO
CREATE FUNCTION get_varint(
@content AS VARBINARY(max)
)
RETURNS BIGINT
WITH EXECUTE AS CALLER
AS BEGIN
DECLARE @index AS INT;
DECLARE @offset AS INT;
DECLARE @value AS BIGINT;
DECLARE @chunk AS BIGINT;
SET @index = 0;
SET @offset = 0;
SET @value = 0;
SET @chunk = 0;
WHILE (1 = 1) BEGIN
SET @index += 1;
SET @chunk = SUBSTRING(@content, @index, 1);
SET @value |= LEFT_SHIFT(@chunk & 0x7f, @offset);
SET @offset += 7;
IF NOT ((@chunk & 0x80) = 0x80) BREAK;
END
RETURN @value;
END;
GO
CREATE FUNCTION get_content_after_varint(
@content AS VARBINARY(max)
)
RETURNS VARBINARY(max)
WITH EXECUTE AS CALLER
AS BEGIN
DECLARE @index AS INT;
DECLARE @offset AS INT;
DECLARE @chunk AS BIGINT;
SET @index = 0;
SET @chunk = 0;
WHILE (1 = 1) BEGIN
SET @index += 1;
SET @chunk = SUBSTRING(@content, @index, 1);
IF NOT ((@chunk & 0x80) = 0x80) BREAK;
END
RETURN SUBSTRING(@content, @index + 1, DATALENGTH(@content));
END;
GO
CREATE FUNCTION get_date_from_bigint_s(
@dt_s AS BIGINT
)
RETURNS DATETIME
WITH EXECUTE AS CALLER
AS BEGIN
RETURN DATEADD(YEAR, -1899, DATEADD(SECOND, @dt_s % 86400, DATEADD(DAY, @dt_s / 86400, '19000101')))
END;
GO
CREATE FUNCTION get_date_from_content (
@content AS VARBINARY(max)
)
RETURNS DATETIME
WITH EXECUTE AS CALLER
AS BEGIN
DECLARE @type AS VARBINARY(1)
DECLARE @length_bin AS VARBINARY(max)
DECLARE @length AS BIGINT
DECLARE @seconds AS BIGINT
SET @content = SUBSTRING(@content, 2, DATALENGTH(@content))
SET @content = dbo.get_content_after_varint(@content)
WHILE (DATALENGTH(@content) > 0) BEGIN
SET @type = SUBSTRING(@content, 1, 1)
SET @length_bin = SUBSTRING(@content, 2, DATALENGTH(@content))
SET @length = dbo.get_varint(@length_bin)
IF (@type = 0x30) BEGIN
SET @seconds = @length / 10000
RETURN dbo.get_date_from_bigint_s(@seconds)
END
SET @content = dbo.get_content_after_varint(@length_bin)
SET @content = SUBSTRING(@content, 1 + @length, DATALENGTH(@content))
END
RETURN NULL;
END;
GO
CREATE TABLE dbo._DataHistoryQueue0_TmpForDeletion (
_MetadataId binary(16) NOT NULL,
_DataId binary(20) NOT NULL,
_Position numeric(9, 0) NOT NULL,
CONSTRAINT PK_Queue PRIMARY KEY (_MetadataId, _DataId, _Position)
)
GO
-- Insert items in table for deletion.
INSERT INTO dbo._DataHistoryQueue0_TmpForDeletion
(_MetadataId, _DataId, _Position)
SELECT
_MetadataId,
_DataId,
_Position
FROM [dbo].[_DataHistoryQueue0]
WHERE
[_MetadataId] NOT IN (
0x8A75BB6E3004590F45A06B581CEEF4A2,
0xBE4FE18B73CE726B4509F392B1DFE632,
0x8169B63ABBFD04EE4A4444917D549218,
0xBA20A37C9742EC794B670349C97305DD,
0xABB1DCE1CCA4EBD34CC879957B04EBC4,
0x90979FE67516B81D4522F56D69EC59C6,
0x873AA9FAFA7853994C34E4BE318716F3,
0x87D1CB8D023DD564481FFB5110CEFB2F,
0x816065A0C64D131848D9CC82319B18A1,
0x8D2F98E82AAE11944AFB72537B86CF07,
0xBCAE1B918895B132476FAB97DC549688,
0xBCC8B09974E023B34910004572B83C8F,
0xB337CDDDD2968BEA42B6E36C050B09A5,
0xBC0986B5B5A4A65B43EDD06059AFD471,
0x984A2A2DA8517F384FA3464C5E0D9DD9,
0xA3307B6F835E2DA7413F86B3E16B931A,
0xA64BCCACFCABC3FB4D7079A3EAE285FE,
0x85F9086B89E1764B4965ED8B8EAC9A31,
0x9C09C3A3CFCCF41446A93DC280B2615F
)
AND dbo.get_date_from_content([_Content]) < '20240424'
GO
-- Delete items.
WHILE 1 = 1 BEGIN
DELETE TOP (100000) dhq
FROM dbo._DataHistoryQueue0 AS dhq
INNER JOIN dbo._DataHistoryQueue0_TmpForDeletion AS tmp
ON dhq._MetadataId = tmp._MetadataId
AND dhq._DataId = tmp._DataId
AND dhq._Position = tmp._Position
IF @@rowcount < 100000
BREAK;
END
GO
SELECT Count(*) FROM dbo._DataHistoryQueue0_TmpForDeletion
GO
DROP TABLE dbo._DataHistoryQueue0_TmpForDeletion
DROP FUNCTION dbo.get_varint
DROP FUNCTION dbo.get_content_after_varint
DROP FUNCTION dbo.get_date_from_bigint_s
DROP FUNCTION dbo.get_date_from_content
```
mixsture
А что не так с порционным удалением средствами платформы?
Эти огромные объемы, которые вы накопили - скорее всего следствие неудаления за много-много месяцев, т.е. проблема разовая. Я пока не вижу, что мешает это разгребать небольшими кусочками за неделю - в плюсах у вас окажется: работа на любой субд и даже файловой версии, привычный язык.
stungnthumz
Присоединяюсь к вопросу.
На одной чаше весов потешенное самолюбие разработчика, на другой -- гарантированный геморрой при изменении структуры хранения, не говоря уже о нарушении лицензионного соглашения 1С.