Управление ресурсами в сжатых эластичных пулах

  • Статья

Область применения:База данных SQL Azure

Эластичные пулы Базы данных SQL Azure — это экономичное решение для управления множеством баз данных с различным уровнем использования ресурсов. Всем базам данных в эластичном пуле выделяются для совместного использования одни и те же ресурсы (ЦП, память, рабочие потоки, место в хранилище и база данных tempdb). Это происходит при условии, что только подмножество баз данных в пуле будет использовать вычислительные ресурсы в конкретный момент времени. Это допущение позволяет эластичным пулам снижать издержки. Вместо оплаты всех ресурсов, которые потенциально могут понадобиться каждой отдельной базе данных, клиенты платят за гораздо меньший набор ресурсов, совместно используемых всеми базами данных в пуле.

Управление ресурсами

Система должна тщательно контролировать совместное использование ресурсов, чтобы минимизировать эффект "шумного соседа", когда база данных с высоким потреблением ресурсов влияет на другие базы данных в этом же эластичном пуле. База данных SQL Azure достигает этих целей путем внедрения управления ресурсами. В то же время система должна предоставлять достаточно ресурсов для надежной работы функций высокой доступности и аварийного восстановления, резервного копирования, мониторинга, хранилища запросов, автоматической настройки и других.

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

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

Важно!

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

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

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

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

  • Настройте рабочую нагрузку запросов: сократите потребление ресурсов или распределите его между несколькими базами данных по времени.
  • Уменьшите плотность пула: переместите несколько баз данных в другой пул или сделайте их автономными.
  • Масштабируйте пул, чтобы получить дополнительные ресурсы.

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

Мониторинг потребления ресурсов

Чтобы избежать снижения производительности, клиенты, которые используют эластичные пулы высокой плотности, должны отслеживать потребление ресурсов и своевременно принимать меры, если возрастающая конкуренция за ресурсы начинает влиять на рабочие нагрузки. Непрерывный мониторинг важен, потому что использование ресурсов в пуле меняется со временем из-за изменений в рабочей нагрузке пользователей, объемах и распределении данных, плотности пула и Базе данных SQL Azure.

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

Чтобы отправить оповещение, когда использование ресурсов пула (ЦП, ввод-вывод данных, ввод-вывод журналов, рабочие роли и т. д.) превышает пороговое значение, рассмотрите возможность создания оповещений с помощью портала Azure или командлета PowerShell Add-AzMetricAlertRulev2. При мониторинге гибких пулов также можно создавать оповещения для отдельных баз данных в пуле, если это необходимо в вашем сценарии. Пример сценария мониторинга эластичных пулов см. в статье Мониторинг производительности Базы данных SQL Azure в мультитенантном приложении SaaS и управление ею.

Имя метрики Описание Рекомендуемое среднее значение
avg_instance_cpu_percent Использование ЦП процессом SQL, который связан с эластичным пулом. Значение измеряется в базовой операционной системе. Метрика доступна в представлении sys.dm_db_resource_stats в каждой базе данных, а также в представлении sys.elastic_pool_resource_stats в базе данных master. Эта метрика также отправляется в Azure Monitor, где она называетсяsql_instance_cpu_percent, и ее можно просмотреть на портале Azure. Это значение одинаково для всех баз данных в одном эластичном пуле. Менее 70 %. Допустимы случайные кратковременные пики до 90 %.
max_worker_percent Использование рабочего потока. Эта метрика предоставляет информацию для каждой базы данных в пуле, а также для всего пула. Ограничения на количество рабочих потоков на уровне базы данных и на уровне пула различаются, поэтому мы рекомендуем отслеживать показатели на обоих уровнях. Метрика доступна в представлении sys.dm_db_resource_stats в каждой базе данных, а также в представлении sys.elastic_pool_resource_stats в базе данных master. Эта метрика также отправляется в Azure Monitor, где она называетсяworkers_percent, и ее можно просмотреть на портале Azure. Менее 80 %. Пики до 100 % приведут к сбоям при попытках подключения и выполнении запросов.
avg_data_io_percent Использование операций ввода-вывода в секунду для чтения и записи физического ввода-вывода. Эта метрика предоставляет информацию для каждой базы данных в пуле, а также для всего пула. Ограничения на количество операций ввода-вывода в секунду на уровне базы данных и на уровне пула различаются, поэтому мы рекомендуем отслеживать показатели на обоих уровнях. Метрика доступна в представлении sys.dm_db_resource_stats в каждой базе данных, а также в представлении sys.elastic_pool_resource_stats в базе данных master. Эта метрика также отправляется в Azure Monitor, где она называетсяphysical_data_read_percent, и ее можно просмотреть на портале Azure. Менее 80 %. Допустимы случайные кратковременные пики до 100 %.
avg_log_write_percent Использование пропускной способности для операций ввода-вывода при записи в журнал транзакций. Эта метрика предоставляет информацию для каждой базы данных в пуле, а также для всего пула. Ограничения на пропускную способность журнала на уровне базы данных и на уровне пула различаются, поэтому мы рекомендуем отслеживать показатели на обоих уровнях. Метрика доступна в представлении sys.dm_db_resource_stats в каждой базе данных, а также в представлении sys.elastic_pool_resource_stats в базе данных master. Эта метрика также отправляется в Azure Monitor, где она называетсяlog_write_percent, и ее можно просмотреть на портале Azure. Если значение этой метрики близко к 100 %, все изменения базы данных (операторы INSERT, UPDATE, DELETE, MERGE, SELECT...INTO, BULK INSERT и т. д.) будут выполняться медленнее. Менее 90 %. Допустимы случайные кратковременные пики до 100 %.
oom_per_second Частота возникновения ошибок нехватки памяти в эластичном пуле. Метрика доступна в представлении sys.dm_resource_governor_resource_pools_history_ex. Ознакомьтесь с примером запроса для вычисления этой метрики. Дополнительные сведения см. в материалах об ограничениях ресурсов для эластичных пулов с использованием DTU или виртуальных ядер, а также в статье Устранение проблем нехватки памяти в Базе данных SQL Azure. При возникновении ошибок, связанных с нехваткой памяти, см. сведения в статье о sys.dm_os_out_of_memory_events. 0
avg_storage_percent Общий объем хранилища, который используется для всех баз данных эластичного пула. Этот показатель не учитывает пустое пространство в файлах баз данных. Метрика доступна в представлении sys.elastic_pool_resource_stats в базе данных master. Эта метрика также отправляется в Azure Monitor, где она называетсяstorage_percent, и ее можно просмотреть на портале Azure. Менее 80 %. Может приближаться к 100 % для пулов без увеличения объема данных.
avg_allocated_storage_percent Общий объем хранилища, который используется для всех файлов баз данных эластичного пула. Этот показатель учитывает пустое пространство в файлах баз данных. Метрика доступна в представлении sys.elastic_pool_resource_stats в базе данных master. Эта метрика также отправляется в Azure Monitor, где она называетсяallocated_data_storage_percent, и ее можно просмотреть на портале Azure. Менее 90 %. Может приближаться к 100 % для пулов без увеличения объема данных.
tempdb_log_used_percent Использование пространства журнала транзакций в базе данных tempdb. Временные объекты, созданные в одной базе данных, не видны в других базах данных в том же эластичном пуле. Но при этом tempdb является общим ресурсом для всех баз данных одного пула. Длительная или потерянная транзакция в tempdb, запущенная из одной базы данных пула, может использовать большую часть журнала транзакций и вызывать сбои запросов в других базах данных этого пула. Метрика является производной от представлений sys.dm_db_log_space_usage и sys.database_files. Эта метрика также отправляется в Azure Monitor, и ее можно просмотреть на портале Azure. Ознакомьтесь с примером запроса, который возвращают текущее значение этой метрики. Менее 50 %. Допустимы случайные пики до 80 %.

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

Имя представления Описание
sys.dm_user_db_resource_governance Представление возвращает фактические параметры конфигурации и емкости, которые используются механизмами управления ресурсами в текущей базе данных или эластичном пуле.
sys.dm_resource_governor_resource_pools Представление возвращает информацию о текущем состоянии пула ресурсов, его текущую конфигурацию и совокупную статистику.
sys.dm_resource_governor_workload_groups Представление возвращает совокупную статистику и текущую конфигурацию группы рабочей нагрузки. Это представление можно объединить с sys.dm_resource_governor_resource_pools в столбце pool_id, чтобы получать информацию о пуле ресурсов.
sys.dm_resource_governor_resource_pools_history_ex Возвращает статистику по использованию пула ресурсов за последнее время на основе количества доступных моментальных снимков. Каждая запись представляет интервал времени. Длительность интервала представлена в столбце duration_ms. Столбцы delta_ возвращают изменения в каждой статистике на протяжении этого интервала.
sys.dm_resource_governor_workload_groups_history_ex Возвращает статистику по использованию группы рабочих нагрузок за последнее время на основе количества доступных моментальных снимков. Каждая запись представляет интервал времени. Длительность интервала представлена в столбце duration_ms. Столбцы delta_ возвращают изменения в каждой статистике на протяжении этого интервала.

Совет

Чтобы выполнять запросы к этим и другим динамическим представлениям управления от имени любого субъекта, кроме администратора сервера, добавьте этот субъект в роль сервера##MS_ServerStateReader##.

Эти представления позволяют отслеживать использование ресурсов и устранять проблемы с конкуренцией за них практически в реальном времени. Пользовательская рабочая нагрузка на первичной реплике и вторичной реплике для чтения, включая геореплики, классифицируется в пул ресурсов SloSharedPool1 и группу рабочей нагрузки UserPrimaryGroup.DBId[N], где N — это значение идентификатора базы данных.

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

Практические рекомендации

Сохраните достаточный запас ресурсов. Как отмечалось выше, когда возникает конкуренция за ресурсы и снижается производительность, для устранения рисков можно переместить некоторые баз данных из проблемного эластичного пула или масштабировать это пул. Но для выполнения этих действий требуются дополнительные вычислительные ресурсы. Для пулов уровня "Премиум" и "Критически важный для бизнеса" нужно будет перенести все данные для перемещаемых баз данных. Если же пул масштабируется, перенос потребуется для всех баз данных эластичного пула. Передача данных — это длительная и ресурсоемкая операция. Если пул уже находится в условиях существенной нехватки ресурсов, сами действия по устранению рисков будут еще больше снижать производительность. В крайних случаях может сложиться ситуация, когда вы не сможете решить проблему конкуренции за ресурсы с помощью перемещения базы данных или масштабирования пула, поскольку необходимые ресурсы будут недоступны. В такой ситуации единственным решением может быть временное снижение рабочей нагрузки запросов на проблемный эластичный пул.

Клиенты, которые применяют пулы высокой плотности, должны внимательно следить за тенденциями использования ресурсов, как это описано выше. Меры по устранению рисков нужно принимать, пока метрики остаются в рекомендуемых диапазонах и в эластичном пуле достаточно ресурсов.

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

Примечание.

Для эластичных пулов DTU метрика eDTU на уровне пула не является максимумом (MAX) или суммой (SUM) использования отдельных баз данных. Она вычисляется на основе использования различных показателей на уровне пула. Ограничения на ресурсы на уровне пула могут быть выше, чем для отдельных баз данных, поэтому в отдельных базах данных может быть достигнут определенный предел (ЦП, ввод-вывод данных, ввод-вывод журнала и т. д.), даже если отчеты eDTU показывают, что предел для пула не достигнут.

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

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

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

  • Переместите несколько баз данных из пула, чтобы уменьшить общее выделенное пространство.
  • Используйте сжатие файлов базы данных, чтобы уменьшить объем пустого выделенного пространства в файлах.
  • Масштабируйте пул до нужных целей обслуживания с увеличенным максимальным размером пула.

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

  • Переместите несколько баз данных из пула, чтобы уменьшить общее используемое пространство.
  • Переместите (заархивируйте) данные за пределы базы данных или удалите данные, которые больше не нужны.
  • Примените сжатие данных.
  • Масштабируйте пул до нужных целей обслуживания с увеличенным максимальным размером пула.

Избегайте серверов с чрезмерной плотностью. База данных SQL Azure поддерживает до 5000 баз данных на один сервер. Клиенты, которые используют эластичные пулы с тысячами баз данных, могут разместить нескольких эластичных пулов на одном сервере с общим количеством баз данных в пределах допустимых ограничений. Но серверы с тысячами баз данных создают проблемы при эксплуатации. Будут выполняться медленнее операции, для которых требуется перебор всех баз данных на сервере, например для просмотра баз данных на портале. Ошибки при эксплуатации, например неправильная модификация правил брандмауэра или имен для входа на уровне сервера, повлияют на большее количество баз данных. При случайном удалении сервера потребуется помощь службы поддержки Майкрософт, чтобы восстановить базы данных на этом сервере. Это приведет к длительному отключению всех затронутых баз данных.

Ограничьте количество баз данных на одном сервере, чтобы оно было меньше, чем максимально поддерживаемое. Для большинства сценариев оптимальным будет использование до 1000–2000 баз данных на один сервер. Чтобы снизить вероятность случайного удаления сервера, настройте блокировку удаления для сервера или его группы ресурсов.

Примеры

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

В динамическом административном представлении sys.dm_user_db_resource_governance можно просмотреть фактические параметры конфигурации и емкости, используемые управлением ресурсами в текущей базе данных или эластичном пуле. Дополнительные сведения см. в sys.dm_user_db_resource_governance.

Запустите этот запрос в любой базе данных в эластичном пуле. Все базы данных в пуле имеют одинаковые параметры управления ресурсами.

SELECT * FROM sys.dm_user_db_resource_governance AS rg
WHERE database_id = DB_ID();

Мониторинг общего потребления ресурсов эластичного пула

Используйте представление каталога системы sys.elastic_pool_resource_stats для отслеживания потребления ресурсов всем пулом. Дополнительные сведения см. в sys.elastic_pool_resource_stats.

Этот пример запроса для просмотра последних 10 минут следует выполнить в базе данных master логического сервера Azure SQL, содержащего нужный эластичный пул.

SELECT * FROM sys.elastic_pool_resource_stats AS rs
WHERE rs.start_time > DATEADD(mi, -10, SYSUTCDATETIME()) 
AND rs.elastic_pool_name = '<elastic pool name>';

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

Используйте динамическое административное представление sys.dm_db_resource_stats для отслеживания потребления ресурсов отдельными базами данных. Дополнительные сведения см. в sys.dm_db_resource_stats. Каждые 15 секунд создается запись, даже если в базе данных не выполняется никаких действий. Хранятся данные за период длительность около одного часа.

Этот пример запроса для просмотра последних 10 минут данных следует выполнить в нужной базе данных.

SELECT * FROM sys.dm_db_resource_stats AS rs
WHERE rs.end_time > DATEADD(mi, -10, SYSUTCDATETIME());

Для более длительного хранения с меньшей частотой можно использовать следующий запрос к sys.resource_stats, запустив его в базе данных master логического сервера Azure SQL. Дополнительные сведения см. в sys.resource_stats (База данных SQL Azure). Каждые пять минут создается одна запись, а данные хранятся за период длительность две недели.

SELECT * FROM sys.resource_stats
WHERE [database_name] = 'sample'
ORDER BY [start_time] desc;

Мониторинг использования памяти

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

SELECT pool_id,
       name AS resource_pool_name,
       IIF(name LIKE 'SloSharedPool%' OR name LIKE 'UserPool%', 'user', 'system') AS resource_pool_type,
       SUM(CAST(delta_out_of_memory_count AS decimal))/(SUM(duration_ms)/1000.) AS oom_per_second
FROM sys.dm_resource_governor_resource_pools_history_ex
GROUP BY pool_id, name
ORDER BY pool_id;

Мониторинг использования пространства журнала в базе данных tempdb

Этот запрос возвращает текущее значение метрики tempdb_log_used_percent, которое показывает использование журнала транзакций в tempdb относительно его максимально допустимого размера. Его можно выполнить в любой базе данных эластичного пула.

SELECT (lsu.used_log_space_in_bytes / df.log_max_size_bytes) * 100 AS tempdb_log_space_used_percent
FROM tempdb.sys.dm_db_log_space_usage AS lsu
CROSS JOIN (
           SELECT SUM(CAST(max_size AS bigint)) * 8 * 1024. AS log_max_size_bytes
           FROM tempdb.sys.database_files
           WHERE type_desc = N'LOG'
           ) AS df
;

Дальнейшие действия