(Первоначально опубликовано на DBA.StackExchange.com, но закрыто; надеюсь, здесь это более актуально.)
Александр и ужасные, кошмарные, нехорошие, очень плохие... резервные копии.
Установка:
У меня есть локальныйSQL Server 2016 Стандартная версияэкземпляр, работающий навиртуальная машинаот VMWare.
@@Версия:
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) 19 марта 2021 г. 19:41:38 Авторские права (c) Microsoft Corporation Standard Edition (64-разрядная версия) на Windows Server 2016 Datacenter 10.0 (сборка 14393: ) (гипервизор)
Сам сервер в настоящее время выделен8 виртуальных процессоров, имеет32 ГБ памяти, и вседиски NVMeкоторые объезжают1 ГБ/сек ввода-вывода. Сами базы данных находятся на диске G:, а резервные копии хранятся отдельно на диске P:. Общий размер всех баз данных составляет около 500 ГБ (до сжатия в сами файлы резервных копий).
План обслуживания запускается один раз в ночь (около 22:30) для полного резервного копирования каждой базы данных на сервере. На сервере не запущено ничего необычного, и в это время не запущено ничего особенного. План электропитания сервера установлен на «Сбалансированный» (а «Отключать жесткий диск через» установлен на 0 минут, то есть никогда не выключать).
Что случилось:
За последний год или около того общее время выполнения работы по плану технического обслуживания составило около 15минутобщее количество для завершения. С прошлой недели это резко возросло и заняло примерно в 40 раз больше времени, около 15часызавершить.
Единственное, что мне известно об изменении в тот же день, когда замедлился план обслуживания, — это следующие обновления Windows, которые были установлены на компьютере до запуска плана обслуживания:
У нас также есть другой экземпляр SQL Server с аналогичными настройками на другой виртуальной машине, который подвергся тем же обновлениям Windows, а затем впоследствии также столкнулся с более медленным резервным копированием. Думая, что обновления Windows были непосредственной причиной, мы полностью откатили их, и план обслуживания резервных копий все равно работает крайне медленно. Как ни странно, восстановление резервных копий для данной базы данных происходит очень быстро и использует почти все 1 ГБ/с ввода-вывода на NVMes.
Что я пробовал:
При использовании sp_whoisactive Адама Механика я определил, что типы последнего ожидания процессов резервного копирования всегда указывают на проблему производительности диска. Я всегда вижу BACKUPBUFFERи BACKUPIOтипы ожидания, в дополнение к ASYNC_IO_COMPLETION:
Если посмотреть на монитор ресурсов на самом сервере во время резервного копирования, раздел «Дисковый ввод-вывод» показывает, что общий объем используемых операций ввода-вывода составляет всего около 14 МБ/с (максимальное значение, которое я когда-либо видел с момента возникновения этой проблемы, составляет 30 МБ/с):
Наткнувшись на эту полезную информациюСтатья Брента Озара об использовании DiskSpd, я попробовал запустить его сам при похожих параметрах (только уменьшив количество потоков до 8, так как у меня 8 виртуальных процессоров на сервере и установив запись на 50%). Это точная команда diskspd.exe -b2M -d60 -o32 -h -L -t8 -W -w50 "C:\Users\...\Desktop\Microsoft DiskSpd\Test\LargeFile.txt". Я использовал текстовый файл, который я сгенерировал вручную, размером чуть меньше 1 ГБ. Я считаю, что измеренный ввод-вывод выглядит нормально, но задержки диска показывали какие-то смехотворные цифры:
Результаты DiskSpd кажутся буквально невероятными. После дальнейшего чтения я наткнулся на запрос Пола Рэндалла, который возвращает метрики задержки диска по базе данных. Вот результаты:
Худшая задержка записи составила 63 миллисекунды, а худшая задержка чтения — 6 миллисекунд, так что это, похоже, большое отклонение от DiskSpd, и не кажется настолько ужасным, чтобы быть основной причиной моей проблемы. Перепроверив все еще раз, я запустил несколько счетчиков PerfMon на самом сервере, поэта статья Microsoft, и вот результаты:
Ничего необычного, максимальное значение всех измеренных мной счетчиков составило 0,007 (что, как я полагаю, составляет миллисекунды?). Наконец, я попросил свою команду по инфраструктуре проверить метрики задержки диска, которые VMWare регистрировала во время задания по резервному копированию, и вот результаты:
Похоже, в худшем случае наблюдался всплеск задержки около 200 миллисекунд около полуночи, а максимальная скорость ввода-вывода составила 600 КБ/с (что я не совсем понимаю, поскольку монитор ресурсов показывает, что резервные копии используют по меньшей мере около 14 МБ/с ввода-вывода).
Что еще я пробовал:
Я только что попытался восстановить одну из самых больших баз данных (около 250 ГБ), и восстановление заняло всего около 8 минут. Затем я попытался запустить DBCC CHECKDBее, и это заняло в общей сложности 16 минут (не уверен, нормально ли это), но Resource Monitor показал похожие проблемы ввода-вывода (максимальный ввод-вывод, который он когда-либо использовал, был 100 МБ/с), при этом больше ничего не работало:
Вот результаты sp_whoisactive, когда я запустил ее в первый раз, DBCC CHECKDBа затем, когда она была выполнена на 5%, обратите внимание, что расчетное оставшееся время увеличилось примерно на 5 минут, даже после того, как она была выполнена на 5%.
Начинать:
5% Готово:
Полагаю, это нормально, поскольку это всего лишь оценка, и 16 минут не кажутся слишком плохими для базы данных объемом 250 ГБ (хотя я не уверен, нормально ли это), но опять же, ввод-вывод был загружен всего на 10% возможностей диска, при этом на сервере или экземпляре SQL больше ничего не работало.
Вот результатыиз DBCC CHECKDB, ошибок не обнаружено.
У меня также возникли странные проблемы с медлительностью SHRINKкоманды. Я только что попытался обратиться к SHRINKбазе данных, в которой оставалось 5% свободного места (около 14 ГБ). Потребовалось всего около 1 минуты, чтобы завершить 90% SHRINK:
Прошло около 5 минут, а процесс по-прежнему завис на том же проценте завершения, а мои резервные копии журнала транзакций (которые обычно завершаются за 1-2 секунды) находятся в состоянии конкуренции уже около 30 секунд:
15 минут спустя и SHRINKтолько что завершился, в то время как резервные копии журнала транзакций все еще находятся в состоянии конкуренции около 6 минут и завершены только на 50%. Я думаю, они немедленно завершились сразу после этого, поскольку завершились SHRINK. Все это время монитор ресурсов показывал, что ввод-вывод все еще отстой:
Затем я получил сообщение об ошибке SHRINKпри завершении команды:
Я повторил попытку SHRINKеще раз, и результат оказался точно таким же, как и выше.
Затем я попытался вручную создать скрипт резервного копирования T-SQL в файл на диске P:, и это выполнялось так же медленно, как и задание резервного копирования плана обслуживания:
В итоге я отменил подписку примерно через 3 минуты, и она тут же откатилась назад.
Краткое содержание:
По совпадению, задание по плану обслуживания резервных копий становилось примерно в 40 раз медленнее (с 15 минут до 15 часов) каждую ночь, сразу после установки обновлений Windows. Откат этих обновлений Windows не исправил проблему. SQL Server Wait Types, Resource Monitor и Microsoft DiskSpd указывают на проблему с диском (в частности, с вводом-выводом), но все остальные измерения из запроса Пола Рэндалла, PerfMon и VMWare Logs не сообщают о каких-либо проблемах с дисками. Восстановление резервных копий для определенной базы данных происходит быстро и использует почти полный ввод-вывод 1 ГБ/с. Я чешу голову...
решение1
В этом случае у нас действительно была проблема с диском, и это не была внутренняя проблема SQL Server, для этой конкретной VM. На самом деле это был случай ошибки, с которым мы столкнулись с Veeam и VMWare.
Подводя итог моему пониманию того, что произошло, очевидно, наши резервные копии Veeam не были признаны VMWare как завершенные. Поэтому каждый день, когда наступало время резервного копирования сервера, VMWare давала Veeam указание повторно сделать резервную копию предыдущего дня, что превратилось в эту кумулятивную растущую проблему в течение двух недель. (Я уверен, что я исковеркал это объяснение, но это, в общем-то, все, что я знаю.)
Veeam / VMWare пришлось удалить каждый файл снимка, который каждый день был больше предыдущего, поэтому их поддержке уровня 3 потребовалось около 26 часов, чтобы закончить. После этого виртуальная машина снова заработала нормально. По-видимому, это не редкая проблема, согласно их технической поддержке.
Извините, это была очень специфическая проблема, и, вероятно, она не поможет многим другим, но я надеюсь, что это так.