Database Replication performance and push mode performance tests

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.