Сериализация операций удаления из кластерных columnstore-индексов

Автор: Aaron Bertrand , Serializing Deletes From Clustered Columnstore Indexes

На Stack Overflow у нас есть несколько таблиц с кластерными columnstore-индексами, которые отлично работают для большей части нашей нагрузки. Но мы недавно столкнулись с ситуацией, когда «идеальные штормы» — несколько процессов, пытающихся одновременно удалить данные из одного columnstore-индекса — перегружали процессор, поскольку они все запускались с высокой степенью параллелизма и боролись за завершение своей операции.

Вот как это выглядело в SolarWinds® SQL Sentry®:

А вот интересные типы ожиданий, связанные с этими запросами:

Конкурирующие запросы имели следующую форму:

DELETE dbo.LargeColumnstoreTable WHERE col1 = @p1 AND col2 = @p2;

План выполнения выглядел так:

А предупреждение на операции сканирования сообщало о довольно значительных дополнительных операциях ввода-вывода:

Таблица содержит 1,9 миллиарда строк, но занимает всего 32 ГБ (спасибо, колончатому хранению!). Тем не менее, каждое удаление одной строки занимало 10–15 секунд, и большая часть этого времени тратилась на ожидание типа SOS_SCHEDULER_YIELD.

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

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

Как администраторы баз данных, мы легко можем контролировать MAXDOP, если это не переопределено на уровне запроса (это тема для другого дня). Мы не обязательно можем контролировать приложение в такой степени, особенно если оно распределённое или не наше. Как мы можем сериализовать операции записи в этом случае без радикального изменения логики приложения?

Моделирование ситуации

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

Давайте представим, что это таблица SuggestedEdits (на самом деле, это не так). Но это простой пример для использования, потому что мы можем взять схему из Stack Exchange Data Explorer. Используя её как основу, мы можем создать эквивалентную таблицу (с несколькими небольшими изменениями, чтобы её было проще заполнить) и поместить на неё кластерный columnstore-индекс:

CREATE TABLE dbo.FakeSuggestedEdits
(
  Id            int IDENTITY(1,1),
  PostId        int NOT NULL DEFAULT CONVERT(int, ABS(CHECKSUM(NEWID()))) % 200,
  CreationDate  datetime2 NOT NULL DEFAULT sysdatetime(),
  ApprovalDate  datetime2 NOT NULL DEFAULT sysdatetime(),
  RejectionDate datetime2 NULL,
  OwnerUserId   int NOT NULL DEFAULT 7,
  Comment       nvarchar (800)   NOT NULL DEFAULT NEWID(),
  Text          nvarchar (max)   NOT NULL DEFAULT NEWID(),
  Title         nvarchar (250)   NOT NULL DEFAULT NEWID(),
  Tags          nvarchar (250)   NOT NULL DEFAULT NEWID(),
  RevisionGUID  uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
  INDEX CCI_FSE CLUSTERED COLUMNSTORE
);

Чтобы заполнить её 100 миллионами строк, мы можем пять раз выполнить декартово произведение (cross join) sys.all_objects и sys.all_columns (в моей системе это даст по 2,68 миллиона строк каждый раз, но у вас может быть по-другому):

-- 2680350 * 5 ~ 3 минуты

INSERT dbo.FakeSuggestedEdits(CreationDate)
  SELECT TOP (10) /*(2000000) */ modify_date
  FROM sys.all_objects AS o
  CROSS JOIN sys.columns AS c;
GO 5

Затем мы можем проверить занимаемое пространство:

EXEC sys.sp_spaceused @objname = N'dbo.FakeSuggestedEdits';

Это всего 1,3 ГБ, но этого должно быть достаточно:

Имитация нашего удаления из кластерного columnstore-индекса

Вот простой запрос, примерно соответствующий тому, что наше приложение делало с таблицей:

DECLARE @p1 int = ABS(CHECKSUM(NEWID())) % 10000000, @p2 int = 7;
DELETE dbo.FakeSuggestedEdits WHERE Id = @p1 AND OwnerUserId = @p2;

План выполнения не совсем совпадает:

Чтобы заставить его выполняться параллельно и создать аналогичную конкуренцию на моём скромном ноутбуке, мне пришлось немного подтолкнуть оптимизатор с помощью такой подсказки:

OPTION (QUERYTRACEON 8649);

Теперь он выглядит правильно:

Воспроизведение проблемы

Затем мы можем создать всплеск конкурентной активности по удалению, используя SqlStressCmd, чтобы удалить 1000 случайных строк, используя 16 и 32 потока:

sqlstresscmd -s docs/ColumnStore.json -t 16
sqlstresscmd -s docs/ColumnStore.json -t 32

Мы можем наблюдать нагрузку, которую это создаёт на процессор:

Нагрузка на процессор сохраняется на протяжении выполнения пакетов, которые длились около 64 и 130 секунд соответственно:

Примечание: Вывод SQLQueryStress иногда немного неточен в отношении итераций, но я подтвердил, что работа, которую вы ему поручаете, выполняется точно.

Возможное решение: очередь удаления

Изначально я подумал о создании таблицы-очереди в базе данных, которую мы могли бы использовать для выгрузки активности по удалению:

CREATE TABLE dbo.SuggestedEditDeleteQueue
(
  QueueID       int IDENTITY(1,1) PRIMARY KEY,
  EnqueuedDate  datetime2 NOT NULL DEFAULT sysdatetime(),
  ProcessedDate datetime2 NULL,
  Id            int NOT NULL,
  OwnerUserId   int NOT NULL
);

Всё, что нам нужно, — это триггер INSTEAD OF, чтобы перехватывать эти неконтролируемые операции удаления, поступающие из приложения, и помещать их в очередь для фоновой обработки. К сожалению, нельзя создать триггер на таблице с кластерным columnstore-индексом:

Msg 35358, Level 16, State 1

CREATE TRIGGER on table 'dbo.FakeSuggestedEdits' failed because you cannot create a trigger on a table with a clustered columnstore index. Consider enforcing the logic of the trigger in some other way, or if you must use a trigger, use a heap or B-tree index instead.

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

CREATE PROCEDURE dbo.DeleteSuggestedEdit
  @Id          int,
  @OwnerUserId int
AS
BEGIN
  SET NOCOUNT ON;

  DELETE dbo.FakeSuggestedEdits 
    WHERE Id = @Id AND OwnerUserId = @OwnerUserId;
END

Это не постоянное состояние; это нужно лишь для сохранения прежнего поведения, изменив только одну вещь в приложении. Как только приложение изменено и успешно вызывает эту хранимую процедуру вместо отправки нерегламентированных запросов на удаление, процедуру можно изменить:

CREATE PROCEDURE dbo.DeleteSuggestedEdit
  @Id          int,
  @OwnerUserId int
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.SuggestedEditDeleteQueue(Id, OwnerUserId)
    SELECT @Id, @OwnerUserId;
END

Тестирование влияния очереди

Теперь, если мы изменим SqlQueryStress, чтобы он вызывал хранимую процедуру:

DECLARE @p1 int = ABS(CHECKSUM(NEWID())) % 10000000, @p2 int = 7;
EXEC dbo.DeleteSuggestedEdit @Id = @p1, @OwnerUserId = @p2;

И отправим аналогичные пакеты (помещая 16K или 32K строк в очередь):

-- Имитация множественных вызовов
-- (В реальности это делается инструментом нагрузки)

Влияние на процессор немного выше:

Но рабочие нагрузки завершаются гораздо быстрее — за 16 и 23 секунды соответственно:

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

Но нам всё ещё нужно выполнить удаление

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

CREATE PROCEDURE dbo.ProcessSuggestedEditQueue
  @JobSize        int = 10000,
  @BatchSize      int = 100,
  @DelayInSeconds int = 2      -- должно быть от 1 до 59
AS
BEGIN
  SET NOCOUNT ON;
  
  DECLARE @d TABLE(Id int, OwnerUserId int);
  DECLARE @rc int = 1,
          @jc int = 0, 
          @wf nvarchar(100) = N'WAITFOR DELAY ' + CHAR(39) 
              + '00:00:' + RIGHT('0' + CONVERT(varchar(2), 
                @DelayInSeconds), 2) + CHAR(39);
                
  WHILE @rc > 0 AND @jc < @JobSize
  BEGIN 
    DELETE @d; 

    UPDATE TOP (@BatchSize) q SET ProcessedDate = sysdatetime() 
      OUTPUT inserted.Id, inserted.OwnerUserId INTO @d 
      FROM dbo.SuggestedEditDeleteQueue AS q WITH (UPDLOCK, READPAST) 
       WHERE ProcessedDate IS NULL; 

    SET @rc = @@ROWCOUNT; 
    IF @rc = 0 BREAK; 

    DELETE fse 
      FROM dbo.FakeSuggestedEdits AS fse 
      INNER JOIN @d AS d 
        ON fse.Id = d.Id 
       AND fse.OwnerUserId = d.OwnerUserId; 

    SET @jc += @rc; 
    IF @jc > @JobSize BREAK;
    
    EXEC sys.sp_executesql @wf;
  END
  RAISERROR('Deleted %d rows.', 0, 1, @jc) WITH NOWAIT;
END

Теперь удаление строк будет занимать больше времени — в среднем для 10 000 строк это 223 секунды, ~100 из которых — намеренная задержка. Но ни один пользователь не ждёт, так что какая разница? Профиль использования процессора почти нулевой, и приложение может продолжать добавлять элементы в очередь с высокой степенью параллелизма, с почти нулевым конфликтом с фоновым заданием. Во время обработки 10 000 строк я добавил ещё 16K строк в очередь, и это использовало тот же процессор, что и раньше, — заняв всего на секунду дольше, чем когда задание не выполнялось:

И план теперь выглядит так, с гораздо лучшими оценёнными / фактическими строками:

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

Другие варианты

Если у вас нет возможности изменить запросы на удаление, поступающие из приложения, или если вы не можете отложить удаления до фонового процесса, вы можете рассмотреть другие варианты для снижения влияния удалений:

• Некластерный индекс на столбцах-предикатах для поддержки точечных поисков (мы можем сделать это изолированно, без изменения приложения).
• Использование только «мягкого» удаления (всё ещё требует изменений в приложении).

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