Источник


Всем привет! Сегодня хотим поговорить об облачных базах данных, а точнее о тех проактивных и разовых мероприятиях, которые непосредственно обеспечивают их производительность.


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


Мы проделали большую работу, запуская и оптимизируя свою новую услугу облачной базы данных на собственной платформе Техносерв Cloud и, конечно же, столкнулись с рядом проблем и выработали свои подходы к их решению. Сейчас, когда сервис протестирован и работает, мы хотим поделиться с вами своим опытом – уверены, что прочитав этот материал, вы сможете избежать повторения чужих ошибок или откроете для себя что-то новое.


Сегодня бизнесу приходится иметь дело с постоянно растущими объемами данных, поэтому возникла потребность каким-то образом облегчить задачу управления всеми этими огромными информационными массивами. Выход был найден благодаря внедрению платформ облачных вычислений и созданию облачных баз данных.


Согласно расчетам, объемы как структурированных, так и неструктурированных данных возрастают в среднем на 60% в год. До сих пор местом хранения всей этой информации служили традиционные базы данных, но этого оказалось недостаточно, и тогда на помощь пришли облачные технологии. Они избавили пользователей от необходимости выделять под базы данных собственные вычислительные мощности, возложив эту обязанность на провайдеров облачных сервисов. Такой подход оказался чрезвычайно продуктивным в плане повышения производительности и доступности баз данных, а также улучшения их масштабируемости.


В нашей статье мы решили сделать небольшой обзор наиболее оптимальных настроек и механизмов обеспечения производительности баз данных MS SQL Server. Данный список ни в коем случае не является исчерпывающим, поскольку рекомендации разработчиков и «лучшие отраслевые практики» корректируются со временем. Отследить все эти изменения и грамотно реализовать их может только команда профессиональных DBA (администраторов баз данных). Именно таким штатом специалистов располагает поставщик услуг облачных баз данных и, с большей вероятностью, наличием подобной команды не может похвастать большинство заказчиков.


Для обеспечения высокой доступности и высокой производительности БД MS SQL Server, размещаемых в облаке, в соответствии с рекомендациями Microsoft и лучшими практиками, мы проводим нижеследующие мероприятия.


Примечание: Хотя основная часть рекомендаций является общими, их применение на каждом конкретном сервере зависит от очень многих факторов. Поэтому ниже даются ссылки на соответствующие документы Microsoft c более детальной информацией.



Оптимизируем настройки MS SQL Server


Устанавливаем на все MS SQL серверы последние Service Packs / Cumulative Updates / Security Updates:


С 2016 года схема обновлений для Microsoft SQL Server была упрощена — теперь обновления выходят регулярно.


Общая последовательность установки обновлений приведена ниже (все обновления перед установкой на Производственных серверах — первоначально тестируем в Тестовом окружении):


• Устанавливаем последний пакет обновления Service Pack (SP).
• Устанавливаем последнее кумулятивное обновление для Service Pack — Cumulative Update (CU).
• В случае выхода Security Updates – также их устанавливаем.
• В случае проблем ищем и применяем Critical On-Demand (COD) — фикс для их устранения.


Примечание: Хотя Microsoft рекомендует устанавливать последние CU как только они выходят, зачастую большинство компаний устанавливают только последний SP, а CU ставят только в случае, если в состав CU входит фикс для имеющейся на сервере проблемы. Мы согласуем данный процесс с закачиком в соответствии с его внутренними инструкциями, но всегда предлагаем вначале рассмотреть официальные рекомендации Microsoft.


Настраиваем оптимальные параметры использования памяти MS SQL Server и оптимальные параметры MaxDOP:


По умолчанию, MS SQL Server может динамически изменять требования к памяти на основе доступности системных ресурсов. По умолчанию параметр min server memory имеет значение 0, а параметр max server memory — значение 2 147 483 647 MБ. О выборе оптимальных параметров использования памяти MS SQL Server можно прочитать здесь.


Если MS SQL Server работает на многопроцессорном компьютере, он определяет оптимальную степень параллелизма, то есть количество процессоров, задействованных для выполнения одной инструкции, для каждого из планов параллельного выполнения. Для ограничения количества процессоров в плане параллельного выполнения может быть использован параметр max degree of parallelism. О выборе оптимальных параметров MaxDOP можно прочитать здесь.


Используем при необходимости trace flags:


Флаги трассировки в MS SQL Server являются своеобразными «переключателями» поведения сервера с заданного по умолчанию на другое. Информацию о флагах трассировки можно найти здесь.


Оптимизируем настройки базы “TempDB” и других системных баз:


В MS SQL Server входят следующие системные базы данных:


• “master” — в этой БД хранятся все данные системного уровня для экземпляра MS SQL Server;
• “msdb” — используется агентом MS SQL Server для планирования предупреждений и задач;
• “model” — используется в качестве шаблона для всех баз данных, создаваемых в экземпляре MS SQL Server. Изменение размера, параметров сортировки, модели восстановления и других параметров базы данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения;
• “Resource” — база данных только для чтения. Содержит системные объекты, которые входят в состав MS SQL Server. Системные объекты физически хранятся в базе данных Resource, но логически отображаются в схеме sys любой базы данных;
• “TempDB” — рабочее пространство для временных объектов или взаимодействия результирующих наборов.


Рекомендациия для настройки оптимальной производительности базы данных “TempDB” можно найти здесь.


Корректно настраиваем параметры дефолтных расположений дата-файлов/лог-файлов:


Когда новая БД создается в MS SQL Server без явного указания расположения для дата-файла/ов и лог-файла, то MS SQL Server создает эти файлы в дефолтном расположении. Данное дефолтное расположение настраивается при установке MS SQL Server. О настройки параметров дефолтных расположений дата-файлов/лог-файлов можно прочитать здесь.


Оптимизируем настройки Windows Server


Используем оптимальные настройки дисковой подсистемы (быстрые накопители SSD, отформатированные с размером кластера 64К):


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


Настраиваем “Мгновенную инициализацию файлов базы данных”:


В MS SQL Server файлы данных могут быть инициализированы мгновенно. Мгновенная инициализация файлов освобождает место на диске, не заполняя пространство нулями. Вместо этого содержимое диска перезаписывается, поскольку в файлы записываются новые данные. Файлы журналов не могут быть инициализированы мгновенно. Подробности – здесь.


Используем разные сетевые интерфейсы для “пользовательской” и для “системной” нагрузок:


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


Оптимизируем настройки пользовательских баз данных



Источник


Проверяем, чтобы параметры “Auto Shrink” и “Auto Close” были выключены:


“Auto Shrink” (Автоматическое сжатие) указывает, что MS SQL Server будет периодически сжимать файлы базы данных (более подробно здесь).
“Auto Close” (Автоматическое закрытие) указывает, что база данных будет закрыта после освобождения всех ее ресурсов и отсоединения всех пользователей (более подробно здесь).


Проверяем, чтобы параметры “Auto Create Statistics” и “Auto Update Statistics” были включены:


Если включен параметр “Auto Create Statistics” (Автоматическое создание статистики), то оптимизатор запросов в случае необходимости создает статистику по отдельным столбцам в предикате запроса, чтобы улучшить оценку количества элементов для плана запроса (более подробно здесь).


Если включен параметр “Auto Update Statistics” (Автоматическое обновление статистики), то оптимизатор запросов определяет, когда статистика может оказаться устаревшей, и обновляет ее, если она используется в запросе (более подробно здесь).


Используем при необходимости “Read Committed Snapshot Isolation”:


Термин “Snapshot” («Моментальный снимок») отражает тот факт, что все запросы в транзакции обнаруживают одинаковую версию, или моментальный снимок базы данных, который соответствует состоянию базы данных в момент начала транзакции. Транзакция моментального снимка не требует блокировок базовых строк или страниц данных, что позволяет выполнять другую транзакцию без ее блокировки предыдущей незавершенной транзакцией. Транзакции, изменяющие данные, не блокируют транзакции, в которых происходит чтение данных, а транзакции, считывающие данные, не блокируют транзакции, в которых происходит запись данных, что обычно также наблюдается при использовании уровня изоляции “Read Committed”, заданного по умолчанию в MS SQL Server. Применение такого подхода, предусматривающего отказ от блокировок, способствует значительному снижению вероятности взаимоблокировок в сложных транзакциях.


Включение параметра “Read Committed Snapshot Isolation” обеспечивает доступ к версиям строк из под дефолтного уровня изоляции “Read Committed”. Если параметр “Read Committed Snapshot Isolation” установлен в значение OFF, то для получения доступа к версиям строк потребуется явно задавать уровень изоляции моментального снимка для каждого сеанса (более подробно здесь).


Проверяем, чтобы параметр “Page Verify” была выставлена в “CHECKSUM”:


Если для параметра базы данных “Page Verify” указано значение “CHECKSUM”, то MS SQL Server рассчитывает контрольную сумму для содержимого страницы в целом и сохраняет значение в заголовке страницы при записи страницы на диск. При считывании страницы с диска контрольная сумма вычисляется повторно и сравнивается со значением из заголовка. Это помогает обеспечить высокий уровень целостности данных в файлах (более подробно здесь).


Оптимально управляем дата-файлом/ами и лог-файлом БД


Дата-файл/ы и лог-файл БД размещаем на отдельных физических дисках:


Размещение файлов данных и файлов журнала на одном устройстве может привести к состязанию, что вызовет снижение производительности. Размещение файлов на разных дисках позволяет выполнять операции ввода-вывода для файлов данных и файлов журнала параллельно (более подробно здесь).


Создаем только один лог-файл БД:


Лог-файл используется MS SQL Server последовательно, а не параллельно, и нет никакого выигрыша по производительности иметь несколько лог-файлов (более подробно здесь).


Не допускаем появления фрагментации “Виртуального лог-файла (VLF)” БД:


Лог-файл БД внутренне разделен на разделы, именуемые виртуальными лог-файлами (Virtual Log Files – VLF), и чем выше фрагментация в лог-файле, тем больше число VLF. После того, как число VLF в лог-файле превысит 200, может ухудшиться производительность связанных с лог-файлом операций, таких как чтение лог-файла (скажем, для транзакционной репликации/отката), резервное копирование лог-файла и т.п. (более подробно здесь).


Выбираем корректные начальные размеры дата-файла/ов и лог-файла БД:


При создании базы данных файлы данных следует делать как можно большего размера, в соответствии с наибольшим предполагаемым объемом данных в базе данных. Например, если мы знаем, что сейчас у нас данных будет 50 ГБ, а через полгода добавится еще 50 ГБ, то начальный размер дата-файла лучше сразу сделать равным 100 ГБ (более подробно здесь).


Выбираем корректные параметры “Авто-роста” для дата-файла/ов и лог-файла БД:


Не рекомендуется использовать “Авто-рост” в процентах, так как, если размер файлов БД большой, то сам процесс увеличения базы может вызвать существенное снижение производительности, поэтому более предпочтительным является увеличение базы на фиксированный размер в МБ (более подробно здесь).


Постоянно отслеживаем размеры дата-файла/ов и лог-файла БД и при необходимости проактивно их увеличиваем во время минимальной нагрузки БД:


В производственной системе функцию “Авто-роста” следует использовать только как средство увеличения размера файлов в чрезвычайных обстоятельствах. Не рекомендуется использовать ее для повседневного управления ростом файлов данных БД. Для наблюдения за размерами файлов и их заблаговременного увеличения обычно используют оповещения или программы мониторинга. Это позволяет избежать фрагментации и перенести выполнение этих операций по обслуживанию на часы, когда нагрузка минимальна (более подробно здесь).


Оптимально обслуживаем БД



Источник


Выполняем проверку целостности данных БД:


Проверяем логическую и физическую целостность всех объектов в базе данных путем выполнения следующих операций (более подробно здесь):


• выполнение инструкции DBCC CHECKALLOC для базы данных;
• выполнение инструкции DBCC CHECKTABLE для каждой таблицы и каждого представления в базе данных;
• выполнение инструкции DBCC CHECKCATALOG для базы данных;
• проверка содержимого каждого индексированного представления в базе данных;
• проверка согласованности между файлами и директориями файловой системы и метаданными таблицы на уровне ссылок при хранении данных varbinary(max) в файловой системе с помощью FILESTREAM;
• проверка данных компонента Service Broker в базе данных.


Выполняем кастомный index rebuild/reorganize в зависимости от фрагментации индексов:


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


Обновляем статистику:


По умолчанию оптимизатор запросов обновляет статистику по мере необходимости для усовершенствования плана запроса. Обновление статистики гарантирует, что запросы будут компилироваться с актуальной статистикой. Однако обновление статистики вызывает перекомпиляцию запросов. Рекомендуется не обновлять статистику слишком часто, поскольку необходимо найти баланс между выигрышем в производительности за счет усовершенствованных планов запросов и потерей времени на перекомпиляцию запросов. Критерии выбора компромиссного решения зависят от приложения (более подробно здесь).


Не используем никакие “плохие” практики, например, такие как “регулярное сжатие”:


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


При необходимости организовываем регулярную очистку БД от “старых” данных:


Зачастую компании должны хранить данные в течение какого-то времени, чтобы соответствовать требованиям действующего законодательства и своим внутренним требованиям. После того как данные становятся не нужны – обычно рекомендуется их удалять, что позволяет повысить производительность MS SQL Server и дает возможность более точно предсказывать возможный рост требований к серверному оборудованию (более подробно здесь).


Осуществляем оптимальное резервное копирование БД



Источник


Определяем лучшую стратегию бэкапирования БД в соответствии с требованиями заказчика по RTO/RPO и лучшими мировыми практиками:


MS SQL Server обеспечивает необходимую защиту важных данных, которые хранятся в базах данных. Чтобы минимизировать риск необратимой потери данных, необходимо регулярно создавать резервные копии баз данных, в которых будут сохраняться производимые изменения данных. Хорошо продуманная стратегия резервного копирования и восстановления защищает базы от потери данных при повреждениях, происходящих из-за различных сбоев (более подробно здесь).


Выполняем регулярное тестовое восстановление бэкапов БД:


Можно сказать, что стратегия восстановления отсутствует, пока резервные копии не протестированы. Очень важно полностью протестировать стратегию резервного копирования для каждой базы данных, восстанавливая копию базы данных в тестовую систему. Необходимо протестировать восстановление каждого типа резервной копии, которую планируется использовать (более подробно здесь).


Используем оптимальную технологию высокой доступности БД (в зависимости от требований)


Always On Failover Cluster Instances:


Экземпляры отказоустойчивой кластеризации AlwaysOn используют функциональные возможности отказоустойчивой кластеризации Windows Server (WSFC) для обеспечения высокого уровня доступности локальных ресурсов за счет избыточности на уровне экземпляра сервера — экземпляра отказоустойчивого кластера (FCI). Экземпляр отказоустойчивого кластера (FCI) является единственным экземпляром MS SQL Server, установленным на всех узлах отказоустойчивой кластеризации Windows Server (WSFC) и, возможно, в нескольких подсетях. Экземпляр отказоустойчивого кластера выглядит в сети как экземпляр MS SQL Server, запущенный на одном компьютере, но экземпляр отказоустойчивого кластера обеспечивает отработку отказа с переходом одного узла WSFC на другой узел, если текущий узел становится недоступным (более подробно здесь).


Always On availability groups:


Группы доступности AlwaysOn — это решение высокой доступности и аварийного восстановления, являющееся альтернативой зеркальному отображению баз данных (“database mirroring”). Группа доступности поддерживает отказоустойчивую среду для набора пользовательских баз данных, известных как базы данных доступности, которые совместно выполняют переход на другой ресурс. Группа доступности поддерживает набор первичных баз данных для чтения/записи и от одного до восьми наборов соответствующих вторичных баз данных. Кроме того, вторичные базы можно сделать доступными только для чтения и/или для некоторых операций резервного копирования (более подробно здесь).


Database mirroring:


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


Log shipping:


MS SQL Server позволяет автоматически отправлять резервные копии журналов транзакций из базы данных-источника экземпляра сервера-источника в одну или более баз данных-получателей других экземпляров сервера-получателя. Резервные копии журналов транзакций применяются к каждой из баз данных-получателей индивидуально (более подробно здесь).


Осуществляем постоянный мониторинг состояния сервера (MS SQL + Windows)


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



Источник


Постоянная оценка производительности базы данных помогает добиться оптимальной производительности путем минимизации времени ответа и максимального увеличения пропускной способности. Приблизительный сетевой трафик, дисковый ввод-вывод и загрузка ЦП — ключевые факторы, влияющие на производительность. Следует тщательно проанализировать требования приложения, понять логическую и физическую структуру данных, оценить использование базы данных и добиться компромисса между такими конфликтующими нагрузками, как оперативная обработка транзакций (OLTP) и поддержка решений (более подробно здесь).

Комментарии (10)


  1. speshuric
    24.08.2017 10:48
    +1

    Вопросы к вашим пунктам.


    1. Чем в вашем контексте отличается процесс настройки и сами настройки "обычной" БД от "облачной"? Из статьи не видно.
    2. О выборе оптимальных параметров использования памяти MS SQL Server можно прочитать здесь. — там почти только о том как настроить, но не какое значение выбрать.
    3. О выборе оптимальных параметров MaxDOP можно прочитать здесь. — там почти только о том как настроить, но не какое значение выбрать. Не засвечена связанная настройка cost threshold for parallelism.
    4. trace flags: Так какие и почему флаги используете? Просто кинуть "а вон, ищи среди пары сотен нужные" это как-то даже некультурно для статьи.
    5. tempdb: Почему вы приводите ссылку на "пенсионерские" рекомендации для SQL Server 2008 R2? Её ж скоро уже с поддержки снимут. И эти рекомендации не работают при 64+ процессорах (а это уже двухсокетник AMD Epyc). Вообще по всей статье сплошные отсылки к рекомендациям 10+ лет.
    6. Как вы боретесь с узкими местами в tempdb? Если у вас 50-60 клиентов начнут её активно использовать явно или неявно (udf или неудачный план могут очень неявно её грузить), что нельзя запретить, то именно tempdb обычно становится главным узким местом.
    7. "Корректно настраиваем параметры дефолтных расположений дата-файлов/лог-файлов" — так какие параметры корректные и почему?
    8. Disk Partition Alignment — Эмм… Вы сами-то смотрели, когда это в последний раз было актуально, и какая версия windows стала сама нормально форматировать?
    9. 64 КБ кластер всегда и для всех? Хм. Это требует обоснования.
    10. Вообще по настройкам дисковой подсистемы очень неплохо начать с Microsoft SQL Server Best Practices And Design Guidelines For EMC Storage — даже если у вас не EMC. Просто чтобы понять что настраивать и в какую сторону дальше читать. Если бы все админы СХД перед тем как общаться с DBA читали это, то это бы сэкономило пару ГЭС электроэнергии и миллиардов долларов.
    11. Настраиваем "Мгновенную инициализацию файлов базы данных" — что вы делаете, чтобы она не включилась?
    12. RCSI — как вы боретесь с тем, что RCSI потенциально делает tempdb сервера узким местом?
    13. Page Verify — еще одна рекомендация прошлого века. Если БД создавалась не 20 лет назад, то она будет CHECKSUM. А если и Torn pages, то клиент сам себе злобный буратин в случае поломки. По "Page Verify", Auto Shrink, Auto Close, Auto Create Statistics и Auto Update Statistics вам проще было сразу в статью запрос написать. Ну или вообще отправить к Бренту Озару или даже сразу сюда
    14. "Дата-файл/ы и лог-файл БД размещаем на отдельных физических дисках". Там тема не столько про распараллеливание, сколько про то, что журнал транзакций это критичная по скорости write-ahead последовательная запись на диск и любая другая дисковая активность превращает её из последовательной в произвольную с увеличением latency примерно на порядок на дисках. На SSD это не всегда так (см выше статеечку от EMC), но пока потенциальный выигрыш от этого пока, наверное, выше, чем затраты на управление.
    15. "Выбираем корректные начальные размеры дата-файла/ов и лог-файла БД", "Выбираем корректные параметры “Авто-роста” для дата-файла/ов и лог-файла БД" — а какие именно настройки корректные для облачных БД и почему?
    16. "Выполняем проверку целостности данных БД" — как вы это делаете для облачной БД? Как выбираете окно и как делаете, что это не влияет на остальные БД?
    17. "Выполняем кастомный index rebuild/reorganize в зависимости от фрагментации индексов" — каким инструментом? Чем это отличается от скриптов Ola Hallengren ?
    18. "Обновляем статистику". Неудачное объяснение. Статистику приходится обновлять явно в первую очередь для того, чтобы не попасть на обновление статистики в "горячее время", во вторую, чтобы обновлённая статистика дала потенциально лучший план запроса. Но если вы обслуживаете индексы, если у вас есть autoupdate statistics и отдельные БД не высоконагруженные (а ваши настройки в целом не высоконагруженные БД), то что вы обновляете, зачем и в каком режиме?
    19. Про "очистку БД от “старых” данных" у меня 2 вопроса. Первый — это что вы в данные клиента вот так лезете прям в рамках облачного администрирования? Второй — а почему не писать запросы так, чтобы данные можно было и за 10 лет хранить? Не сваливайтесь в сканы и делов-то.
    20. Резервное копирование: Как вы уходите от деградации произвдительности во время резервного копирования? Какую модель реально и почему используете? Как в эту модель ложатся RPO/RTO? Как обеспечивается хранение копий (хотя бы в скольки экземплярах и где)? И, да, как написано в этой статье
      Резервное копирование — это последний рубеж обеспечения сохранности системы. Если администратору базы данных приходится восстанавливать продуктовую систему из резервных копий, значит, с большой вероятностью было допущено множество грубых ошибок.
    21. Выполняем регулярное тестовое восстановление бэкапов БД. Ок. И как вы планируете реагировать, если резервная копия битая?
    22. Почему в одной статье вы одновременно используете и Database mirroring и AlwaysOn AAG? Как вы между ними выбор делаете?
    23. Как вы используете Log shipping в облачной БД? Вы что клиенту прямо файловые ресурсы отдаёте?
    24. Кластеры — как используете? Есть ли дизастероустойчивость (если ЦОД дохнет весь), обеспечивается ли при этом RPO/RTO и какие? Если такие кластера есть, то как живете с задержками журналов между ЦОДами и какие они у вас в абсолютных цифрах.
    25. Осуществляем постоянный мониторинг состояния сервера. Всё тот же вопрос. Какие именно показатели, какие именно счетчики собираете и как трактуете? Особенно в ситуации "много потребителей, много ресурсов".

    В статье не отражено:


    1. Проверка настроек или настройки безопасности. Банально: xp_cmdshell запрещен, OLE запрещен, CLR запрещен? Логины AD или SQL (в обоих случаях еще куча вопросов)?
    2. Используете ли именованные экземпляры? Как делаете чтобы они не влияли друг на друга? Настраиваете ли у них affinity или еще как? Доступен ли сервису Lock pages in memory и почему (есть и за и против).
    3. Используете ли штатный Resource Governor? С какими настройками? Как вообще клиентов по нагрузке разводите? Особенно в динамике, и по дискам и по памяти и по блокировкам/латчам?
    4. Если испольуете "свежие" версии SQL, то как быть с БД старых уровней совместимости?
    5. Безопасность в смысле "чтобы клиенты друг друга не видели" — как обеспечить?
    6. SSAS, SSRS предоставляете? Как рулите?
    7. Как у вас реально HADR сделан на 99,95%?
    8. Какие инструменты есть у клиента, чтобы диагностировать нагрузку? Может ли он хотя бы посмотреть "кто сейчас к моей БД подключен"? И как вы ему это дали не давая View server state? Какие dmv он может смотреть, а какие фигушки?
    9. Какой опыт настроек позаимствовали от Azure, а в чем отличаетесь и почему надо идти к вам, а не в Azure?
    10. Настраиваете ли вы Delayed Transaction Durability горячим промежуточным БД?
    11. Есть ли политика хранения BLOBов в БД? Даёте ли FileStream на них? Как живёте с этим?

    Я выдохся. Вопросы еще есть, а статья ни о чем.


    1. Daar
      24.08.2017 23:34

      Благодарю за труд, интересно :)


      1. TS_Cloud Автор
        25.08.2017 09:34

        Daar, большое спасибо! будем стараться и дальше. В следующем материале планируем максимально детально разложить услугу «Облачная база данных».


        1. Daar
          25.08.2017 12:14

          Да я честно говоря спасибо сказал speshuric, он много правильного озвучил, и о котором я даже и не задумывался :) А так с ним согласен, что как-то все поверхностно, по «вершкам» пробежались и все. Статья больше для какого-то журнала типа ЦОД или подобных, а на Хабре ожидаются какие-то тесты, сравнения, решения на крови.


          1. TS_Cloud Автор
            25.08.2017 13:38

            Daar, вас поняли, будем исправляться. На вопросыspeshuric ответим.


  1. TS_Cloud Автор
    24.08.2017 12:33

    speshuric, рекомендуемые нами настройки как для «облачных», так и для «не-облачных» баз обычно совпадают (применительно к MS SQL Server), исключения могут составлять сервисы вида AWS RDS, где очень урезаны возможности конечного пользователя относительно настроек SQL сервера. В статье мы рассказали не только о том, как мы инсталлируем SQL server, но и как оптимально его настраиваем и поддерживаем в соответствии с потребностями заказчика.
    Нашей целью было коротко пройтись по «узким местам» настройки SQL Server и указать на наиболее оптимальные настройки и механизмы обеспечения производительности. Если начать писать о том, как все настраивать, то объем статьи легко вырастет до “SQL Server Books Online". Кроме того, для основной части настроек существуют только общие рекомендации, причем зачастую нет официальных данных от Microsoft, и для каждого конкретного случая надо принимать свои решения.
    Нам кажется, что имеет смысл написать отдельные статьи на очень многие вопросы, которые вы затронули. Что скажете?


    1. speshuric
      24.08.2017 21:15

      Позволю себе не согласиться с "рекомендуемые нами настройки" и "В статье мы рассказали". Я, собственно, и удивлён, что в статье нет почти ни одной рекомендации о конкретных настройках. Сплошные "надо настроить параметр такой-то", а как его настраивать — не скажем. По ссылкам тоже в основном "чтобы настроить параметр X выполните команду ALTER X Y, где Y новое значение".
      В интернете полно рекомендаций, как настраивать standalone БД. Есть получше, есть похуже, но полно. Не надо делать еще одну статью об этом (или надо, но не этого уровня).


      Вы назвали статью "Как обеспечить производительность баз данных Microsoft SQL Server, размещаемых в облаке". Я прочитал вкратце на вашем сайте, что вы под этим имеете в виду (а это не так однозначно): под облачной БД вы подразумеваете ВМ с развернутыми службами MS SQL Server на котором развернута БД (например так описано тут) и фактически вы являетесь администраторами/DBA для этой ВМ. То есть сразу видно, что вопросы взаимного влияния баз решаются не внутри инстанса SQL Server, а разведением ВМ. Не самая худшая стратегия, хотя большинство гипервизоров достаточно уныло разделяют IO нагрузку. Но вопрос остаётся актуальным "Как обеспечить производительность баз данных Microsoft SQL Server, размещаемых в облаке"?
      Как настроить обычную БД понятно, а какие отличия в настройках виртуализованных больших ферм с БД?
      Как вы делите диски (с учетом того, что они вирутальные)?
      Используете ли thin pools/LUNs и т.п.? Если да, то как боретесь с latency LDF, сколько она сейчас у вас в миллисекундах? Если нет, то как вы управляетесь с сотнями LUN хитро разбитыми по задачам и как обеспечиваете отсутствие взаимного влияния инстансов ВМ? Какие носители для какой нагрузки используете?
      Как и где хрянятся бэкапы, как обеспечиваете RPO/RTO без влияния на производительность?
      Как с вашими планами обслуживания (перестроение индексов, DBCC CHECKs) вы обеспечиваете 99,95% (это 4 часа недоступности в год). Или вы считаете, что если инстанс пингуется, но таблица в 100 ГБ ребилдит индексы не online, то это тоже доступность?
      Как обеспечиваете HADR с точки зрения производительности? Это совсем не просто. Что в ваших терминах значат "Отказоустойчивая база данных", "База данных повышенной доступности", "Кластерное решение"?
      Используете ли Lock pages in memory (если да, то страдает гипервизор, если нет, то страдает БД)?
      У вас не так много видов ВМ, всего 5 (по vCPU/vRAM) — какая стандартная настройка предела памяти для каждого вида и сколько файлов tempdb, какой MaxDOP по каждому варианту? Такая табличка будет ценнее всей статьи.


      Я не зря ссылку на h14621 кидал в комментарии. Мне очень нравятся технические white-papers от EMC именно как образец того, что нужно учесть при планировании БД. Кроме общего у них еще куча white-papers "частных случаев" есть, мне лень искать более подходящий, но там прям кладезь знаний. Есть ли у вас что-то что можно добавить хотя бы к этому документу? Если есть — это очень интересно и важно. Пусть это и будет не "быстро и коротко", а "глубоко по одному аспекту".


  1. TS_Cloud Автор
    25.08.2017 17:06

    speshuric, прежде чем ответить, хотим вас поблагодарить (кажется, мы этого еще не делали) за то, что потратили столько времени на анализ нашего поста и вообще на нас. Это очень полезная для нас критика и важное внимание. Мы постарались ответить на основные, на наш взгляд, поставленные вами вопросы.
    Как вы делите диски (с учетом того, что они виртуальные)?
    Ограничиваем производительность дисков, управляя SLA на уровне СХД.
    Используете ли thin pools/LUNs и т.п.? Если да, то как боретесь с latency LDF, сколько она сейчас у вас в миллисекундах? Если нет, то как вы управляетесь с сотнями LUN хитро разбитыми по задачам и как обеспечиваете отсутствие взаимного влияния инстансов ВМ? Какие носители для какой нагрузки используете?
    Нет, не используем. Для баз данных наших заказчиков мы используем производительную систему хранения данных Dell SC9000 c полками Dell SC420 на SSD дисках.
    Как и где хрянятся бэкапы, как обеспечиваете RPO/RTO без влияния на производительность?
    Бекапы хранятся на выделенной СХД. Базы данных и логи баз данных хранятся на другой выделенной СХД.
    Как обеспечиваете HADR с точки зрения производительности? Это совсем не просто. Что в ваших терминах значат «Отказоустойчивая база данных», «База данных повышенной доступности», «Кластерное решение»?
    Ответим на примере MS SQL
    Отказоустойчивая база данных
    Предоставляется одна система с необходимыми характеристиками. Резервное копирование выполняется системой резервного копирования Commvault для надёжного и долговременного хранения. Резервируются файлы базы данных и журналы транзакций, которые сохраняются в течение 7 дней. В течение этого периода можно восстановить состояние базы данных по состоянию на момент последнего резервирования журнальных файлов (производится каждый час). Время восстановления сервиса в случае потери базы данных зависит от объёма базы данных и интенсивности её использования. В случае аппаратного сбоя наша система автоматически обеспечит замену системы в течение нескольких минут.
    База данных повышенной доступности (Always On Availability Groups)
    Предоставляется две системы с необходимыми характеристиками. На одной из машин располагается основной экземпляр, на второй располагается резервный. В случае выхода из строя основной базы данных происходит переключение на резервную базу данных. Время восстановления сервиса в случае потери базы данных не превышает 15 минут. Резервное копирование выполняется системой резервного копирования Commvault для надёжного и долговременного хранения. Резервируются файлы базы данных и журналы транзакций, которые сохраняются в течение 7 дней. В течение этого периода можно восстановить состояние базы данных по состоянию на момент последнего резервирования журнальных файлов (производится каждый час). Время восстановления сервиса в случае потери базы данных зависит от объёма базы данных и интенсивности её использования. В случае аппаратного сбоя наша система автоматически обеспечит замену системы в течение нескольких минут.
    Гео-кластерное решение (Failover Clustering и Always On Availability Groups). Данная опция в разработке. Плановый срок ноябрь 2017г.
    Предоставляется три выделенных системы с необходимыми характеристиками. Две машины объединяются в кластер и обслуживают работу с основной базой данных. Третья машина работает с резервной базой данных. В случае выхода из строя одного из узлов кластера, сервис поднимается на втором узле. В случае выхода из строя основной базы данных происходит переключение на резервную. Сервис остаётся доступным в случае выхода из строя любого из трёх хостов. Время восстановления сервиса в случае потери базы данных не превышает 15 минут. Резервное копирование выполняется системой резервного копирования Commvault для надёжного и долговременного хранения. Резервируются файлы базы данных и журналы транзакций, которые сохраняются в течение 7 дней. В течение этого периода можно восстановить состояние базы данных по состоянию на момент последнего резервирования журнальных файлов (производится каждый час).
    Используете ли Lock pages in memory (если да, то страдает гипервизор, если нет, то страдает БД)?
    Для не misson-critical БД — нет, не используем. Нет, БД не страдает. Для mission-critical БД – да, используем. При этом обязательно выставляем соответствующий параметр max SQL server memory (как и для не mission-critical баз) и, соответственно, настраиваем гипервизор, чтобы не страдал :).
    Как с вашими планами обслуживания (перестроение индексов, DBCC CHECKs) вы обеспечиваете 99,95% (это 4 часа недоступности в год). Или вы считаете, что если инстанс пингуется, но таблица в 100 ГБ ребилдит индексы не online, то это тоже доступность?
    Для расчета доступности сервиса используются данные системы мониторинга, контролирующей доступность каждого из нижеприведенных компонентов сервиса: хоста, базы данных, процессов прослушивания (Listener).
    Поэтому, да, если инстанс пингуется, то это доступность сервиса.
    При приходе нового заказчика мы выясняем его бизнес процессы, окна простоя и деградации производительности и настраиваем планы обслуживания в соответствии с бизнес ожиданиями заказчика.
    Естественно никто не будет ребилдить индексы бездумно. У нас есть пример ребилда 2 терабайтных индексов, и на это отводится время раз в месяц в соответствии с договоренностями с бизнес подразделениями Заказчика.
    Если же клиент ощутит нехватку производительности при плановых работах, то заводится инцидент и по нему начинается анализ.
    Если выяснится, что влияет именно DBCC CHECKDB, то возможны варианты развития событий, например, вынести CHECKDB на вторичную реплику в AAG; вынести CHECKDB на систему восстановленную из бекапа, делать CHECKDB не регулярно.
    Мы как DBA предлагаем наиболее приемлемый вариант из Best practices с описанием всех достоинств и недостатков, а клиент выбирает подходящий ему вариант.
    У вас не так много видов ВМ, всего 5 (по vCPU/vRAM) — какая стандартная настройка предела памяти для каждого вида и сколько файлов tempdb, какой MaxDOP по каждому варианту?

    Yes

    *Для типовой нагрузки со стороны приложения. В случае кастомных нагрузок – возможно увеличение.
    ** Для типовой нагрузки со стороны приложения. В случае кастомных нагрузок при использовании SQL server 2016 – возможно увеличение.


    1. speshuric
      25.08.2017 19:31

      О! Спасибо за прекрасный комментарий по существу. Этот комментарий, кажется, в несколько раз информативнее и лучше статьи :) Спасибо, что поделились опытом.
      На самом деле описанные решения разумные и обоснованные и абстрактную средненькую БД хостить с таким подходом не страшно. Только max server memory какой-то странный, но, я думаю, это опечатка и это параметр для RAM в ГБ указанного в поле количество CPU: странно выделять 29 ГБ из 512 на СУБД.


  1. TS_Cloud Автор
    28.08.2017 10:41

    speshuric, добрый день! Да, и правда, опечатались. Приносим свои извинения. Вот правильная таблица:

    Yes