要解决复制延迟问题,需要首先定位复制延迟发生点,再找出复制延迟的原因,再做相应处理。
复制延迟发生点:1. 发布服务器2. 分发服务器3. 订阅服务器4. 发布服务器与分发服务器和分发服务器与订阅服务器之间的网络
延迟测试方式:1. 使用复制token参考:http://www.cnblogs.com/TeyGao/p/3521130.html
2. 使用复制存储过程sp_replmonitorhelpXXX
--==========================================================--参考:http://msdn.microsoft.com/zh-cn/library/ms188073.aspx--返回发布服务器上属于一个或多个发布的订阅的当前状态信息,--并为每个返回的订阅返回一行。 在分发服务器上对分发数据库--执行此存储过程,用于监视复制。--@publication_type=0:事务发布--@mode=3:只返回带错误或已生成在达到阈值度量指标时发出的警告的订阅。EXEC distribution.dbo.sp_replmonitorhelpsubscription @publisher = null,@publisher_db = null,@publication = null,@publication_type =0,@mode = 3,@topnum = 0,@exclude_anonymous = null,@refreshpolicy = 0--===========================================================--参考:http://msdn.microsoft.com/zh-cn/library/ms186304.aspx--返回发布服务器上一个或多个发布的当前状态信息。 在分发服务器--的分发数据库上执行此存储过程,用于监视复制。--@publication_type=0:事务发布EXEC distribution.dbo.sp_replmonitorhelppublication@publisher = null,@publisher_db = null,@publication = null,@publication_type = 1,@refreshpolicy =0--==============================================================View Code
PS: 某牛X电商使用sp_replmonitorhelpXXX来检查延迟,不明觉厉。
3. 使用sp_replcounters
--================================================--为每个发布数据库返回有关滞后时间、吞吐量和事务计--数的复制统计信息。 此存储过程在发布服务器的任何数--据库中执行。--参考:http://msdn.microsoft.com/zh-cn/library/ms190486.aspxexec sp_replcounters--================================================View Code
延迟诊断顺序:
1. 如果只有一个订阅延迟,优先检查该订阅服务器2. 如果有多个订阅延迟,优先检查发布服务器和分发服务器
--==================================================================
发布服务器上延迟分析
原因1: 镜像或ALWAYS ON 阻塞了复制
诊断方式:使用镜像监视器或相关存储过程查看镜像同步情况
处理建议:
建议A:等待镜像同步完成或取消镜像,
建议B:使用TRACE FLAG 1448(慎用)
原因2:磁盘IO存在压力
诊断方式:使用性能计数器查看日志所在磁盘的磁盘队列
处理建议:
建议A:提升日志所在磁盘的性能或将日志文件放于独享磁盘上
原因3:虚拟日志文件数量过多
诊断方式: 使用DBCC LOGINFO来查看虚拟日志数量
处理建议:
建议A: 虚拟日志文件数量应保持一个合理的数量(数量过少和过多都会出现问题)
原因4:数据库事务日志过多,而复制相关日志较少
诊断方式:
--==========================================================--查看是否因为发布库日志太多导致日志读取慢Use <published database>GO-- Total records in the logSELECT count(*) FROM ::fn_dblog(NULL, NULL)GO-- Records marked for REPLICATIONSELECT count(*) FROM ::fn_dblog(NULL, NULL) WHERE Description='REPLICATE'GOView Code
处理建议:
建议A: 设置合理的索引维护及其他会导致大量日志写入操作的运行时间
建议B: 业务拆分,将与复制不相关的业务拆分出去
原因5:复制发布article上有较大事务运行
诊断方式:
--========================================================--使用发布库日志来查找大事务--在发布库上运行SELECT [Transaction ID],COUNT(1) AS LogCountFROM ::fn_dblog(NULL, NULL) WHERE Description='REPLICATE'GROUP BY [Transaction ID]HAVING COUNT(1)>500--===============================================================--在分发库上查找大事务USE distributionGOSELECT xact_seqno, COUNT(*) AS [COUNT] INTO #MSrepl_commands FROM dbo.MSrepl_commandsGROUP BY xact_seqnoHAVING COUNT(*)>100SELECT t.xact_seqno,t.entry_time,c.[count] FROM MSrepl_Transactions t INNER JOIN#MSrepl_commands c ON t.xact_seqno=c.xact_seqnoORDER BY c.count DESC,t.entry_timeView Code
处理建议:
建议A: 按照业务逻辑拆分事务
建议B: 修改复制相关的配置文件设置
--==================================================================
分发服务器上延迟分析
延迟原因1:磁盘IO存在压力
诊断方式:使用性能计数器查看日志所在磁盘的磁盘队列
处理建议:
建议A:提升日志所在磁盘的性能或将日志文件放于独享磁盘上
延迟原因2:分发数据库写日志等待
诊断方式:使用DMV查看在分发数据库上存在写日志等待
处理建议:
建议A:提高磁盘性能
建议B:将不同的发布服务器拆分到不同的分发库上,减少分发库对应的发布数量
延迟原因3:复制分发清理作业和日志读取代理作业相互阻塞
诊断方式:检查分发库上命令数量和事务数量,检查是否因为复制设置不合理保持过多的事务和命令
参考:http://blogs.msdn.com/b/apgcdsd/archive/2012/09/07/10347168.aspx
处理建议:
建议A: 设置合理的事务保持期和发布属性设置
建议B: 修改复制分发清理作业的运行时间
延迟原因4:复制事务表和复制命令表包含过多的数据
诊断方式:检查表中数据分表属于那些发布article
-=============================================================--当前msrepl_commands表中命令涉及表的分布情况USE distribution;GOWITH cte AS(SELECT a.xact_seqno,b.entry_time,REPLACE(CONVERT(NVARCHAR(1024),SUBSTRING(a.command,17,1024)),'[dbo].[sp_MS','') commandsFROM dbo.MSrepl_commands a(NOLOCK)JOIN MSrepl_transactions b(NOLOCK)ON a.xact_seqno=b.xact_seqno)SELECT SUBSTRING(commands,9,CHARINDEX(']',commands)-9),COUNT(1)FROM cte WHERE CHARINDEX(']',commands)>9GROUP BY SUBSTRING(commands,9,CHARINDEX(']',commands)-9)ORDER BY COUNT(1) DESCView Code
处理建议:
建议A: 将不同的发布服务器拆分到不同的分发服务器上。
建议B:分析数据变化情况,是否可以减少数据变更
PS: 曾遇到一个案例,按照开发部门需求,搭建复制订阅,该发布表按天记录数据,当天数据变化特别频繁,导致复制延迟较高。调研发现订阅端业务只访问前一天数据,于是在发布端新增一张表,每天凌晨将头一天数据导入此表,并对该表搭建复制订阅。更改前每天要传递数千万次甚至上亿次事务命令给订阅服务器,更改后只需要传递数百万事务命令道订阅服务器。
--==================================================================
订阅服务器上延迟分析
延迟原因1:订阅库上有阻塞
诊断方式:使用DMV检查事务阻塞
处理建议:
建议A:优化订阅上查询
建议B:使用较低事务隔离级别或NOLOCK
延迟原因2:订阅库日志写等待
诊断方式:使用DMV查看在分发数据库上存在写日志等待
处理建议:
建议A:提高磁盘相应速度。
--==================================================================
相关补充:
1.查看阻塞和资源等待:http://www.cnblogs.com/TeyGao/p/3522958.html
2.查看数据库文件级别IO操作情况:
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(),null) AS T2
--====================================================================
新闻热点
疑难解答