Read/write Splitting #

Database throughput is facing great challenges with increasing TPS. For applications with massive concurrence read but less write, we can divide the database into a primary database and a replica database.

The primary database is responsible for the insert, delete and update of transactions, while the replica database is responsible for queries. It can significantly improve the query performance of the whole system by effectively avoiding row locks.

One primary database with multiple replica databases can further enhance processing capacity and improve the utilization of slave resources by distributing queries evenly into multiple data replicas.

Overview #

Read/write splitting is a database performance optimization technique that can significantly improve system throughput in business scenarios where there are significantly more read requests than write requests, i.e. the master library is responsible for handling transactional add, delete, and change requests, while the slave library is only responsible for handling query requests.

Read/write splitting is one of the SphereEx-DBPlusEngine plugins, which can do database-level read/write separation for business traffic entering SphereEx-DBPlusEngine, and can support the configuration of dynamic read/write splitting and static read/write splitting.

At the same time, SphereEx-DBPlusEngine has a variety of built-in read library load balancing algorithms, including polling algorithms, random access algorithms, and weighted access algorithms, which can meet the needs of most business scenarios, and the supported read library load balancing algorithms are listed below.

The database architectures supported by SphereEx-DBPlusEngine read/write splitting are listed in the table below.

The features supported by the SphereEx-DBPlusEngine read/write splitting dynamic and static read/write splitting rules are listed below.

Products and architectures supported by database topology probing

Core Concept #

• Primary Database

  The database is responsible for handling INSERT, UPDATE, and DELETE requests in a read/write scenario.

• Replica Database

  The database is responsible for handling SELECT requests in a read/write scenario.

• Replica Database Load Balancing

  Refers to a cluster environment with multiple replica databases, where SELECT requests can be routed by specifying a load balancing policy.

Applicable Scenarios #

• Scenarios where there are significantly more read requests than write requests.
• Scenarios where there is a high level of system concurrency and insufficient connections.
• Scenarios that are not sensitive to the real-time nature of data queries.

Prerequisite for use #

• SphereEx-DBPlusEngine and the database cluster are installed on the server and the service is running properly.
• If you need to use dynamic read/write splitting rules, you need to use them in conjunction with the database’s discovery rules.

Usage Norms #

Supported #

• Provide the read/write splitting configuration of one primary database with multiple replica databases, which can be used alone or with a sharding table and database.
• Primary nodes need to be used for both reading and writing in the transaction.
• Forcible primary database route based on SQL Hint.
• The primary database provides read traffic when all secondary databases are down.

Unsupported #

• Data replication between the primary and the replica databases.
• Data inconsistency caused by replication delay between databases.
• Double or multiple primary databases to provide write operation.
• The data for transactions across primary and replica nodes are inconsistent; In the read/write splitting model, primary nodes need to be used for both reading and writing in the transaction.

Notes #

As data between primary and replica is usually transferred in asynchronous mode, there will be transient inconsistencies between primary and replica data, depending on the deployment environment.

During the architecture planning phase, the business requirements for latency need to be met by configuring primary-replica latency thresholds (supported by dynamic rules based on MHA only) in SphereEx-DBPlusEngine, taking into account the acceptable range of data latency for business query requests.

Introduction to the Principle #

SphereEx-DBPlusEngine’s read/write splitting relies on kernel-related functions, including the parsing and routing engines.

SphereEx-DBPlusEngine analyzes SQL semantics, and the read/write splitting module routes read and write operations to primary and replica databases respectively, i.e. the parsing engine converts user SQL into Statement information that SphereEx-DBPlusEngine can recognize, and the routing engine routes SQL according to its read and write types and transaction The routing engine routes SQL based on the read and write type of SQL and the status of the transaction.

In a multi-replica environment, SphereEx-DBPlusEngine supports the configuration of various load-balancing algorithms, including polling algorithms, random access algorithms, and weighted access algorithms, as described in the overview.

SphereEx-DBPlusEngine provides a primary-replica node latency verification mechanism for dynamic read/write splitting rules, and the underlying call is the MySQL SHOW SLAVE STATUS command. When the primary-replica latency exceeds the set threshold, the replica is disabled and no longer provides query services, and if the latency of all replica nodes exceeds the set threshold, read requests can be taken up by the primary. When the latency is restored to within the threshold, the replica will provide read service again. This checksum mechanism meets the needs of different business scenarios for primary-replica delay requirements. Refer to the description of the delay-milliseconds-threshold parameter in Database Discovery for the configuration method.

At the same time, replica read nodes can be manually disabled and enabled through SphereEx-DBPlusEngine’s fusing capability, which is used to cope with situations such as hardware and software upgrades, network maintenance, etc.

In terms of read/write splitting categories, SphereEx-DBPlusEngine supports both data source-based read/write splitting and SQL Hint-based read/write splitting.

• Data Source-Based

In the data source-based read/write splitting scenario, SphereEx-DBPlusEngine identifies INSERT, DELETE, UPDATE requests, and SELECT requests and routes the SQL to different data sources respectively, as shown in the figure below.

• SQL Hint-Based

In SQL Hint-based read/write splitting scenarios, SphereEx-DBPlusEngine can specify SQL configuration read/write rules based on SQL Hint information, such as specifying that a query SQL must retrieve data from the primary library, which can meet business scenarios that require data consistency.

User Guide #

A description of dynamic read/write splitting and static read/write splitting is as follows:

• Dynamic read/write splitting: SphereEx-DBPlusEngine automatically detects (relying on database discovery rules) the primary and replica roles of the database cluster, and when the roles of the database nodes change, the configuration of read/write splitting is automatically adjusted, which can support MGR, MHA for high availability scenarios.
• Static read/write splitting: fixed configuration of primary and replica in SphereEx-DBPlusEngine, which does not dynamically sense primary-replica role changes. When there is a role change, manual intervention is required to adjust the read/write splitting accordingly, which can support various high-availability scenarios.

1. Environment Confirmation

   Before using the SphereEx-DBPlusEngine read/write splitting plug-in, you need to do a comprehensive confirmation of the current environment, such as the primary-replica architecture information of the database, the hardware configuration information of all nodes of the primary and replica, and the latency of the network between the primary and replica nodes.

2. Rule confirmation

   • Dynamic and static rules

     SphereEx-DBPlusEngine provides dynamic and static read/write splitting configuration rules.

     For MGR and MHA architectures, it is recommended to use dynamic read/write splitting rules, as SphereEx-DBPlusEngine will automatically recognize the role of the nodes, and can adapt to changes in the primary-replica role without manual intervention to adjust the read/write splitting policy.

     For database clusters that use domain names, VIPs, or 1 master 1 replica minimalist architectures, it is recommended to use static read/write splitting rules.

   • Load balancing

     For environments where there are multiple replica nodes, polling load can be considered in general scenarios. If there is a difference in host resources between replicas, consider configuring a weighting ratio.

3. Data Source Confirmation and Registration

   The connectivity of all data sources in turn can be confirmed by visiting the node where SphereEx-DBPlusEngine is located.

   In DBPlusEngine, the data sources are registered via the REGISTER STORAGE UNIT command.

4. Create a database discovery rule (optional)

   If you choose to use dynamic read/write splitting rules, you need to configure the database discovery rules in advance, such as business explicit primary-replica delay requirements, in this step.

5. Create read/write splitting rules

   When creating a read/write splitting rule, configure it according to the rules planned in step 2, including dynamic rules, load balancing rules, and the corresponding ratio.

6. Configure validation

   Create a test table and use the PREVIEW SQL command to verify the additions, deletions, and changes routing. The SELECT statement should be routed to the slave node.

Operation Guide #

The configuration of dynamic read/write splitting relies on database discovery rules, and SphereEx-DBPlusEngine can dynamically and in real-time adjust the read/write splitting policy as database cluster roles change. As this section is an introduction to the read/write splitting plug-in, please refer to database discovery for a more detailed description of the database discovery capabilities, which are described in the following steps.

1. Create the database in MGR and prepare the data source
2. Complete the logical database creation in SphereEx-DBPlusEngine
3. Add the data sources and complete the cluster build
4. Create the database discovery rules
5. Create dynamic read/write splitting rules
6. Create test tables and insert test data
7. Verify the execution path of read/write requests in sequence

For static read/write splitting, it is sufficient to configure the read/write data source information without focusing on the database discovery rules.

Operation Example #

SphereEx-DBPlusEngine’s read/write splitting configuration is relatively simple, so here’s a quick look at SphereEx-DBPlusEngine’s dynamic and static read/write splitting capabilities in two common scenarios.

Dynamic Read/Write Splitting Configuration #

Environment Explanation #

This scenario is based on the MGR architecture configured for dynamic read/write splitting, using four test machines, one of which is used to deploy SphereEx-DBPlusEngine and the other three are MGR, one primary, and two replicas. As the scenario is a functional demonstration, a relatively basic configuration (1c/2g) is used for the test machines.

Topology Diagram #

Configuration Process #

1. Create the database in MGR and prepare the data source for the subsequent build set.

--Log in to your MySQL instance and create a database called test
# mysql -uroot -p -h192.168.xx.103 -P3306
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.05 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

2. Log in to SphereEx-DBPlusEngine using the MySQL client and create the logical library.

--Access to DBPlusEngine instances
# mysql -uroot -p -P3307 -h192.168.xx.102
mysql> SHOW DATABASES;
+--------------------+
| schema_name        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.06 sec)

mysql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW DATABASES;
+--------------------+
| schema_name        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.06 sec)

3. Add the data source and complete the cluster build.

Register each node of the MGR with SphereEx-DBPlusEngine. The resources added below are at the Schema level, which is more granular than the instance level.

mysql> USE testdb;
Database changed

mysql> REGISTER STORAGE UNIT ds_0 (
     URL="jdbc:mysql://192.168.xx.103:3306/test?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123"
), ds_1 (
     URL="jdbc:mysql://192.168.xx.104:3306/test?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123"
), ds_2 (
     URL="jdbc:mysql://192.168.xx.105:3306/test?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123"
);
Query OK, 0 rows affected (0.67 sec)

mysql> SHOW STORAGE UNITS\G

4. Create database discovery rules.

The configuration of dynamic read/write splitting relies on database discovery rules, and SphereEx-DBPlusEngine allows dynamic, real-time adjustment of read/write splitting policies as database cluster roles change. This section is an introduction to the read/write splitting plug-in, so for a more detailed description of the database discovery capabilities, please refer to db-discovery

--Create database topology discovery rules
mysql> CREATE DB_DISCOVERY RULE primary_replica_rule (
     STORAGE_UNITS(ds_0,ds_1,ds_2),
     TYPE(NAME='MySQL.MGR',PROPERTIES('group-name'='b309fcc3-93e8-11ec-b5bd-080027c850b1')),
     HEARTBEAT(PROPERTIES('keep-alive-cron'='0/5 * * * * ?'))
);
Query OK, 0 rows affected (0.85 sec)

mysql> SHOW DB_DISCOVERY RULES primary_replica_rule\G
*************************** 1. row ***************************
              group_name: primary_replica_rule
       data_source_names: ds_0,ds_1,ds_2
primary_data_source_name: ds_0
          discovery_type: {name=primary_replica_ds1_MySQL.MGR, type=MySQL.MGR, props={group-name=b309fcc3-93e8-11ec-b5bd-080027c850b1}}
     discovery_heartbeat: {name=primary_replica_rule_heartbeat}
1 rows in set (0.01 sec)

This step creates a discovery rule for a database with the discovered data sources ranging from ds_0, ds_1, and ds_2, and labels the database cluster type as MGR, and SphereEx-DBPlusEngine probes the topology and the role of each data source every 5 seconds.

5. Create dynamic read/write splitting rules.。

Create a dynamic read/write splitting rule based on the database topology discovery rule configured in the previous step. AUTO_AWARE_RESOURCE corresponds to the name of the database topology discovery rule.

mysql> CREATE READWRITE_SPLITTING RULE dynamic_readwrite_rule (
    AUTO_AWARE_RESOURCE=primary_replica_rule
);
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW READWRITE_SPLITTING RULES;
+------------------------+-----------------------------+------------------------+------------------------+--------------------+---------------------+
| name                   | auto_aware_data_source_name | write_data_source_name | read_data_source_names | load_balancer_type | load_balancer_props |
+------------------------+-----------------------------+------------------------+------------------------+--------------------+---------------------+
| dynamic_readwrite_rule | primary_replica_rule        | ds_0                   | ds_1,ds_2              |                    |                     |
+------------------------+-----------------------------+------------------------+------------------------+--------------------+---------------------+
1 row in set (0.05 sec)

6. Create a test table and insert a number of rows.

mysql> DROP TABLE IF EXISTS t_user;
Query OK, 0 rows affected (1.16 sec)

mysql> CREATE TABLE `t_user` (
  `id` int(8) not null,
  `mobile` char(20) NOT NULL,
  `id_card_no` varchar(18) NOT NULL,
  PRIMARY KEY (`id`)
);
Query OK, 0 rows affected (1.20 sec)

mysql> INSERT INTO t_user (id, mobile, id_card_no) VALUES
(1,18236483857, 220605194709308170),
(2,15686689114, 360222198806088804),
(3,14523360225, 411601198601098107),
(4,18143924353, 540228199804231247),
(5,15523349333, 360924195311103360);
Query OK, 5 rows affected (0.40 sec)

7. Validate the execution path of a written request.

PREVIEW is a command used by DBPlusEngine to validate the SQL execution route. When the command is executed, only the execution path is output, not the actual execution.

mysql> PREVIEW INSERT INTO t_user (id, mobile, id_card_no) VALUES (6,13261527931, 513229195302236086);
+------------------+----------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                             |
+------------------+----------------------------------------------------------------------------------------+
| ds_0             | INSERT INTO t_user (id, mobile, id_card_no) VALUES (6,13261527931, 513229195302236086) |
+------------------+----------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> PREVIEW INSERT INTO t_user (id, mobile, id_card_no) VALUES (7,13921892133, 500108194806107214);
+------------------+----------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                             |
+------------------+----------------------------------------------------------------------------------------+
| ds_0             | INSERT INTO t_user (id, mobile, id_card_no) VALUES (7,13921892133, 500108194806107214) |
+------------------+----------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

The above output confirms that the different INSERT requests are routed by SphereEx-DBPlusEngine to ds_0, the MGR’s primary repository, and the results are as expected.

8. Verify the execution path of the read request.

Next, verify the routing of the query operation with the PREVIEW command, which will return either ds_1 or ds_2 depending on the configuration rules for read/write splitting.

mysql> PREVIEW SELECT * FROM t_user WHERE id=1;
+------------------+---------------------------------+
| data_source_name | actual_sql                      |
+------------------+---------------------------------+
| ds_1             | SELECT * FROM t_user WHERE id=1 |
+------------------+---------------------------------+
1 row in set (0.05 sec)

mysql> PREVIEW SELECT * FROM t_user WHERE id=1;
+------------------+---------------------------------+
| data_source_name | actual_sql                      |
+------------------+---------------------------------+
| ds_2             | SELECT * FROM t_user WHERE id=1 |
+------------------+---------------------------------+
1 row in set (0.06 sec)

mysql> PREVIEW SELECT * FROM t_user WHERE id=2;
+------------------+---------------------------------+
| data_source_name | actual_sql                      |
+------------------+---------------------------------+
| ds_1             | SELECT * FROM t_user WHERE id=2 |
+------------------+---------------------------------+
1 row in set (0.01 sec)

mysql> PREVIEW SELECT * FROM t_user WHERE id=2;
+------------------+---------------------------------+
| data_source_name | actual_sql                      |
+------------------+---------------------------------+
| ds_2             | SELECT * FROM t_user WHERE id=2 |
+------------------+---------------------------------+
1 row in set (0.05 sec)

The above output confirms that the SELECT request was routed to ds_1 and ds_2 by SphereEx-DBPlusEngine, while the query was polled and the results were as expected.

This completes the configuration of a dynamic read/write splitting scenario based on MGR with one master and two slaves via SphereEx-DBPlusEngine.

Static Read/Write Splitting Configuration #

Compared to dynamic read/write splitting, the static configuration is a little easier as there is no configuration for database topology discovery. After preparing the data source, it is straightforward to configure the rules. Also, the example includes a reference to the configuration of weights.

1. Create the database in the primary-replica cluster and prepare the data source

2. Complete the logical library creation in SphereEx-DBPlusEngine

3. Add the data sources and complete the cluster build

4. Create static read/write splitting rules

5. Create test tables and insert test data

6. Verify the execution path of read/write requests in sequence

Environment Explanation #

Topology Diagram #

Configuration Process #

1. Create the database in the primary-replica cluster and prepare the data source for subsequent build sets.

# mysql -uroot -p -h192.168.xx.103 -P3306
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.05 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

2. Log in to SphereEx-DBPlusEngine using the MySQL client and create the logical library.

# mysql -uroot -p -P3307 -h192.168.xx.102
mysql> SHOW DATABASES;
+--------------------+
| schema_name        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW DATABASES;
+--------------------+
| schema_name        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.05 sec)

3. Add the data source and complete the cluster build.

mysql> USE testdb;
Database changed

mysql> REGISTER STORAGE UNIT ds_0 (
     URL="jdbc:mysql://192.168.xx.103:3306/test?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123"
), ds_1 (
     URL="jdbc:mysql://192.168.xx.104:3306/test?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123"
), ds_2 (
     URL="jdbc:mysql://192.168.xx.105:3306/test?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123"
);
Query OK, 0 rows affected (0.62 sec)

mysql> SHOW STORAGE UNITS\G

4. Create a static read/write splitting rule.。

In the process of creating a static read/write splitting, all data source names need to be configured at the location of the specified resource.

mysql> CREATE READWRITE_SPLITTING RULE ms_group_0 (
    WRITE_STORAGE_UNIT=ds_0,
    READ_STORAGE_UNITS(ds_1,ds_2),
    TYPE(NAME="random")
);
Query OK, 0 rows affected (0.26 sec)

mmysql> SHOW READWRITE_SPLITTING RULES;
+------------+-----------------------------+---------------------------------+-------------------------+-------------------------+--------------------+---------------------+
| name       | auto_aware_data_source_name | write_data_source_query_enabled | write_storage_unit_name | read_storage_unit_names | load_balancer_type | load_balancer_props |
+------------+-----------------------------+---------------------------------+-------------------------+-------------------------+--------------------+---------------------+
| ms_group_0 |                             |                                 | ds_0                    | ds_1,ds_2               | random             |                     |
+------------+-----------------------------+---------------------------------+-------------------------+-------------------------+--------------------+---------------------+
1 row in set (0.00 sec)

As above, a static read/write splitting rule based on a 1 primary 2 replica architecture has been created.

5. Create a test table and insert a number of rows.

--Execute the following command in SphereEx-DBPlusEngine
mysql> DROP TABLE IF EXISTS t_user;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `t_user` (
  `id` int(8) not null,
  `mobile` char(20) NOT NULL,
  `id_card_no` varchar(18) NOT NULL,
  PRIMARY KEY (`id`)
);
Query OK, 0 rows affected (1.15 sec)

mysql> INSERT INTO t_user (id, mobile, id_card_no) VALUES
(1,18236483857, 220605194709308170),
(2,15686689114, 360222198806088804),
(3,14523360225, 411601198601098107),
(4,18143924353, 540228199804231247),
(5,15523349333, 360924195311103360);
Query OK, 5 rows affected (0.16 sec)

6. Verify the execution path of the write request.

Confirm the routing of the INSERT statement with the PREVIEW command.

mysql> PREVIEW INSERT INTO t_user (id, mobile, id_card_no) VALUES (6,13261527931, 513229195302236086);
+------------------+----------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                             |
+------------------+----------------------------------------------------------------------------------------+
| ds_0             | INSERT INTO t_user (id, mobile, id_card_no) VALUES (6,13261527931, 513229195302236086) |
+------------------+----------------------------------------------------------------------------------------+
1 row in set (0.15 sec)

mysql> PREVIEW INSERT INTO t_user (id, mobile, id_card_no) VALUES (7,13921892133, 500108194806107214);
+------------------+----------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                             |
+------------------+----------------------------------------------------------------------------------------+
| ds_0             | INSERT INTO t_user (id, mobile, id_card_no) VALUES (7,13921892133, 500108194806107214) |
+------------------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The above output confirms that the different INSERT requests are routed by SphereEx-DBPlusEngine to ds_0, i.e. the write requests are routed to the main library and the results are as expected.

7. Verify the execution path of the read request.

With the read library weight information configured in the rule, verify that the random reads are in effect.

mysql> PREVIEW SELECT * FROM t_user WHERE id=1;
+------------------+---------------------------------+
| data_source_name | actual_sql                      |
+------------------+---------------------------------+
| ds_1             | SELECT * FROM t_user WHERE id=1 |
+------------------+---------------------------------+
1 row in set (0.10 sec)

mysql> PREVIEW SELECT * FROM t_user WHERE id=1;
+------------------+---------------------------------+
| data_source_name | actual_sql                      |
+------------------+---------------------------------+
| ds_1             | SELECT * FROM t_user WHERE id=1 |
+------------------+---------------------------------+
1 row in set (0.00 sec)

mysql> PREVIEW SELECT * FROM t_user WHERE id=1;
+------------------+---------------------------------+
| data_source_name | actual_sql                      |
+------------------+---------------------------------+
| ds_2             | SELECT * FROM t_user WHERE id=1 |
+------------------+---------------------------------+
1 row in set (0.04 sec)

The above output confirms that the SELECT request was routed by SphereEx-DBPlusEngine to ds_1 and ds_2 with a 2:1 weighting from the output, which is as expected.

At this point, the configuration of a static read/write splitting scenario based on a 1 primary 2 replica architecture has been completed by SphereEx-DBPlusEngine.

Based on SQL Hint #

SQL Hint can route the specified SQL to any data source, so in a read/write splitting scenario, the specified SQL can be configured more flexibly by SQL Hint. For example, if SQL has to retrieve data from the primary database, SQL Hint can be used to meet the business scenario where data consistency is required.

Based on the previous scenario, the following is a verification of the use of SQL Hint for querying and routing SELECT * FROM t_user requests to the master by way of Hint.

This is done by adding a comment message like /* SHARDINGSPHERE_HINT: WRITE_ROUTE_ONLY=true */ to the SQL header.

To verify the results of the Hint intervention, the PREVIEW command is used again to verify the execution path of SQL, as follows.

# mysql -uroot -p -h192.168.xx.102 -P3307 -c
-- If using a MySQL client for authentication, please make sure to add the '-c' parameter, otherwise the hint message will not work

mysql> USE testdb;

mysql> PREVIEW SELECT * FROM t_user;
+------------------+----------------------+
| data_source_name | actual_sql           |
+------------------+----------------------+
| ds_1             | SELECT * FROM t_user |
+------------------+----------------------+
1 row in set (0.04 sec)

mysql> /* SHARDINGSPHERE_HINT: WRITE_ROUTE_ONLY=true */ PREVIEW SELECT * FROM t_user;
+------------------+---------------------------------------------------------------------+
| data_source_name | actual_sql                                                          |
+------------------+---------------------------------------------------------------------+
| ds_0             | /* SHARDINGSPHERE_HINT: WRITE_ROUTE_ONLY=true */SELECT * FROM t_user|
+------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 在执行时，请确保 /* SHARDINGSPHERE_HINT: WRITE_ROUTE_ONLY=true */ 前没有空格

The above output shows that the specified read request statement has been routed to the primary library as expected when intervening with Hint.

Parameters #

• delay-milliseconds-threshold

  Usage: delay-milliseconds-threshold: duration (ms)

• Parameter Description: This parameter is used to configure the limit of primary-replica latency for dynamic read/write splitting rules in ms, and the value is a positive integer. When the set threshold is exceeded, the slave will be disabled, and when all replica exceed the set threshold, the primary will be responsible for reading and writing. This parameter needs to be configured in the database discovery rules. Please refer to thedatabase discovery instructions.

• writeDataSourceQueryEnabled

Parameter Description: When all read replicas are down, this parameter determines whether the primary database should still provide query services. It supports dynamic and static read-write separation configurations, with a default value of true.

Static read-write separation example configuration:

rules:
- !READWRITE_SPLITTING
  dataSources:
    readwrite_ds:
      writeDataSourceName: write_ds
      readDataSourceNames:
        - read_ds_0
        - read_ds_1
      loadBalancerName: random
      writeDataSourceQueryEnabled: true
  loadBalancers:
    random:
      type: RANDOM

Dynamic read-write separation example configuration:

rules:
  - !READWRITE_SPLITTING
    dataSources:
      replica_ds:
        autoAwareDataSourceName: readwrite_ds
        writeDataSourceQueryEnabled: true

• transactionalReadQueryStrategy

Parameter Description: Routing strategy for read requests within a transaction. Optional values: PRIMARY (routed to the primary database), FIXED (routed to a fixed data source within the same transaction), DYNAMIC (routed to non-fixed data sources within the same transaction). Default value: DYNAMIC

Example Configuration:

rules:
- !READWRITE_SPLITTING
  dataSources:
    readwrite_ds:
      writeDataSourceName: write_ds
      readDataSourceNames:
        - read_ds_0
        - read_ds_1
      transactionalReadQueryStrategy: PRIMARY
      loadBalancerName: random
  loadBalancers:
    random:
      type: RANDOM

FAQ #

1. Do the read/write splitting rules need to be adjusted when the replica database goes down?

   If dynamic read/write splitting rules are configured, there is no need to adjust the rules after the replica database is down.

   If the configuration is a static read-write splitting rule, the operation and maintenance personnel should intervene and modify the resource configuration in the rule according to the actual situation.

2. After adding a new replica to the database cluster, such as 1 master and 2 slaves becoming 1 master and 3 slaves, how do the rules of read/write splitting need to be adjusted?

   First, add the slave nodes to the database cluster and make sure the data has been synchronized. Then use DistSQL to configure the new replica node information into the read/write splitting rules, which will take effect immediately after the configuration.

3. What is the impact on dynamic and static read/write splitting when the primary/replica role of the database cluster is switched?

   There is no impact on dynamic read/write splitting rules, which are based on database discovery rules, and read/write traffic will be routed according to the latest role relationship.

   There is an impact on the static read/write splitting rules. After the role switch occurs, the operation and maintenance personnel need to change the read/write separation policy on time.

4. When there is a network flashback in the replica database, how will the business read requests be handled?

   Based on dynamic rules: business is not affected, and read requests will only be routed to nodes with normal networks.

   Based on static rules: the business is affected, read requests will continue to be routed to read requests according to static rules, and business will experience a timeout during a network flashback. Operations and maintenance personnel need to intervene to disable the node in time and enable it again when the network is restored to normal.