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

Синхронная фиксация ставит в приоритет минимизацию потери данных над производительностью

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

В следующей ссылке говорится об синхронной фиксации.

Режимы доступности (группы доступности AlwaysOn)

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

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

Транзакция должна быть отображена в первичной и синхронной вторичной репликах для фиксации

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

Демо: измерение производительности рабочей нагрузки в синхронной фиксации

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

Включите счетчики монитора производительности

Включите следующие счетчики в мониторе производительности первичной реплики:

Для базы данных групп доступности в нашем примере:

SQL Server:Databases:Log Bytes Flushed/sec

SQL Server:Databases:Log Flushes/sec

SQL Server:Databases:Write Transactions/sec

SQL Server:Database Replica:Transaction Delay

Запуск рабочей нагрузки, просмотр времени выполнения, оценка эффективности транзакций

Следующая рабочая нагрузка вставляет 10 000 строк 10 раз и использует режим автоматической фиксации, что приведет к 100 000 транзакций. Она сообщит о среднем времени, затраченном на вставку 10 000 строк в секундах, и о среднем времени, затрачиваемом на вставку одной строки в миллисекундах.

use agdb

go

declare @y int=0

declare @x int=0

declare @starttime datetime

declare @endtime datetime

create table test (col int)

create table results (duration float)

while @x<10

    begin

    Set @starttime =getdate()

                 while @y<10000

                begin

                        insert test (col)

                        select @y

                        set @y=@y+1

                  end                                       

    set @endtime =getdate()

    insert results (duration)

    select datediff(millisecond,@starttime, @endtime)/1000.0

    set @x=@x+1

    set @y=0

    end

select * from results

select avg(duration) as 'avg duration in sec for 10,000 INSERTs', avg(duration)/10 as 'avg duration in ms for each INSERT' from results

drop table results

drop table test

После того, как мы создадим нашу группу доступности и базу данных и настроим ее для синхронной фиксации с помощью одной вторичной реплики, мы запускаем нашу рабочую нагрузку. Отчет состоит в том, что наш средний INSERT занял 4,4 мс для завершения цикла, зафиксированного  в файле журнала на первичной и вторичной репликах. Загрузка заняла 7 минут 50 секунд.

HADR_SYNC_COMMIT waittype сообщает о синхронной задержке фиксации

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

DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR)

Давайте продемонстрируем серьезность этого waittype в экземпляре. Сообщается большое количество этих ожиданий.

Обзор счетчиков монитора производительности

Давайте рассмотрим счетчики монитора производительности, собранные во время загрузки. Показатель  Log Bytes Flushed / sec для базы данных довольно низкий, менее 1 мб / с.

Сравнение Log Flushes/sec с Write Transactions/sec

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

Подтверждение расчетного времени транзакции с помощью монитора производительности

В рабочей нагрузке была указано значение 4,4 мс для «средней продолжительности каждой транзакции в MS». Мы можем использовать монитор производительности, чтобы сделать второй расчет, подтверждающий точность этого значения: разделим одну секунду на среднее значение 211 Write Transactions, выполненных в секунду,  что составляет 1/211 = .0047 секунд или 4,7 мс. Это число очень похоже на то, о котором сообщали наши рабочие нагрузки. Это время, которое потребовалось, чтобы зафиксировать изменение журнала, доставить его второму, записать его в файл журнала на первичной и вторичной основе и сообщить клиенту, что он завершен.

Не полагайтесь на автофиксации - определите явные транзакции

Загрузка был изменена с явной транзакцией в рабочей нагрузке таким образом, что  вместе будет выполняться 10 000 операций INSERT. Это гораздо эффективнее, учитывая требования синхронной фиксации о том, что каждая транзакции необходимо упрощение операции для файлов журналов каждой реплики и цикла сети, необходимых для выполнения этой работы. Обратите внимание на добавленные в загрузку и прокомментированные команды BEGIN TRAN и COMMIT TRAN.

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

use agdb
go

declare @y int=0
declare @x int=0
declare @starttime datetime
declare @endtime datetime
create table test (col int)
create table results (duration float)

while @x<10
        begin
        Set @starttime =getdate()
         begin tran                            --BEGIN 10000 row transaction
                     while @y<10000

                    begin
                            insert test (col)
                            select @y
                            set @y=@y+1
                      end                                       
         commit tran                            --COMMIT 10000 row transaction
        set @endtime =getdate()
        insert results (duration)
        select datediff(millisecond,@starttime, @endtime)/1000.0
        set @x=@x+1
        set @y=0
        end

select * from results
select avg(duration) as 'avg duration in sec for 10,000 INSERTs', avg(duration)/10 as 'avg duration in ms for each INSERT' from results

drop table results
drop table test

Наши результаты почти мгновенные: они получены за пару секунд!

HADR_SYNC_COMMIT waittype сообщает о синхронной задержке фиксации

Сбросив счетчики ожидания SQL Server, используя DBCC SQLPERF (описанный ранее), мы видим, что ожидания HADR_SYNC_COMMIT значительно ниже.

Обзор счетчиков монитора производительности

Просмотрев монитор производительности, мы замечаем, что Log Bytes Flushed/sec подскочил до 10,4 мб / сек в сравнении с нашим более ранним тестом, в котором он работал в среднем менее 1 мб/сек. Это показывает, насколько большую пропускную способность SQL Server может выполнить, если не фиксировать несколько байтов для отдельных небольших транзакций.

При сравнении Log Flushes/sec с Write Transactions/sec  корреляция не так очевидна. Во время пакетного исполнения Log Flushes/secподнимается до 181, а транзакции записи намного ниже, поскольку сама рабочая нагрузка выполняет только 10 явных транзакций вместо 100 000.

Вывод

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

No Comments