Как ускорить высокопараллельные вставки строк в SQL Server за считанные часы: опыт Mindbox

Привет, Хабр!

Меня зовут Тимур Маннапов, и я самый обычный senior-разработчик в Mindbox.

На примере нашего продукта я расскажу, почему при загрузке CPU наполовину или меньше скорость параллельных вставок на SQL-сервере упирается в «невидимый» предел, а потом и вовсе замедляется. На нашем железе предел был в районе ~120 тысяч строк в минуту в одну таблицу. Поделюсь, как его преодолеть, не потратив годы на разработку и миллионы на новый сервер.

Как архитектура базы данных влияет на скорость параллельных вставок

Наша команда создает и поддерживает CDP — Сustomer Data Platform. Это облачная платформа для бизнеса, где маркетологи объединяют данные о клиентах из разных источников в цифровые профили. Одно из хранилищ на бекенде — это реляционная база данных. 

Когда объем параллельных вставок достиг 2 тысяч строк в секунду в одну таблицу, запросы начали вставать в очередь, а задержки по вызовам асинхронного API могли составлять до нескольких часов. Такое случалось у крупных клиентов в часы пик — например, по вечерам. Хотя CPU/диск были загружены меньше чем наполовину, а объема RAM было достаточно для корректной работы, предел по вставкам все равно сохранялся на уровне 100–120 тысяч в минуту. 

Мы стали разбираться, где и когда возникают задержки вставок, и увидели сразу много PAGELATCH блокировок. В норме их либо нет совсем, либо совсем немного с ожиданием не более 10 мс. 

Сами по себе блокировки вреда не приносят: они защищают данные на странице от одновременных изменений в разных сессиях/подключениях. Но когда их количество растет, запросы на изменения «встают в очередь» и выполняются с заметной задержкой. А при увеличении нагрузки эта очередь растет, а скорость вставок новых строк снижается. 

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

Рост PAGELATCH блокировок может быть спровоцирован любым индексом, в который вставляются монотонно возрастающие значения: например, индекс по полю CreatedAt. А еще с замедлением вставок из-за блокировок может столкнуться любой бизнес и любая база данных. На старте разработки сложно предугадать, с каким объемом данных придется работать в будущем. Пока платформа обрабатывала 500–1500 вставок в секунду, PAGELATCH блокировок почти не было и наше архитектурное решение еще не стреляло нам в ногу. Но за 10 лет нашими клиентами стали 1000+ компаний, цифровых следов в интернете стало в несколько раз больше, и вот мы уже хотим обрабатывать 2000+ вставок в секунду. Но не можем. 

Как обнаружить проблему 

Для того, чтобы PAGELATCH блокировки начали влиять на скорость вставок, у вас должна быть высоконагруженная многопоточная система на SQL-сервере с автоинкрементом в первичном ключе таблицы. В нашем случае нагрузку обеспечивают интеграции с другими сервисами — например, кассовыми аппаратами, личными кабинетами на сайтах, CRM-системами.

Чтобы выявить проблему, выполните следующий запрос к sql server: 

SELECT r.session_id, r.wait_type, r.wait_time, r.wait_resource  FROM sys.dm_exec_requests as r WHERE r.session_id > 50 and r.wait_type LIKE 'PAGELATCH%' ORDER BY r.wait_time DESC

Если вы увидите десятки или сотни строк с длительностью более 10 миллисекунд и одинаковым wait_resource, то столкнетесь (или уже столкнулись) с ограничениями по скорости вставок.

Проблемный индекс, который скрывается под wait_resource, можно определить по запросу: 

DBCC PAGE(5,1,1501261182,0)

Если вы получите ошибку «DBCC PAGE execution completed. If DBCC printed error messages, contact your system administrator», то нужно установить флаг 3604, выполнив еще один запрос к базе данных:

DBCC TRACEON (3604)

Далее имя индекса можно найти по системной таблице sys.indexes по следующему запросу:

SELECT [name] FROM sys.indexes WHERE [object_id] = OBJECT_ID('dbo.TestIdentity') AND index_id = 1

Как ускорить параллельные вставки, (не) перестраивая архитектуру базы данных

Мы уже выяснили, что бутылочное горлышко — это последовательная вставка 2000+ строк на последнюю страницу в таблице. Если страниц для вставок станет больше, блокировок будет меньше, а скорость вставок вырастет.

Однако нельзя так просто взять и переписать живую базу данных с 1000 клиентов, чья прибыль напрямую зависит от корректной работы платформы. Поэтому мы рассмотрели несколько решений. Покажу, как они работают, на примере вставок 2 млн строк в пустую тестовую таблицу на моем личном ноутбуке с 8-ядерным CPU и SSD.

Структура тестовой таблицы:

• id — идентификатор;

• SomeDate — nvarchar(100), которая заполнялась строкой случайной длины от 20 до 100 символов.

Замеры для базового решения с identity на поле id:

Скрипт создания таблицы:

CREATE TABLE [dbo].[TestAutoIncrement](  [Id] [bigint] IDENTITY(1,1) NOT NULL,  [SomeData] [nvarchar](100) NOT NULL,  CONSTRAINT [PK_TestAutoIncrement] PRIMARY KEY CLUSTERED ([Id] ASC)  )

Разделить одну таблицу на несколько 

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

Тем не менее, это рабочее решение, если вы проектируете базу данных с нуля или можете «расселить» данные по разным таблицам. 

Использовать OPTIMIZE_FOR_SEQUENTIAL_KEY

Это готовое решение от Sql server. По описанию SQL Server Blog, оно может дать ускорение до 30% и займ ет при этом всего пару минут: нужно только обновить метаданные таблицы. 

В тестах мы наблюдали улучшение на 5–20%, но в продакшене никакого эффекта не заметили. Однако это решение все равно не задействует все свободные ресурсы, поэтому нам оно не подошло. Но возможно, у вас оно сработает лучше. 

Скрипт создания таблицы

 CREATE TABLE [dbo].[TestAutoIncrement](   [Id] [bigint] IDENTITY(1,1) NOT NULL,   [SomeData] [nvarchar](100) NOT NULL,   CONSTRAINT [PK_TestAutoIncrement] PRIMARY KEY CLUSTERED ([Id] ASC)   WITH(OPTIMIZE_FOR_SEQUENTIAL_KEY = ON)   )

Ускорение вставок с OPTIMIZE_FOR_SEQUENTIAL_KEY по сравнению с данными в тестовой таблице

Использовать GUID в качестве id

Мы можем заменить identity на UniqueIdentifier. Само значение может генерировать как сервер, так и клиент. Новые строки будут добавляться в случайные страницы — так мы задействуем 100% ресурсов CPU. 

Однако каждая строка и каждая запись в индексе потяжелеет на 12 байт, что может заметно отразиться на стоимости хранения данных. Также вместе с типом данных нужно будет поменять код везде, где мы обращаемся к id по identity, и мигрировать данные в связанных таблицах. Мы оценили эту задачу в 2–3 месяца разработки для команды из 5 человек.

Скрипт создания таблицы 

CREATE TABLE [dbo].[TestAutoIncrement](   [Id] [UniqueIdentifier] NOT NULL,   [SomeData] [nvarchar](100) NOT NULL,   CONSTRAINT [PK_TestAutoIncrement] PRIMARY KEY CLUSTERED ([Id] ASC)   )

Ускорение вставок с GUID в качестве id по сравнению с данными в тестовой таблице

Секционировать таблицу

Данные можно разбить на секции, и тогда вставки будут выполняться в разные ее части с меньшим конкурентным доступом к последней странице. Подробно это решение описано в статье «Как справиться с PAGELATCH при высокопараллельных INSERT-нагрузках».

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

Генерировать id из последовательности

Мы можем отказаться от генерации id из identity при добавлении каждой новой строки. Вместо этого мы заранее отберем и закешируем 1000 значений из последовательности в каждом сервере/поде. Когда потребуется новый id, выберем его в случайном порядке из закешированных ранее. 

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

Подробнее о том, как задать последовательность, читайте в статье из справочного центра SQL Server.

Скрипт создания таблицы и последовательности

CREATE SEQUENCE [dbo].[TestSequence]    AS [bigint]   START WITH -9223372036854775808   INCREMENT BY 1   MINVALUE -9223372036854775808   MAXVALUE 9223372036854775807   CACHE    CREATE TABLE [dbo].[TestAutoIncrement](   [Id] [bigint] NOT NULL,   [SomeData] [nvarchar](100) NOT NULL,   CONSTRAINT [PK_TestAutoIncrement] PRIMARY KEY CLUSTERED ([Id] ASC)   )

Скрипт получения значений из последовательности

DECLARE @range_first_value_output sql_variant;  EXEC sys.sp_sequence_get_range   @sequence_name = N'dbo.TestSequence',   @range_size = 2000,   @range_first_value = @range_first_value_output OUTPUT;   SELECT CONVERT(bigint, @range_first_value_output) AS FirstNumber; 

Ускорение вставок с генерацией id из последовательности по сравнению с данными в тестовой таблице

В результате мы выбрали именно этот способ: так мы задействовали ресурсы CPU на максимум, ускорили вставку до 250 тысяч строк в минуту — и знаем, что это не предел. Также мы снизили задержки при выполнении асинхронных операций и не увеличили затраты на хранение.

На всю разработку, включая тестирование описанных выше решений, у нас ушло 6 недель для команды из 3 человек. Код для всех решений уже лежит в нашем репозитории на GitHub: будем рады, если он окажется вам полезен 🙂