SQL Server 2016/2017: производительность и модель повтора вторичной реплики групп доступности
Когда Availability Group была первоначально выпущена с SQL Server 2012, повторный журнал транзакций обрабатывался одним повторным потоком для каждой базы данных вторичной реплики AG. Эта модель также называется последовательным повторением. В SQL Server 2016 модель Redo была дополнена несколькими параллельными рабочими потоками для каждой базы данных для совместного использования рабочей нагрузки. Кроме того, в каждой базе данных есть новый рабочий поток помощника для обработки грязной страницы ввода-вывода. Эта новая модель повтора называется параллельным повторением. С новой моделью параллельного повтора, которая является параметром по умолчанию начиная с SQL Server 2016, ожидается, что рабочие нагрузки с малыми транзакциями с высокой степенью многопоточности достигнут лучшей производительности повтора. Когда транзакция операции повтора имеет интенсивность CPU, например, когда включено шифрование данных и / или сжатие данных, параллельный повтор имеет еще большую пропускную способность повторения (Redone Bytes / sec) по сравнению с последовательным повторением. Более того, непрямая контрольная точка позволяет параллельному повторению разгружать больше IO (и IO ожидания на медленный диск) в свой поток рабочего помощника и освобождает основной поток повтора для перечисления большего количества принятых записей журнала во вторичной реплике. Это еще больше ускоряет работу повтора. Однако параллельный повтор, который позволяет использовать многопоточную модель, имеет соответствующую стоимость.
- Несмотря на то, что основной поток повтора останавливает выполнение отдельной транзакции операции повтора журнала,, он отвечает за перечисление и отправку каждого журнала транзакций в свои параллельные рабочие потоки. Стоимость отправки этих журналов может быть значительно выше в сценариях, где операция повтора журнала не является интенсивной в CPU, например, повторение транзакции 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.
- Параллельность рабочей нагрузки может контролироваться первичным счетчиком (Object – SQLServer:General Statistics, Counter – User Connections) или DMV "sys.dm_exec_connection" & "sys.dm_exec_sessions"в первичной реплике.
- Частые и / или длительные запросы только для чтения должны выполняться в базе данных вторичной реплики, которая имеет одновременные рабочие нагрузки журнала транзакций. Запрос и повтор не должны запускаться против одного и того же набора таблиц в базе данных.
- Симптомы модели параллельнойповторенияl:
- Частые ожидания DIRTY_PAGE_TABLE_LOCK
- Мониторинг первичного счетчика (Object – SQLServer:Database Replica, Instance – [DBName], Counter-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 |
Происходит, когда основной поток повторов не может отправлять больше записей журнала транзакций, когда массив кеша журнала для отправленного журнала транзакций заполнен. |
Указывает на то, что один или несколько рабочих потоков параллельных повторов не могут справиться с основной диспетчерской скоростью журнала транзакций или блокироваться некоторыми ресурсами, такими как ожидания другого типа. Когда это ожидание происходит часто, рабочие потоки параллельных повторов не работают эффективно. |
|
PARALLEL_REDO_TRAN_TURN |
Происходит, когда рабочий поток параллельного повтора должен дождаться повторного ввода зависимого журнала транзакций, прежде чем переходить к текущему журналу транзакций. Зависимый журнал транзакций может назначаться другому потоку параллельного повтора, что означает перекрестный поток. |
Только в читаемой вторичной реплике, когда новая вставка запускает транзакцию с разбивкой по страницам или записывает обновление в таблице “кучи”, генерирует пересылаемую запись. |
|
DIRTY_PAGE_TABLE_LOCK |
Происходит, когда есть ожидание блокировки, которая контролирует доступ к таблице грязных страниц. Таблица грязных страниц содержит информацию об измененных страницах данных, которые необходимо очистить на диске. При использовании параллельной модели повторной обработки отложенные журналы транзакций, ожидающие повторного использования, связаны с грязными записями страниц. |
Когда в readablesecondary replica есть запросы только для чтения. Запросы обрабатывают журналы ожидающих транзакций во время сканирования страницы данных. Это означает, что потоки запросов должны взаимодействовать с основным потоком повтора и параллельными рабочими потоками повтора для этого доступа к таблице грязных страниц и могут замедлять работу как запроса, так и повтора при одновременном запуске. Это ожидание больше не будет создано после исправления производительности для одновременного запроса только для чтения и освобождения журнала повтора. |
|
DPT_ENTRY_LOCK |
Происходит, когда есть ожидание блокировки, которая контролирует доступ к записи грязной страницы, которая имеет повтор, чтобы догнать поток пользовательских запросов. |
Возникает только тогда, когда рабочий поток параллельного повтора и поток запросов пользователя одновременно обрабатывают операции повтора для той же грязной записи страницы. |
Типы ожидания без влияния на производительность:
|
Type |
Description |
Comment |
|
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.