Работая с MS SQL, я привык воспринимать название Ring Buffer как небольшую структуру в памяти, организованную по принципу FIFO overwrite. И чаще всего в контексте Extended Events. Но как-то я встретил упоминание того же Ring Buffer в заголовке статьи про секционирование таблиц! Купился на название, прочёл статью и сохранил себе идею.
В статье описывалось, как Ring Buffer решает задачу ротации данных во времени, которую принято решать с помощью Sliding Window. И я постараюсь передать эту идею так, чтобы после прочтения у вас появился еще один способ решить обычную задачу необычным способом. Не для галочки в резюме, а для рассказов на встречах с коллегами. В моей работе этот подход позволил сделать интересной скучную задачу организации хранения статистики производительности сервера, но может быть использован и для других данных с ограниченным сроком хранения или, иначе говоря, с фиксированным количеством секций. Например, данных аудита.
Сравнение Sliding Window с Ring Buffer
Оба подхода имеют много общего, но ключевое отличие заключается в работе с секциями, а именно в том, как они переиспользуются.
В подходе Sliding Window секции вовсе не переиспользуются: вместо этого выполняется удаление старой секции (MERGE RANGE) и создание новой секции (SPLIT RANGE). Важно, что обе операции должны точно выполняться, потому что:
без
MERGE«тащится хвост» устаревших данных,без
SPLITновые данные либо продолжают записываться в последнюю доступную секцию, либо вовсе теряются.
При этом порядок выполнения не важен.
В подходе Ring Buffer все необходимые секции создаются в самом начале. Далее необходимо очищать секцию с устаревшими данными перед началом записи в неё новых. Это помогает избежать потери данных, но не спасёт от разрастания секций при ошибке в процессе очистки. Это значит, что совсем избавиться от джобов обработки не удастся, но можно обойтись только транкейтом секций (для MS SQL server 2016 и более новых), при условии, что данные безнадёжно устаревают к концу периода хранения. Например, как это бывает с логами. Шаб��он таблицы приведу в примерах ниже.
И, как я отмечал в начале статьи, в нашей компании мы применяем такой подход именно к таблицам со статистикой производительности — для статистики ожиданий, активных запросов и наличия блокировок. Детальная информация для анализа устаревает через 7–30 дней, в зависимости от её типа.
Сразу перейду к деталям реализации и начну с вводных.
Пример использования
Дано. Таблица с логами, которые необходимо сохранять в течении 7 дней. Например, статистику ожиданий запросов во время высокой нагрузки для последующего анализа и оптимизации.
Задача. Организовать ротацию этих логов с возможностью быстрого удаления и сохранения устаревшей информации при необходимости.
Уверен, что у читателя уже нарисовалась в голове схема и функция секционирования и пишется привычная процедура для поддержки по sliding window. А ниже я опишу решение этой задачи с применением Ring Buffer, который и создан для работы с таблицами с фиксированным количеством секций.
В примере буду использовать таблицу со следующей структурой:
CREATE TABLE dbo.Logs ( ... – много полезных данных collection_time datetime2(7) NOT NULL, offset AS (CONVERT(tinyint, DATEDIFF(DAY, (0), collection_time) % (14))) PERSISTED NOT NULL –where 14 is equal to buffer size ) ON PSRingBuffer(offset)
Особого внимания требует поле offset. В случае с нашим решением тут используется дата, точнее, день и номер этого дня делится на 14. Именно это число определяет максимальное количество секций, которые используются таблицей. И тут выбрано 14, а не 7 — специально для обеспечения запаса расширения глубины хранения. Предполагается, что более двух недель истории запросов для анализа нам не понадобится, даже чтобы перекрыть новогодние выходные.
Для разных таблиц может использоваться одна и та же функция и схема секционирования.Количество секций, доступных в функции и схеме, может быть больше, чем необходимо для конкретной таблицы. Например, в функции может быть описано 30 секций, а таблица будет использовать только 14 из них.
Как это использовать? Например, для таблиц, похожих по смыслу или уменьшения количества сущностей в базе.
Описание самих секций:
CREATE PARTITION FUNCTION [PFRingBuffer] tinyint AS RANGE RIGHT FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13) CREATE PARTITION SCHEME [PSRingBuffer] AS PARTITION [PFRingBuffer] ALL TO ([PRIMARY])
Структура выше — это только основа. Сама идея кольцевого буфера реализуется в процедуре ротации секций.
Вот сигнатура процедуры, используемой у нас:
EXEC dbo.RingbufferRotate @Retention = 7, -- сколько секций останется после запуска @BufferSize = 14, -- сколько всего их используется целевой таблицей @CurrentPartition = $PARTITION.PFRingBuffer(DATEDIFF(DAY, (0), GETUTCDATE()) % 14), @SourceTblName = N'Logs', @TargetTblName = N'LogsSwitch' -- таблица, в которую будут сбрасываться секции для архивирования/очистки
В последующих вычислениях будут прибавляться/вычитаться магические значения, к которым мы с коллегами пришли, чтобы учесть внутренние особенности сиквела.
В идеальном мире определение секции с самыми старыми данными, которые нужно сохранить, выглядело бы так:
SET @OldestPartition = (@CurrentPartition - @Retention + @BufferSize) % @BufferSize
Но MS SQL сервер добавляет секцию для значений меньше первой границы и Null, чем сдвигает их порядковые номера.
Также требуется учесть, что значения в поле offset начинаются с 0, тогда как номера секций с 1.
Таким образом, финальная формула выглядит так:
SET @OldestPartition = (@CurrentPartition - @Retention + 1 - 2 + @BufferSize) % @BufferSize + 2
Где:
@Retention + 1помогает правильно вычесть заданную глубину хранения;-2смешает номера секций из-за наличия Null-секции;последнее
+2возвращает соответствие номеров в sys.partitions.
Имея под рукой все эти данные, начинаем обход секций, избавляясь от устаревших данных.
WHILE 1=1 BEGIN SET @CurrentPartition = (@CurrentPartition + 1 - 2) % @BufferSize + 2 IF @CurrentPartition = @OldestPartition BEGIN BREAK END SET @Sql = 'TRUNCATE TABLE @SourceTblName WITH (PARTITIONS(@CurrentPartition))' EXEC sp_executesql @Sql END
Вот несколько примеров работ�� процедуры очистки.
Предположим, что сегодня день номер 11 (offset), 14 секций доступно в функции и 7 должны остаться после очистки.
Сначала определится номер секции для значения 11 — это 13.
Начиная со следующей секции будет происходить движение «вперёд» и очистка каждой следующей секции. То есть 14, 15, 16…
Но 16 не вяжется с выбранным количеством секций и будет преобразовано делением на размер буфера % @BufferSize. Счёт продолжится до тех пор, пока номер секции в итерации не совпадёт с номером секции с самыми старыми данными, которые хотим оставить.
Визуально это будет выглядеть так:
Для дня 12 (offset) при тех же параметрах выглядит так:
И на десерт, offset = 2 с аналогичными вводными создает впечатление, что данные закольцованы:
Сохранение данных где-то вне таблицы читателю придётся реализовать самостоятельно, выполняя следующий код для каждой секции:
'ALTER TABLE <@SourceTblName> SWITCH PARTITION <@CurrentPartition> TO <@TargetTblName> PARTITION <@CurrentPartition>'
Исходная и целевая секции должны находится в одной и той же файловой группе. Но нас интересует только очистка и поэтому в процедуре выше выполняется:
'TRUNCATE TABLE @SourceTblName WITH (PARTITIONS(@CurrentPartition))'
Важно понимать, что код выше приведён с целью показать реализацию самого алгоритма. Перед использованием на проде требуется обложить его проверками и экранированием переменных.
Оригинальная статья Thomas Kejser раньше была доступна по ссылке. Но на момент написания она перестала работать, а найти работающую мне не удалось.
Заключение
Подход отлично подошёл нам для организации хранения логов производительности сервера БД, хотя и представлял больше академического интереса на старте.
Среди наших требований были:
довольно ограниченная глубина хранения;
предсказуемый размер данных;
ротация, которая не будет мешать вставке новых данных;
минимум усилий для поддержки.
Хочу отметить, что статья не преследует цели сравнить возможности MS SQL Server и каких либо других СУБД ни с точки зрения работы с секциями, ни с точки зрения способов организации хранения данных.
