Всем привет! Сегодня хотим поговорить об облачных базах данных, а точнее о тех проактивных и разовых мероприятиях, которые непосредственно обеспечивают их производительность.
Облачные базы данных давно решили вопрос быстрого прироста мощностей и запуска новых баз данных, и сегодня стали практически насущной потребностью компаний любого размера, благодаря переносу задач по администрированию и мониторингу на сторону провайдера.
Мы проделали большую работу, запуская и оптимизируя свою новую услугу облачной базы данных на собственной платформе Техносерв 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) и поддержка решений (более подробно здесь).
Автор: TS_Cloud
