www.9778.com 4

www.9778.com[AlwaysOn Availability Groups]监控AG性能

6.性能排查场景

Scenario

Description

排查:AG超过RTO

自动或者手动故障转移后,没有数据丢失,但是故障转移超过了RTO。或者评估发现故障转移时间超过了

排查:AG超过RPO

强制故障转移后,都是的数据超过了RPO。或者异步提交的replica能够承受的数据丢失超过了RPO。

排查:Primary上的修改无法在Secondary体现

客户端程序可以成功的完成primary的修改,但是查询replia却没有反应。

4. 有一个Report负荷堵塞了Redo线程

Redo线程在secondary副本被一个只读长运行语句堵塞。

原因: 在secondary副本,只读查询获得Sch-s锁,这些sch-s锁会堵塞redo线程获得sch-m锁执行DDL修改。被堵塞的redo线程不能应用log记录,直到被释放。一旦被释放,可以执行redo。并且允许执行随后的undo和failover过程执行。

诊断和解决: 当redo线程被堵塞,扩展时间会生产,sqlserver.lock_redo_blocked。另外你可以查询sys.dm_exec_request,查看那个会话堵塞了redo。

select session_id, command, blocking_session_id, wait_time, wait_type, wait_resource

from sys.dm_exec_requests where command = ‘DB STARTUP’

可以通过kill会话,强制释放锁。

1.通常原因

1.网络延迟太高,网络吞吐量太低,导致Primary的日志堆积
2.磁盘IO瓶颈导致LOG固化速度降低

5.监控RPO和RTO

本章介绍如何对RPO和RTO进行监控,RPO和RTO的计算请查看上面2节的介绍。你可以监控这2个值,在超过阀值时发送告警。

通过以下步骤创建AG的告警:
1.启动ageng服务
2.点开ALwaysOn启动用户定义AlwaysOn策略
3.创建条件,Add(@EstimatedRecoveryTime,60) <= 600
4.创建条件EstimatedDataLoss<=3600
5.创建RTO策略,创建RPO策略

5. 因为资源争用导致redo落后

大报表行为降低了secondary的性能,导致redo线程被落下

原因: 当应用log记录,redo线程读取log记录,并且应用这些log访问数据page。Page访问可能造成IO瓶颈,如果page不在内存中。如果还有IO密集型的负荷,照成IO资源争用,会降低redo线程的性能。

诊断和解决: 你可以通过DMV查看被落下了多少,通过对比last_redone_lsn和last_received_lsn

select recovery_lsn, truncation_lsn, last_hardened_lsn, last_received_lsn,

   last_redone_lsn, last_redone_time

from sys.dm_hadr_database_replica_states

如果redo线程被真的落下了,就需要研究secondary上的性能问题,是否有IO争用问题。可以通过Resource
Governor.aspx) 来限制其他会话的资源使用

 

 

2. 网络延迟太高,网络吞吐量太低,导致Primary的日志堆积

很多超过RPO的原因是日志发送到secondary副本不够快。

原因: Primary副本在日志发送启动了流量控制,因为日志发送超过了最大运行的非通知信息的量。直到这些信息被通知,不然不能在发新的信息到secondary副本。因为数据丢失会影响secondary副本的固化。这些没有发送的日志的数据就会被丢失。

诊断和解决: 日志高度重复,说明primary和secondary上的延迟很高。可以查看DMV的log_send_rate和性能指标log bytes flushed/sec对比。如果flushed速度大于发送的速度,那么数据丢失会越来越大。
通过检查性能指标,SQL Server:Availability
Replica> Flow Control Time(ms/sec)和SQL Server:Availability Replica > Flow
Comtrol/sec。这2个性能指标可以说明上一秒有多少时间用来等待flow
control清理。Flow control等待越久,发送速度越小。
以下是一组指标可以用来诊断网络延迟和吞吐量,也可以用一些Windows工具,比如ping,Resource
Monitor,
和Network
Monitor :

www.9778.com[AlwaysOn Availability Groups]监控AG性能。·  DMV sys.dm_hadr_database_replica_states,
log_send_queue_size

·  DMV sys.dm_hadr_database_replica_states,
log_send_rate

·  Performance counter SQL
Server:Database > Log Bytes Flushed/sec

·  Performance counter SQL
Server:Database Mirroring > Send/Receive Ack Time

·  Performance counter SQL
Server:Availability Replica > Bytes Sent to Replica/sec

·  Performance counter SQL
Server:Availability Replica > Bytes Sent to Transport/sec

·  Performance counter SQL
Server:Availability Replica > Flow Control Time (ms/sec)

·  Performance counter SQL
Server:Availability Replica > Flow Control/sec

·  Performance counter SQL
Server:Availability Replica > Resent Messages/sec

4.评估RPO

RPO,RPO的公司如下:

www.9778.com 1

Log_send_queue可以查看sys.dm_hdar_database_replica_states的log_send_queue_size和日志的生成速度,SQLServer:Database
> Log Bytes Flushed/sec.

www.9778.com,如果AG包含了多个可用性数据库,最大的 Tdata_loss 会变成限制RPO的关键。

Log发送队列表示所有数据会因为严重错误丢失的。不能使用log_send_rate来代替log生成速度,因为RPO评估数据丢失是基于日志生成速度的,而不是基于同步速度的。

最简单的评估 Tdata_loss 是使用last_commit_time.priamry会把这个值发给所有的secondary,你可以计算primary副本和secondary
副本的值的差,来评估需要多久secondary副本可以追上primary副本。虽然不能准确的表示数据丢失,但是已经很接近了。

排查:Primary上的修改无法在Secondary体现

客户端进程在primary上修改成功,但是在Secondary上却无法看到修改结果。这个case假设你的可用性组有同步的健康问题。很多情况下这个情况会在几分钟之后自动解决。

如果几分之后依然看不到,那么可能在同步的工作流上有瓶颈问题。这个瓶颈会因为是不是同步提交的而不同。

 

Commit Mode

Possible Bottleneck

Explanation

Synchronous Commit

  • Primary上长运行事务
  • secondary上的redo线程

每个在primary上成功的更新,都同步到了secondary或者日志记录已经为固话刷新。因此瓶颈应该在redo线程上,如果一旦redo跟上,secondary上的读取负荷都是快照隔离级别的。

Asynchronous Commit

  • Primary上长运行事务
  • 网络延迟或者吞吐量
  • secondary中的日志固话
  • secondary上的redo

因为一部提交一旦事务被写入到磁盘就会被通知,瓶颈可能在这个点之后任意位置出现。

3.磁盘I/O瓶颈降低secondary副本的日志固化

根据数据库文件部署,日志固化会因为IO争用被降低。

原因: 只要日志被固化到磁盘,就可以防止数据丢失。因此隔离日志文件和数据文件的IO变的很重要。如果日志文件和数据文件使用同一个物理磁盘,IO密集型查询会消耗日志固化需要的IO能力。日志固化变慢会间接导致primary通知变慢,导致flow control等待时间变长。

诊断和解决: 如果你诊断了网络,没有很高的延迟或者很低的吞吐量,然后你应该看看secondary是否有IO争用问题。
以下脚本可以让你知道每个数据文件和日志文件的读写次数。
SELECT DB_NAME(database_id) AS

   [Database Name] ,

   file_id ,

   io_stall_read_ms ,

   num_of_reads ,

   CAST(io_stall_read_ms /( 1.0

  • num_of_reads ) AS NUMERIC(10,
    1)) AS [avg_read_stall_ms] ,

   io_stall_write_ms ,

   num_of_writes ,

   CAST(io_stall_write_ms /( 1.0

  • num_of_writes ) AS NUMERIC(10,
    1)) AS [avg_write_stall_ms] ,

   io_stall_read_ms + io_stall_write_ms AS [io_stalls] ,

   num_of_reads + num_of_writes AS [total_io] ,

   CAST((
io_stall_read_ms + io_stall_write_ms ) /( 1.0 +
num_of_reads

  • num_of_writes) AS NUMERIC(10,1)) AS
    [avg_io_stall_ms]

FROM sys.dm_io_virtual_file_stats(NULL, NULL)

WHERE DB_NAME(database_id) IN(SELECT
DISTINCT database_name FROM sys.dm_hadr_database_replica_cluster_states)

ORDER BY avg_io_stall_ms DESC;

下面脚本提供了某个时间点IO请求被挂起的快照:
SELECT DB_NAME(mf.database_id) AS [Database] ,

   mf.physical_name ,

   r.io_pending ,

   r.io_pending_ms_ticks ,

   r.io_type ,

   fs.num_of_reads ,

   fs.num_of_writes

FROM sys.dm_io_pending_io_requests AS r

INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS
fs ON r.io_handle = fs.file_handle

INNER JOIN sys.master_files AS mf
ON fs.database_id = mf.database_id

AND fs.file_id = mf.file_id

ORDER BY r.io_pending , r.io_pending_ms_ticks DESC;

你可以通过读写IO,来识别是否有IO争用问题。以下是一些关于IO的性能指标:
·  Physical
Disk: all counters

·  Physical
Disk: Avg. Disk sec/Transfer

·  SQL Server:
Databases > Log Flush Wait Time

·  SQL Server:
Databases > Log Flush Waits/sec

·  SQL Server:
Databases > Log Pool Disk Reads/sec

如果你发现有IO瓶颈,并且log文件和数据文件在同一个磁盘下,第一件要做的事情就是把日志文件和数据文件分开。

 

1. 数据同步步骤

为了评估是否有性能问题,首先需要理解同步过程。性能问题可能出现在同步过程的任何一个环节,瓶颈的定位可以让你深入的理解问题。以下图标演示了数据通过过程:

www.9778.com 2

 

Sequence

Step Description

Comments

Useful Metrics

1

Log Generation

日志数据被刷新到磁盘。日志必须被复制到secondary副本。日志记录进入到发送队列.

SQL Server:Database > Log bytes flushedsec

2

Capture

每个数据库的日志被获取并且发送到相关的partner队列,每个数据库副本都有一个队列。在可用组在连接的情况下,并且数据移动并没有被挂起,获取进程持续运行,并且数据库副本显示要不是同步的,要不是正在同步,如果获取进程不能及时扫描并把消息放入队列,日志发送队列就会筑高。

SQL Server:Availability Replica > Bytes Sent to Replicasec, which is an aggregation of the sum of all database messages queued for that availability replica.

log_send_queue_size (KB) and log_bytes_send_rate(KB/sec) on the primary replica.

3

Send

数据库副本中的消息出队列,并且发送到相关的secondary副本.

SQL Server:Availability Replica > Bytes sent to transportsec and SQL Server:Availability Replica > Message Acknowledgement Time(ms)

4

Receive and Cache

每个secondary副本接受并且缓存这些信息.

Performance counter SQL Server:Availabiltiy Replica > Log Bytes Received/sec

5

Harden

日志在secondary副本被刷新。在日志刷新后,一个通知被发送到primary副本。一旦日志被固化,就表示不会再有数据丢失。

Performance counter SQL Server:Database > Log Bytes Flushed/sec

Wait typeHADR_LOGCAPTURE_SYNC

6

Redo

Redo刷新secondary副本中的page。Page被存放在redo队列等待被redo完成。

SQL Server:Database Replica > Redone Bytes/sec

redo_queue_size (KB) andredo_rate.

Wait type REDO_SYNC

2. 长运行活动事务

primary的长运行事务阻止了从secondary上读取。

原因: 所有secondary上的读负荷都是快照隔离级别的。在快照隔离下,只读客户端只能看到secondary的可用数据库中在redone log中最早的活动事务开始点之前的数据。如果事务几个小时没有提交,事务会block所有只读查询可以看到新的更新数据。

诊断和解决: 在primary,使用dbcc opentran查看最老的活动事务,查看是否可以回滚。一旦最老的事务被回滚并且同步到secondary,在secondary上的读负荷就能之后更新的数据了。

排查:AG超过RPO

在异步提交的secondary上执行了切换,你可能会发现数据的丢失大于RPO,或者在计算可以忍受的数据都是超过了RPO。

2.流量控制门(Flow Control Gates)

AG被设计时,在primary副本上带了流量控制,为了避免太多资源消耗,比如网络,内存资源在所有可用副本上的消耗。这些流量控制不会影响可用副本的健康状态,但是会影响可用数据库性能,包括RPO。

日志被primary副本捕获之后,有2个级别的流量控制。

Level

Number of Gates

Number of messages

Useful Metrics

Transport

1 per availabiltiy replica

8192

Extended event database_transport_flow_control_action

Database

1 per availability database

11200 (x64)

1600 (x86)

DBMIRROR_SEND

Extended event hadron_database_flow_control_action

一旦到达任意一个阀值,log信息就不会被发送到指定副本或者指定数据库。一旦从副本收到通知,已发送的消息下降,就可以再发。

除了流量控制,还有一个因素会阻止日志发送。副本的同步要保证LSN是顺序的被发送的。在日志被发送之前,日志的LSN会通过最小通知LSN检查,保证小于阀值。如果2个LSN的空隙大于阀值,消息就不会被发送。一旦空隙小于阀值,消息就会被发送。

有2个性能指标,SQL Server:Availability Replica > Flow
control/sec 和SQL Server:Availability Replica > Flow Control Time
(ms/sec)
表示在上一秒,有多少流量控制被激活并且有多少时间是用来等待流量控制。等待值越高表示RPO越多。跟多信息查看:排查:AG超过RPO

3. 高网络延迟,网络吞吐量低导致,Primary的日志堆高

高网络延迟或者低吞吐量会阻止日志被发送到secondary。

原因: 如果未响应信息发送超过最大允许值,primary会激活flow control,不再发送信息到secondary。直到有些信息有了反馈。这种状态会严重影响数据丢失能力,甚至超过RPO。

诊断和解决: 如果DMV的log_send_queue_size很大,表示log被堵塞在primary上。如果值除以log_send_rate可以初略的评估多久之后才能赶上primary。

也可以检查 SQL
Server:Availability Replica > Flow Control Time
(ms/sec).aspx)和 SQL
Server:Availability Replica > Flow
control/sec.aspx)。这2个值相乘可以得到每秒至少花多少时间来等待flow control。flow control等待时间越长,速度越慢。

以下是有用的网络延迟和吞吐的诊断值。也可以使用windows工具,比如ping, Resource
Monitor 来评估网络使用率。

·  DMV log_send_queue_size.aspx)

·  DMV log_send_rate.aspx)

·  Performance counter SQL
Server:Database > Log Bytes Flushed/sec

·  Performance counter SQL
Server:Database Mirroring > Send/Receive Ack Time

·  Performance counter SQL
Server:Availability Replica > Bytes Sent to
Replica/sec

·  Performance counter SQL
Server:Availability Replica > Bytes Sent to
Transport/sec

·  Performance counter SQL
Server:Availability Replica > Flow Control Time
(ms/sec)

·  Performance counter SQL
Server:Availability Replica > Flow Control/sec

·  Performance counter SQL
Server:Availability Replica > Resent Messages/sec

3.评估故障转移时间

故障转移时间的公式如下:
www.9778.com 3

如果AG有多个可用库,最高的故障转移时间变长了限制RTO总要因素。

Tdetection错误诊断时间,是用来发现系统错误的时间。这个时间依赖于集群设置级别,而不是个别可用性副本的设置。根据设置的自动故障转移的条件,故障转移在SQLServer出现严重的内部错误会出发,比如,孤立的自旋锁。这个时候诊断在sp_server_diagnostics发送到WSFC集群马上启动。故障转移也会因为超时发生,比如集群健康检查超时(默认30秒)或者资源DLL和SQL
Server实例的租用超时(默认20秒)。这个时间诊断时间最长为超时的间隙。具体看:FlexibleFailover
Policy for Automatic Failover of an Availability Group (SQL Server).

Secondary副本唯一要做的事情就是,redo这些获取的日志。Redo时间是Tredo,公式如下:

www.9778.com 4

Redo_queue是redo队列的长度,redo_rate是redo的速度。这2个值可以查看:sys.dm_hadr_database_replica_states

Toverheadover
head的时间就是WSFC集群故障转移,数据库online的时间。通常这个时间都很小。

1.通常原因

导致primary修改后没有反映到secondary上原因:
1.长运行活动事务
2.高网络延迟,网络吞吐量低导致,Primary的日志堆高
3.有一个Report负荷堵塞了Redo线程
4.因为资源争用导致redo落后

监控AG性能

AG的性能的性能方面,在关键任务数据库上进行语句级维护性能是很重要的。理解AG如何传输日志到secondary副本对评估RTO和RPO,表明AG是否性能不好。

7. 使用扩展事件

以下扩展时间可以用来排查副本在同步中的情况:

Event Name

Category

Channel

Availability Replica

redo_caught_up

transactions

Debug

Secondary

redo_worker_entry

transactions

Debug

Secondary

hadr_transport_dump_message

alwayson

Debug

Primary

hadr_worker_pool_task

alwayson

Debug

Primary

hadr_dump_primary_progress

alwayson

Debug

Primary

hadr_dump_log_progress

alwayson

Debug

Primary

hadr_undo_of_redo_log_scan

alwayson

Analytic

Secondary