Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to do database read-write separation". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought. Let's study and learn "how to do database read-write separation"!

Mode of realization

For the use of read-write separation, it is mainly divided into two ways, client mode and proxy mode.

The client-side approach can be implemented using either Spring's own AbstractRoutingDataSource or an open source framework, such as Sharding-JDBC.

The agent mode needs to write a proxy service to manage all nodes, and the application does not need to pay attention to the information of multiple database nodes. It can be implemented on its own, with open source frameworks, or with commercial cloud services.

Data delay

When it comes to data latency, you have to understand the principle of the master-slave architecture. The operation of adding, deleting and modifying the data is performed on the master database, and the query is executed on the slave database. When the data is just inserted into the master database and then queried immediately, it is very likely that the query will not be available before the data is synchronized to the slave database.

For example, I published articles on some websites and jumped to the list page after publication. I found that there were no newly published articles and refreshed the page again. This is the phenomenon caused by the data delay after the separation of reading and writing.

Whether the delay of forced routing data should be resolved or not generally depends on the business scenario. For business scenarios where real-time requirements are not so high, a certain delay is allowed. For scenarios with high real-time requirements, the only way is to query directly from the main database, so that the latest data just inserted or modified can be read in time.

Forced routing

Is a solution, that is, to force the read request to be distributed to the main library for query. Most middleware supports Hint syntax / FORCE_MASTER/ and / FORCE_SLAVE/.

Take Sharding-JDBC as an example, the framework provides HintManager to force routing, which is used in the following ways:

HintManager hintManager = HintManager.getInstance (); hintManager.setMasterRouteOnly ()

For ease of use, it is recommended to encapsulate an annotation, add a note to the business method that requires real-time query, and set the forced routing through the section.

The annotations use:

@ MasterRoute @ Override public UserBO getUser (Long id) {log.info ("query user [{}]", id); if (id = = null) {throw new BizException (ResponseCode.PARAM_ERROR_CODE, "id cannot be empty");} UserDO userDO = userDao.getById (id); if (userDO = = null) {throw new BizException (ResponseCode.NOT_FOUND_CODE);} return userBoConvert.convert (userDO);}

Section settings:

@ Aspect public class MasterRouteAspect {@ Around ("@ annotation (masterRoute)") public Object aroundGetConnection (final ProceedingJoinPoint pjp, MasterRoute masterRoute) throws Throwable {HintManager hintManager = HintManager.getInstance (); hintManager.setMasterRouteOnly (); try {return pjp.proceed ();} finally {hintManager.close ();}

Transaction operation

Should the read request in the transaction go to the master library or the slave library? For this problem, the easiest way is that all the operations in the transaction go to the master library, and there is often a scenario in which the transaction is inserted and then re-queried. In this case, the transaction is not committed, and even if the synchronization is fast, the slave database has no data. So we can only go to the master library.

But there are still some requests that just need to query the slave library, and it's not good to force routing for operations in all transactions. The practice in Sharding-JDBC is good. For the same thread and the same database connection, if there are write operations, the subsequent read operations are read from the main database to ensure data consistency. If we have a query request before the data is written, we should go from the database to reduce the pressure on the main database.

Dynamic forced routing

When the function is developed, it is decided which interfaces should be forced to go to the main library. At this time, we will control the routing on the code, that is, the custom comments mentioned earlier. If some of them are not added, but when running online, you still have to go to the main library, then you need to change the code and rerelease it.

Dynamic forced routing can be combined with the configuration center to determine which interfaces should be forced routing, and then set it through HintManager in Filter to avoid code change and restart.

It can also be configured through dynamic routing that is faceted down to the business method level.

Traffic distribution

Scenario 1:

Suppose you have one master node and two slave nodes, with more read requests and a little bit of pressure on the two slave nodes. At this time, only a third slave node can be added to share the pressure. The phenomenon is that the pressure of the master database is not great, and there are fewer writes. From the cost point of view, is it possible not to add a third slave node?

Scenario 2:

Suppose you have an 8-core 64-gigabyte master library, an 8-core 64-gigabyte slave library and a 4-core 32-gigabyte slave library. from a configuration point of view, the slave processing capacity of 4-core 32G is definitely lower than that of the other two. At this time, if we do not customize the proportion of traffic distribution, there will be problems caused by high pressure on low-configuration databases. Of course, this can also avoid using different rules of the slave library.

The above scenario needs to be able to manage requests. A read-write separation routing algorithm is provided in Sharding-JDBC. We can customize the algorithm to manage the distribution of traffic.

Implementation algorithm class:

Public class KittyMasterSlaveLoadBalanceAlgorithm implements MasterSlaveLoadBalanceAlgorithm {private RoundRobinMasterSlaveLoadBalanceAlgorithm roundRobin = new RoundRobinMasterSlaveLoadBalanceAlgorithm (); @ Override public String getDataSource (String name, String masterDataSourceName, List slaveDataSourceNames) {String dataSource = roundRobin.getDataSource (name, masterDataSourceName, slaveDataSourceNames); / / Control logic, for example, different slave nodes (different configurations) can have different return dataSource;} @ Override public String getType () {return "KITTY_ROUND_ROBIN" } @ Override public Properties getProperties () {return roundRobin.getProperties ();} @ Override public void setProperties (Properties properties) {roundRobin.setProperties (properties);}}

Configuration based on SPI mechanism:

Org.apache.shardingsphere.core.strategy.masterslave.RoundRobinMasterSlaveLoadBalanceAlgorithm org.apache.shardingsphere.core.strategy.masterslave.RandomMasterSlaveLoadBalanceAlgorithm com.cxytiandi.kitty.db.shardingjdbc.algorithm.KittyMasterSlaveLoadBalanceAlgorithm

Configuration of read-write separation:

Spring.shardingsphere.masterslave.load-balance-algorithm-class-name=com.cxytiandi.kitty.db.shardingjdbc.algorithm.KittyMasterSlaveLoadBalanceAlgorithm spring.shardingsphere.masterslave.load-balance-algorithm-type=KITTY_ROUND_ROBIN thank you for your reading, the above is the content of "how to do database read-write separation". After the study of this article, I believe you have a deeper understanding of how to do database read-write separation, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.