16 Read Write Separation Design Replication Delay Is Actually Your Misuse

16 Read Write Separation Design - Replication Delay Is Actually Your Misuse #

In the last lecture, we learned about the principle of master-slave replication and the selection of different replication types for different businesses. Today, let’s take a look at the issue of replication delay in master-slave replication.

Many students may find that their master-slave replication has a delay in data synchronization, which can even lead to serious problems in read-write separation and architecture design at the business level, such as the inability to read data that has already been inserted into the master database.

But this may not be a problem with MySQL replication, but rather a problem with your business not being designed according to the characteristics of MySQL replication.

So in this lecture, we will learn about the reasons for replication delay in master-slave replication and how to avoid this annoying problem.

Pros and Cons of Logical Logs #

After studying Lecture 15, you should have noticed that MySQL replication is based on binary logs, which are logical logs that write the before and after images of each modified record in each transaction.

With the changes to each record, users can easily synchronize the data in MySQL to heterogeneous data platforms such as HBase, Elasticsearch, Hive, and other big data platforms by analyzing the content of MySQL’s binary logs.

We can see that logical logs are simple and easy to understand, and they facilitate data synchronization between systems. However, they have the following drawbacks: transactions cannot be too large, otherwise the binary logs will be very large, and committing a large transaction will be very slow.

For example, suppose you have a DELETE operation that deletes monthly data, and the data volume may be 100 million records, which may result in 100GB of binary logs. Then, when this SQL statement is committed, it needs to wait for the 100GB of binary logs to be written to disk. If the disk’s writing speed for binary logs is 100MB/s, it will take at least 1000 seconds to complete the transaction.

Therefore, in MySQL, you must treat large transactions differently. In summary:

  1. During design, convert DELETE operations into DROP TABLE/PARTITION operations;
  2. During business design, split large transactions into smaller transactions.

Regarding the first point (converting DELETE operations into DROP TABLE/PARTITION operations), the main idea is to partition log or transaction tables by time during design. By doing this, when deleting data, the content of the binary logs will be a DROP TABLE/PARTITION SQL statement, and the writing speed will be very fast.

Splitting large transactions into smaller transactions, as mentioned in the second point, can also control the size of the binary logs. For example, for the previous DELETE operation, if no partitioning was done during design, you can split the transaction into smaller operations like the following:

DELETE FROM ...
WHERE time between ... and ...
LIMIT 1000;

The above SQL statement splits a large DELETE operation into small operations that delete 1000 records at a time. Another advantage of smaller transactions is that they can be performed concurrently with multiple threads, further improving deletion efficiency.

In MySQL, large transactions not only slow down the commit speed but also cause replication delay in master-slave replication.

Imagine that a large transaction runs on the master server for 30 minutes, then it will take 30 minutes to run on the slave server as well. During the replay process on the slave, there will be a delay in data synchronization between the master and slave. Another possibility that can cause large transactions is when there are no indexes created on the master server, resulting in a simple operation taking a long time. This will also cause replication delay when replayed on the slave.

In addition to splitting large transactions into smaller ones to avoid replication delay in master-slave replication, you can also configure replication-related parameters for replay optimization. Next, we will analyze the optimization of replication delay in master-slave replication.

Optimization of Replication Delay in Master-Slave Replication #

You must remember: to completely avoid MySQL replication delay, your database version must be upgraded to 5.7 or above, because in previous versions of MySQL, the replay of binary logs on the slave was single-threaded (in 5.6, it was based on single-threading at the library level).

Starting from MySQL 5.7, MySQL supports a multi-threaded replay of binary logs on the slave, usually referred to as “parallel replication” or “Multi-Threaded Slave (MTS)” as stated in the official documentation.

MySQL’s parallel replication on the slave has two modes:

  1. COMMIT ORDER: The slave replays in parallel exactly as it was done on the master.
  2. WRITESET: Based on each transaction, as long as the records updated by the transactions do not conflict with each other, they can be replayed in parallel.

In COMMIT ORDER mode, the slave replays in parallel according to the parallelism of the master server. In theory, there is minimal delay in master-slave replication. However, if the parallelism on the master server is very low but the transactions are not small, for example, if a single-threaded insert operation inserts 1000 records each time, the slave will also replay in a single thread, resulting in some replication delay.

On the other hand, in WRITESET mode, replication on the slave is based on each transaction, and as long as the records updated by the transactions do not conflict with each other, they can be replayed in parallel. Using the example of a single-threaded insert operation that inserts 1000 records each time, if there is no conflict in the inserted records, such as unique index conflicts, even though the master is single-threaded, the slave can replay in multiple threads in parallel!!!

Therefore, in WRITESET mode, there is almost no replication delay in master-slave replication. To enable WRITESET replication mode, you need to make the following configurations:

binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16

Because replication delay in master-slave replication can affect subsequent high availability failover and read-write separation architecture design, in real business scenarios, you need to monitor replication delay.

Monitoring Replication Delay in Master-Slave Replication #

Seconds_Behind_Master #

Many students may know that the command SHOW SLAVE STATUS can be used to check replication delay, which includes the column Seconds_Behind_Master, as shown below:

Drawing 0.png

However, Seconds_Behind_Master is not accurate! It is not suitable for strict judgment of replication delay issues. There are three reasons for this.

  1. The calculation of Seconds_Behind_Master is based on the time difference between the current replayed binary log and the time in the binary log. If the I/O thread has a delay, Seconds_Behind_Master will be 0, but at this point, the slave may be significantly behind, especially when there are large transactions involved.
  2. For cascading replication, the delay of the downstream slave is not accurate because it only represents the delay between the current slave and the previous master;
  3. If the time zones of the master and slave are different, then the seconds_behind_master is also not accurate;

In summary, monitoring replication delay using the seconds_behind_master value in production is not accurate. An extra table needs to be introduced to truly monitor the replication delay between the master and slave servers.

Heartbeat Table #

To monitor the replication delay between the master and slave servers in real-time and accurately, an additional heartbeat table can be introduced on the master server to periodically update the time (e.g., every 3 seconds). With the master-slave replication mechanism, the time written on the master server will be replicated to the slave server. At this point, the replication delay can be determined based on the following rule:

Replication delay = Current time on the slave server - Time in the heartbeat table

This can solve the problems that exist with the seconds_behind_master value. The heartbeat table and the periodic updating time can be designed similarly:

USE DBA;

CREATE TABLE heartbeat (

  server_uuid VARCHAR(36) PRIMARY KEY,

  ts TIMESTAMP(6) NOT NULL

);

REPLACE INTO heartbeat(@@server_uuid, NOW())

In the above design, we create a database called DBA and a table called heartbeat under that database to record the current time.

The REPLACE statement is used to periodically update the current time and store it in the heartbeat table. The heartbeat table should only have one record under normal operation. The periodic execution of the REPLACE statement can be scheduled using a script or using the built-in event scheduler in MySQL, like this:

CREATE EVENT e_heartbeat

ON SCHEDULE

    EVERY 3 SECOND

DO

BEGIN

    REPLACE INTO DBA.heartbeat VALUES (@@server_uuid,NOW())

END

With the above two sections explained, you should be able to configure parallel replication correctly and monitor the replication delay. At this point, you can design a business architecture called read-write separation.

Read-Write Separation Design #

Read-write separation design refers to distributing read and write requests to different database servers. For write operations, they can only be requested on the master server, while for read operations, the requests can be distributed to different slave servers.

This effectively reduces the load on the master server, improves the utilization of resources on the slave servers, and further enhances the overall performance of the system. The following diagram shows a common architecture design for read-write separation:

Drawing 1.png

The diagram introduces a load balancer component, which means the database server’s requests don’t need to care about how many slave servers are behind them. For the business, it is transparent, and they only need to access a load balancer server’s IP or domain name.

By configuring the load balancing service, read requests can be evenly distributed or distributed based on weights to different slave servers. This can be flexibly designed according to the architecture’s needs.

Please keep in mind that the premise of read-write separation design is that the slave servers should not lag behind the master server by a lot. It is best to have near real-time data synchronization. Be sure to start parallel replication and make sure that significant transactions have been split into smaller ones.

Of course, for some report-type queries, as long as they don’t affect the final results, the business can tolerate some delay. But no matter what, please make sure to monitor the replication delay in the online database environment.

If, due to some unforeseen circumstances, such as a junior DBA performing a large transaction operation on the master server, causing replication delay, how can we ensure fallback for read-write separation design?

Drawing 2.png

On the load balancer server, a small percentage of read requests can be configured to access the master server, as shown in the above diagram, with only 1%. The remaining three slave servers can each handle 33% of the read requests.

If a serious master-slave replication issue occurs, the weights for the slave servers can be set to 0, and the weight for the master server can be set to 100%. This way, there will be no impact on the business due to data delay.

Summary #

This lecture is an extension of the previous lecture on replication. We learned how to solve the potential data delay issue in master-slave servers and how to build a read-write separation architecture based on the master-slave replication mechanism. In summary:

  1. MySQL binary log is a logical log that facilitates data synchronization to heterogeneous data platforms.
  2. The logical log is written only upon transaction commit. If there are large transactions, the commit speed will be slow and will affect the synchronization of data between the master and slave.
  3. In MySQL, it is essential to split large transactions into smaller ones in order to avoid replication delay between the master and slave.
  4. By configuring MTS (Multi-Threaded Slave) parallel replication mechanism, the replication delay between the master and slave can be further reduced. It is recommended to use MySQL 5.7 version and configure it based on WRITESET replication format.
  5. Monitoring master-slave replication delay cannot rely on the value of seconds_behind_master. The best method is to configure an additional heartbeat table.
  6. Read-write separation is a very common method in architectural design, and it is essential to master it and to design fallback measures in case the read-write separation architecture fails.

The content of this lecture is quite advanced, so I hope you can read it several times at home. It will be of great help to your architectural design skills.