В результате, конечно, разобрался и нашел всё что нужно, но время потрачено много. В очередной раз думая, как глобально (да-да, во всём мире или хотя бы на ? части суши) уменьшить бесполезно потраченные часы — решил написать статью, которая поможет людям быстро сориентироваться в незнакомом программном обеспечении.
Писать я буду не про линукс — проблему хоть и решил, но профессионалом вряд ли стал. Напишу про более знакомый мне MS SQL. Благо, уже приходилось много раз отвечать на вопросы и список типовых уже готов.
Для кого пишу
Если вы админ в Сбере (или в Яндексе или <другая топ-100 компания>), вы можете сохранить статью в избранное. Да, пригодится! Когда к вам, в очередной раз, с одними и теми же вопросами придут новички — Вы дадите им ссылку на нее. Это сэкономит Ваше время.
Если без шуток, эта СУБД часто используется в небольших компаниях. Часто совместно с 1С либо другим ПО. Отдельного БД-админа таким компаниям держать затратно — надо будет выкручиваться обычному ИТ-шнику. Для таких и пишу.
Какие проблемы рассмотрим
Если сервер вам сообщает “закончилось место на диске Е” — глубокий анализ не нужен. Не будем рассматривать ошибки, решение которых очевидно из текста сообщения. Также не будем рассматривать ошибки по которым гугл сразу выдает ссылку на msdn с решением.
Рассмотрим проблемы по которым не очевидно что гуглить. Такие как, например, внезапное падение производительности или, например, отсутствие соединения. Рассмотрим основные инструменты для настройки. Рассмотрим средства анализа. Поищем где лежат логи и другая полезная информация. И в целом, попробую в одной статье собрать нужную информацию для быстрого старта.
Самое первое
Начнем с лидера списка частых вопросов, настолько он опережает всех, что рассмотрим его отдельно. Вдобавок, об этом пишут во всех статьях про работу MS SQL — и я не буду нарушать традицию.
Если у вас вдруг, ни с того ни с сего, стало работать медленно, а вы ничего не меняли (как поставили, так всё и работало, никто ничего не трогал) — в первую очередь, обновите статистику и перестройте индексы. Только удостоверившись, что это выполнено — имеет смысл копать глубже. Еще раз подчеркну — делать это нужно обязательно, вопрос только как часто.
В интернете полно рецептов как это делать, приводятся примеры скриптов. Предположу, что все те методы для “профи” и новичкам непонятны. Что ж, опишу способ наипростейший: для его внедрения вам потребуется только владение мышью.
- SSMS — приложение “Microsoft SQL Server Management Studio”, находится в “Пуске”. Устанавливается отдельной галочкой (Client management tools) с дистрибутива сервера. Начиная с 2016 версии, доступно бесплатно на сайте MS в виде отдельного приложения. Старшие версии студии нормально работают с младшими версиями сервера. Наоборот — тоже иногда работают (основные функции).
docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms “SSMS is free! It does not require a license to install and use.” - Profiler — приложение “SQL Server Profiler”, находится в “Пуске”, устанавливается вместе с SSMS.
- Performance Monitor (Системный монитор) — оснастка панели управления. Позволяет мониторить счетчики производительности, журналировать и просматривать историю замеров.
Обновление статистики с помощью “плана обслуживания”:
- запускаем SSMS;
- подключаемся к нужному серверу;
- разворачиваем в Object Inspector дерево: Management \ Maintenance Plans (Планы обслуживания)
- правой кнопкой на узле, выбираем “Maintenance Plan Wizard”
- в визарде мышкой отмечаем нужные нам задачи:
- rebuild index (перестроить индекс)
- update statistics (обновить статистику)
- отметить можно обе задачи сразу, либо сделать два плана обслуживания по одной задаче в каждом (смотрим “важные замечания” ниже);
- далее, отмечаем галочками нужную нам БД (или несколько). Делаем это для каждой задачи (если выбрали две задачи — будет два диалога с выбором БД).
- Next, Next, Finish
После этих действий у вас создастся (а не выполнится) “план обслуживания”. Запуск можно выполнить вручную — правой кнопкой на нем, выбрать “Execute”. Либо настроить запуск через “SQL Agent”.
Важные замечания:
- Обновление статистики — неблокирующая операция. Можно выполнять в рабочем режиме. Дополнительную нагрузку конечно создаст, но ведь у вас и так всё тормозит, будет чуть больше — незаметно.
- Перестроение индекса — блокирующая операция. Запускать только в нерабочее время. Есть исключение — Enterprise редакция сервера допускает выполнение “онлайнового ребилда”. Эта опция включается галочкой в настройках задачи. Обратите внимание, галочка есть во всех редакциях, но работает только в Enterprise.
- Конечно, эти задачи необходимо выполнять регулярно. Предлагаю простой способ определения, как часто это делать:
- при первых проблемах выполняете план обслуживания;
- если помогло — ждете пока не начнутся проблемы снова (как правило, до очередного закрытия месяца/расчета зп/ и т.п. массовых операций);
- получившийся срок нормальной работы и будет вам ориентиром;
- например, настройте выполнение плана обслуживания в два раза чаще.
Сервер работает медленно — что делать?
Используемые сервером ресурсы
Как и любой другой программе, серверу нужны: время процессора, данные на диске, объемы оперативной памяти и пропускная способность сети.
Оценить нехватку того либо иного ресурса в первом приближении можно с помощью Task Manager (Диспетчер задач), как бы по кэпски это не звучало.
Загрузка ЦП
Посмотреть загрузку в диспетчере сможет даже школьник. Здесь нам надо просто убедиться, что если процессор загружен, то именно процессом sqlserver.exe.
Если это ваш случай, то надо переходить к анализу активности пользователей, чтобы понять, что именно стало причиной загрузки (листаем ниже).
Загрузка диска
Многие смотрят только загрузку процессора, но не надо забывать что СУБД — это хранилище данных. Объемы данных растут, производительность процессоров растет, а скорость HDD практически не меняется. С SSD ситуация получше, но терабайты на них хранить затратно.
Получается так, что я чаще сталкиваюсь с ситуациями, когда узким местом становится именно дисковая система, а не ЦПУ.
Для дисков нам важны следующие показатели:
- средняя длина очереди (операций ввода-вывода ожидающих выполнения, штук);
- скорость чтения-записи (в Мб/с).
Серверная версия диспетчера задач, как правило (зависит от версии системы), показывает и то и другое. Если нет — запускаем оснастку панели управления “Performance Monitor” (Системный монитор). Нас интересуют счетчики:
- Физический (логический) диск / Среднее время чтения (записи)
- Физический (логический) диск / Средняя длина очереди диска
- Физический (логический) диск / Скорость обмена с диском
Развернуто — можно почитать мануалы производителя, например тут social.technet.microsoft.com/wiki/contents/articles/3214.monitoring-disk-usage.aspx. Вкратце:
- Очередь желательно чтобы не превышала 1. Допустимы кратковременные всплески, если они быстро спадают. Всплески могут быть разными в зависимости от вашей системы. Для простого рэйда-зеркала из двух HDD — очередь больше 10-20 проблема. Для крутой библиотеки с супер кешированием я видел всплески до 600-800 которые мгновенно рассасывались, не приводя к задержкам.
- Нормальная скорость обмена тоже зависит от типа дисковой системы. Обычный (настольный) HDD “качает” по 50-100 Мб/с. Хорошая дисковая библиотека по 500 Мб/с и более. Для мелких случайных операций скорость меньше. Примерно так и ориентируйтесь.
- Эти параметры надо смотреть в комплексе. Если ваша библиотека качает 50Мб/с и при этом выстраивается очередь в 50 операций — явно что-то не так с железом. Если очередь выстраивается при прокачке близкой к максимальной — то скорее всего диски не виноваты — они просто больше не могут — надо искать способ уменьшить нагрузку.
- Нагрузку надо смотреть раздельно по дискам (если их несколько) и сопоставлять с размещением файлов сервера. Диспетчер задач может показать наиболее активно используемые файлы. Это удобно использовать, чтобы убедиться, что нагрузка идет именно от СУБД.
Чем могут быть вызваны проблемы с дисковой системой:
- проблемы с железом
- погорел кэш, резко упала производительность;
- дисковая система используется чем-то еще;
- Недостаток оперативной памяти. Свопинг. Ухудшилось кэширование, производительность упала (смотрим раздел про ОП ниже).
- Увеличилась пользовательская нагрузка. Необходимо оценить работу пользователей (проблемный запрос / новый функционал / увеличение количества пользователей / увеличение объема данных / и т.п.).
- Фрагментация данных БД (смотрим ребилд индексов выше), фрагментация файлов системы.
- Дисковая система достигла своих максимальных возможностей.
Если у вас последний вариант — не спешите выкидывать оборудование. Иногда из системы можно выжать чуть больше если подойти к проблеме с умом. Проверьте размещение файлов системы на соответствие рекомендуемым требованиям:
- не смешивайте файлы ОС с файлами данных БД. Размещайте их на физически разных носителях чтобы система не конкурировала с СУБД за ввод-вывод.
- БД состоит из файлов двух видов: данные (*.mdf, *.ndf) и логи (*.ldf). Файлы данных, как правило, больше используются на чтение. Логи — больше на запись (причем запись — последовательная). Из понимания этого факта, следует рекомендация размещать логи и данные на физически разных носителях, чтобы запись в лог не прерывала чтение данных (как правило, операция записи имеет приоритет выше чем у чтения).
- MS SQL для обработки запросов может использовать “временные таблицы”. Они хранятся в системной базе tempdb. Если у вас высокая нагрузка на файлы этой БД — то можно попробовать вынести ее на физически отдельные носители.
Резюмируя по размещению файлов, используйте принцип “разделяй и властвуй”. Оцените к каким файлам идут обращения и попробуйте их распределить на разные носители. Также, используйте особенности RAID систем. Например, RAID-5 читает быстрее чем пишет — что хорошо подходит для файлов данных.
В продолжении:
- анализируем использование ОП и сети.
- смотрим детально работу пользователей используя SSMS, profiler и прямые запросы к системным представлениям.
- план и статистика запросов (рассмотрим несколько способов получения). live query statistics.
- waits (ожидания). текущая информация и статистика.
- проблемы с подключением к серверу. процессы/порты/протоколы
Комментарии (26)
ildarz
24.08.2017 14:03+2Что касается нагрузки на диски — в первую очередь нужно выучить, что такое IOPS и как они связаны с пропускной способностью. И тогда внезапно может стать понятно, что, например, 50Мб/сек на 4K блоках со случайным доступом может быть совсем не мало, и высокая очередь — это никакие не проблемы с железом, а банально недостаточно производительная дисковая.
не смешивайте файлы ОС с файлами данных БД. Размещайте их на физически разных носителях чтобы система не конкурировала с СУБД за ввод-вывод.
А что, разве ОС сама по себе создает какую-то серьезную нагрузку на диски? Разве только при постоянном своппинге, но тогда не о разнесении нагрузки на разные диски думать надо, а совсем о другом. Предлагаю вам на досуге померять, что будет быстрее — ОС + СУБД на RAID-10 из 4-х дисков, или ОС и СУБД на отдельных зеркалах.
И т.д, и т.п.
Резюмируя — на самом деле нужна голова на плечах и минимальное понимание происходящих процессов. Тогда половина рекомендаций в таких статьях сразу окажутся капитанскими, а вторая половина — вредными.
71rmn Автор
24.08.2017 14:11согласен с резюме.
надеюсь статья поспособствует тому, чтобы больше людей стали считать эти советы капитанскими.ildarz
24.08.2017 14:18-1Я не вижу, как это может произойти, если вы описываете волшебные пилюли вместо того, чтобы давать понимание. Для примера — ну нельзя понять, что в случае СУБД происходит с нагрузкой на диски, не оперируя понятием IOPS, а вы это даже мимоходом не упоминаете.
71rmn Автор
24.08.2017 14:25-1исходил из предположения что мсскл пишет данные страницами. в этом случае зависимость между IOPS и Mb/s — линейная.
просто для меня лично удобнее прокачку оценивать в Мб/с, но тут кому как.ildarz
24.08.2017 15:21При одном постоянном типе нагрузки она, конечно, линейная. Но при этом оперировать скоростью чтения, а не иопсами, может только человек с некоторым опытом. Если начинать с азов, то нужно понимать, чем вообще скорости работы с дисками ограничиваются. А это совершенно разные параметры для случайных и последовательных операций.
Если понимать, что скорость случайного чтения ограничена средним временем поиска, то становится ясно, что, скажем, с 15К SAS HDD с паспортным средним временем поиска 3.3мс вы не снимете больше примерно 300 IOPS, что, например, на 4К блоках даст нам жалкие 1.2 Мбайт/сек. А для Enterprise SATA, которые сейчас весьма популярны, цифры уменьшаются еще вдвое. А, скажем, 50Мбайт/сек в таких условиях можно снять только с массива из минимум 40 15K SAS HDD, что в природе хоть и встречается, но отнюдь не у целевой аудитории вашей статьи.
Поэтому для оценки, насколько адекватно работает система в режиме случайного доступа, в перфмоне смотреть надо вовсе не скорость чтения, а количество операций в секунду (ну или уметь пересчитывать одно в другое).
MrRoger
24.08.2017 14:26+1Автор, скорее всего, имел ввиду не размещать на одном логическом диске файлы mdf и ldf. Т.к. mdf пишется параллельно, а ldf последовательно. Тут может быть гонка на ввод-вывод, если они рядом.
71rmn Автор
24.08.2017 14:32А что, разве ОС сама по себе создает какую-то серьезную нагрузку на диски?
ОС может и не создает, но сам диск может оказаться совсем не производительным. Встречал случаи когда С: создан внутри виртуальной машины которая лежит на общей хранилке с кучей всего остального и производительность там совсем никакая.
В таком случае, вполне разумно файлы БД убрать с С:.
прошу не воспринимать раздел «советы» как руководство к действию, а лучше как «варианты на подумать».
надо будет в следующий раз в статье предупреждение указать :)
LoadRunner
24.08.2017 14:42У Вас сравнение нечестное. Надо сравнивать ОС+СУБД на RAID10 vs. ОС на RAID10 + СУБД на RAID10.
Разумеется, RAID10 быстрее RAID1 примерно в два раза будет. А ОС не даст такую же нагрузку, как СУБД.ildarz
24.08.2017 15:08Все честно. Вот есть у нас сервер с N дисками. Тормозит. Мы решаем вынести ОС на отдельное зеркало. Но откуда диски под него взять? При работе в рамках имеющегося оборудования, без апгрейда — только изъять из имеющегося массива. Вот и получаем в реальной жизни сравнение "N дисков, но вместе с ОС" против "N-2, но без ОС".
LoadRunner
24.08.2017 15:42Погодите. Вы решаете задачу «разделить ОС и данные имеющимися средствами» или приводите аргумент в пользу своего «ОС не даёт сильной нагрузки и пофиг, на одном носителе или на разных у нас данные и ОС»?
Если первое — Вам надо куда-то забэкапить данные, чтобы пересобрать один RAID10 на два RAID1.
Если второе — мы оперируем любым количеством дисков для соблюдения одинаковых условий — и там, и там RAID10.ildarz
24.08.2017 17:53Это не я решаю задачу. :) Это автор предлагает решать задачу тормозов дисковой подсистемы конкретного сервера путем разнесения ОС и СУБД на разные физические диски. И мои комментарии следует воспринимать исключительно в этом контексте, а не в контексте некоего абстрактного тестирования в лабораторных условиях.
YaMishar
24.08.2017 15:21А я вот помню, как я (как раз айтишник, а не ДБА) пытался разобраться с резким падением прозводительности на SaaS интсансе при больших операциях. Ну просто одна большая операция, в которой копируются миллионы строчек вдруг после бодрого начала начинала безбожно тормозить. Даже до того, что начало было бодрое, нужно было ещё докопаться, я даже график строил, зависимости времени от количества операций, пока не познакомился с разными типами дисков на AWS:
docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSVolumeTypes.html
А так всё перепробовал. и таймауты, и настройки различные…
BaranovskiyNE
24.08.2017 17:47Не совсем согласен с началом решения проблем. Перестраивать индексы и статистику надо только на таблицах, где они реально портятся, а так можно мышкой накликать задач, которые будут только еще больше замедлять работу сервера. Можно конечно перестраивать только сильно попорченные индексы, статистики и фрагментированные таблицы, но серебряной пули тут не существует, если что-то тормозит, надо садиться и разбираться, какие запросы тормозят, смотреть их планы, в реальности кроме индексов и статистики может быть еще тысяча причин торможения
OlegAxenow
24.08.2017 18:14Очередь желательно чтобы не превышала 1. Допустимы кратковременные всплески, если они быстро спадают. Всплески могут быть разным
Откуда такая информация? Даже для старых дисков говорили про 2N, где N — количество шпинделей.
Если интересно ознакомиться с темой глубже — вот неплохая статья (правда, трёхлетней давности): Управление глубиной очереди дисков для достижения лучшей производительности
speshuric
29.08.2017 09:56Если очередь выше 1, то значит в ней кто-то ждёт завершения предыдущей операции. Если эти операции записи в mdf, то пусть будут. Если они на чтение, то, как и написано в вашей статье, задержки будут расти линейно. От количества шпинделей это никак не зависит (это странная байка откуда-то из 90-х или раньше, сам я это году в 97-98 читал, применительно в NT4 встроенным RAID).
Но. Если это write-ahead запись в LDF, то очередь 1 — это значит, что вы полностью упёрлись в производительность диска. Потому что тут: SQL кинул запись в журнал транзакций (очередь стала 1), подождал, получил ответ от ОС «я кончила» (очередь стала 0) и тут же кинул новую запись в журнал.
Так что если очередь 1, то уже нужно убедиться, что это не связано с однопоточным синхронным сценарием IO.
mrigi
25.08.2017 11:26+1Почти десять лет ежедневно работаю с mssql, ни разу не перестраивал индексы. Статистика да, иногда в особо запущенных случаях временно приходилось. Но избавится от этого можно более простым кодом. Парсер/оптимизатор запросов у mssql, увы, не шибко умный. Почти всегда перефразированный, упрощенный и разбитый на более мелкие части запрос решает проблемы. Вместо вложенных запросов делайте выборки во временные таблицы, например. Но индексы ребилдить — это лишнее, проблема у вас где-то в другом. Имхо.
71rmn Автор
29.08.2017 06:22как верно заметили выше, нужно понимать что при этом происходит…
например, выборка во временные таблицы приведет к дополнительному вводу-выводу в tempdb…mrigi
29.08.2017 11:33Думаю мы говорим про разные ситуации. Я про настроенный специалистами сервер, вы — про песочницу для начинающих.
Когда сервер выполняет неоптимальные запросы, то смотреть на надрывающееся железо равносильно лечению не причины, а последствий. То есть совершенно бесмысленно. Если сервер тупит на простых запросах, это совсем другая история.
Если у вас достаточно памяти и использовать переменные табличного типа, то выборка никаких i/o не добавит.
flashfm
25.08.2017 12:06в первую очередь, обновите статистику и перестройте индексы
Статистика по-умолчанию обновляется сама,
а индексы перестраивать надо только те, которые имеют определенное количество страниц и уровень фрагментации. Перестройка индекса очищает кеш планов, что в некоторых случаях воспринимается как «волшебная помощь» именно от перестройки индекса, хотя дело-то в кеше планов.
Кроме того, так как ни слов «кеш планов», ни IOPS, в вашей статье нет, то рекомендовать ее кому-либо, в том числе новичкам, смысла я не вижу.
Bergtagen
27.08.2017 13:41Пользуясь случаем, хотел спросить немного не по теме. Какая сейчас линуксовая ФС наиболее подходящая для sql-ных и 1с-ных баз? Btrfs хороша, но кажется не доросла ещё до времени, когда её можно пускать в продакшн, XFS хороша дефрагментацией на лету, но боится сбоев по питанию, ZFS хороша, но тогда BSD надо ставить наверно… А что тогда? Как всегда старая добрая ext4?
win32nipuh
29.08.2017 06:17+1" В кратце:"-> " Вкратце:"
«скорость варьирует»->«скорость изменяется(варьируется)»
ascheck
29.08.2017 06:17Описанные вами админы обычно используют MS SQL для 1С. Для них в интернете уже много есть рекомендаций, среди которых вы упустили достаточно (для новичков) полезные:
- Использовать протокол Shared Memory
- Изменить «шаг» роста для рабочих баз со стандартного 1 МБ на 100 МБ
- Убедиться, что план электропитания стоит «Высокая производительность». В случае виртуализованного сервера — и на гипервизоре
Кто-то скажет, что эти пункты о быстродействии, а не об анализе, но у меня и от статьи такое ощущение.
Marwin
Меня (возможно и не только меня) уже на протяжении многих лет в разных проектах преследует спонтанно возникающий глюк у mssql: условно говоря раз в месяц/другой (но строгой периодичности нет) он беспричинно начинает грузить одно ядро процессора и почти перестает отвечать на запросы. Что я только ни делал… обрубал все сетевые коннекты, останавливал сайты, даже рестартовал инстанс- всё равно он сразу возвращался к тормозам. В логах и профайлере никаких признаков ддоса или циклических запросов выявить не могу. Чаще всего помогает ребут винды.
71rmn Автор
в разделе про анализ активности пользователей (в процессе написания) освещу вопросы как посмотреть кто именно работает, что делает при этом и сколько каких ресурсов потребляет.
надеюсь, статья вам поможет
Marwin
спасибо, буду рад )) Правда в моем случае я не уверен, что эти проблемы в прямой зависимости от запросов пользователей (ну или учетки движка сайта), ибо тогда оно по-любому отлавливалось бы профайлером.
micb
У нас такое было, когда мы на одном виндовом сервере поставили два Sql Server: Developer Edition (default instance) и Express Edition (named instance). Developer сервер начал безпричинно тормозить, хотя никто его не трогал. Все, что знал из мониторинговых средств, попробовал — ничего страшного не нашел. С выключением и последующим удалением Express сервера тормоза исчезли. Справедливости ради, спустя какое-то время снова попробовал поставить второй инстанс Sql Express, с тех пор они живут дружно уже почти полгода.