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

15.03.15

База данных - 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.

См. также

HighLoad оптимизация Программист Платформа 1С v8.3 Бесплатно (free)

Метод очень медленно работает, когда параметр приемник содержит намного меньше свойств, чем источник.

06.06.2024    8535    Evg-Lylyk    61    

43

HighLoad оптимизация Программист Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    4812    spyke    28    

49

HighLoad оптимизация Программист Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    7141    vasilev2015    20    

42

HighLoad оптимизация Инструменты администратора БД Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих запросов на sql, ожиданий, конвертация запроса в 1С и рекомендации, где может тормозить.

2 стартмани

15.02.2024    11672    223    ZAOSTG    76    

115

HighLoad оптимизация Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Принимать, хранить и анализировать показания счетчиков (метрики) в базе 1С? Почему бы нет? Но это решение быстро привело к проблемам с производительностью при попытках построить какую-то более-менее сложную аналитику. Переход на PostgresSQL только временно решил проблему, т.к. количество записей уже исчислялось десятками миллионов и что-то сложное вычислить на таких объемах за разумное время становилось все сложнее. Кое-что уже практически невозможно. А что будет с производительностью через пару лет - представить страшно. Надо что-то предпринимать! В этой статье поделюсь своим первым опытом применения СУБД Clickhouse от Яндекс. Как работает, что может, как на нее планирую (если планирую) переходить, сравнение скорости работы, оценка производительности через пару лет, пример работы из 1С. Все это приправлено текстами запросов, кодом, алгоритмами выполненных действий и преподнесено вам для ознакомления в этой статье.

1 стартмани

24.01.2024    5174    glassman    17    

40

HighLoad оптимизация Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    12039    doom2good    49    

71
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. mc1c80 20.03.15 10:44 Сейчас в теме
4. AlexO 135 20.03.15 11:38 Сейчас в теме
(1) mc1c80,
Спасибо за статью.
Я думаю, что и вы напишите статью, ничем не хуже, чем эта. И с таким же смыслом.
7. gallam99 237 20.03.15 12:22 Сейчас в теме
(4) AlexO, vasyak319
Для невежды нет ничего лучше молчания, но если бы он знал,
что для него лучше всего, — не был бы он невеждой.
8. AlexO 135 20.03.15 14:05 Сейчас в теме
(7) вы лучше не корите себя, а учитесь.
2. AlexO 135 20.03.15 11:27 Сейчас в теме
Как правило, повышенную нагрузку на диски можно определить различными способами. Основной из них – это получение счетчика «Средней длины очереди к диску»
Сразу неверное предположение, отсюда - неверные выводы.
И "решения", где-то правильные, где-то - бессмысленные, но точно не связанные с проблемой "повышенная нагрузка на диски", или связанная, но не так, как предположил автор.
Дальше статью можно и не читать, но рассмотрим подробнее.
Очередь к диску сама по себе - это не "нагрузка на диск", а следствие интенсивного использования диска, т.е. следствие повышенной нагрузки.
Т.е. это очень важный показатель оценки работы дисковой подсимтемы, но не он определят проблему, и бороться надо не с очередью к диску, а с причиной - недостаточной (или уже несоответствующей) скоростью записи-чтения системы HDD (что наиболее эффективно), либо, с другой стороны, заставлять приложения меньше использовать HDD, и больше - ОЗУ.
Вы, собственно, это и рассматриваете далее, но из-за неверной предпосылки - рассматриваете не в том контексте и верные, и неверные подходы.
5. AlexO 135 20.03.15 11:43 Сейчас в теме
(2) сообщение получилось как повтор.
Так как изменить нельзя, то можно рассматривать сообщение ( 2) - как краткое резюме по статье, сообщение ( 3) - как более подробный развернутый анализ.
3. AlexO 135 20.03.15 11:28 Сейчас в теме
Как правило, повышенную нагрузку на диски можно определить различными способами. Основной из них – это получение счетчика «Средней длины очереди к диску»
Сразу неверное предположение, отсюда - неверные выводы.
И "решения", где-то правильные, где-то - бессмысленные, но точно не связанные с проблемой "повышенная нагрузка на диски", или связанная, но не так, как предположил автор.
Дальше статью можно и вовсе не читать, но все же рассмотрим подробнее, даже если автор против этого.
Очередь к диску сама по себе - это не "нагрузка на диск", а следствие интенсивного использования диска, т.е. следствие повышенной нагрузки.
Т.е. это очень важный показатель оценки работы дисковой подсимтемы, но не он определяет проблему, и бороться надо не с очередью к диску, а с причиной - недостаточной (или уже несоответствующей) скоростью записи-чтения системы HDD (что наиболее эффективно), либо, с другой стороны, заставлять приложения меньше использовать HDD, и больше - ОЗУ.
Вы, собственно, это и рассматриваете далее, но из-за неверной предпосылки - рассматриваете не в том контексте и верные, и неверные подходы.
Кратко:
Демонстрация вытеснения данных из кеша SQL Server
Понятно, что если данные в кэше (будем считать, что в ОЗУ, т.к. кэш может быть и на диске - это кто как реализует его хранение) - то чем чаще используется кэш, тем менее используются диски. Никаких графиков тут и не нужно - все эти графики "зависимости очереди от ... страниц памяти..." - не более, чем красивые картинки, т.к. данная "проблема" - всего лишь отражение работы конкретного физического механизма: данные в кэше - используется кэш, нет данных - происходит обращение к диску.
И никакие "найти тяжелые неоптимальные запросы" (и остальные предложенные "приемы устранения проблемы") тут вообще роли не играют: если требуются данные, которых нет в кэше - вот хоть какой запрос будет легким, но все равно будет обращение к БД (диску).
Да, можно увеличить размер используемого кэша ОЗУ (всякие там "ключи использвоания памяти выше 2 ГБ" и т.д.), но это опять же "решения на час" - какого угодно размера кэш забьется, но если не будет необходимых данных - будет обращение к диску.
2. Нагрузка на диски, обусловленная свопированием памяти на диски вследствие нехватки свободной памяти.
Это тоже понятно, что если своп (виртуальный кэш, используемый приложением) находится на диске - будет нагружаться диск, в ОЗУ - диск будет разгружен. Это все и без графиков понятно всем, из одного предложения.
Другое дело, что можно регулировать объем того и другого для приложений, но об этом - как раз ни слова (именно из-за неверной предпосылки - ушел в "объяснениях" совсем в другую сторону).
3. Нагрузка на диски, обусловленная внутренними механизмами работы SQL Server.
А это вообще откровенная профанация (т.е. преднамеренное запутывание).
Не хочешь, чтобы "внутренние механизмы SQL" создавали нагрузку - отключи SQL. И никакой нагрузки не будет вовсе.
Регламентные операции - будь то резервное копирование, логирование и прочие, определяются не мифической "нагрузкой на диск из-за внутренних механизмов", а необходимостью: не нужно - отключи. Нужно, но тормозит - совершенствуй дисковую подсистему. Больше вариантов нет.
Log Shipping - это также регламентная операция передачи журнала транзакций, и суть её не в "создании нагрузки", а в резервировании лога транзакций: не нужно - отключи.
Т.е. по третьему пункту: автор, SQL слишком "тяжел" для твоих серверов - выключи его, переходи на файловую 1С.
Но не сваливай на регламент СУБД проблемы с дисковой подсистемой.
zemiramira; cleaner_it; +2 Ответить
16. almas 257 21.03.15 07:43 Сейчас в теме
(3)(3) AlexO, полностью согласен. Фигня полная. Ни ссылок на более полные описания ни конкретных примеров где и что нужно изменить. Кароче хлам.
cleaner_it; +1 Ответить
6. vasyak319 152 20.03.15 11:58 Сейчас в теме
Фигня какая-то. Весь абзац под рис.1 написан в стиле "масло масляное" и я бы сказал, что и полезной инфы от него столько же, но это не скажу, потому что автор вводит некие магические "возможности диска":
В случае, если средняя очередь к диску больше, чем возможности диска

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

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

(12) AlexO,
Если я в будущем буду писать статью для Вас,
я учту Ваши пожелания по названию.
15. AlexO 135 20.03.15 16:03 Сейчас в теме
(14) Спасибо, жду статью для меня.
Хотя как раз по заголовку статьи я ничего не менял )
zemiramira; 11k65m; +2 Ответить
17. user1328272 27.05.24 14:41 Сейчас в теме
Оставьте свое сообщение