gifts2017

Повышенная нагрузка на диски сервера баз данных SQL Server

Опубликовал Павел Баркетов (gallam99) в раздел Администрирование - Оптимизация БД (HighLoad)

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


Повышенная нагрузка на диски сервера баз данных 

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

                Как правило, повышенную нагрузку на диски можно определить различными способами. Основной из них – это получение счетчика «Средней длины очереди к диску»:

Рисунок 1 

Рис.1. Средняя длина очереди к диску для чтения и записи

На рис. 1 можно наблюдать типичную ситуацию с повышенной очередью к диску, «на пальцах» этот параметр можно объяснить, как среднее количество пакетных заданий для физического диска в очереди к выполнению. В моменты повышенной очереди к диску возникают задержки на всех, даже минимальных операциях с диском, что в ряде случаев приводит к общему падению производительности. Следует учитывать возможности каждого диска по параллельной обработке, так как от этого зависит критичность проблемы. В случае, если средняя очередь к диску больше, чем возможности диска, то проблема стоит очень остро и повлияет в общем на скорость всех операций и информационной системе. Если же средняя очередь к диску больше 1, но меньше возможностей диска, то диск справляется с нагрузкой за счет своих ресурсов, но это не значит, что проблемы не существует вообще, – повышенная нагрузка на диск может привести к уменьшению срока жизни механизмов диска. 

Рассмотрим несколько основных причин повышенной нагрузки на диски для систем на базе MS SQL Server.

 

 

  1. Нагрузка на диски обусловлена быстрым вытеснением данных из кеша SQL Server.

Рисунок 2 

Рис.2. Демонстрация вытеснения данных из кеша SQL Server

 

                На рисунке 2 показаны 3 условных этапа различной нагрузки на диск. На этапе 1 и этапе 3 – очереди к диску были минимальны. Почему же на этапе 2 очередь резко возросла и это привело к появлению проблем производительности у пользователей? Ответ на этот вопрос легко найти на втором графике рисунка 2: «Ожидаемый срок жизни страницы памяти», который показывает предполагаемое время нахождения страницы данных в кеше SQL Server. Между двумя этапами видим резкое понижение этого графика со значения 3000 до 200. С точки зрения логики работы SQL Server это означает, что данные будут находится к кеше не 3000 секунд как раньше, а 200 секунд, следовательно, если пользователь запросит данные через 300 секунд, то SQL Server с почти 100% вероятностью не найдет их в оперативной памяти (кеше) и придется выполнять операцию чтения с диска. Этими операциями обеспечивается рост очереди к диску. В течение всего этапа 2 кеш «прогревался» (заполнялся данными) и на этапе 3 нагрузка на диск упала.  

Мы определили вид проблемы, теперь рассмотрим варианты решения.

                Что надо сделать:

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

- Возможно проблема в качестве обслуживания статистик и индексов MS SQL Server.

Что не надо делать:

-          Не надо покупать новые диски (дисковые массивы), это не решает проблему, а скорее ее усугубляет.

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

Рассмотрим еще несколько практических ситуаций с повышенной нагрузкой на диск, где причиной являются совершенно различные по природе причины.

 

 

2. Нагрузка на диски, обусловленная свопированием памяти на диски вследствие нехватки свободной памяти.

Рисунок 1

Рис.1. Практический пример повышенной нагрузки на диск

На рисунке 1 показана практическая ситуация на сервере БД SQL Server у клиента в течение 1,5 часов. Как видно по счетчику «Средней длины очереди к диску» диск нагружен и не справляется с количеством обращений к нему.

На рисунке также показаны два других показателя: «Нагрузка CPU», «Свободная оперативная память» для поиска причин торможения диска. Условно делим ситуацию на два этапа: первый этап – очередь к диску практически равна 0 и пользователи работают в обычном режиме, и второй этап – в течение которого очередь к диску поднимается до максимальных значений (342) и пользователи не могут качественно работать. Чем же обусловлена такая нагрузка на диск?

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

Показатель «Свободная оперативная память» как раз показывает доступность реальной оперативной памяти для других процессов, а, следовательно, чем его значение больше, тем меньше вероятность свопирования. На рисунке 1 значение свободной оперативной памяти на сервере баз данных постоянно уменьшается до 500 Мб, далее до 200 Мб, это в свою очередь и привело к нагрузке на диск (на этапе 2).

Встает вопрос – а зачем на рисунке 1 мы показали счетчик «Нагрузка CPU»? Все просто, на этапе 1 средняя загрузка CPU была около 50%, на этапе 2 – 40%, при этом в системе работало аналогичное количество пользователей. Такое уменьшение значения говорит о том, что процессор недозагружен и узкое место в производительности сместилось в сторону диска (он не справляется).

Для исправления этой ситуации достаточно правильно распределить потребление оперативной памяти и не допустить уменьшение ее объема до 500Мб (как рекомендация). Неправильным вариантом решения была бы покупка более производительного физического диска или хранилища.

 

 

    3. Нагрузка на диски, обусловленная внутренними механизмами работы SQL Server.

Рисунок 2 

Рис. 2. Периодическая нагрузка на диск

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

Из нашего опыта это могут быть следующие операции:

-   Увеличение размера файлов данных и лога транзакций (особенно если указан фиксированный размер прироста).

- Резервная копия файла данных или журнала транзакций.

- Операция лог-шиппинга.

Сбор и анализ данных осуществлялся с использованием мониторинга производительности PerfExpert.

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Макс Савостин (mc1c80) 20.03.15 10:44
2. Алекс Ю (AlexO) 20.03.15 11:27
Как правило, повышенную нагрузку на диски можно определить различными способами. Основной из них – это получение счетчика «Средней длины очереди к диску»
Сразу неверное предположение, отсюда - неверные выводы.
И "решения", где-то правильные, где-то - бессмысленные, но точно не связанные с проблемой "повышенная нагрузка на диски", или связанная, но не так, как предположил автор.
Дальше статью можно и не читать, но рассмотрим подробнее.
Очередь к диску сама по себе - это не "нагрузка на диск", а следствие интенсивного использования диска, т.е. следствие повышенной нагрузки.
Т.е. это очень важный показатель оценки работы дисковой подсимтемы, но не он определят проблему, и бороться надо не с очередью к диску, а с причиной - недостаточной (или уже несоответствующей) скоростью записи-чтения системы HDD (что наиболее эффективно), либо, с другой стороны, заставлять приложения меньше использовать HDD, и больше - ОЗУ.
Вы, собственно, это и рассматриваете далее, но из-за неверной предпосылки - рассматриваете не в том контексте и верные, и неверные подходы.
3. Алекс Ю (AlexO) 20.03.15 11:28
Как правило, повышенную нагрузку на диски можно определить различными способами. Основной из них – это получение счетчика «Средней длины очереди к диску»
Сразу неверное предположение, отсюда - неверные выводы.
И "решения", где-то правильные, где-то - бессмысленные, но точно не связанные с проблемой "повышенная нагрузка на диски", или связанная, но не так, как предположил автор.
Дальше статью можно и вовсе не читать, но все же рассмотрим подробнее, даже если автор против этого.
Очередь к диску сама по себе - это не "нагрузка на диск", а следствие интенсивного использования диска, т.е. следствие повышенной нагрузки.
Т.е. это очень важный показатель оценки работы дисковой подсимтемы, но не он определяет проблему, и бороться надо не с очередью к диску, а с причиной - недостаточной (или уже несоответствующей) скоростью записи-чтения системы HDD (что наиболее эффективно), либо, с другой стороны, заставлять приложения меньше использовать HDD, и больше - ОЗУ.
Вы, собственно, это и рассматриваете далее, но из-за неверной предпосылки - рассматриваете не в том контексте и верные, и неверные подходы.
Кратко:
Демонстрация вытеснения данных из кеша SQL Server
Понятно, что если данные в кэше (будем считать, что в ОЗУ, т.к. кэш может быть и на диске - это кто как реализует его хранение) - то чем чаще используется кэш, тем менее используются диски. Никаких графиков тут и не нужно - все эти графики "зависимости очереди от ... страниц памяти..." - не более, чем красивые картинки, т.к. данная "проблема" - всего лишь отражение работы конкретного физического механизма: данные в кэше - используется кэш, нет данных - происходит обращение к диску.
И никакие "найти тяжелые неоптимальные запросы" (и остальные предложенные "приемы устранения проблемы") тут вообще роли не играют: если требуются данные, которых нет в кэше - вот хоть какой запрос будет легким, но все равно будет обращение к БД (диску).
Да, можно увеличить размер используемого кэша ОЗУ (всякие там "ключи использвоания памяти выше 2 ГБ" и т.д.), но это опять же "решения на час" - какого угодно размера кэш забьется, но если не будет необходимых данных - будет обращение к диску.
2. Нагрузка на диски, обусловленная свопированием памяти на диски вследствие нехватки свободной памяти.
Это тоже понятно, что если своп (виртуальный кэш, используемый приложением) находится на диске - будет нагружаться диск, в ОЗУ - диск будет разгружен. Это все и без графиков понятно всем, из одного предложения.
Другое дело, что можно регулировать объем того и другого для приложений, но об этом - как раз ни слова (именно из-за неверной предпосылки - ушел в "объяснениях" совсем в другую сторону).
3. Нагрузка на диски, обусловленная внутренними механизмами работы SQL Server.
А это вообще откровенная профанация (т.е. преднамеренное запутывание).
Не хочешь, чтобы "внутренние механизмы SQL" создавали нагрузку - отключи SQL. И никакой нагрузки не будет вовсе.
Регламентные операции - будь то резервное копирование, логирование и прочие, определяются не мифической "нагрузкой на диск из-за внутренних механизмов", а необходимостью: не нужно - отключи. Нужно, но тормозит - совершенствуй дисковую подсистему. Больше вариантов нет.
Log Shipping - это также регламентная операция передачи журнала транзакций, и суть её не в "создании нагрузки", а в резервировании лога транзакций: не нужно - отключи.
Т.е. по третьему пункту: автор, SQL слишком "тяжел" для твоих серверов - выключи его, переходи на файловую 1С.
Но не сваливай на регламент СУБД проблемы с дисковой подсистемой.
4. Алекс Ю (AlexO) 20.03.15 11:38
(1) mc1c80,
Спасибо за статью.
Я думаю, что и вы напишите статью, ничем не хуже, чем эта. И с таким же смыслом.
5. Алекс Ю (AlexO) 20.03.15 11:43
(2) сообщение получилось как повтор.
Так как изменить нельзя, то можно рассматривать сообщение ( 2) - как краткое резюме по статье, сообщение ( 3) - как более подробный развернутый анализ.
6. Василий Коровин (vasyak319) 20.03.15 11:58
Фигня какая-то. Весь абзац под рис.1 написан в стиле "масло масляное" и я бы сказал, что и полезной инфы от него столько же, но это не скажу, потому что автор вводит некие магические "возможности диска":
В случае, если средняя очередь к диску больше, чем возможности диска

и ценность этого фрагмента сразу падает ниже нуля.
Нет у диска "возможностей" ни по параллельной обработке, ни как-либо связанных с очередью, потому что и параллельность и очереди это всё проблемы ОС. Диск вообще не в курсе, что там происходит, ему сказали: "пиши" - он пишет, сказали: "читай" - читает, а почему ОС сказала читать/писать именно это, ему до лампочки.

Иллюстрация, на которой два диких всплеска очереди, когда памяти было 500 и дальнейшая ровная работа, когда памяти стало 200, сопровождаемая выводом, что "смотрите, как было хорошо при 500 и как стало плохо, когда память упала до 200" это вообще без комментариев. Автор, если ваши выводы не коррелируют с иллюстрацией, то вместо скучных графиков вставляйте котиков - пользы столько же, но хоть веселее.
Pavl0; softcreator; AlexO; +3 Ответить 2
7. Павел Баркетов (gallam99) 20.03.15 12:22
(4) AlexO, vasyak319
Для невежды нет ничего лучше молчания, но если бы он знал,
что для него лучше всего, — не был бы он невеждой.
8. Алекс Ю (AlexO) 20.03.15 14:05
(7) gallam99, вы лучше не корите себя, а учитесь.
9. Алекс Ю (AlexO) 20.03.15 14:07
(6) vasyak319,
Нет у диска "возможностей" ни по параллельной обработке, ни как-либо связанных с очередью, потому что и параллельность и очереди это всё проблемы ОС. Диск вообще не в курсе, что там происходит, ему сказали: "пиши" - он пишет, сказали: "читай" - читает, а почему ОС сказала читать/писать именно это, ему до лампочки.
Именно так.
И первый, и последний "вопрос нагрузки" на HDD (систему дисков) - это скорость записи-чтения. С неё начинается, и ею все заканчивается в "оптимизации дисковой подсистемы".
10. ООО "Студия-24бит" Компания (softcreator) 20.03.15 14:43
Много букв... и не по делу... Открыл статью, думал кто то опытом делится, а тут "посмотрите на лево", "посмотрите на право".
(6) Поддерживаю про котиков :)
11. Павел Баркетов (gallam99) 20.03.15 14:45
(10) softcreator,
Кто ищет тот всегда найдет
12. Алекс Ю (AlexO) 20.03.15 14:47
(11) gallam99, предлагаю статью исправить:
заголовок:
"Повышенная нагрузка на диски сервера баз данных SQL Server"
Статья:
"Для невежды нет ничего лучше молчания, но если бы он знал,
что для него лучше всего, — не был бы он невеждой."
"Кто ищет тот всегда найдет".
Думаю, такая статья была бы воспринята более дружелюбно ))
13. ООО "Студия-24бит" Компания (softcreator) 20.03.15 15:15
(11) Да я и не искал. Заголовок статьи привлек внимание. Всегда интересно узнать что-то новое. Здесь не узнал.
14. Павел Баркетов (gallam99) 20.03.15 15:36
(13) softcreator,
В будущем, если вопросы такие встанут, может и вернетесь, может и польза будет.

(12) AlexO,
Если я в будущем буду писать статью для Вас,
я учту Ваши пожелания по названию.
15. Алекс Ю (AlexO) 20.03.15 16:03
(14) gallam99, Спасибо, жду статью для меня.
Хотя как раз по заголовку статьи я ничего не менял )
16. Алексей Захаров (almas) 21.03.15 07:43
(3)(3) AlexO, полностью согласен. Фигня полная. Ни ссылок на более полные описания ни конкретных примеров где и что нужно изменить. Кароче хлам.