08 How to Integrate Database and Table Data Into the Master Slave Architecture for Read Write Separation

08 How to Integrate Database and Table Data into the Master-Slave Architecture for Read-Write Separation #

Read-write separation architecture is an integral part of modern database architecture to cope with high-concurrency database access requirements. Today, let’s learn about the read-write separation mechanism provided by ShardingSphere and how it can be integrated with the sharding and forced routing mechanisms discussed earlier.

Read-write Separation in ShardingSphere #

To implement read-write separation in ShardingSphere, a powerful configuration system is used. To better understand the configuration related to read-write separation, it is necessary to have a basic understanding of read-write separation and the master-slave architecture.

Read-write Separation and Master-Slave Architecture #

Currently, most mainstream relational databases provide solutions for the master-slave architecture. By configuring the master-slave relationship between two or more database servers, data updates in one database server can be automatically synchronized to another server. Applications can make use of this functionality to achieve read-write separation and improve database load pressure.

image.png

As you can see, read-write separation in this context means routing write operations to the master database and routing read operations to the slave database. For internet applications, the demand for reading data is often much greater than writing data, so multiple slave databases are usually used. Of course, for more complex system architectures, multiple master databases can be used as well.

Read-write Separation in ShardingSphere #

Regarding ShardingSphere, supporting read-write separation in the master-slave architecture is a core feature. Currently, ShardingSphere supports read-write separation in sharding environments using a master-slave architecture with a single master and multiple slaves. It does not yet support scenarios with multiple master databases.

In a master-slave architecture, since there are usually multiple slave databases, a load balancing mechanism needs to be implemented to route SQL statements to the target slave database. ShardingSphere provides two default load balancing algorithms, Random and RoundRobin, to achieve this goal.

On the other hand, due to the synchronization delay and data inconsistency between the master and slave databases, in some scenarios, we may prefer to retrieve the latest data from the master database. ShardingSphere also takes this into consideration, and developers can use the Hint mechanism to enforce routing to the master database.

Configuring Read-write Separation #

To implement read-write separation, configuration work needs to be done. Our goal is to obtain a MasterSlaveDataSource that supports read-write separation through configuration. The creation of MasterSlaveDataSource depends on the MasterSlaveDataSourceFactory class:

public final class MasterSlaveDataSourceFactory {
    
    public static DataSource createDataSource(final Map<String, DataSource> dataSourceMap, final MasterSlaveRuleConfiguration masterSlaveRuleConfig, final Properties props) throws SQLException {
        return new MasterSlaveDataSource(dataSourceMap, new MasterSlaveRule(masterSlaveRuleConfig), props);
    }
}

In the code above, we can see that the createDataSource method takes three parameters: the familiar dataSourceMap and props, and a MasterSlaveRuleConfiguration that contains all the read-write separation information we need to configure:

public class MasterSlaveRuleConfiguration implements RuleConfiguration {
    // Name of the data source used for read-write separation
    private final String name;
    // Name of the master data source
    private final String masterDataSourceName;
    // List of names of slave data sources
    private final List<String> slaveDataSourceNames;
    // Load balancing algorithm for slave data sources
    private final LoadBalanceStrategyConfiguration loadBalanceStrategyConfiguration;
    ...
}

From the variables defined in the MasterSlaveRuleConfiguration class, it is easy to see that we need to configure four items: the name of the data source used for read-write separation, the name of the master data source, the list of names of slave data sources, and the load balancing algorithm for slave data sources. That’s all.

System Transformation: How to Implement Read-write Separation? #

After understanding the basic concepts of read-write separation and the related configuration items, let’s go back to the case study and see how to introduce the read-write separation mechanism into a single-database, single-table architecture.

The first step is to set up the data sources for read-write separation. To demonstrate a master-slave architecture, we initialize one master data source “dsmaster” and two slave data sources “dsslave0” and “dsslave1”:

spring.shardingsphere.datasource.names=dsmaster,dsslave0,dsslave1 
spring.shardingsphere.datasource.dsmaster.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.dsmaster.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.dsmaster.url=jdbc:mysql://localhost:3306/dsmaster
spring.shardingsphere.datasource.dsmaster.username=root
spring.shardingsphere.datasource.dsmaster.password=root 
spring.shardingsphere.datasource.dsslave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.dsslave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.dsslave0.url=jdbc:mysql://localhost:3306/dsslave0
spring.shardingsphere.datasource.dsslave0.username=root
spring.shardingsphere.datasource.dsslave0.password=root 
spring.shardingsphere.datasource.dsslave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.dsslave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.dsslave1.url=jdbc:mysql://localhost:3306/dsslave1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.dsslave1.username=root
spring.shardingsphere.datasource.dsslave1.password=root

After setting up the data sources, we need to set the four configuration items specified in the MasterSlaveRuleConfiguration class. Here, the load balancing algorithm is set to random:

spring.shardingsphere.masterslave.name=health_ms
spring.shardingsphere.masterslave.master-data-source-name=dsmaster
spring.shardingsphere.masterslave.slave-data-source-names=dsslave0,dsslave1 
spring.shardingsphere.masterslave.load-balance-algorithm-type=random

Now, let’s insert a User object. From the logs in the console, we can see that ShardingSphere is using the master-slave routing type, and the specific SQL execution occurs in the dsmaster master database:

2020-05-25 19:58:08.721  INFO 4392 --- [           main] ShardingSphere-SQL                       : Rule Type: master-slave
2020-05-25 19:58:08.721  INFO 4392 --- [           main] ShardingSphere-SQL                       : SQL: INSERT INTO user (user_id, user_name) VALUES (?, ?) ::: DataSources: dsmaster
Insert User:1
2020-05-25 19:58:08.721  INFO 4392 --- [           main] ShardingSphere-SQL                       : Rule Type: master-slave
2020-05-25 19:58:08.721  INFO 4392 --- [           main] ShardingSphere-SQL                       : SQL: INSERT INTO user (user_id, user_name) VALUES (?, ?) ::: DataSources: dsmaster

Insert User: 2
...

Then, we perform a query operation on the User object and get the SQL execution log:

2020-05-25 20:00:33.066  INFO 3364 --- [main] ShardingSphere-SQL : Rule Type: master-slave
2020-05-25 20:00:33.066  INFO 3364 --- [main] ShardingSphere-SQL : SQL : SELECT * FROM user; ::: DataSources: dsslave0

We can see that the DataSource used here is dsslave0, which means that the query operation occurred on the dsslave0 slave database. Since the random load balancing strategy is set, the target DataSource will alternate between dsslave0 and dsslave1.

System Reconstruction: How to implement read-write separation + sharding? #

We can also add read-write separation functionality based on sharding. In this case, we need to set up two master data sources, dsmaster0 and dsmaster1, and then set up two slave data sources for each master data source:

spring.shardingsphere.datasource.names=dsmaster0,dsmaster1,dsmaster0-slave0,dsmaster0-slave1,dsmaster1-slave0,dsmaster1-slave1

The sharding strategy default-database-strategy for database sharding points to the two main data sources, dsmaster0 and dsmaster1, respectively:

spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=dsmaster$->{user_id % 2}

As for the table sharding strategy, we still use the sharding method introduced in lesson 07:

spring.shardingsphere.sharding.tables.health_record.actual-data-nodes=dsmaster$->{0..1}.health_record$->{0..1}
spring.shardingsphere.sharding.tables.health_record.table-strategy.inline.sharding-column=record_id
spring.shardingsphere.sharding.tables.health_record.table-strategy.inline.algorithm-expression=health_record$->{record_id % 2}

After completing these settings, we also need to set the configuration items for the two main data sources:

spring.shardingsphere.sharding.master-slave-rules.dsmaster0.master-data-source-name=dsmaster0
spring.shardingsphere.sharding.master-slave-rules.dsmaster0.slave-data-source-names=dsmaster0-slave0, dsmaster0-slave1
spring.shardingsphere.sharding.master-slave-rules.dsmaster1.master-data-source-name=dsmaster1
spring.shardingsphere.sharding.master-slave-rules.dsmaster1.slave-data-source-names=dsmaster1-slave0, dsmaster1-slave1

In this way, we have added support for read-write separation on the basis of sharding. The powerful configuration system provided by ShardingSphere allows developers to add new configuration items on the basis of the original configuration without making too many adjustments to the original configuration.

System Reconstruction: How to implement forced routing in read-write separation? #

In the previous lesson, we introduced forced routing. Based on this, I will provide a solution for forced routing to the master database in the read-write separation scenario, using Hint.

To implement forced routing to the master database, we still need to use HintManager. HintManager specifically provides a setMasterRouteOnly method to force routing SQL to the master database. We also encapsulate this method in the HintManagerHelper class:

public class HintManagerHelper {
    static void initializeHintManagerForMaster(final HintManager hintManager) {
        hintManager.setMasterRouteOnly();
    }
}

Now, let’s add the functionality of forced routing to the master database in the business code. The following code demonstrates the process:

@Override
public void processWithHintValueMaster() throws SQLException, IOException {
    DataSource dataSource = DataSourceHelper.getDataSourceForMaster();
    try (HintManager hintManager = HintManager.getInstance();
         Connection connection = dataSource.getConnection();
         Statement statement = connection.createStatement()) {
        HintManagerHelper.initializeHintManagerForMaster(hintManager);
        ResultSet result = statement.executeQuery("select user_id, user_name from user");

        while (result.next()) {
            System.out.println(result.getLong(1) + ": " + result.getString(2));
        }
    }
}

By executing this code, we can get the results in the console log:

2020-05-25 22:06:17.166  INFO 16680 --- [main] ShardingSphere-SQL : Rule Type: master-slave
2020-05-25 22:06:17.166  INFO 16680 --- [main] ShardingSphere-SQL : SQL: select user_id, user_name from user ::: DataSources: dsmaster
1: user_1
2: user_2
...

Obviously, the routing type here is master-slave, and the executing DataSource for the SQL is only dsmaster, which means we have achieved forced routing to the master database.

Conclusion #

Continuing from the previous lesson, today we explained the read-write separation mechanism in ShardingSphere. In daily development, read-write separation is an effective technique to handle high-concurrency data access. In ShardingSphere, read-write separation can be used alone or in combination with sharding. Another powerful feature of ShardingSphere is that it provides a mechanism for forced routing to the master database, which is very useful in scenarios where you need to ensure access to the latest data in the master database.

Here’s a question for you to think about: If we want to only access data from the master database in a master-slave architecture, what methods are available in ShardingSphere to achieve this?