Database Replication Performance Test and push Mode Performance Test

Database Replication is performed on two servers, the master server and the backup server. After the master server is modified, the backup server is automatically modified.ArticleHas made a detailed description, here is not repeated, see the http://www.cnblogs.com/AK2012/archive/2012/06/19/2012-0619.html for details

 

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 (hereinafter referred to as dql)It 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. Enable replication and synchronous writing,   The dql average 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,10000 million data writes in an endless loop     Process : Disable replication and write data. For the first test, the average value of dql is: 3.05- 3.08 second test: 2.2-2.30 Enable replication and synchronous writing, The 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.

 

Third case: Disable replication and write data to the master server  10000 data records, Write three tables each timeAnd then enable Dql remains unchanged, because data is written by the replication server, which is not highly 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 modification, we are going SQL Server Profiler You can see that the consumption is very small.     If your Server replication mode is subscription, you can modify the subscription time in the "proxy configuration file" and "Distribution agent ". The above is purely a personal opinion. For more information, see the source. -------------- AK: 2012-06-26