Database Replication performance test push Mode Performance Test

Database Replication is performed on two servers, the primary server and the backup server. After the primary server is modified, the backup server is automatically modified. This is described in detail in previous articles, this is not repeated here. For more information, see

Http://www.jb51.net/article/30661.htm

The first concern of people who use database replication is the performance consumption of the master server and backup server. I am also very worried about this. After checking for half a day, I basically did not find similar test instructions, I tested it myself. The following test results are for your reference only.
I am using the replication mode of database push. The following test page is based on this mode.
Because database replication is mainly for I/O operations, this test mainly tests the server's hard disk read/write operations. The main Monitored object of this test is
Avg. disk queue length (dql) can be understood as an external manifestation of disk data throughput. Generally, two different points are randomly obtained on the curve. A higher value indicates that the number of read/write operations in progress is relatively large, and the opposite is relatively small.
First case: write data once per second, write data to three tables at a time, and write 10000 data records cyclically
Process: Disable replication and write data. The average dql value is 0.126.
Enables replication and synchronous writing. The average dql value is 0.132.
Conclusion: In view of this proportion, one second is the write and synchronization of this small database. We can ignore it completely.

Case 2: Ignore the wait time, write data to three tables at a time, and write 10000 data in an endless loop
Process: Disable replication and write data. First test: dql average value: 3.05-3.08 second test: 2.2-2.30
Enable replication and synchronous writing. The maximum dql average value is 3.06-3.10. Second test: 2.2-2.34.
Conclusion: The interval between the two tests is long, and the machine conditions are inconsistent. However, the results are obvious, but the differences are not significant.

The third case: Disable replication. The master server writes 10000 data records, writes three tables each time, and then enables the server. The dql of the master server remains unchanged because the master server writes data, not relevant to the master server

In the above cases, replication basically does not affect the performance consumption of the master Server. However, by monitoring SQL Server Profiler, we can find that there are a large number of replication monitors, such replication monitors, this will consume a lot of server performance, resulting in server slowness. Because the push mode is used, the master server must monitor its own changes at all times, resulting in performance consumption, such

How can this problem be solved? We will first think of reducing the monitoring frequency of the master server and enabling the replication monitor,

Right-click the publishing server attribute settings and modify the refresh speed. Generally, we can accept a latency of 30 to 60 seconds.

After the modification, we will go to SQL Server Profiler to view the results, and we will find that the consumption will be very small.

If your Server replication mode is subscription, you can modify the subscription time in the "proxy configuration file" and "Distribution agent ".
Author: cnblogs zookeeper