20.11.26

Ускоряем ночной ETL с помощью TABLOCKX

Автор: Brent Ozar, Make Your Nightly ETL Faster with TABLOCK

Предположим, каждую ночь вам нужно создавать совершенно новую копию таблицы.

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

Блокировки, ожидания блокировок и уровни изоляции

Коротко суть такова:

  • Если вы можете управлять запросом — используйте кучу, но критически важно применить подсказку TABLOCKX или выполнить select-into.
  • Если управлять запросом вы не можете, а кто-то другой выполняет обычную вставку без подсказок — используйте кластерный индекс.

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

Ручек, которые здесь можно покрутить, немало.

Причём куда больше, чем можно ожидать. Часть из них Microsoft описала в руководстве SQL Server 2008 Data Loading Performance Guide (копия в Wayback Machine — на случай, если оно пропадёт, а ещё лучше сразу в переводе на этом же сайте), но с тех пор его ни разу не обновляли, а за прошедшие годы многое изменилось. Пол Уайт написал несколько статей (часть 1, часть 2, часть 3), проясняющих некоторые нюансы минимального журналирования.

Вот лишь некоторые из этих ручек:

  • Какая модель восстановления у целевой базы данных?
  • Является ли вставка межбазовым запросом? От этого зависит, доступна ли вам отложенная фиксация.
  • Целевая таблица — это кластерный индекс или куча?
  • Есть ли у целевой таблицы какие-нибудь индексы?
  • Загружаем таблицу с нуля или в ней уже есть строки?
  • Загрузка идёт из одного сеанса или из нескольких параллельно?
  • Можем ли мы изменить запрос, наполняющий таблицу, — например, добавить подсказки?

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

  • Есть у таблицы кластерный индекс или нет?
  • Применяется ли при загрузке таблицы подсказка TABLOCKX или нет?

Если решите повторить эти тесты, делайте это на разогретом SQL Server, где таблица Comments уже находится в кэше, а в файлах данных и журнале транзакций достаточно свободного места, — так вы избежите посторонних ожиданий, связанных с выделением памяти или пространства файлов.

Тест 1: вставка в кластерный индекс

В моём случае клиент делал примерно следующее с таблицей на 20 ГБ, поэтому я возьму таблицу Comments из базы данных Stack Overflow.

/* Test 1: Insert into a table with a clustered primary key */ DROP TABLE IF EXISTS dbo.Comments_PK; GO CREATE TABLE [dbo].[Comments_PK]( [Id] [int] PRIMARY KEY CLUSTERED NOT NULL, [CreationDate] [datetime] NOT NULL, [PostId] [int] NOT NULL, [Score] [int] NULL, [Text] [nvarchar](700) NOT NULL, [UserId] [int] NULL ) ON [PRIMARY] GO INSERT INTO dbo.Comments_PK(Id, CreationDate, PostId, Score, Text, UserId) SELECT Id, CreationDate, PostId, Score, Text, UserId FROM dbo.Comments; GO

В моей лаборатории (4-ядерный инстанс AWS с 32 ГБ оперативной памяти, локальный NVMe-накопитель, SQL Server 2025) запрос выполнялся 98 секунд, израсходовав при этом 86 секунд процессорного времени. План выполнения предельно прост:

Тест 1 — кластерный индекс

Тест 2: вставка в кучу

Возможно, вы слышали, что таблица без кластерного индекса — она же куча — лучше подходит для промежуточных сред в хранилищах данных. Казалось бы, объект, где не нужно ничего сортировать, по определению должен быть более быстрым приёмником, чем таблица с упорядоченным кластерным индексом. Проверим:

/* Test 2: Insert into a heap */ DROP TABLE IF EXISTS dbo.Comments_Heap; GO CREATE TABLE [dbo].[Comments_Heap]( [Id] [int] NOT NULL, [CreationDate] [datetime] NOT NULL, [PostId] [int] NOT NULL, [Score] [int] NULL, [Text] [nvarchar](700) NOT NULL, [UserId] [int] NULL ) ON [PRIMARY] GO INSERT INTO dbo.Comments_Heap(Id, CreationDate, PostId, Score, Text, UserId) SELECT Id, CreationDate, PostId, Score, Text, UserId FROM dbo.Comments; GO

Этот тест занимает 150 секунд и 131 секунду процессорного времени. На 50% медленнее. Почему? План запроса выглядит предельно просто:

Тест 2 — вставка в кучу

Главная беда вовсе не в том, что вставка в кучу медленна: 150 секунд на загрузку примерно 20 ГБ данных — это вполне достойные 130 МБ/с, быстрее, чем способна Azure SQL DB Hyperscale при любом уровне масштабирования.

«Проблема» в другом: у SQL Server есть оптимизация. Если источник гарантированно отдаёт данные ровно в том порядке, который нужен целевой таблице, загрузка идёт быстрее. Это работало ещё со времён руководства 2008 года, но, похоже, со временем производительность стала только лучше — тогда как кучи при обычных инструкциях INSERT так и не ускорились.

Пока что первый вывод такой: если вы выполняете обычные INSERT без подсказок, целевой должна быть таблица с кластерным индексом. Однако всё меняется, когда мы начинаем добавлять в запросы подсказки.

Тест 3: вставка в кластерный индекс с TABLOCKX

Подсказка TABLOCKX гарантирует, что кроме нас целевую таблицу никто не трогает:

/* Test 3: Clustered primary key, with tablock */ DROP TABLE IF EXISTS dbo.Comments_PK; GO CREATE TABLE [dbo].[Comments_PK]( [Id] [int] PRIMARY KEY CLUSTERED NOT NULL, [CreationDate] [datetime] NOT NULL, [PostId] [int] NOT NULL, [Score] [int] NULL, [Text] [nvarchar](700) NOT NULL, [UserId] [int] NULL ) ON [PRIMARY] GO INSERT dbo.Comments_PK WITH (TABLOCKX) (Id, CreationDate, PostId, Score, Text, UserId) SELECT Id, CreationDate, PostId, Score, Text, UserId FROM dbo.Comments; GO

Этот тест занимает всего 53 секунды и лишь 47 секунд процессорного времени. Вдвое быстрее, чем без подсказки TABLOCKX, — хотя план запроса выглядит так же:

Кластерный индекс с TABLOCKX

Ну хорошо, звучит логично: у SQL Server есть оптимизации, которые он может применить, когда уверен, что таблицу никто больше не трогает. Но так ли это логично на самом деле? Мне — нет. Ведь эскалация блокировок всё равно случится почти мгновенно, и мы получим монопольную блокировку на таблицу. Так почему бы, если оценка числа строк в исходной таблице превышает 5 000, не взять TABLOCKX автоматически? Зачем заставлять пользователя указывать её вручную? И зачем мне стирать банные полотенца? Чем они пачкаются, если я лишь вытираюсь после чистого душа? Иные загадки так и не разгадать.

Тест 4: вставка в кучу с TABLOCKX

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

/* Test 4: Insert into a heap, tablock hint */ DROP TABLE IF EXISTS dbo.Comments_Heap; GO CREATE TABLE [dbo].[Comments_Heap]( [Id] [int] NOT NULL, [CreationDate] [datetime] NOT NULL, [PostId] [int] NOT NULL, [Score] [int] NULL, [Text] [nvarchar](700) NOT NULL, [UserId] [int] NULL ) ON [PRIMARY] GO INSERT INTO dbo.Comments_Heap WITH (TABLOCKX) (Id, CreationDate, PostId, Score, Text, UserId) SELECT Id, CreationDate, PostId, Score, Text, UserId FROM dbo.Comments; GO

НУ ВЫ ДАЁТЕ: запрос отрабатывает всего за 18 секунд! Но... при этом сжигается 67 секунд процессорного времени — верный признак того, что здесь сработала совершенно иная оптимизация:

Вставка в кучу с TABLOCKX

Это первый план запроса в статье с параллелизмом, причём параллельно и чтение из исходной таблицы, и запись в целевую. Вот оно что! На моей лабораторной машине всего 4 ядра, и чем мощнее сервер, на котором крутится ваш ночной процесс, тем больший выигрыш даст этот приём.

Забавный совет: запрос выполнится ещё быстрее — за считанные СЕКУНДЫ, — если подсказку tablockx поставить и на исходную таблицу, вот так:

INSERT INTO dbo.Comments_Heap WITH (TABLOCKX) (Id, CreationDate, PostId, Score, Text, UserId) SELECT Id, CreationDate, PostId, Score, Text, UserId FROM StackOverflow.dbo.Comments WITH (TABLOCKX);

Потому что тогда запрос впадает во взаимоблокировку — с самим собой, ха-ха. Оценочный план для такого запроса вы получите, но выполняться он не будет — по крайней мере, на SQL Server 2025 CU10. Глупые взаимоблокировки параллелизма. Впрочем, можно взять подсказку TABLOCK вместо TABLOCKX — тогда всё сработает.

Тест 5: SELECT INTO в кучу

Кое-кто из вас ленив и не любит заранее объявлять таблицы — попробуем и такой вариант:

DROP TABLE IF EXISTS dbo.Comments_Heap; GO SELECT Id, CreationDate, PostId, Score, Text, UserId INTO dbo.Comments_Heap FROM dbo.Comments; GO

Неплохо: 21 секунда выполнения и 83 секунды процессорного времени. Не так хорошо, как при явном создании таблицы, но всё же лучше, чем возиться с кластерным индексом.

Тест 5 — select into heap

Я лично не любитель select-into: он часто медленнее, как мы только что видели, а ещё с ним можно нарваться на неточные типы данных в целевой таблице — особенно когда источником служит объединение.

Финальная таблица результатов: кучи с TABLOCKX

Результаты — от худшего к лучшему:

  1. INSERT INTO в кучу: 150 секунд
  2. INSERT INTO в кластерный индекс: 98 секунд
  3. INSERT INTO в кластерный индекс с подсказкой TABLOCKX: 53 секунды
  4. SELECT INTO в кучу: 21 секунда
  5. INSERT INTO в кучу с подсказкой TABLOCKX: 18 секунд

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

  • Если вы можете управлять запросом — используйте кучу, но критически важно применить подсказку TABLOCKX или выполнить select-into.
  • Если управлять запросом вы не можете, а кто-то другой выполняет обычную вставку без подсказок — используйте кластерный индекс.

Если вы выбрали путь кучи, а конечная таблица должна в итоге получить кластерные индексы, тестирование ещё не закончено. Возможно, вам окажется быстрее писать сразу в кластерный индекс с TABLOCKX и на этом остановиться. Но в случае моего клиента всё было иначе: сначала над одной и той же таблицей выполнялось несколько проходов обновлений с соединениями с другими таблицами, чтобы что-то изменить и удалить, поэтому разумнее было:

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

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





Комментариев нет:

Отправить комментарий