Как добавить индекс на нагруженной системе 24/7 без простоя?
Друзья, в конце января у нас стартует новый курс под названием «MS SQL Server разработчик». В преддверии его запуска мы попросили преподавателя курса, Кристину Кучерову, подготовить авторскую статью. Эта статья будет вам полезна, если у вас есть очень популярная таблица на проде с доступом 24/7 и вдруг неожиданно вы поняли, что срочно нужно добавить индекс и ничего не сломать в процессе.
Итак, что же делать? Традиционный способ CREATE INDEX WITH (ONLINE = ON) вам не подходит, потому что, например, вызывает падение системы и сердечный приступ вашего ДБА, все топы пристально следят за response time вашей системы и в случае увеличения оного приходят к вам и вашему ДБА на разговор по поводу завышенных цифр вашей компенсации за труд.
Скрипты и описанные приёмы были использованы на системе с нагрузкой 400К requests per minute, версии SQL Server 2012 и 2016 (Enterprise).
Есть два очень разных подхода создания индекса, которые используются в зависимости от размера таблицы.
Кейс № 1. Маленькая, но очень популярная таблица
Таблица 50 тыс. записей (небольшая), но очень популярная (несколько тысяч обращений в минуту). Вам нужен новый индекс и минимальное время простоя и блокировок на таблице.
В приложении весь доступ к БД только через процедуры.
При ошибке приложение сделает повторную попытку обратится к таблице.
В чём проблема применить этот индекс просто, спросите вы? С предложением WITH ONLINE = ON (да, нам повезло, и этот был Enterprise).
Дело в том, что при таком активном доступе получить блокировку (даже ту минимальную, которая нужна с опцией with Online = ON) занимает какое-то время. В процессе ожидания новые запросы ставятся в очередь, очередь копится, ЦПУ растет, ДБА потеет и нервно косится в сторону разработчиков, при этом на графиках мониторинга приложения плавно, но неотвратимо начинает повышаться ваш response time. Ваш Vice President of Engeneering нежно интересуется, а не случится ли из-за этого роста времени ответа какого-нибудь простоя системы, что в конце года доступность приложения будет оценена не 5 девяток (99,999), а ниже? А то у компании контракты, обязательства и большие штрафы в случае снижения доступности, и, конечно, не будем забывать о репутационных потерях.
Что мы сделали, чтобы избежать этой прискорбной ситуации?
Индекс системе всё-таки нужен.
Забрали права у всех, кроме текущей сессии на эту таблицу.
Применили индекс.
Да, у решения есть минус: все, кто обратился в эти секунды к таблице получит Access Denied. Если ваше приложение нормально обработает такую ситуацию и повторит запрос к базе, то стоит присмотреться к этому варианту. В случае нашего проекта этот способ отлично срабатывал. Опять же можно убрать спокойно ONLINE = ON, так как мы знаем, что во время создания индекса доступ к таблице будет только у этой сессии.
Код для применения индекса:
REVOKE EXECUTE ON [dbo].[spUserLogin] TO [User1]
REVOKE EXECUTE ON [dbo].[spUserLogin] TO [User2]
REVOKE EXECUTE ON [dbo].[spUserCreate] TO [User1]
REVOKE EXECUTE ON [dbo].[spUserCreate] TO [User2]
CREATE NONCLUSTERED INDEX IX_Users_Email_Status
ON [dbo].[Users] ([Email],[Status]);
GRANT EXECUTE ON [dbo].[spUserCreate] TO [User1]
GRANT EXECUTE ON [dbo].[spUserCreate] TO [User2]
GRANT EXECUTE ON [dbo].[spUserLogin] TO [User1]
GRANT EXECUTE ON [dbo].[spUserLogin] TO [User2]
График изменения времени ответа и процента ошибок во время тестирования под нагрузкой.
Способ можно применять, если у вас, как в описанном случае, небольшая таблица, и вы знаете, что без нагрузки индекс создастся за секунды (или за приемлемое для вас время). При этом, как вы видите из графика выше, response time приложения расти не будет, хотя видно, что error rate в секунды без доступа к таблице был выше.
Кейс № 2. Большая таблица
Если у вас большая таблица и вам нужно изменить индексы на ней, то часто самый безболезненный способ для прода — это создать рядом таблицу с правильным индексом и постепенно перенести данные в новую таблицу.
При этом есть 2 пути:
- Если у вас есть специальная процедура для изменения таблицы, вы просто меняете код процедуры, чтобы новые данные вставлялись только в новую таблицу, удаление шло из обеих, update тоже применялся к обеим, а выборка делалась из двух таблиц с UNION ALL.
- Если у вас много разных частей кода, где вы можете менять данные в таблице, то есть два популярных приема: вью с триггерами или переписывание всех частей кода на вставку данных в новую таблицу, delete из обеих и update обеих таблиц. Вью с триггерами — вариант, когда вы создаёте вью с двумя таблицами и делаете ренейм, вашу текущую таблицу переименовываете в TableOld, а вью в Table. Тогда у вас автоматом все обращения к таблице попадают на вью, тут с ренеймом может быть тоже проблема, так как нужен SchemaLock, но ренейм проходит очень быстро.
Чуть подробнее вариант про переписывание обращений на новую таблицу:
- У вас таблица Orders, создаёте новую таблицу OrdersNew с той же схемой, но уже с нужным индексом. При этом, если вы используете Indentity, то нужно установить, чтобы первое значение identity в новой таблице было равно максимальное значение в старой таблице + шаг изменения либо зазор, который вы можете себе позволить отступить от максимального значения в Orders.
- Создаём представление OrdersView, внутри которого выборка из Orders UNION ALL OrdersNew
- Изменяете все процедуры\вызовы на выборку данных из представления, вставку в OrdersNew, удаление и изменение обеих таблиц.
- Мигрируете данные из старой таблицы в новую, например, так:
DECLARE @rowcount INT, @batchsize INT = 4999; SET IDENTITY_INSERT dbo.OrdersNew ON; SET @rowcount = @batchsize; WHILE @rowcount = @batchsize BEGIN BEGIN TRY DELETE TOP (@batchsize) FROM dbo.Orders OUTPUT deleted.Id ,deleted.Column1 ,deleted.Column2 ,deleted.Column3 INTO dbo.OrdersNew (Id ,Column1 ,Column2 ,Column3); SET @rowcount = @@ROWCOUNT; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; THROW; END CATCH; END; SET IDENTITY_INSERT dbo.OrdersNew OFF; - Возвращаете все процедуры на версию до миграции — с одной таблицей. Это можно делать через alter или через удаление и создание процедур (тогда не забудьте про права), и можно переименовать новую таблицу в Orders, удалив пустую таблицу и представление.
На шаге 2 можно было, если позволяет загрузка, сделать переименование основной таблицы Orders → OrdersOld, а OrdersView → Orders и само представление на OrdersOld UNION ALL OrdersNew, тогда не нужно менять все места, где есть выборка из таблицы.
При переносе блоками из одной таблицы в другую данные будут фрагментированы.
Если изменяемая таблица активно используется для чтения, но данные в ней редко меняются, вы можете опять же воспользоваться триггерами — записать копию всех изменений в 3-ю таблицу — перенести данные из таблицы через bcp out и bcp in (или bulk insert) в новую таблицу, создать на ней индексы после переноса данных и затем применить изменения из таблицы с логом изменений — и переключить одну таблицу на другу — текущую, переименовав в TableOld, а новую из TableNew в Table.
Вероятность ошибок в этой ситуации несколько выше, поэтому протестируйте применение изменений и разные кейсы переключений в этом случае.
Описанные варианты не являются единственными. Они были использованы мной на высоконагруженной базе SQL Server и не вызвали проблем при применении, чем порадовали нашу команду ДБА. Такие подпрыгивания обычно не нужны для баз с более спокойным режимом нагрузки, когда можно спокойно применить изменения в часы наименьшей активности. Пользователи проекта, в котором использовались описанные подходы, находятся в США и Европе и активно используют приложение в рабочие дни и в выходные, а таблицы, на которых применялись изменения, используются постоянно в работе. Более «спокойные» объекты обычно изменялись автоматическими скриптами, сгенерированными через Redgate Toolkit после ревью скриптов разработчиком и одним из ДБА.
Всем добра! Поделитесь в комментариях, использовали ли вы что-то из этих способов или опишите свой способ! Также мы приглашаем вас на открытый урок и день открытых дверей нашего нового курса «MS SQL Server разработчик»
