数据库镜像——事务日志传输方式间的性能差异

背景

SQL Server主服务器和镜像服务器传输消息和日志记录的次序根据事务安全性的设置而不同：同步传输和异步传输，两者的性能差异到底有多大呢？

 性能测试

-- 创建测试表

create table t1(c1 int not null)

 

set nocount on

go

 

-- 开始测试计时

declare @i int;

declare @v int;

set @i=1;

while(@i<=10000)

begin

  set @v=RAND()*1000;

  insert into t1(c1) values(@v);

  set @i=@i+1;

end

 

-- 测试结果

如果主服务器和镜像服务器位于不同的硬件机器上，在主服务器上执行上面的测试代码：

synchronous 第一次01:10  第二次01:14

asynchronous 第一次00:00 第二次00:00（大约700ms左右）

异步的执行时间明显少于同步模式下的执行时间，“联网”图如下所示：

 

 

从网络传输的曲线图中也可以看出，异步传输的时间要明显短于同步传输，而且传输的数据量也要明显少很多。

另外，如果主服务器和镜像服务器如果是位于一台硬件服务器上的不同实例，这样的话就有可能出现，异步反而比同步花费更多执行时间的情况。（本本上的执行时间是：同步18秒，异步23秒左右）

         【出自blog.csdn.net/jerrynet，转载请注明作者出处】

 

补充阅读（摘自官方镜像文档）

当SQL Server将事务事件记录在事务日志中时，日志记录被写入磁盘前暂时存放在日志缓冲区中。 数据库镜像时，每次日志缓冲区被输出到硬盘时（硬化），主服务器也将相同的日志记录块发送到镜像服务器。

1. 当safety设置为FULL，只要SQL Server主服务器硬化它的日志记录块，就同时将相同的日志记录块发送到镜像服务器，并认为本地的日志I/O和远程镜像服务器的日志I/O从本质上来说是一样 的。这种传输称为同步的，因为在一个事务提交之前，主服务器既要等待本地的I/O（硬化）还要等待等待镜像服务器有关完成I/O（硬化）的答复。

每次主服务器或者镜像服务器硬化日志缓冲区时，都会将缓冲区中最高的日志序列号（LSN）+ 1作为mirroring_failover_lsn记录在元数据中。mirroring_failover_lsn用于协商事务日志最后的保障点，这样两个伙伴数据库就可以在初始化时保持同步，在故障转移后也保持同步。

当主服务器发送日志记录给镜像服务器时，主服务器上的mirroring_failover_lsn通常会提前一些。镜像服务器硬化日志记录时会记录其mirroring_failover_lsn，然后回复主服务器。但是等主服务器接收到来自镜像的确认信息时，主服务器可能已经开始硬化新的一组日志记录了。

表 8显示了主服务器和镜像服务器safety为FULL时的一个事件序列示例。

表 8. Safety为FULL （同步传输）事件序列的示例。

 

Server A

Server B

Principal, Synchronized

Mirror, Synchronized

开始一个包含数据更新的多语句事务

 

主数据库的事务日志记录被放入事务日志缓冲区

 

事务日志缓冲区内容被写入磁盘（硬化），日志记录块被发送到镜像服务器，主服务器记录日志块的 mirroring_failover_lsn，然后等待镜像服务器的确认。 

 

 

镜像服务器接收日志记录并放入事务日志缓冲区

 

镜像服务器将日志缓冲区输出到磁盘，记录 mirroring_failover_lsn，然后通知主服务器日志块已被硬化

主服务器接收日志记录已被镜像服务器硬化到磁盘的通知

镜像服务器继续重新执行REDO队列中的事务日志

包含了COMMIT的日志写入事务日志缓冲区

 

事务日志缓冲区内容被写入磁盘（硬化），

包含了COMMIT的日志记录块被发送到镜像服务器，主服务器记录日志块的 mirroring_failover_lsn，然后等待镜像服务器的确认。 

 

 

镜像服务器接收日志记录并放入事务日志缓冲区

 

镜像服务器将日志缓冲区输出到磁盘，记录the mirroring_failover_lsn，然后通知主服务器日志块已被硬化

主服务器接收日志记录已被镜像服务器硬化到磁盘的通知，至此整个事务提交

镜像服务器继续重新执行REDO队列中包含了COMMIT的事务日志，修改数据页面

新事务被写入主服务器的日志缓冲区

 

 

 

以上事件序列中关键的一点就是：当 safety设置为FULL时，主服务器硬化日志缓冲区以及将日志缓冲区中日志记录的副本发送到镜像服务器，二者是同时进行的。然后主服务器开始等待自己的I/O以及镜像服务器的I/O，两个I/O都完成后才认为事务完成了。当主服务器接收到来自镜像的答复后，再开始处理下一次硬化。

当safety设置为FULL时，尽管主服务器和镜像服务器之间协调紧密，但是数据库镜像不是分布式事务，也不使用两阶段提交协议。

·         在数据库镜像中，两个事务分别在两台服务器上执行，并不是一个跨服务器的分布式事务。

·         数据库镜像不使用伙伴服务器作为分布式事务中的资源管理器。

·         数据库镜像事务不经历准备和提交阶段。

·         最重要的是，镜像服务器上事务提交失败不会导致主服务器上的事务会滚，这一点与分布式事务不同。

2. 当safety设置为OFF时，主服务器不等待来自镜像服务器的确认消息，因此主服务器上已提交事务数量可能多于镜像服务器，如图9所示：

表 9. Safety为OFF （异步传输）事件序列的示例。

 

Server A

Server B

Principal, Synchronizing

Mirror, Synchronizing

开始一个包含数据更新的多语句事务

 

数据更新的事务日志记录被写入事务日志缓冲区

 

事务日志缓冲区内容被强制输出到磁盘（硬化），日志记录块被发送到镜像服务器，主服务器记录日志块的 mirroring_failover_lsn 

 

包含了COMMIT的日志被写入事务日志缓冲区，加上其他的事务活动

镜像服务器接收日志记录并放入事务日志缓冲区

事务日志缓冲区内容被写入磁盘，

包含了COMMIT的日志记录块被发送到镜像服务器

镜像服务器将日志缓冲区输出到磁盘，记录the mirroring_failover_lsn，然后通知主服务器日志块已被硬化

提交事务

镜像服务器继续重新执行REDO队列中的事务日志

 

镜像服务器接收日志记录并放入事务日志缓冲区

 

镜像服务器将日志缓冲区输出到磁盘，记录the mirroring_failover_lsn，然后通知主服务器日志块已被硬化

 

         【出自blog.csdn.net/jerrynet，转载请注明作者出处】