Мониторинг производительности для групп доступности Always On

Область применения:SQL Server

Аспект производительности групп доступности AlwaysOn играет важную роль с точки зрения соблюдения соглашения об уровне обслуживания (SLA) для критически важных баз данных. Понимание того, как группа доступности передает журналы вторичным репликам, может помочь оценить цель времени восстановления (RTO) и целевую точку восстановления (RPO) для вашей системы доступности, а также выявить узкие места в малоэффективных группах доступности или репликах. Эта статья описывает процесс синхронизации, показывает, как вычислить некоторые основные метрики, а также содержит ссылки на некоторые распространенные сценарии по устранению неполадок с производительностью.

Процесс синхронизации данных

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

Шлюзы управления потоком

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

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

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

SQL Server 2022 (16.x) увеличивает ограничения на количество сообщений, разрешенных каждому шлюзу. С помощью флага трассировки 12310 увеличенный предел также доступен для следующих версий SQL Server: SQL Server 2019 (15.x) CU9, SQL Server 2017 (14.x) CU18, SQL Server 2016 (13.x) SP1 CU16.

В следующей таблице сравниваются ограничения сообщений:

Два полезных счетчика производительности — SQL Server: реплика доступности > поток управления в секунду и SQL Server: реплика доступности > время управления потоком (мс/с) — показывают, сколько раз активировалось управление потоком и сколько времени ушло на ожидание управления потоком за последнюю секунду. Более длительное время ожидания управления потоком приводит к увеличению RPO. Дополнительные сведения о типах проблем, которые могут привести к большому времени ожидания в элементе управления потоком, см. в статье "Устранение неполадок: потенциальная потеря данных с помощью реплики группы доступности с асинхронной фиксацией".

Оценка времени восстановления после отказа (RTO)

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

Время обнаружения ошибки Tdetection — это время, необходимое системе для обнаружения сбоя. Это время зависит от параметров на уровне кластера, а не от отдельных реплик доступности. В зависимости от настроенного условия автоматической отказоустойчивости, переход может быть запущен в качестве мгновенного ответа на критическую внутреннюю ошибку SQL Server, такую как осиротевшие спин-блокировки. В этом случае обнаружение может быть как быстро, так как отчет об ошибке sp_server_diagnostics отправляется в отказоустойчивый кластер Windows Server (WSFC). Интервал по умолчанию — 1/3 времени ожидания проверки работоспособности. Переключение на резервный ресурс также может быть активировано, например, из-за истечения времени ожидания проверки работоспособности кластера (по умолчанию 30 секунд) или истечения срока аренды между библиотекой DLL ресурсов и экземпляром SQL Server (по умолчанию 20 секунд). В этом случае время обнаружения совпадает с продолжительностью интервала тайм-аута. Дополнительные сведения см. в разделе о гибкой политике автоматического перехода на другой ресурс группы доступности (SQL Server).

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

где redo_queue — это значение в redo_queue_size, а redo_rate — это значение в redo_rate.

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

Оценка потенциальной потери данных (RPO)

Ваша RPO в соглашении об уровне обслуживания зависит от возможной потери данных при реализации Always On в любой момент времени. Эту возможную потерю данных можно выразить следующей формулой:

где log_send_queue — это значение log_send_queue_size, а скорость создания журнала — это значение SQL Server:Database > байты журнала, сброшенные в секунду.

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

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

Оценка RTO и RPO с помощью панели мониторинга SSMS

В группах доступности AlwaysOn вычисляются RTO и RPO и отображаются для баз данных, размещенных на вторичных репликах. На панели мониторинга SQL Server Management Studio (SSMS) на первичной реплике RTO и RPO группируются по вторичной реплике.

Чтобы просмотреть RTO и RPO на панели мониторинга, сделайте следующее:

1. В SQL Server Management Studio разверните узел Высокий уровень доступности AlwaysOn, щелкните правой кнопкой мыши имя группы доступности и выберите команду Показать панель мониторинга.

2. Выберите "Добавить/Удалить столбцы" на вкладке "Группировать по". Проверьте как предполагаемое время восстановления (секунды) [RTO], так и предполагаемую потерю данных (время) [RPO].

   

Расчет RTO вторичной базы данных

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

Для вторичной базы данных (DB_sec), вычисление и отображение RTO основаны на её redo_queue_size и redo_rate:

За исключением редких случаев, формула для вычисления RTO вторичной базы данных такова:

Расчет RPO вторичной базы данных

Для вторичной базы данных (DB_sec) вычисление и отображение ее RPO основываются на значениях is_failover_ready, last_commit_time и значениях last_commit_time соответствующей первичной базы данных (DB_pri). Если значение DB_sec.is_failover_ready равно 1, данные между первичной и вторичными синхронизированы, и при переключении на резервный узел потеря данных не происходит. Однако если это значение 0имеется, то между базой данных-источником и last_commit_time базой данных-получателем существует разрывlast_commit_time.

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

Метрики производительности, используемые в формулах RTO/RPO

• redo_queue_size (КБ): размер очереди повтора, используемый в RTO, — это размер журналов транзакций между ними last_received_lsn и last_redone_lsn. Значением last_received_lsn является идентификатор блока журнала, определяющий точку, к которой были получены все блоки журнала вторичной репликой, в которой размещена эта второстепенная база данных. Значением last_redone_lsn является номер последовательности последней записи журнала, которая была восстановлена в базе данных-получателе. На основе этих двух значений можно найти идентификаторы начального блока журнала (last_received_lsn) и конечного блока журнала (last_redone_lsn). Пространство между этими двумя блоками журнала может представлять, сколько блоков журнала транзакций еще не переопределено. Это измеряется в Килобайтах (КБ).

• redo_rate (КБ/с): Используется в вычислении RTO, это накопительное значение, показывающее, сколько из журнала транзакций (КБ) было перепроиграно или воспроизведено на вторичной базе данных в секунду.

• last_commit_time (datetime): используется в RPO, это значение имеет другое значение между первичной и вторичной базой данных. Для базы данных-источника last_commit_time используется время фиксации последней транзакции. Для вторичной базы данных last_commit_time используется последний коммит транзакции в основной базе данных, который также был успешно зафиксирован во вторичной базе данных. Это значение на вторичном сервере должно быть синхронизировано с тем же значением на первичном сервере, поэтому разница между этими двумя значениями представляет собой оценку потери данных (RPO).

Оценка RTO и RPO с помощью динамических представлений управления (DMV)

Можно запросить динамические административные представления sys.dm_hadr_database_replica_states и sys.dm_hadr_database_replica_cluster_states для оценки RPO и RTO базы данных. Ниже приведены запросы, которые создают хранимые процедуры, выполняющие обе эти задачи.

Создание хранимой процедуры для оценки RTO

1. На целевой вторичной реплике создайте хранимую процедуру proc_calculate_RTO. Если эта хранимая процедура уже существует, сначала удалите и заново создайте ее.

   IF object_id(N'proc_calculate_RTO', 'p') IS NOT NULL
       DROP PROCEDURE proc_calculate_RTO;
   GO
   
   RAISERROR ('creating procedure proc_calculate_RTO', 0, 1)
       WITH NOWAIT;
   GO
   
   -- name: proc_calculate_RTO
   --
   -- description: Calculate RTO of a secondary database.
   -- 
   -- parameters: @secondary_database_name nvarchar(max): name of the secondary database.
   --
   -- security: this is a public interface object.
   --
   CREATE PROCEDURE proc_calculate_RTO
   @secondary_database_name NVARCHAR (MAX)
   AS
   BEGIN
       DECLARE @db AS sysname;
       DECLARE @is_primary_replica AS BIT;
       DECLARE @is_failover_ready AS BIT;
       DECLARE @redo_queue_size AS BIGINT;
       DECLARE @redo_rate AS BIGINT;
       DECLARE @replica_id AS UNIQUEIDENTIFIER;
       DECLARE @group_database_id AS UNIQUEIDENTIFIER;
       DECLARE @group_id AS UNIQUEIDENTIFIER;
       DECLARE @RTO AS FLOAT;
       SELECT @is_primary_replica = dbr.is_primary_replica,
              @is_failover_ready = dbcs.is_failover_ready,
              @redo_queue_size = dbr.redo_queue_size,
              @redo_rate = dbr.redo_rate,
              @replica_id = dbr.replica_id,
              @group_database_id = dbr.group_database_id,
              @group_id = dbr.group_id
       FROM sys.dm_hadr_database_replica_states AS dbr
            INNER JOIN sys.dm_hadr_database_replica_cluster_states AS dbcs
                ON dbr.replica_id = dbcs.replica_id
               AND dbr.group_database_id = dbcs.group_database_id
       WHERE dbcs.database_name = @secondary_database_name;
       IF @is_primary_replica IS NULL
          OR @is_failover_ready IS NULL
          OR @redo_queue_size IS NULL
          OR @replica_id IS NULL
          OR @group_database_id IS NULL
          OR @group_id IS NULL
           BEGIN
               PRINT 'RTO of Database ' + @secondary_database_name + ' is not available';
               RETURN;
           END
       ELSE
           IF @is_primary_replica = 1
               BEGIN
                   PRINT 'You are visiting wrong replica';
                   RETURN;
               END
       IF @redo_queue_size = 0
           SET @RTO = 0;
       ELSE
           IF @redo_rate IS NULL
              OR @redo_rate = 0
               BEGIN
                   PRINT 'RTO of Database ' + @secondary_database_name + ' is not available';
                   RETURN;
               END
           ELSE
               SET @RTO = CAST (@redo_queue_size AS FLOAT) / @redo_rate;
       PRINT 'RTO of Database ' + @secondary_database_name + ' is ' + CONVERT (VARCHAR, ceiling(@RTO));
       PRINT 'group_id of Database ' + @secondary_database_name + ' is ' + CONVERT (NVARCHAR (50), @group_id);
       PRINT 'replica_id of Database ' + @secondary_database_name + ' is ' + CONVERT (NVARCHAR (50), @replica_id);
       PRINT 'group_database_id of Database ' + @secondary_database_name + ' is ' + CONVERT (NVARCHAR (50), @group_database_id);
   END
   

2. Выполните команду proc_calculate_RTO с именем целевой вторичной базы данных:

   EXECUTE proc_calculate_RTO @secondary_database_name = N'DB_sec';
   

3. Выходные данные содержат значение RTO для вторичной реплики целевой базы данных. Сохраните group_id, replica_id, и group_database_id для использования в хранимой процедуре оценки RPO.

   Пример выходных данных:

   RTO of Database DB_sec' is 0
   group_id of Database DB4 is F176DD65-C3EE-4240-BA23-EA615F965C9B
   replica_id of Database DB4 is 405554F6-3FDC-4593-A650-2067F5FABFFD
   group_database_id of Database DB4 is 39F7942F-7B5E-42C5-977D-02E7FFA6C392
   

Создайте хранимую процедуру для оценки RPO

1. На первичной реплике создайте хранимую процедуру proc_calculate_RPO. Если он уже существует, сначала удалите его, а затем пересоздайте.

   IF object_id(N'proc_calculate_RPO', 'p') IS NOT NULL
       DROP PROCEDURE proc_calculate_RPO;
   GO
   
   RAISERROR ('creating procedure proc_calculate_RPO', 0, 1)
       WITH NOWAIT;
   GO
   
   -- name: proc_calculate_RPO
   --
   -- description: Calculate RPO of a secondary database.
   -- 
   -- parameters: @group_id uniqueidentifier: group_id of the secondary database.
   --             @replica_id uniqueidentifier: replica_id of the secondary database.
   --             @group_database_id uniqueidentifier: group_database_id of the secondary database.
   --
   -- security: this is a public interface object.
   --
   CREATE PROCEDURE proc_calculate_RPO
   @group_id UNIQUEIDENTIFIER, @replica_id UNIQUEIDENTIFIER, @group_database_id UNIQUEIDENTIFIER
   AS
   BEGIN
       DECLARE @db_name AS sysname;
       DECLARE @is_primary_replica AS BIT;
       DECLARE @is_failover_ready AS BIT;
       DECLARE @is_local AS BIT;
       DECLARE @last_commit_time_sec AS DATETIME;
       DECLARE @last_commit_time_pri AS DATETIME;
       DECLARE @RPO AS NVARCHAR (MAX);
       SELECT @db_name = dbcs.database_name,
              @is_failover_ready = dbcs.is_failover_ready,
              @last_commit_time_sec = dbr.last_commit_time
       FROM sys.dm_hadr_database_replica_states AS dbr
            INNER JOIN sys.dm_hadr_database_replica_cluster_states AS dbcs
                ON dbr.replica_id = dbcs.replica_id
               AND dbr.group_database_id = dbcs.group_database_id
       WHERE dbr.group_id = @group_id
             AND dbr.replica_id = @replica_id
             AND dbr.group_database_id = @group_database_id;
       SELECT @last_commit_time_pri = dbr.last_commit_time,
              @is_local = dbr.is_local
       FROM sys.dm_hadr_database_replica_states AS dbr
            INNER JOIN sys.dm_hadr_database_replica_cluster_states AS dbcs
                ON dbr.replica_id = dbcs.replica_id
               AND dbr.group_database_id = dbcs.group_database_id
       WHERE dbr.group_id = @group_id
             AND dbr.is_primary_replica = 1
             AND dbr.group_database_id = @group_database_id;
       IF @is_local IS NULL
          OR @is_failover_ready IS NULL
           BEGIN
               PRINT 'RPO of database ' + @db_name + ' is not available';
               RETURN;
           END
       IF @is_local = 0
           BEGIN
               PRINT 'You are visiting wrong replica';
               RETURN;
           END
       IF @is_failover_ready = 1
           SET @RPO = '00:00:00';
       ELSE
           IF @last_commit_time_sec IS NULL
              OR @last_commit_time_pri IS NULL
               BEGIN
                   PRINT 'RPO of database ' + @db_name + ' is not available';
                   RETURN;
               END
           ELSE
               BEGIN
                   IF DATEDIFF(ss, @last_commit_time_sec, @last_commit_time_pri) < 0
                       BEGIN
                           PRINT 'RPO of database ' + @db_name + ' is not available';
                           RETURN;
                       END
                   ELSE
                       SET @RPO = CONVERT (VARCHAR, DATEADD(ms, datediff(ss, @last_commit_time_sec, @last_commit_time_pri) * 1000, 0), 114);
               END
       PRINT 'RPO of database ' + @db_name + ' is ' + @RPO;
   END
   
   -- secondary database's last_commit_time -- correlated primary database's last_commit_time
   

2. Выполните proc_calculate_RPO с group_id целевой вторичной базы данных, replica_id и group_database_id.

   EXECUTE proc_calculate_RPO
       @group_id = 'F176DD65-C3EE-4240-BA23-EA615F965C9B',
       @replica_id = '405554F6-3FDC-4593-A650-2067F5FABFFD',
       @group_database_id = '39F7942F-7B5E-42C5-977D-02E7FFA6C392';
   

3. Результаты отображают значение RPO для целевой базы данных вторичной копии.

Мониторинг для RTO и RPO

Этот раздел демонстрирует, как мониторить ваши группы доступности для метрик RTO и RPO. Эта демонстрация аналогична приведенной в учебнике по графическому пользовательскому интерфейсу Модель работоспособности AlwaysOn, часть 2. Расширение модели работоспособности.

Элементы времени отработки отказа и потенциальные вычисления потери данных в оценке времени отработки отказа (RTO) и оценке потенциальной потери данных (RPO) удобно предоставляются в качестве метрик производительности в состоянии реплики базы данных управления политиками. Дополнительные сведения см. в разделе "Просмотр аспектов управления на основе политик" в объекте SQL Server. Вы можете отслеживать эти две метрики по расписанию и получать оповещения, когда метрики превышают значения RTO и RPO.

Приведенные скрипты создают две системные политики, выполняемые по собственным расписаниям со следующими характеристиками:

• Политика RTO, которая не выполняется, если оценочное время аварийного переключения превышает 10 минут, оценка проводится каждые 5 минут.

• Политика RPO, которая терпит неудачу, когда оценочная потеря данных превышает 1 час, оценивается каждые 30 минут.

• Две эти политики имеют идентичную конфигурацию на всех репликах доступности.

• Политики проверяются на всех серверах, но только в группах доступности, для которых локальная реплика доступности является основной. Если локальная реплика доступности не является основной репликой, политики не проверяются.

• Неудачи в политике удобно отображаются на панели мониторинга Always On, когда вы просматриваете её на первичной реплике.

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

1. Запустите службу агента SQL Server , если она еще не запущена.

2. В SQL Server Management Studio в меню Инструменты выберите Параметры.

3. На вкладке SQL Server AlwaysOn выберите "Включить определяемую пользователем политику AlwaysOn " и нажмите кнопку "ОК".

   Этот параметр позволяет отобразить правильно настроенные пользовательские политики на панели мониторинга AlwaysOn.

4. Создайте условие управления на основе политик, используя следующие спецификации:

   • Имя: RTO
   • Аспект: Состояние копии базы данных
   • Поле: Add(@EstimatedRecoveryTime, 60)
   • Оператор: <=
   • Значение: 600

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

5. Создайте второе условие управления на основе политик, используя следующие спецификации:

   • Имя: RPO
   • Аспект: Состояние копии базы данных
   • Поле: @EstimatedDataLoss
   • Оператор: <=
   • Значение: 3600

   Это условие не выполняется, когда потенциальная потеря данных превышает 1 час.

6. Создайте третье условие управления на основе политик, используя следующие спецификации:

   • Имя: IsPrimaryReplica
   • Аспект: Группа доступности
   • Поле: @LocalReplicaRole
   • Оператор: =
   • Значение: Primary

   Это условие проверяет, является ли локальная реплика первичной репликой для данной группы доступности.

7. Создайте политику управления на основе политик, используя следующие спецификации:

   • Страница Общие:

     • Имя: CustomSecondaryDatabaseRTO

     • Проверка условия: RTO

     • Против целей: все DatabaseReplicaState в IsPrimaryReplica AvailabilityGroup

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

       • Режим оценки: По расписанию

       • Расписание: CollectorSchedule_Every_5min

       • Включено: выбрано

   • Страница Описание:

     • Категория: Предупреждения базы данных доступности

       Этот параметр позволяет отобразить результаты оценки политики на панели мониторинга AlwaysOn.

       • Описание: Текущая реплика имеет RTO более 10 минут, при условии, что процесс обнаружения и восстановления занимает 1 минуту. Следует немедленно рассмотреть проблемы с производительностью на этом экземпляре сервера.

       • Отображаемый текст: Превышено значение RTO.

8. Создайте вторую политику управления на основе политик, используя следующие спецификации:

   • Страница Общие:

     • Имя: CustomAvailabilityDatabaseRPO
     • Проверка условия: RPO
     • Относительно целей: Каждое состояние реплики базы данных (DatabaseReplicaState) в IsPrimaryReplica группы высокой доступности (AvailabilityGroup)
     • Режим оценки: По расписанию
     • Расписание: Расписание_сборщика_каждые_30мин
     • Включено: выбрано

   • Страница Описание:

     • Категория: Предупреждения базы данных доступности

     • Описание. База данных доступности превысила RPO в 1 час. Необходимо немедленно изучить проблемы с производительностью реплик доступности.

     • Отображаемый текст: Превышено значение RPO.

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

Вы можете просмотреть журнал заданий, чтобы изучить результаты оценки. Ошибки оценки также заносятся в журнал приложений Windows (в средстве просмотра событий) с кодом события 34052. Можно также настроить агент SQL Server для отправки предупреждений о сбоях политик. Дополнительные сведения см. в разделе "Настройка оповещений" для уведомления администраторов политик о сбоях политики.

Сценарии устранения неполадок с производительностью

Ниже перечислены распространенные сценарии устранения неполадок, связанные с производительностью.

Полезные расширенные события

Следующие расширенные события полезны при устранении проблем с репликами в состоянии Синхронизации.