The separation of database reading and writing is a development stage that many companies can't get around , From monomer database , To active standby mode , Then to read-write separation , Sub database and sub table . Each stage can solve some problems for us , But it also brings new challenges . In this article, we mainly study the database read-write separation and how to solve the problems it brings .
1 Development of database architecture
First, briefly introduce the development of database architecture , Basically monomer , Active and standby , Read write separation , Sub database and sub table , Let's introduce them respectively .
1.1 Monomer architecture
Early stage of business development , The pressure on the database is relatively small , At this time, a single library can be used .
Questions raised ： If the database fails , Our business cannot be used , It can only be said to stop and restart to repair the fault .
1.2 Active / standby architecture
Problems caused by monomer , At this time, we need to add a spare library , In case of emergency, it can be used on the top of the standby warehouse , It's equivalent to adding a substitute .
adopt MySQL Built in master-slave synchronization mechanism , We can put our substitutes online .
Be a regular member （ Main library ） Failure , We can manually get it offline , Let the substitutes （ Spare database ） highest point .
Questions raised ： With the large-scale outbreak of business , The pressure of the main reservoir is too high , We just want the backup library to take on greater responsibility .
1.3 Read write separation architecture
The essence of the read-write separation architecture is the active / standby architecture , There is no essential difference between active and standby architectures , It is based on the active and standby architecture , Add a layer to handle read / write requests , So that it can make greater use of the standby library to share some reading pressure for us .
Read write separation architecture , You need to add a layer in the middle to control the routing of read-write requests
1.4 Sub database and sub table
The essence of sub database and sub table is to segment data , It is due to the improvement of data level , Not segmenting data will seriously affect the read and write performance of the database .
Even if you don't cut it , disk , Memory ,CPU Can't bear such pressure , The database is on the verge of collapse at any time .
Sub database and sub table are essentially different from the first three , After dividing the database into tables, each database partition can adopt any of the above three methods , It can be monomer fragment , It can also be active and standby partition , It can also be a fragment with read-write separation .
The relationship between sub database and sub table and one of the first three is symbiosis .
2 Read write separation design scheme
Master slave replication is MySQL Functions of database , But if we want to separate reading and writing, we need to do some work ourselves MySQL Master slave synchronization . There are many options .
2.1 agent
Add an agent layer between the application and the database , The agent layer receives the application's request for the database , Forward to different instances according to different request types , Load balancing can be realized while separating reading and writing （ Read requests are transmitted to each slave node according to load balancing rules ）.
Agent is the way of using middleware , Control different types of requests , Enter different databases .
Currently commonly used mysql Read write separation Middleware ：
*
MySQL-Proxy
MySQL Own an open source project , Through its own Lua Script SQL judge
*
Atlas
Qihoo 360, stay mysql-proxy 0.8.2 Based on version , It is optimized , Added some new features .
*
MyCat
*
MaxScale
MariaDB development
*
Amoeba
Ali development
*
...
2.2 Intra application routing
Control in the program , We use the interceptor implementation of the persistence layer framework , Dynamically route different data sources .
utilize Sharding-JDBC It can also be realized
Realization idea ：
* Configure multiple data sources
* Set default data source , Configure the switching strategy of the data source
* Intercept requests to enter the database , Set which data source to go according to business requirements .
3 What about the read delay caused by read-write separation ?
Generally, the read-write separation architecture is adopted , There will be synchronization delay , We can only find a way to overcome this problem .
3.1 Data synchronous write slave Library
Master slave replication mode , Generally, data is written to the slave library asynchronously , Of course, this asynchrony can also be set to synchronous , Only when writing from the library is complete , Only write requests on the main library can be returned .
This scheme is the best and most effective one , But it is also the one with the worst performance , Especially when there are a large number of slave libraries , Seriously affect request efficiency .
3.2 cache （ middleware ） Routing method
Cache record on write request key, this key The failure time of is set as the delay of master-slave synchronization , When reading a request, first go to the cache to confirm whether it exists key, If key Existence indicates that a write request has occurred , Data not synchronized to slave Library , Then go to the main warehouse , If this does not exist key, You can query directly from the library .
Middleware should also be able to judge whether synchronization is completed , Similar to using cached records .
The biggest disadvantage of this scheme is the introduction of cache , System complexity increases .
3.3 Selective forced reading of Primary Library
For some special business scenarios , Forced reading of main library is adopted .
malpractice , We need to find out every such situation , Set to force the main library .
3.4 etc. GTID programme
MySQL After executing the transaction , The transaction's GTID Will give the client , The client can then use this command to execute the read operation from the library , Wait for the
GTID, After waiting for success , Then perform the read operation ; If the wait times out , Then go to the main library to read , Or change another slave library to perform the above process .
MariaDB of MaxScale Is to use the scheme ,MaxScale yes MariaDB A database intelligent agent service is developed ( Also support
MySQL), Allow database SQL Statement to redirect a request to a target server or servers , Steering rules of various complexity can be set .
3.5 Maintaining the status quo
There is a delay, there is a delay , Scenarios with low requirements for strong data consistency can be left unchecked .
Technology