SQLServer提高事务复制效率优化教程

SQL Server提高事务复制效率优化教程

总体概述 

随着公司业务的发展，数据量增长迅速，在解决Scale Out的同时，还要考虑到主从的复制延迟问题，尽量降到1s以内满足线上业务，如果不调整，SQL Server默认的配置可能平均要3s左右。生产的复制架构采用的是推送方式进行事务复制，发布服务器下面有4个从节点，两两指向同一虚拟IP，构成负载均衡，服务于不同的线上业务。对于4个节点，发布库和分法库的压力都很大，订阅库每秒I/O能达到5M，高峰时能达到数十兆。研究并试验了一些时间，给出一些优化建议：

1.硬件、数据库设计：

∙使用SSD磁盘，有钱的可以使用PCI-e卡，SSD做RAID10，所有节点部署在内网。

∙更改事务隔离级别为READ_COMMITTED_SNAPSHOT。说实话，SQL Server默认事务隔离级别是读已提交，但是没有多版本的概念，需要设置为READ_COMMITTED_SNAPSHOT才是我们理解上的读已提交。如果不设置这个，照样会读写互相阻塞。     

∙在创建订阅之前，先将目标数据库的恢复模式改成简单，能够减少日志

ALTER DATABASE [数据库名称] SET RECOVERY SIMPLE WITH NO_WAIT

2.发布、订阅设计：

∙仅发布需要的数据，对于将日志写入数据库而且还要同步复制的，DBA可直接拒绝。

∙表中存在大量非聚集索引，发布时可以不勾选复制非聚集索引选项。经过测试，虽然数据库引擎是在应用完快照最后创建索引的，但创建时间很长，有时甚至会出错。可以在后期手工建立。我们采用导出脚本方式，然后只保留非聚集索引的脚本在订阅库中建立。

∙如果资源允许，单独建立一个分发服务器。发布和分发服务器在一个服务器上会增加性能损耗，尤其是分发服务器下面有多个订阅服务器时。

∙分发库默认是distribution库，会有专门的作业对该库进行清理，名字就叫“分发清除：distribution”。但这个进程清除分发库的效率太低了，会导致分发库积累大量数据，需要修改运行间隔和每次清楚的数据量。详见之前的随笔：distribution库过大的问题

∙合理使用订阅方式，如果选择推送订阅，势必加大分发服务器的压力，但延迟低 ；如果选择请求订阅，代理服务是在订阅服务器上，减少了分发服务器压力，但延迟会增加。

∙快照代理的生成。对于生成几百GB数据库的快照，采用SSD盘可能在10分钟左右。但会造成很高的性能损耗，要在业务非高峰期生成快照。快照代理文件也要放在SSD盘上，如果资源允许，单独一个驱动盘也可以。

∙如果已经有备份了，那么可以通过备份直接来初始化订阅，省去了生成快照和应用快照的步骤。

∙新发布新的表对象，对于发布库很大的情况下重新初始化是不实际的，可以增量发布，详见sqlserver同步后在不重新初始化快照的情况下新增表

∙对于已发布的表有批处理更新，一种方法是将批量更新分成若干小批量执行，减小事务处理的数据量；另一种方法是通过存储过程执行更改，并将存储过程发布。推荐第一种

∙将项目分布在多个发不上，这样相当于变向的实现了多线程并行发布，当然也可以通过参数实现并行，下面会讲到

3.参数优化：

∙降低复制代理的详细级别，在初始测试、监视或调试期间除外。 减小分发代理或合并代理的 –HistoryVerboseLevel 参数和 –OutputVerboseLevel 参数。这样可以减少为跟踪代理历史记录和输出而插入的新行数。相反，具有相同状态的以前的历史记录消息将更新为新的历史记录信息。提高测试、监视和调试的详细级别，这样就可以获得有关代理活动的尽可能多的信息。在系统开销不是很大的情况下，建议使用默认值1，本身这点对性能消耗可以忽略。

∙增大快照代理的BcpBatchSize参数，-BcpBatch参数表示一个批事务的行数。

∙将快照代理的- MaxNetworkOptimization设置为1，减少将不必要的信息发送到订阅服务器上。

∙使用快照代理、分发代理的 –MaxBcpThreads 参数（指定的线程数不应超过计算机上的处理器数）。此参数指定创建和应用快照时可以并行执行的大容量复制操作的数目。

∙为分发代理使用 –SubscriptionStreams 参数。 –SubscriptionStreams 参数可以显著提高聚合复制吞吐量。它使到一台订阅服务器的多个连接可以并行应用批量更改，同时在使用单线程时保持多个事务特征的存在。如果有一个连接无法执行或提交，则所有连接将中止当前批处理，而且代理将用单独的流重试失败的批处理。在重试阶段完成之前，订阅服务器上会存在临时事务不一致。失败的批处理成功提交后，订阅服务器将恢复到事务一致状态。官方文档有这个参数，但增加这个参数报错。

∙增大分发代理的 -CommitBatchSize 和CommitBatchThreshold参数的值。 提交一组事务的开销是固定的；通过不经常提交大量事务，就可以将开销分散在大量数据上。但是，增大此参数的优势因应用更改的开销由其他因素（例如包含日志的最大磁盘 I/O）限制而降低。另外，需要考虑以下权衡问题：任何导致分发代理重新开始的失败必须回滚并重新应用大量事务。对于不可靠的网络，越小的值导致失败的几率越小，如果导致失败，将回滚并重新应用较少量事务。

∙增大日志读取器代理的 -ReadBatchSize 参数的值。-ReadBatchSize 表示日志读取器代理和分发代理支持事务读取和提交操作的批大小。批大小的默认值为 500 个事务。日志读取器代理从日志中读取特定数量的事务，而不管这些事务是否标记为复制。将大量事务写入发布数据库，而其中只有一小部分标记为复制时，则应使用 -ReadBatchSize 参数增加日志读取器代理的读取批大小。此参数不适用于 Oracle 发布服务器。

∙为日志读取器代理使用 –MaxCmdsInTran 参数。–MaxCmdsInTran 参数指定日志读取器向分发数据库写入命令时组成一个事务的语句的最大数量。使用此参数能够使日志读取器代理和分发代理在订阅服务器上应用命令时将发布服务器上的大事务（由许多命令组成）分成若干个较小的事务。指定此参数可以减少分发服务器的争用问题并缩短发布服务器与订阅服务器之间的滞后时间。由于初始事务是以较小的单元应用的，订阅服务器可以在初始事务结束之前访问一个较大的逻辑发布服务器事务的行，因而会破坏事务的原子性。默认值为 0，这将保持发布服务器的事务边界。官方文档有该参数，但实际设置里面没有。

∙合理使用订阅方式，如果选择推送订阅，势必加大分发服务器的压力，但延迟低 ；如果选择请求订阅，代理服务是在订阅服务器上，减少了分发服务器压力，但延迟会增加。

∙快照代理的生成。对于生成几百GB数据库的快照，采用SSD盘可能在10分钟左右。但会造成很高的性能损耗，要在业务非高峰期生成快照。快照代理文件也要放在SSD盘上，如果资源允许，单独一个驱动盘也可以。

∙如果已经有备份了，那么可以通过备份直接来初始化订阅，省去了生成快照和应用快照的步骤。

∙新发布新的表对象，对于发布库很大的情况下重新初始化是不实际的，可以增量发布，详见sqlserver同步后在不重新初始化快照的情况下新增表

∙对于已发布的表有批处理更新，一种方法是将批量更新分成若干小批量执行，减小事务处理的数据量；另一种方法是通过存储过程执行更改，并将存储过程发布。推荐第一种

∙将项目分布在多个发不上，这样相当于变向的实现了多线程并行发布，当然也可以通过参数实现并行，下面会讲到

3.参数优化：

∙降低复制代理的详细级别，在初始测试、监视或调试期间除外。 减小分发代理或合并代理的 –HistoryVerboseLevel 参数和 –OutputVerboseLevel 参数。这样可以减少为跟踪代理历史记录和输出而插入的新行数。相反，具有相同状态的以前的历史记录消息将更新为新的历史记录信息。提高测试、监视和调试的详细级别，这样就可以获得有关代理活动的尽可能多的信息。在系统开销不是很大的情况下，建议使用默认值1，本身这点对性能消耗可以忽略。

∙增大快照代理的BcpBatchSize参数，-BcpBatch参数表示一个批事务的行数。

∙将快照代理的- MaxNetworkOptimization设置为1，减少将不必要的信息发送到订阅服务器上。

∙使用快照代理、分发代理的 –MaxBcpThreads 参数（指定的线程数不应超过计算机上的处理器数）。此参数指定创建和应用快照时可以并行执行的大容量复制操作的数目。

∙为分发代理使用 –SubscriptionStreams 参数。 –SubscriptionStreams 参数可以显著提高聚合复制吞吐量。它使到一台订阅服务器的多个连接可以并行应用批量更改，同时在使用单线程时保持多个事务特征的存在。如果有一个连接无法执行或提交，则所有连接将中止当前批处理，而且代理将用单独的流重试失败的批处理。在重试阶段完成之前，订阅服务器上会存在临时事务不一致。失败的批处理成功提交后，订阅服务器将恢复到事务一致状态。官方文档有这个参数，但增加这个参数报错。

∙增大分发代理的 -CommitBatchSize 和CommitBatchThreshold参数的值。 提交一组事务的开销是固定的；通过不经常提交大量事务，就可以将开销分散在大量数据上。但是，增大此参数的优势因应用更改的开销由其他因素（例如包含日志的最大磁盘 I/O）限制而降低。另外，需要考虑以下权衡问题：任何导致分发代理重新开始的失败必须回滚并重新应用大量事务。对于不可靠的网络，越小的值导致失败的几率越小，如果导致失败，将回滚并重新应用较少量事务。

∙增大日志读取器代理的 -ReadBatchSize 参数的值。-ReadBatchSize 表示日志读取器代理和分发代理支持事务读取和提交操作的批大小。批大小的默认值为 500 个事务。日志读取器代理从日志中读取特定数量的事务，而不管这些事务是否标记为复制。将大量事务写入发布数据库，而其中只有一小部分标记为复制时，则应使用 -ReadBatchSize 参数增加日志读取器代理的读取批大小。此参数不适用于 Oracle 发布服务器。

∙为日志读取器代理使用 –MaxCmdsInTran 参数。–MaxCmdsInTran 参数指定日志读取器向分发数据库写入命令时组成一个事务的语句的最大数量。使用此参数能够使日志读取器代理和分发代理在订阅服务器上应用命令时将发布服务器上的大事务（由许多命令组成）分成若干个较小的事务。指定此参数可以减少分发服务器的争用问题并缩短发布服务器与订阅服务器之间的滞后时间。由于初始事务是以较小的单元应用的，订阅服务器可以在初始事务结束之前访问一个较大的逻辑发布服务器事务的行，因而会破坏事务的原子性。默认值为 0，这将保持发布服务器的事务边界。官方文档有该参数，但实际设置里面没有。

减小日志读取器代理和分发代理的-PollingInterval 参数的值。-PollingInterval 参数指

定为要复制的事务查询已发布数据库的事务日志的频率。默认值为5

秒。如果减小此值，日

志的轮询将更加频繁，这会使事务从发布数据库传递到分发数据库的滞后时间较低。但是，应该对较低滞后时间的需要和因频繁轮询导致的服务器上增加的负荷之间进行平衡。

快照初始化优化

测试数据表量1500w+，使用初始化默认的快照代理参数，复制的三个过程包括快照初始化，订阅初始化和数据修改复制，主要对快照代理、分发代理、日志读取代理分别作了参数优化，并给出优化前后的对照实验测试。

1.初始化快照

•在复制监视器中找到快照代理位置，右键，选择代理配置文件：

•查看默认配置：

·初始化快照时间:

2.优化参数测试

•新建一个快照代理配置文件，命名为“快照代理优化参数”

•查找这个代理配置文件的pro

use msdb

SELECT * FROM msdb.dbo .MSagent_profiles WHERE agent_type =1或者

EXEC sp_help_agent_pro 1

这里的agent_type注解如下

代理的类型：

1 = 快照代理

2 = 日志读取器代理

3 = 分发代理

4 = 合并代理

9 = 队列读取器代理

•修改配置文件，这里新增了MaxBcpThreads，MaxNetworkOptimization参数，修改了BcpBatchSize,HistoryVerboseLevel参数，对于性能提高最大的参数就是

MaxBcpThreads，其他参数调整对性能调高不大，但MaxNetworkOptimization参数一定要调整到1，可以减少传输到订阅服务器上不必要的操作。

USE msdb

EXEC sp_add_agent_parameter @pro = 17,

@parameter_name = 'MaxBcpThreads', @parameter_value = 4

EXEC sp_add_agent_parameter @pro = 17,

@parameter_name = 'MaxNetworkOptimization', @parameter_value = 1

EXEC sp_change_agent_parameter @pro = 17,

@parameter_name = 'BcpBatchSize', @parameter_value = 200000

EXEC sp_change_agent_parameter @pro = 17,

@parameter_name = 'HistoryVerboseLevel', @parameter_value = 1

•调整后的参数：

优化后测试结果，时间大大减少。但是如果在有业务负载同时生成快照，那么资源消耗很大，建议合理调整参数，不要设置太大并发，用默认配置已经有很高的I/O了。

订阅初始化优化

初始化订阅主要是由分发代理分发和应用快照代理之前生成的快照，所以优化的主体是分发代理。

1、初始化订阅

•首先在本地创建一个订阅，发布服务器、分发服务器和订阅服务器都在同一台服务器上，仅为了测试生产环境不要这样做

•分发服务器参数配置

•快照代理会在快照生成的初始阶段使用锁，虽然时间很短，但是影响范围较大。•在快照生成过程中，不能对表的结构进行更改。

2.优化参数测试

•新建一个分发代理配置文件，命名为“分发代理优化参数”

•查找这个代理配置文件的pro

SELECT * FROM msdb.dbo .MSagent_profiles WHERE agent_type =3

或者

EXEC sp_help_agent_pro 3

•修改配置文件

EXEC sp_change_agent_parameter @pro = 18,

@parameter_name = 'MaxBcpThreads', @parameter_value = 4 EXEC sp_change_agent_parameter @pro = 18,

@parameter_name = 'BcpBatchSize', @parameter_value = 100000 EXEC sp_change_agent_parameter @pro = 18,

@parameter_name = 'PollingInterval', @parameter_value = 500000

•优化后订阅库生成测试结果，从测试结果上看并没有明显的优化。MaxBcpThreads并没有实现多线程的数据导入，后台查询也只有一个会话在执行bulk in操作，BcpBatchSize对参数影响较小，建议设置的大一些；另外两个参数不影响订阅数据库的生成效率。其实订阅库生成是整个过程的性能瓶颈所在，如果能使用多线程还是不错的解决方法，希望看到博客的同学有好方法提供一下。

修改数据同步过程优化

1、原理

我说的数据修改同步过程指的是在快照生成完毕，分发代理将快照应用于订阅服务器完成订阅服务器初始化后，发布服务器后续的更改同步到订阅服务器过程，这也就是我们常常关注的延迟。此过程主要涉及两个代理，分发代理和日志读取代理，因为我们使用的是推送订阅，所以这两个代理都工作在分发数据库上。首先，日志读取代理读取发布事务日志，并表示任何INSERT、UPDATE 以及DELETE语句，然后将这些事务定时将批量复制到分发服务器的分发数据库中。分发库中存储着事务和命令，每个事务以多条命令组成。分发代理顺序分发分法库中的事务，以逻辑命令（SQL 语句）方式在订阅库上执行。流程图见下：

优化这两个代理主要考虑从两方面考虑，一方面是缩短每个批次的间隔，一方面需要在小间隔的情况下增加代理处理的性能，这两个方面是相关联的，所以要一起修改才能起作用。

2.复制延迟测试

•查看日志读取代理配置文件的默认参数：

•查看分发代理配置文件的默认参数：