Is mysql master slave synchronization going on all the time?

After turning it on, there is no downtime, error or anything, and it will continue to be carried out.

Theoretically, master-slave replication supports an infinite number of slaves, but in practice, it is affected by the server bandwidth and read and write capabilities.

Theoretically, by using a single master multi-slave setup, the system can be expanded by adding more slaves until the network bandwidth is used up or your update load has grown to a point where the master server can't handle it.

In order to determine how many slaves you can have, and how much you can improve your site's performance, you need to know query patterns and benchmark and empirically determine the relationship between the read (reads per second, or max reads) throughput and the write (max writes) throughput of a typical master and slave server. Using a hypothetical system with replication, this example gives a very simple calculation of hail burial.

Let's say the system load consists of 10% writes and 90% reads, and we determine that max reads are 1200 2 max writes by benchmarking. In other words, without writes, the system can perform 1,200 reads per second, with an average write operation taking twice as long as the average read operation, and the relationship is linear. We assume that the master server and each slave server have the same performance, and we have a master server and n slave servers.

Well, for each server (master or slave), we have:

reads=1200_2×writes

reads=9 writes (n1) (reads are detached, but writes to all servers).

9×writes/(n1)2×writes=1200

writes=1200/(29/(n1))

The final equation indicates the maximum number of writes to n slave servers, assuming that the maximum possible read rate is 1,200 per minute, and the ratio of reads to writes is 9.

The above analysis can lead to the following conclusions:

If n=0 (which indicates that there is no replication), the system can handle about 1200 11=109 writes per second.

If n = 1, you get 184 writes per second.

If n=8, you get 400 writes per second.

If n = 17, you get 480 writes per second.

1.Too many slaves cause replication delays.

Optimization: It is recommended that the number of slaves be 3-5.

2.The slave hardware is worse than the master hardware.

Optimization: Improved hardware performance.

3.There are too many slow SQL statements.

Optimization: SQL statements take too long to execute, and SQL statements 4Design issues with master-slave replication.

Optimization: Master-slave replication is single-threaded, which can be solved by multi-threaded IO scheme. In addition, multi-threaded IO replication is supported.

5.Network latency between master and slave databases.

Optimization: Keep the link as short as possible and increase the port bandwidth.

6.The primary database has high read and write pressure.

Optimization: Add buffer and cache to the frontend. Master-slave delay out of sync:

No matter how delayed, as long as it doesn't affect the business, it's fine.

7. Business design defects cause delays that affect the business.

Optimization: No data from the secondary database is redirected to the primary database.

When I use pt-table-checksum, will it affect business performance?

Experiment. Let's start with a pair of master and slave:

Then use mysqlslap to run a constant stress:

Open another lease mega session and open the general log on the master:

Then make a comparison via pt-table-checksum:

Looking at the general log of the master, there is a lot of content in the general log due to mysqlslap, we find the thread related to pt-table-checksum:

The operations of this thread are listed separately by the World Bank

There are many operations, let's explain them little by little:

Here the tool has reduced the INNODB lock wait time. As a result, as long as there is a slight lock waiting on the innodb, the operation will be abandoned immediately, and the impact on the business will be small.

In addition, the tool has reduced the wait timeout time, which has no special effect.

The tool adjusts the isolation level to the RR level, and the maintenance cost of the transaction will be higher than that of RC, but as we will see later, each transaction used by the tool is very small, and the waiting time for the innodb lock mentioned earlier is adjusted to a small one, which will cause less cost to the online business.

The RR level is a basic requirement for data comparison.

The tool provides an overview of the table through a series of operations. The tool is validated one block at a time, and here the lower boundary of the first block is obtained.

Next, the tool obtains the lower boundary of the next data block, and will explain it before each SQL to see if the execution is cheating, and the leaseback is very careful.

After that, the tool obtains the checksum of a data block, which is not large, and if there is a conflict with the business traffic, it will immediately start The lock of innodb times out and immediately retreats.

The above are some of the designs of pt-table-checksum, and you can see that these places are carefully maintained to ensure that business traffic is not affected.

The tool also designs other mechanisms to ensure business traffic, such as the parameters --max-load and --pause-file, as well as well-designed data block division methods, index selection methods, etc. Everyone can use it together according to their own situation to achieve good results.

Summary. In this issue, we introduce a simple analysis of whether pt-table-checksum will affect business traffic, and various parameters of the tool are recommended or not recommended to be used, and there are many cases of fortune telling, so you can use simple experiments to analyze the mechanism.

Again, performance testing cannot be trusted by hearsay, but must be analyzed through experiments.