Shulou(Shulou.com)06/01 Report--

Introduction

MySQL Binlog is used to record the structured query language (Structured Query Language,SQL) statement information that users perform on the database. Is the binary log of the MySQL database, and you can use the mysqlbin command to view the contents of the binary log. Iqiyi uses MySQL Binlog in the member order system to achieve order event-driven. After using Binlog, it not only simplifies the system design, but also improves the usability and data consistency of the system.

This article will understand the relevant technical principles of MySQL from the perspective of practical application, from the combination of technical principles and working practice, to help you and the potential problems in the related design, hoping to give you some help and inspiration and common progress.

Author introduction: the author Uncle Fan is mainly responsible for the technology and architecture of iqiyi member trading system, focusing on asynchronous programming, service governance, code refactoring and other fields, loving technology and willing to share.

Background

Binlog is a very important log in MySQL, which is mainly used for synchronous data replication between MySQL master and slave. Because of this function of Binlog, it is also used for MySQL to synchronize data to other types of databases, as well as event-driven design of business processes. Through research and analysis, we find that using MySQL Binlog to achieve event-driven design is not as simple as expected, so we will take you to understand MySQL's Binlog, Redo Log, data update internal processes, and through the introduction of these technical principles, to analyze the problems that may be caused by the business process, and how to avoid these problems. I hope that through the analysis of this article, we can help you understand some of the principles of MySQL, so as to help you use the popular database technology of MySQL more smoothly.

Event-driven based on Binlog

First of all, this paper introduces the design of the member order system, which sends messages directly to MQ and drives the subsequent business process through asynchronous messages to realize the message-driven design. The general schematic diagram of the business process is as follows:

Figure 1: order event driver for sending messages directly

This design needs to ensure the data consistency of database operations and message operations, that is, data preservation and message transmission should be all successful or all failed. Obviously, it is not appropriate to send messages before the data is saved and in the transaction. We send a message outside the database transaction after the data update operation. If the data is saved successfully, but the message fails, the payment system needs to re-notify (step 1 above) until the notification is successful. Although this design realizes the function and the basic requirements for usability, it has the following shortcomings: 1. The business system directly depends on the message middleware: the failure of the message middleware will not only affect the processing of payment notification, but also affect other interfaces on the business system. two。 The business system must achieve reliable retry: both the request initiator and the request receiver must achieve reliable retry in order to achieve the goal of best effort notification. 3. Increasing the retry interval can cause business delay: as the number of retries increases, the interval between each retry usually becomes larger and larger, which is called Exponential Backoff (exponential Backoff). This design can make the fault handling of the request receiver more leisurely and avoid the service of the request receiver being difficult to recover due to intensive retry. However, doing so may cause the request receiver to process the backlog of messages long after the service is restored, resulting in business delay. We can adopt an adaptive design similar to Hystrix and return to the normal request rate after the request receiver service is restored. But such a design would obviously be much more complicated. In order to solve the above problems and simplify the technical architecture, we adopt the design idea of event table and use the order table as the event table. By subscribing to the Binlog of the order table, order events are generated and subsequent business processes are driven. In the system architecture, the business system does not directly rely on message middleware, but only needs to focus on database operations. Through the introduction of an independent system that receives Binlog, MySQL data changes are transformed into business event-driven subsequent processes. The specific process is as follows:

Figure 2: order event-driven based on Binlog

Hidden questions

As mentioned above, although there are many advantages of order event-driven design based on Binlog, it is later found that there are hidden problems. Through the experiment, we found that there are occasional delays in the performance of orders.

In the normal process, after receiving the order payment event, the order fulfillment service will check the order status, and if the order status is paid, the fulfillment process will be carried out. However, for an order with performance delay, after the order fulfillment service receives the payment event for this order, the query database finds that the order is not a payment status. After investigation, we eliminate the problem of data concurrent coverage, and the order status query occurs on the master database, and there is no master-slave synchronization delay problem.

So what is the reason that after the business system receives the order payment event generated according to Binlog, the order data obtained by querying the main database is not paid?

Let's put aside the causes of this problem and take a look at the internal principles of MySQL when updating data.

Principles related to MySQL data updates this section will introduce the principles related to MySQL data updates, as well as the two most important logs in the process: Redo Log and Binlog.

Redo Log and Binlog first introduce Redo Log and Binary Log (Binlog):

Redo Log:Redo Log is a physical log structure provided by the InnoDB storage engine, which is used to describe the specific content of the operation of the underlying data pages. It is mainly used to implement crash-safe and improve the efficiency of disk operation. Binlog:Binlog is a kind of logical log provided by MySQL itself, which has nothing to do with the specific storage engine. It describes the SQL statements or data changes executed by the database, and is mainly used for data replication. The purpose of introducing Redo Log into InnoDB is to achieve crash-safe and improve the efficiency of data update. If every data write operation of InnoDB is persisted directly to the data page on disk, it will greatly increase the number of random IO on disk. With the introduction of Redo Log, some random IO writes will be changed into sequential writes when writing to data. Because the sequential IO of disks is much more efficient than random IO, the introduction of the Redo Log mechanism helps improve performance when updating data (how to implement crash-safe is described in the next section). The following table illustrates the role of the two logs and their differences:

Redo Log

Binlog

Log type

Physical log, that is, the real two-tier data in the data page, with fast recovery

Logical logs, SQL statements (statement) or data logical changes (row), slow recovery

Storage format

Storage based on InnoDB data page format

SQL statement or data change content

Use

Redo data page

Data replication

Hierarchy

InnoDB storage engine layer

MySQL Server layer

Recording mode

Circular writing

Additional write

Here comes the problem. Now there are two log structures in MySQL: Redo Log and Binlog. Although they have different structures and functions, they record the same data. How to ensure the consistency of these two kinds of log data, and how to implement crash-safe? This leads to a two-stage submission design. > Because MySQL uses plug-in storage engine design, when a transaction is committed, both the server itself and the storage engine need to commit data. So from the point of view of MySQL server, it is faced with the problem of distributed transaction. To solve this problem, MySQL introduced two-phase commit. During the two-phase commit process, Redo Log has two operations: Prepare and Commit. Binlog writes are sandwiched between Redo Log's Prepare and Commit operations. We can imagine how the design of two-phase commit under different failure scenarios ensures data consistency: 1. Redo Log Prepare succeeds, crashes before writing Binlog: transactions are rolled back after failure recovery. In this way, the contents of Redo Log and Binlog are the same. This situation is relatively simple, but what is more complicated is the next case, that is, how does MySQL handle a crash between writing Binlog and Redo Log Commit? two。 After writing Binlog, but Redo Log crashed before Commit. If Redo Log has a Commit logo, it means that Redo Log has actually succeeded in Commit. At this point, the transaction is committed directly; if the Redo Log does not have a Commit identity, use XID (transaction ID) to query the corresponding Binlog log and check the integrity of the log. If the Binlog is complete, commit the transaction, otherwise roll back; how to determine whether the Binlog is complete? To put it simply, Binlog in Statement format ends with Commit, or Binlog in Row format has XID Event, then Binlog is complete. > The actuator first goes to the engine to fetch the ID=2 line. ID is the primary key, and the engine uses a tree search to find this line. If the data page of the ID=2 line is already in memory, it is returned directly to the executor; otherwise, you need to read the memory from disk and then return it. The executor takes the row data given by the engine, adds 1 to this value, for example, it was N, now it is Number1, gets a new row of data, and then calls the engine interface to write this new line of data. The engine updates this new line of data to memory. Updates to the in-memory data page are then recorded in Redo Log Buffer (Redo Log Buffer is not covered in detail here. Just know that the operation on Redo Log is not written directly to the file, but is recorded in memory and then written to disk at a specific time. The data update operation is completed at this point. The next step is to perform the transaction commit. When the transaction commits, the Redo Log is marked as Prepare. Usually at this point, Redo Log is written to disk from Buffer (innodb_flush_log_at_trx_commit, with a value of 1, Redo Log is written to disk each time the transaction is committed). The InnoDB then informs the executor that the execution is complete and the transaction can be committed. The executor generates the Binlog for this operation and writes the Binlog to disk. The executor calls the commit transaction interface of the engine, and the engine changes the Redo Log just written to the commit Commit status, and the update is completed.

The figure describes the MySQL executor, InnoDB, and the interaction between Binlog and Redo Log during the execution of update statements (the dark green background is the stage in which the MySQL executor is responsible, and the light green background is the stage in which InnoDB is responsible)

Problem analysis

From the introduction of the principle of MySQL, we know that writing Binlog occurs in the transaction commit phase, but MySQL introduces a two-phase commit because it introduces different log structures in both the Server layer and the storage engine layer. Writing to Binlog occurs before the storage engine actually commits the transaction, which makes it possible for systems that theoretically synchronize data through Binlog (MySQL slave libraries, other databases, or business systems) to validate the most recently committed data before the MySQL master library.

Therefore, after receiving the order payment event based on Binlog, the order fulfillment service mentioned above found that the corresponding order was unpaid, probably because when the order fulfillment service queried the data, the order payment data update operation had not completely completed the transaction submission within MySQL.

We recreate this phenomenon by developing a verification program. When the validator receives the complete Binlog after the transaction commits, it will query the corresponding records on the MySQL main database again, and the result will have some overview to get the data before the transaction commit.

In addition, after understanding, some peers have reported that they have encountered the problem of seeing the data submission from the database earlier than the main database.

The solution to the problem

After understanding the reasons behind the problem, we need to think about how to solve the problem. There are currently two ways to solve this problem: try again and use Binlog data directly.

Retrying this is simple and crude, and since the problem is that Binlog predates transaction commit, it will be solved by retrying the query later. However, in practice, you need to consider the implementation of retry and whether the service exception will be caused by too many or even unlimited retries. For the implementation of retry, the methods that can be used are threaded Sleep method and message recasting. The thread Sleep approach is usually not recommended because it can cause thread utilization to decrease and even cause the service to fail to respond. However, considering the low probability of this problem, we think that the thread Sleep method can be used, and this method is simple and easy, and can be used to quickly fix the problem.

The second retry method is message reinvestment. For example, message reinvestment can be triggered when Consumer returns ConsumeConcurrentlyStatus.RECONSUME_LATER in RocketMQ. However, the cost of this retry method is higher than that of the previous method, and the retry interval is also relatively large, which has a greater impact on time-sensitive business, so whether to adopt this method needs to be considered from both business and technical point of view.

In addition to considering how to retry, also consider the ABA problem, that is, the state change occurs in the way A-> B-> A. The expected state of the business system is B, but it may no longer be B in practice. Therefore, before using retry to solve this problem, you need to rule out the possibility of ABA problems in the business system. The problem of state ABA can be solved by means of state machine and so on, and it will not be discussed here.

In addition to retrying, another way is to use Binlog directly. Because Binlog (row format) directly reflects the changes in the data, in which the complete data involved in the transaction commit can be recorded, it can be used directly as a business process. This also reduces the database QPS. If it is a newly designed system, I think this is more ideal. However, for the existing system, this method may be changed greatly, and whether to adopt it or not needs to weigh the costs and benefits.

Recruitment information

Iqiyi member development team is looking for Java senior engineers / technical experts. Membership business is one of iqiyi's core business. We are committed to serving the core business through technical means and developing universal and highly available business systems. At the same time, we also need talents who are good at technologies such as database, service governance, MQ and so on. Students who are interested are welcome to send their resumes to: luodi@qiyi.com (email title please indicate: member development)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.