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

总体概述? 随着公司业务的发展，数据量增长迅速，在解决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才是我们理解上的读已提交。如果不设置这个，照样会读写互相阻塞。? ? ?在创建订阅之前，先将目标数据库的恢复模式改成简单，能够减少日志ALTERDATABASE[数据库名称]SET RECOVERY SIMPLE WITH NO_WAIT2.发布、订阅设计：仅发布需要的数据，对于将日志写入数据库而且还要同步复制的，DBA可直接拒绝。表中存在大量非聚集索引，发布时可以不勾选复制非聚集索引选项。经过测试，虽然数据库引擎是在应用完快照最后创建索引的，但创建时间很长，有时甚至会出错。可以在后期手工建立。我们采用导出脚本方式，然后只保留非聚集索引的脚本在订阅库中建立。如果资源允许，单独建立一个分发服务器。发布和分发服务器在一个服务器上会增加性能损耗，尤其是分发服务器下面有多个订阅服务器时。分发库默认是distribution库，会有专门的作业对该库进行清理，名字就叫“分发清除：distribution”。但这个进程清除分发库的效率太低了，会导致分发库积累大量数据，需要修改运行间隔和每次清楚的数据量。详见之前的随笔：distribution库过大的问题合理使用订阅方式，如果选择推送订阅，势必加大分发服务器的压力，但延迟低 ；如果选择请求订阅，代理服务是在订阅服务器上，减少了分发服务器压力，但延迟会增加。快照代理的生成。对于生成几百GB数据库的快照，采用SSD盘可能在10分钟左右。但会造成很高的性能损耗，要在业务非高峰期生成快照。快照代理文件也要放在SSD盘上，如果资源允许，单独一个驱动盘也可以。如果已经有备份了，那么可以通过备份直接来初始化订阅，省去了生成快照和应用快照的步骤。新发布新的表对象，对于发布库很大的情况下重新初始化是不实际的，可以增量发布，详见sqlserver同步后在不重新初始化快照的情况下新增表对于已发布的表有批处理更新，一种方法是将批量更新分成若干小批量执行，减小事务处理的数据量；另一种方法是通过存储过程执行更改，并将存储过程发布。推荐第一种将项目分布在多个发不上，这样相当于变向的实现了多线程并行发布，当然也可以通过参数实现并行，下面会讲到3.参数优化：降低复制代理的详细级别，在初始测试、监视或调试期间除外。 减小分发代理或合并代理的 –HistoryVerboseLevel 参数和 –OutputVerboseLevel 参数。这样可以减少为跟踪代理历史记录和输出而插入的新行数。相反，具有相同状态的以前的历史记录消息将更新为新的历史记录信息。提高测试、监视和调试的详细级别，这样就可以获得有关代理活动的尽可能多的信息。在系统开销不是很大的情况下，建议使用默认值1，本身这点对性能消耗可以忽略。增大快照代理的BcpBatchSize参数，-BcpBatch参数表示一个批事务的行数。将快照代理的- MaxNetworkOptimization设置为1，减少将不必要的信息发送到订阅服务器上。使用快照代理、分发代理的 –MaxBcpThreads 参数（指定的线程数不应超过计算机上的处理器数）。此参数指定创建和应用快照时可以并行执行的大容量复制操作的数目。为分发代理使用 –SubscriptionStreams 参数。 –SubscriptionStreams 参数可以显著提高聚合复制吞吐量。它使到一台订阅服务器的多个连接可以并行应用批量更改，同时在使用单线程时保持多个事务特征的存在。如果有一个连接无法执行或提交，则所有连接将中止当前批处理，而且代理将用单独的流重试失败的批处理。在重试阶段完成之前，订阅服务器上会存在临时事务不一致。失败的批处理成功提交后，订阅服务器将恢复到事务一致状态。官方文档有这个参数，但增加这个参数报错。增大分发代理的 -CommitBatchSize 和CommitBatchThreshold参数的值。 提交一组事务的开销是固定的；通过不经常提交大量事