Database read-write separation is a development stage that many companies cannot get around, from single database, to master/standby mode, and then to read-write separation, separate database and separate tables. Each stage solves some problems for us, but it also brings new challenges. In this article, we will focus on database read-write separation and how to solve the problems it brings.

1 development history of database architecture

First of all, a brief introduction to the development of database architecture, which is basically monomer, master and standby, read and write separation, database and table, we will introduce them respectively.

1.1 Single Architecture

In the early stages of a business, when database pressure is relatively low, a single library can be used.

If the database fails, our business will not be able to use it. We can only shut down and restart to repair the fault.

1.2 Active/Standby Architecture

At this time, we need to add a spare library due to the problems of the single unit. In case of emergency, we can use the top of the standby library, which is equivalent to adding a substitute.

With MySQL’s own master/slave synchronization mechanism, we can put our substitutes online.

When a regular player (the main library) fails, we can manually take him offline and replace him (the standby library).

Questions: With the explosion of business, the pressure on the main library is too much, so we want the standby library to take on more responsibility.

1.3 Read/write Separation Architecture

The read/write separation architecture is essentially an active/standby architecture. It is essentially the same as the active/standby architecture. On the basis of the active/standby architecture, a layer of read/write request processing is added to enable the standby database to share some read burden for us.

Read/write separation architecture requires a layer in the middle to control the routing of read/write requests

1.4 Database and table

The essence of database and table partitioning is to split data, which is due to the increase of data magnitude. Failure to split data will seriously affect database read and write performance.

Even if not shard, disk, memory, CPU can not bear such pressure, database at any time on the edge of collapse.

There are essential differences between library and table. After table, each library fragment can adopt any of the above three methods, such as single fragment, active and standby fragment, or read and write fragment.

One of the first three is a symbiotic relationship.

Do not know how to carry out the sub-table design can read my previous article “good collection of the martial arts secrets, let you sub-table no worries”

2 Read/write separation design scheme

Master/slave replication is a built-in function of MySQL database, but to do read/write separation, we need to do some work with MySQL master/slave synchronization. There are many alternatives.

2.1 the agent

The proxy layer is added between the application program and the database. The proxy layer receives the requests from the application program to the database and forwards them to different instances according to different request types. It can realize read and write separation and load balancing (read requests are transmitted to each slave node according to the rules of load balancing).

Proxy is a middleware approach that controls different types of requests into different databases.

Currently commonly used mysql read-write separation middleware includes:

  • MySQL-Proxy

    MySQL is an open source project of its own, through its own Lua script for SQL judgment

  • Atlas

    Qihoo 360, based on mysql-Proxy version 0.8.2, has been optimized to add some new features.

  • MyCat

  • MaxScale

    MariaDB development

  • Amoeba

    Ali development

  • .

2.2 Intra-Application Routing

In the program to control, we use the persistence layer framework of the interceptor implementation, dynamic routing of different data sources.

This can also be done using Sharding-JDBC

Implementation idea:

  • Configuring multiple Data Sources
  • Set the default data source and configure the data source switchover policy
  • Intercepts requests to the database and sets which data source to go to based on business requirements.

3 What about the read delay caused by READ/write separation?

With any read/write architecture, there is a synchronization delay problem, which we have to find a way to overcome.

3.1 Synchronizing data into the secondary library

In master-slave replication mode, data is written asynchronously to the slave database. However, this asynchronism can also be set to synchronization. Only when the slave database completes, the write request on the master database can be returned.

This scheme is the best and most efficient one, but it also has the worst performance, especially in the case of a large number of slave libraries, which seriously affects the request efficiency.

3.2 Cache (middleware) routing

The cache records a key when a write request is made, and the expiration time of the key is set to the delay of master/slave synchronization. When a read request is made, the cache checks whether the key exists. If the key exists, a write request is made and data is not synchronized to the slave library.

The middleware should also be able to determine whether synchronization is complete, similar to using cached records.

The biggest disadvantage of this scheme is the introduction of cache, which increases the system complexity.

3.3 Forcibly read the primary library selectively

For some special business scenarios, it is mandatory to read the master library.

The downside is that you need to find each of these situations and set it up to force the master library.

3.4 GTID scheme

After a transaction is executed, MySQL will send the GTID of the transaction to the client. Then the client can use this command to execute the read operation from the library, wait for the GTID, wait for the success, and then execute the read operation. If the wait times out, go to the primary library to perform the read operation, or switch to a secondary library to perform the above process.

MariaDB uses this solution with MaxScale, a database intelligent proxy service developed by MariaDB (which also supports MySQL) that allows requests to be directed to one or more servers based on database SQL statements, with varying levels of complexity.

The same should change

There are delays, and scenarios that do not require strong data consistency can be left alone.