Database replication is made up of two servers, primary servers and backup servers, the main server modified, the backup server automatically modified, in the previous article has done a detailed description, here is not repeated, see
Http://www.jb51.net/article/30661.htm
Using the database replication, the first concern is the primary server and backup server performance consumption problem, I am also very worried about this, check for a half-day, basically did not find a similar test instructions, on their own test, the following for the test results, for reference only
I am using the database push replication mode, the following test page is based on this pattern
Because database replication is mainly I/O operations, this test the main test server's hard drive read and write operations, this test mainly monitored the object is
Avg. Disk Queue Length (hereinafter referred to as DQL) is simple to understand the external manifestation of disk data throughput. Popular will be on the curve randomly take two different points, a high point is that the volume of read and write operations is larger, conversely, smaller.
The first case: 1 seconds to write data, one data to write three tables, loop to write 10,000
Process: Turn off replication, simple write, dql mean maximum value is: 0.126
Open replication, synchronous write, DQL mean maximum value: 0.132
Conclusion: Given this ratio, 1 seconds is the writing of this small database, synchronization problem, we can completely ignore the
Second: Ignore wait time, write data to three tables, death loop write 10,000 times data
Process: Turn off replication, simple write, first test: DQL mean maximum value: 3.05-3.08 Second Test: 2.2-2.30
Open replication, synchronous write, DQL mean maximum value: 3.06-3.10 Second Test: 2.2-2.34
Conclusion: Due to the long interval of two Tests, the condition of the machine is not consistent, but the result is very obvious.
The third situation: Turn off replication, the primary server write 10,000 data, write three tables each time, and then turn on the server, the primary server dql basically unchanged, because the replication server write data, and the primary server is not associated with
In this case, replication basically does not affect the performance consumption of the primary server, but by monitoring SQL Server Profiler we will find that a large number of replication monitors, this replication monitor is very consuming server performance, resulting in slow server, because the push mode, So the main server to monitor their own changes at all times, resulting in performance consumption, the following figure
How to solve this problem? The first thing we can think of is to reduce the primary server's monitoring frequency, to open the Replication Monitor,
Right--"Publisher server property settings, modify the refresh rate, generally we can accept the range is 30-60 seconds of delay
When we go to the SQL Server Profiler, we see that we're basically going to consume very little.
If your server replication mode is subscription mode, then you go--agent profile---Distribution Agent--Inside to modify your subscription time
Author: cnblogs Habits
