Автор: Erik Darling, Automatic Soft-NUMA and SOS_SCHEDULER_YIELD Waits In SQL Server
Автоматическая soft-NUMA (auto soft-NUMA) может приводить к увеличению ожиданий SOS_SCHEDULER_YIELD в больших системах с ограниченной конкурентностью больших параллельных запросов. В этой статье содержится воспроизведение проблемы и краткий анализ. Я надеюсь, что читатели из Microsoft оценят мою сдержанность в том, что я не сострил на тему «Это просто работает медленнее».
Что такое автоматическая soft-NUMA?
Автоматическая soft-NUMA появилась в SQL Server 2016 и включается автоматически. Однако она действует только в том случае, если SQL Server может определить, что в сокете 9 или более ядер. Документация не очень точна, но страница документации Microsoft — хорошая отправная точка для тех, кто не знаком с этой функцией. Если кратко, планировщики в узле памяти разбиваются на группы soft-NUMA в зависимости от общего количества планировщиков и того, может ли SQL Server обнаружить гиперпоточность.
Microsoft ожидает, что автоматическая soft-NUMA улучшит масштабируемость и производительность для большинства рабочих нагрузок. Они не объясняют эту идею подробно, но говорят о том, что определённые внутренние структуры разбиваются по узлам soft-NUMA, и такое разбиение может быть полезно для больших систем.
Возможно, это не то, что они имеют в виду, но есть один системный процесс LOG WRITER на узел soft-NUMA в SQL Server 2016 до максимума 4. Однако не все процессы записи журнала распределены по нескольким узлам NUMA. Например, однокристальный сервер с 32 ядрами будет иметь один процесс записи журнала без автоматической soft-NUMA. С автоматической программной NUMA будет четыре узла soft-NUMA и, как следствие, четыре процесса записи журнала на ЦП 1-4. Это может быть полезно для некоторых рабочих нагрузок.
Ещё одно наблюдаемое изменение поведения, вызванное узлами soft-NUMA, — это различия в планировании. Влияние на распределение планировщиков для запросов с MAXDOP 1 хорошо известно, но есть более тонкие проблемы, которые могут возникнуть при выполнении параллельных запросов.
Тестовый сервер
Тестовый сервер представлял собой виртуальную машину с 96 ядрами на четырёх физических узлах NUMA. Виртуальная машина была единственным гостем на физическом хосте, и виртуальная топология соответствовала физической. В SQL Server — 96 планировщиков и 4 узла памяти. Каждый узел памяти разбит на 3 программных узла NUMA по 8 планировщиков, потому что SQL Server не может определить, включена ли гиперпоточность, а 8 делится на 24 без остатка. Вот вывод sys.dm_os_nodes с включённой автоматической soft-NUMA.
MAXDOP сервера установлен на 8. В теории это должна быть идеальная настройка. Microsoft говорит, что они считают восьмёрку магическим числом, когда речь идёт о масштабируемости параллельных процессов. Если автоматическая программная NUMA отключена, то есть только четыре узла NUMA, по одному на каждый узел памяти. Вот вывод sys.dm_os_nodes с отключённой автоматической soft-NUMA.
Тестовый код
Для тестов я решил просто чередовать наборы параллельных запросов, которые завершаются очень быстро, с параллельными запросами, которые выполняются долго. В итоге я создал простую хранимую процедуру, которая использует только таблицу spt_values и запускает указанное пользователем количество параллельных запросов, которые завершаются почти мгновенно, а затем запрос, который выполняет перекрёстное соединение миллионов строк. Последний запрос в процедуре не завершится за разумное время; он предназначен для отмены. Идея в том, чтобы дать наблюдателю как можно больше времени для изучения различных DMV и метрик о том, как были запланированы потоки.
CREATE OR ALTER PROCEDURE
[dbo].[RUN_SET_OF_QUERIES] (@num_cheap_queries INT) AS
BEGIN
SET NOCOUNT ON;
DECLARE @dummy INT,
@queries_run_so_far INT = 0,
@filter INT = 0;
WHILE @queries_run_so_far
BETWEEN 0 AND @num_cheap_queries - 1
BEGIN
SELECT @dummy = MAX(t1.high + t2.high)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
WHERE @filter = 1
OPTION (MAXDOP 8);
SET @queries_run_so_far = @queries_run_so_far + 1;
END;
SELECT @dummy =
MAX(t1.high + t2.high + t3.high + t4.high)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
CROSS JOIN master..spt_values t4
OPTION (MAXDOP 8);
END;
Для этой цели я выбрал выполнение хранимой процедуры через sqlcmd. Дорогие запросы не изменяют данные, поэтому очень быстро отменить все выполняющиеся запросы можно закрытием окна sqlcmd. Читатели, которые следят за этим на своих тестовых серверах с 96 ядрами, могут использовать любую методику для запуска хранимых процедур. Я посчитал важным иметь возможность запускать хранимую процедуру с заданной пользователем задержкой между выполнениями и не ждать завершения хранимой процедуры перед отправкой следующих запросов. Ниже приведён пример синтаксиса для пакетного файла, который запускает четыре вызова хранимой процедуры с задержкой около 2,5 секунд между вызовами. Каждая хранимая процедура выполняет два быстрых параллельных запроса перед выполнением очень дорогого.
START /B sqlcmd -d {{db_name}} -S {{server_name}} -Q "EXEC [dbo].[RUN_SET_OF_QUERIES] @num_cheap_queries=2" > nul
ping 192.2.0.1 -n 1 -w 2500 > nul
START /B sqlcmd -d {{db_name}} -S {{server_name}} -Q "EXEC [dbo].[RUN_SET_OF_QUERIES] @num_cheap_queries=2" > nul
ping 192.2.0.1 -n 1 -w 2500 > nul
START /B sqlcmd -d {{db_name}} -S {{server_name}} -Q "EXEC [dbo].[RUN_SET_OF_QUERIES] @num_cheap_queries=2" > nul
ping 192.2.0.1 -n 1 -w 2500 > nul
START /B sqlcmd -d {{db_name}} -S {{server_name}} -Q "EXEC [dbo].[RUN_SET_OF_QUERIES] @num_cheap_queries=2" > nul
ping 192.2.0.1 -n 1 -w 2500 > nul
Наконец, мне нужен был запрос для изучения распределения параллельных рабочих потоков в системе. В общем, вы хотите, чтобы ваши параллельные рабочие потоки были распределены достаточно широко, чтобы все планировщики могли выполнять полезную работу. Я использовал следующий запрос, чтобы получить представление о распределении параллельных рабочих потоков:
SELECT
session_id
, dop
, start_time
, request_scheduler_id
, STRING_AGG
(
CASE WHEN exec_context_id = 0
THEN NULL ELSE scheduler_id END
, ','
)
WITHIN GROUP (ORDER BY scheduler_id)
AS used_schedulers_for_parallel_workers
FROM
(
SELECT
dot.session_id
, dot.scheduler_id
, dot.exec_context_id
, req.scheduler_id AS request_scheduler_id
, req.command
, req.dop
, req.start_time
, dos.parent_node_id
, dos.cpu_id
, dos.is_idle
, dos.load_factor
, dos.active_workers_count
FROM
(
SELECT DISTINCT
session_id
, scheduler_id
, exec_context_id
FROM sys.dm_os_tasks
) dot
LEFT OUTER JOIN sys.dm_exec_requests req
ON dot.session_id = req.session_id
AND req.request_id = 0
LEFT OUTER JOIN sys.dm_exec_sessions ses
ON dot.session_id = ses.session_id
LEFT OUTER JOIN sys.dm_os_schedulers dos
ON dos.scheduler_id = dot.scheduler_id
WHERE ses.is_user_process = 1
) t
GROUP BY
session_id
, dop
, start_time
, request_scheduler_id
ORDER BY start_time
OPTION (MAXDOP 1);
Этот запрос «ленив» в том смысле, что он не обрабатывает правильно планы с несколькими параллельными зонами. Однако он работает достаточно хорошо для тестов на простых параллельных запросах или для правильно написанных пакетных запросов.
Тестирование с автоматической soft-NUMA
Напомню, что с включённой автоматической soft-NUMA на моём сервере было 12 узлов soft-NUMA по 8 планировщиков. Я перезапустил SQL Server и запустил .bat-файл со следующими командами, повторёнными 12 раз:
START /B sqlcmd -d {{db_name}} -S {{server_name}} -Q "EXEC [dbo].[RUN_SET_OF_QUERIES] @num_cheap_queries=2" > nul
ping 192.2.0.1 -n 1 -w 2500 > nulДругими словами, я запустил в общей сложности 24 очень быстрых параллельных запроса и 12 очень долго выполняющихся параллельных запросов. Вот как выглядело распределение моих параллельных рабочих потоков:
Это довольно плохой результат. У меня 12 запросов с MAXDOP 8, и все параллельные рабочие потоки назначены планировщикам только на четырёх узлах NUMA. Каждый ЦП в этих узлах NUMA имеет эквивалент 300% работы, назначенной ему. Контекст выполнения 0 не выполняет много работы для тестового запроса, поэтому у меня 64 ЦП с едва заметной работой. Маловероятно, что загрузка ЦП сервера превысит 33%. Вот статистика ожиданий после двух минут выполнения рабочей нагрузки:
Мы накопили два часа ожиданий SOS_SCHEDULER_YIELD всего за две минуты. Не то, что вы хотите видеть на сервере с загрузкой ЦП около 33%. Что пошло не так?
Больше планировщиков — больше проблем
Планирование параллельных запросов было изменено в SQL Server 2012. Боб Дорр писал об этом в своём блоге, и это лучший источник, который мне известен. Даже так, у меня было много проблем с пониманием того, что именно означают слова в том посте. Читатели этого блога, возможно, поймут. Я наблюдал на практике только тип распределения Spread, поэтому наиболее релевантная часть связанного поста — это:
Spread: This is the most common decision made by SQL Server. The decision spreads the workers across multiple nodes as required. The design is similar to full except the starting position is based on the saved, next node, global enumerator.
Это наиболее распространённое решение, принимаемое SQL Server. Решение распределяет рабочие потоки по нескольким узлам по мере необходимости. Дизайн похож на full, за исключением того, что начальная позиция основана на сохранённом глобальном нумераторе следующего узла.
Рассмотрим сервер с узлами soft-NUMA по 8 планировщиков с MAXDOP 8. Первый параллельный запрос будет отправлен на узел NUMA 0. Количество активных рабочих потоков точно соответствует числу планировщиков, поэтому каждый активный рабочий поток назначается на отдельный планировщик в узле NUMA. Второй параллельный запрос будет отправлен на узел NUMA 1. Третий параллельный запрос — на узел NUMA 2, и так далее. Выполнение последовательных запросов или создание сеансов не имеет значения. Это продвигает счётчик, отдельный от «глобального нумератора», используемого для размещения параллельных запросов. Насколько я могу судить, планировщик, назначенный контексту выполнения 0, не влияет на планирование потоков параллельных рабочих потоков, хотя он может влиять на производительность параллельных запросов.
Описанный выше сценарий не звучит так уж плохо. Он может работать хорошо, если параллельные запросы выполняются примерно за одно и то же время и MAXDOP запроса соответствует числу планировщиков на узел soft-NUMA. Проблемы могут возникнуть, когда хотя бы одно из этих условий не выполняется. При типе распределения Spread возможно, что количество работы, уже назначенной планировщикам, не влияет на размещение параллельных запросов. Давайте это осознаем. У вас может быть 100 последовательных запросов, назначенных планировщикам в NUMA узле 0, но SQL Server всё равно может отправить параллельный запрос на этот NUMA узел. Это зависит от позиции «глобального нумератора», а не от текущей работы на сервере.
Именно это поведение и делает воспроизведение в этой статье правдоподобным. С общей суммой в 12 узлов soft-NUMA мне нужно только выполнять запросы в шаблоне «быстро-быстро-медленно», чтобы медленные запросы удваивались и утраивались на планировщиках. В некоторых случаях отправка большего количества параллельных запросов на сервер может быть допустимой стратегией, если загрузка ЦП не так высока, как хотелось бы. Однако здесь это может не сработать. Отправка большего количества запросов будет в основном накапливать дополнительные ожидания SOS_SCHEDULER_YIELD.
Неверно, что SQL Server никогда не учитывает объём работы на планировщике при назначении параллельных рабочих потоков. У узлов NUMA есть ограничения на количество параллельных рабочих потоков, как видно в sys.dm_exec_query_parallel_workers. По-видимому, существуют варианты планирования, которые учитывают коэффициент загрузки или количество рабочих потоков, когда набор параллельных рабочих потоков заполняет только часть узла soft-NUMA. Рассмотрим пару запросов с MAXDOP 12, выполняющихся на том же сервере. Предположим, что «глобальный нумератор» стартует с позиции 0. Первый запрос захватит 8 планировщиков из узла NUMA 0 и 4 планировщика из узла NUMA 1. SQL Server имеет некоторый выбор, какие планировщики он захватывает из узла NUMA 1. Однако для узла NUMA 0 выбора нет, потому что он захватывает все. Второй параллельный запрос захватывает 4 планировщика из узла NUMA 1 и 8 планировщиков из узла NUMA 2. Опять же, SQL Server может сделать выбор, какие планировщики он использует из узла NUMA 1. Это решение может учитывать загрузку системы. Как и в случае с последовательными запросами, если запросы отправляются на сервер слишком быстро, вы можете увидеть ненужное удвоение планировщиков в узле NUMA 1.
Я не пытался полностью разобраться в деталях, но надеюсь, что вышесказанное даёт вам общее представление о том, какие проблемы вы можете увидеть с планированием параллельных запросов на серверах с более чем одним узлом NUMA.
Тестирование без автоматической soft-NUMA
Вооружившись новыми знаниями, давайте рассмотрим, что может произойти с предыдущей рабочей нагрузкой, если автоматическая soft-NUMA отключена. Предположим, что сервер был перезапущен и глобальный нумератор стартует с позиции 0. Три запроса с MAXDOP 8 могут поместиться в каждом узле NUMA из 24 планировщиков. Дорогой запрос для первого выполнения хранимой процедуры будет отправлен на планировщики первого узла NUMA. Дорогой запрос для второго выполнения — на планировщики второго узла NUMA, третий — на третий узел NUMA, а четвёртый — на четвёртый узел NUMA. По мере выполнения большего количества запросов мы будем циклически повторяться, но ключевое отличие в том, что SQL Server может размещать параллельные рабочие потоки на 24 планировщиках по своему усмотрению. Он может учитывать такие факторы, как коэффициент загрузки или количество рабочих потоков на планировщик. После того как все 12 хранимых процедур запущены, мы можем получить такое распределение:
Каждый планировщик имеет по крайней мере один поток для параллельного рабочего потока или поток контекста выполнения 0. Планировщик 22 — один из восьми планировщиков с более чем одним назначенным параллельным рабочим потоком. Контекст выполнения 0 для этих запросов, как ожидается, будет выполнять очень мало работы, поэтому можно утверждать, что лучшее распределение — это ровно один параллельный рабочий поток на планировщик. Однако в целом это довольно хорошее распределение, и мы можем поднять загрузку ЦП до 90%. После двух минут выполнения у нас значительно меньше времени, потраченного на ожидания SOS_SCHEDULER_YIELD, по сравнению с предыдущим:
В этой ситуации мы видим значительное улучшение использования ресурсов сервера при отключении автоматической soft-NUMA. Для других рабочих нагрузок и смесей запросов поведение планирования, предлагаемое с автоматической soft-NUMA, может подходить лучше. Ключевые факторы включают шаблон параллельных запросов, MAXDOP и количество планировщиков на узел памяти.
Заключительные мысли
Мы наблюдали узкое место с ожиданиями SOS_SCHEDULER_YIELD для ETL-нагрузки, для которой было нелегко масштабировать количество запросов. Это может произойти, если есть только определённое количество разделов для обработки или если ETL-запросы требуют больших распределений памяти, например, для сжатия данных колоночного индекса. Мы смогли сократить общее время рабочей нагрузки на 30%, используя привязку ЦП регулятора ресурсов (Resource Governor CPU affinity) и выполняя собственное планирование. Менее радикальные обходные пути включают отключение автоматической soft-NUMA, изменение MAXDOP, увеличение порога стоимости параллелизма (Cost Threshold for Parallelism, CTFP) или обуздание некоторых запросов, которые не нуждаются в параллельном выполнении. Спасибо за чтение!
Комментариев нет:
Отправить комментарий