SQL Server 2016/2017: модель повтора вторичной реплики группы доступности и производительность
Когда группа доступности была первоначально выпущена с SQL Server 2012, повторный журнал транзакций обрабатывался одним повторным потоком для каждой базы данных вторичной реплики AG. Эта повторная модель также называется последовательным повторением. В SQL Server 2016 модель повтора была дополнена несколькими параллельными рабочими потоками для каждой базы данных для совместного использования рабочей нагрузки. Кроме того, в каждой базе данных есть новый вспомогательный рабочий поток для обработки грязной страницы ввода-вывода. Эта новая модель повтора называется параллельным повторением. С новой моделью параллельным повторения, которая является параметром по умолчанию со времени SQL Server 2016, ожидается, что рабочие нагрузки с малыми транзакциями высокой степени многопоточности достигнут лучшей производительности повтора. Когда операция повторной транзакции имеет интенсивность ЦП, например, когда включено шифрование данных и / или сжатие данных, параллельный повтор имеет еще большую пропускную способность повторения (Redone Bytes / sec) по сравнению с последовательным повторением. Более того, непрямая контрольная точка позволяет параллельному повторению разгружать больше IO (и IO ожидания на медленный диск) в его поток рабочего помощника и освобождает основной поток повтора для перечисления большего количества принятых записей журнала во вторичной реплике. Это еще больше ускоряет работу повтора. Однако параллельный повтор, который позволяет использовать многопоточную модель, имеет соответствующую стоимость.
- Несмотря на то, что основной поток изменений останавливает выполнение индивидуальной операции журнала повторов транзакции, он отвечает за перечисление и отправку каждого журнала транзакций в свои параллельные рабочие потоки. Стоимость отправки этих журналов может быть значительно выше в сценариях, где операция журнала повтора не является интенсивной в ЦП, например, повторение транзакции DML на узкой таблице (с небольшими размерами строк).
- Системные транзакции для разбиения на страницы, вызванные новыми вставками записей, могут привести к тому, что PARALLEL_REDO_TRAN_TURN ждет параллельных потоков рабочих повторов, когда вторичная реплика сконфигурирована как читаемая реплика. В зависимости от частоты операций вставки это может значительно замедлить производительность параллельного повтора.
- Когда запросы, доступные только для чтения, выполняются на читаемой вторичной реплике, потоки запросов пытаются применить ожидающие операции журнала повтора и должны взаимодействовать с рабочими потоками повтора с DIRTY_PAGE_TABLE_LOCK ожиданиями, которые могут часто генерироваться и замедлять как повторение, так и производительность запроса, если есть одновременная перезагрузка. Проблема производительности, связанная с ожиданием DIRTY_PAGE_TABLE_LOCK, будет исправлена в предстоящем выпуске накопительного обновления для SQL Server 2016 SP и SQL Server 2017.
Основываясь на текущем исследовании производительности, следующие типы рабочей нагрузки транзакций или конфигурации SQL, как правило, лучше работают с параллельной моделью повторного использования по умолчанию:
- Малые транзакции с высокой степенью многопоточности.
- Дорогостоящая операция журнала повторов (например, шифрование данных или сжатие данных) с косвенной контрольной точкой
- Нечитаемая вторичная реплика или только случайные запросы только для чтения к читаемой вторичной реплике при загруженном трафике транзакций журнала повтора
В следующих сценариях ожидается, что переход на последовательный повтор должен обеспечить лучшую пропускную способность повтора:
- Долгосрочные транзакции с преобладающими вставками и ограниченной многопоточностью (типичный пример - перестраивание индексов в сети кластеризованного индекса большой таблицы)
- Симптомы модели параллельного повторения:
- Частота ожидания PARALLEL_REDO_TRAN_TURN обычно пропорциональна объему операций вставки. Дополнительные вставки могут вызывать больше разрывов страниц, что соответствует большим ожиданиям PARALLEL_REDO_TRAN_TURN.
- Многопоточность рабочей нагрузки может контролироваться первичным счетчиком(Объект – SQLServer:General Statistics, Счетчик – User Connections) или DMV "sys.dm_exec_connection" и "sys.dm_exec_sessions" в первичной реплике
- Частые и / или длительные запросы только для чтения должны выполняться в базе данных вторичной реплики, которая имеет одновременные рабочие нагрузки журнала транзакций. Запрос и повтор не должны запускаться против одного и того же набора таблиц в базе данных.
- Симптомы модели параллельного повторения:
- Частые ожидания DIRTY_PAGE_TABLE_LOCK
- Мониторинг первичного счетчика (Объект– SQLServer:Database Replica, Экземплярт– [DBName], Счетчик - Redone Bytes/sec) для сравнения пропускной способности повтора, когда запрос запущен и нет
Чтобы переключиться на модель последовательного повтора, необходимо включить TF 3459. Но после того, как экземпляр SQL Server запущен в модели последовательного повтора, единственный способ изменить его обратно на параллельный повтор - это перезапустить службу SQL Server. Множество факторов влияют на производительность повтора, некоторые из них применимы только к новой модели повтора - параллельной, например ожидания PARALLEL_REDO_TRAN_TURN и DIRTY_PAGE_TABLE_LOCK. Для разных рабочих нагрузок транзакций и размещенных конфигураций машины не всегда понятно, какое подмножество факторов оказывает более сильное влияние на производительность повтора. Если производительность повтора вашей рабочей нагрузки не согласуется с объяснением в этом документе, пожалуйста, поделитесь своими данными о рабочей нагрузке и главной машине с Microsoft Customer Service and Support (CSS). Это поможет им построить более полное представление и оценить будущие улучшения с помощью повторной работы.
Новые типы ожидания
Несколько новых типов ожидания потоков были добавлены с новой моделью Parallel Redo в SQL Server 2016. Эта информация может быть запрошена у sys.dm_os_wait_stats. Некоторые из этих типов ожидания указывают на влияние производительности, в то время как другие могут считаться доброкачественными.
Тип ожидания с эффектом производительности:
|
Тип |
Описание |
Комментарий |
|
PARALLEL_REDO_FLOW_CONTROL |
Происходит, когда основной поток изменений не может отправлять больше записей журнала транзакций, когда массив кеша журнала для отправленного журнала транзакций заполнен. |
IУказывает на то, что один или несколько параллельных рабочих потоков повторов не могут справиться с основной диспетчерской скоростью журнала транзакций или блокированы некоторыми ресурсами, такими как ожидания другого типа. Когда это ожидание происходит часто, параллельные рабочие потоки повторов не работают эффективно. |
|
PARALLEL_REDO_TRAN_TURN |
Происходит, когда параллельный рабочий рабочий поток должен дождаться повторного ввода зависимого журнала транзакций, прежде чем переходить к текущему журналу транзакций. Зависимый журнал транзакций может назначаться другому модулю параллельного повтора, что означает перекрестный поток. |
Только в читаемой вторичной реплике, когда новая вставка запускает транзакцию с разбивкой по страницам или записывает обновление в таблице кучи, генерирует пересылаемую запись (дополнительная информация о пересылаемых записях). |
|
DIRTY_PAGE_TABLE_LOCK |
Происходит, когда есть ожидание блокировки, которая контролирует доступ к таблице грязных страниц. Таблица грязных страниц содержит информацию об измененных страницах данных, которые необходимо очистить на диске. При использовании модели параллельной повторной обработки отложенные журналы транзакций, ожидающие повторного использования, связаны с записями грязных страниц. |
Когда в readablesecondary replica есть запросы только для чтения. Запросы обрабатывают журналы ожидающих транзакций во время сканирования страницы данных. Это означает, что потоки запросов должны взаимодействовать с основным потоком повтора и параллельными рабочими потоками повтора для этого доступа к блокировке страницы с грязной страницей и могут замедлять работу как запроса, так и повтора при одновременном запуске. Это ожидание больше не будет создано после исправления производительности для одновременного запроса только для чтения и выхода журнала. |
|
DPT_ENTRY_LOCK |
Происходит, когда есть ожидание блокировки, которая контролирует доступ к записи грязной страницы, которая имеет повтор, чтобы догнать поток пользовательских запросов. |
Возникает только тогда, когда параллельный рабочий поток повтора и поток запросов пользователя одновременно обрабатывают операции повтора для той же записи грязной страницы. |
Тип ожидания без эффекта производительности:
|
Тип |
Описание |
Комментарий |
|
PARALLEL_REDO_WORKER_WAIT_WORK |
Возникает, когда ни один параллельный рабочий поток повтора или вспомогательный поток повтора не имеют ничего общего. |
Это праздное ожидание и предполагается, что будет происходить регулярно.Указывает, что ни один из основных рабочих потоков не может отправлять журналы транзакций так же быстро, как общая скорость повторения всех параллельных рабочих повторов, или, чаще всего, соответствующая база данных в первичной реплике AG неактивна и не генерирует много журналов транзакций для вторичной реплики для повтора. |
|
PARALLEL_REDO_DRAIN_WORKER |
Возникает, когда основному потоку изменений необходимо слить все неразрешенные операции повтора, например, при изменении размера файла или контрольной точки и т.д. |
Ожидается регулярно |
|
PARALLEL_REDO_LOG_CACHE |
Происходит, когда есть ожидание блокировки, которая контролирует обновление для массива кеша журнала из параллельных рабочих потоков. |
Редкие ожидания. Возникает, когда параллельные рабочие потоки повторов помогают сигнализировать повторные шаги после того, как происходит управление потоком, большинство времени основного потока изменений делает это без ожидания блокировки. |
|
PARALLEL_REDO_TRAN_LIST |
Происходит, когда есть ожидание блокировки, которая контролирует доступ к специальному кешу. Кэш содержит информацию о транзакциях, которые полностью переделаны и готовы к выпуску. |
Редкие ожидания. Возникает, когда основной рабочий модуль повтор должен получить доступ к этому списку транзакций при сбрасывании выдающихся операций повтора. В большинстве случаев вспомогательный модуль получает доступ к списку транзакций без ожидания блокировки. |
|
PARALLEL_REDO_WORKER_SYNC |
Возникает при ожидании остановки всех параллельных рабочих потоков. |
Когда параллельные потоки повтора не создаются или не прекращаются (например, включение TF 3459) |
Параллельное повторное использование потоков и контроль модели повторения
Использование параллельных повторных потоков хорошо описано в разделе «Использование потоков по группам доступности».
Экземпляр SQL Server использует до 100 потоков для параллельного повтора для вторичных реплик. Каждая база данных использует до половины общего количества ядер ЦП, но не более 16 потоков на базу данных. Если общее количество требуемых потоков для одного экземпляра превышает 100, SQL Server использует один поток повтора для каждой оставшейся базы данных.
Когда хост-сервер имеет 32 или более ядра ЦП, каждая база данных будет занимать 16 параллельных рабочих потоков повтора и один вспомогательный рабочий поток. Это означает, что все базы данных, начиная с 7-й базы данных (упорядоченной по имени базы данных по возрастанию), которая присоединилась к группе доступности, будут в однопоточном повторе или последовательном повторе независимо от того, какая база данных имеет фактическую рабочую нагрузку повтора. Если экземпляр SQL Server имеет несколько баз данных и желательно, чтобы конкретная база данных работала под моделью параллельного повтора, необходимо учитывать порядок создания базы данных. Та же идея может быть применена, чтобы заставить базу данных всегда работать в серийной модели повтора. Опять же, на уровне экземпляра SQL Server способ переключения между параллельным повтором и последовательным - это TF 3459. Все базы данных в одном экземпляре SQL Server будут переключаться вместе. Кроме того, чтобы переключиться с последовательного повтора на параллельный, отключив TF 3459, необходим перезапуск службы SQL Server.