Logo
Dual Routing

Dual Routing #

SphereEx-DBPlusEngine includes two access modes: Driver and Proxy. In complex business scenarios, architects usually use a hybrid deployment architecture to flexibly build application systems suitable for various scenarios by mixing DBPlusEngine-Driver and DBPlusEngine-Proxy, while using a unified registry to configure the sharding strategy.

To improve the query performance and stability of the application, we can consider forwarding the SQL that consumes more resources to the independent service form of DBPlusEngine-Proxy, which is responsible for calculating the query results and returning them to the application uniformly.

Overview #

SphereEx-DBPlusEngine’s dual routing plugin allows users to configure routing based on SQL characteristics, and SQL that consumes resources can be handled by DBPlusEngine-Proxy, giving full play to the strengths of both access ends. The dual routing plugin will minimize the impact of SQL forwarding, allowing users to use the DBPlusEngine-Driver and DBPlusEngine-Proxy hybrid deployment cluster just as using a database.

  • DBPlusEngine-Driver

DBPlusEngine-Driver adopts a decentralized architecture and shares resources with the application. It’s suitable for Java-developed high-performance lightweight OLTP applications, but because DBPlusEngine-Driver shares resources with the application, when executing SQL that consumes more resources, the stability and performance of the application will be affected.

At the same time, DBPlusEngine-Driver consumes more connections, and when the application and database are deployed in different network partitions, the impact of network latency on performance will be more obvious.

  • DBPlusEngine-Proxy

DBPlusEngine-Proxy provides a unified static entrance and is deployed independently of the application. It’s suitable for OLAP applications and scenarios where sharded databases are managed and operated. Users use DBPlusEngine-Proxy to execute SQL that consumes more resources, which can effectively avoid affecting the application.

Basic Concepts #

Tag #

The tag attribute configured for the DBPlusEngine-Proxy instance is used to distinguish instances. When the DBPlusEngine-Driver turns on the traffic function, the forwarding target is the DBPlusEngine-Proxy instance corresponding to the tag.

Forwarding Strategy #

For SQL at the access end of DBPlusEngine-Driver, use the forwarding strategy, including target proxy instance label, forwarding algorithm, and load balancing algorithm.

According to different algorithms, the forwarding strategy can be divided into transaction forwarding strategy and ordinary forwarding strategy. When the algorithm in the strategy is configured as TransactionTrafficAlgorithm the forwarding strategy is transaction forwarding strategy, and when the algorithm in the strategy is configured as other algorithms, the forwarding strategy is ordinary forwarding strategy.

Forwarding Algorithm #

Algorithms used to determine whether the current SQL needs forwarding include HintTrafficAlgorithm, SegmentTrafficAlgorithm, and TransactionTrafficAlgorithm.

  • HintTrafficAlgorithm based on SQL Hint function to do SQL forwarding.

  • SegmentTrafficAlgorithm forwards based on SQL statements, and internally provides forwarding algorithms based on SQL strings and SQL regular matching.

  • TransactionTrafficAlgorithm is an algorithm specially used to handle how to forward SQL statements in a transaction when a transaction is started. Currently, it supports FIRST_SQL, JDBC, and Proxy three strategies. FIRST_SQL means the first forwarding result based on the transaction statement, to forward the SQL statement in the transaction. JDBC indicates that all SQL transaction statements are forwarded to the JDBC access terminal for execution. Proxy means that all SQL transaction statements are forwarded to the proxy access terminal for execution.

Load Balancing Algorithm #

The load-balancing algorithm forwards SQL statements to different proxy instances corresponding to tags for execution. At present, it has two load-balancing algorithms: RANDOM and ROUND_ROBIN.

Applicable Scenarios #

Hybrid deployment of DBPlusEngine-Driver and DBPlusEngine-Proxy, and need to forward SQL that consumes more resources to DBPlusEngine-Proxy for execution.

Requirements #

  • The dual routing function requires a hybrid deployment architecture, and both DBPlusEngine-Driver and DBPlusEngine-Proxy are deployed.
  • ZooKeeper needs to be deployed, and the hybrid architecture will be managed uniformly through the registry center (ZooKeeper).

Limitations #

  • SQL that is not supported by the kernel function configured by the user is still not supported after forwarding.
  • Doesn’t support forwarding SQL to different access ends or Proxy instances after opening a transaction.
  • Only allow adding routing rules through YAML configuration or DistSQL at the DBPlusEngine-Proxy access end.

Notice #

Although forwarding SQL that consumes more resources at the DBPlusEngine-Driver access end to DBPlusEngine-Proxy for execution can effectively improve the performance and stability of the application, this also makes the deployment architecture more complex, and users need to judge which statements need to be forwarded to DBPlusEngine-Proxy and develop related business logic at the DAO layer to control SQL forwarding.

Additionally, in the scenario of opening a transaction, forwarding some SQL in the transaction to DBPlusEngine-Proxy for execution will affect the consistency and visibility of the transaction, thereby affecting the business system’s use of the transaction.

How it works #

Traffic Rule is used to judging whether the currently executed SQL statement needs to be forwarded and internally supports multiple forwarding algorithms. The following shows a Traffic Rule configuration example, trafficStrategies is used to declare the request forwarding strategy. When the user configures multiple forwarding strategies, they will be matched in the order of configuration, and the first matching strategy will be used for request forwarding processing. The forwarding strategy contains three attributes:

  • labels: the label corresponding to the request forwarding Proxy, supports configuring multiple labels, and the same label may also have multiple Proxy instances.
  • algorithmName: the request forwarding matching algorithm, such as matching the operation type is select, and the statement containing group by. The matching is successful due to whether the Proxy instance to be forwarded is found according to labels.
  • loadBalancerName: used to specify the forwarding load balancing strategy, because there may be multiple Proxy instances corresponding to labels, and the load balancing strategy needs to decide which instance to forward to.

In order to cooperate with the labels attribute in Traffic Rule, the Proxy access end needs to support the label attribute setting and can store the IP and port information of the Proxy instance.

Since the dual routing function is used, the SQL that was originally executed on the JDBC access end will be forwarded to the Proxy access point for execution because of the matching forwarding strategy. In this scenario, some statements in the same transaction unit may be executed on the JDBC access end and some on the Proxy access end, which cannot guarantee the consistency of the transaction.

In order to solve the problem of transaction consistency, we have added a transaction forwarding strategy in forwarding strategy. The configuration of the transaction forwarding strategy includes FIRST_SQL, JDBC, and PROXY three types of transaction matching algorithms. FIRST_SQL will determine the forwarding result of the transaction unit based on the first SQL forwarding result. JDBC will not forward the transaction unit and execute it on JDBC, while PROXY will forward the transaction unit to the Proxy instance for execution. In order to ensure data consistency, the transaction unit will be executed on the same instance.

When internally initializing the routing strategy, Traffic Rule will classify and maintain the strategy according to whether it is for the transaction scenario. The strategy for the transaction scenario will only be used when the transaction is opened. Otherwise, other non-transaction strategies will be used.

User Guide #

  1. Confirm the SQL that consumes more resources in the current system, like aggregation statements, and extract specific statement information. SphereEx-Console or other monitoring tools can capture the information.
  2. In DBPlusEngine-Proxy, complete the configuration of the specified SQL through DistSQL.
  3. In DBPlusEngine-Proxy, enable the SQL display function, and the business initiates a new request to confirm the routing situation of the specified SQL.

Operation Guide #

  1. Confirm the SQL statement that needs to be forwarded to Proxy for execution.
  2. In Proxy, complete the configuration of the dual routing rules through DistSQL.
  3. Enable SQL display in Proxy.
  4. The application initiates a request and confirms SQL through Proxy logs.
  5. After confirming the result, disable the SQL display in Proxy.

Sample #

Environment Description #

InstanceIP AddressService PortHost NameNote
1DBPlusEngine 1.2.0192.168.xx.1023307dbplusengine
2Zookeeper 3.6.3192.168.xx.1022181dbplusengine
3MySQL 8.0.28192.168.xx.1033306mysql_0
4MySQL 8.0.28192.168.xx.1043306mysql_1

Topological Diagram #

Configuration Process #

  1. Confirm the SQL statements that need to be forwarded to the Proxy.

  2. Configure dual routing rules.

mysql> CREATE TRAFFIC RULE sql_match_traffic (
    LABELS(OLTP),
    TRAFFIC_ALGORITHM(TYPE(NAME=SQL_MATCH,PROPERTIES("sql" = "SELECT * FROM t_order WHERE order_id = ?; UPDATE t_order SET order_id = ?;"))),
    LOAD_BALANCER(TYPE(NAME=RANDOM, PROPERTIES("key"="value")))
), sql_hint_traffic_1( 
    TRAFFIC_ALGORITHM(TYPE(NAME= SQL_HINT)),
    LOAD_BALANCER(TYPE(NAME=ROUND_ROBIN, PROPERTIES("key"="value")))
), sql_hint_traffic_2( 
    LABELS(OLAP, order_by),
    TRAFFIC_ALGORITHM(TYPE(NAME= SQL_HINT))
), sql_hint_traffic_3( 
    TRAFFIC_ALGORITHM(TYPE(NAME= SQL_HINT))
);

 mysql> SHOW TRAFFIC RULES;
  1. Enable SQL display in Proxy
mysql> SET VARIABLE sql_show = true;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW ALL VARIABLES;
+---------------------------------------+----------------+
| variable_name                         | variable_value |
+---------------------------------------+----------------+
| sql_show                              | true           |
| sql_simple                            | false          |
| kernel_executor_size                  | 0              |
| max_connections_size_per_query        | 1              |
| check_table_metadata_enabled          | false          |
| sql_federation_enabled                | true           |
| proxy_frontend_database_protocol_type |                |
| proxy_frontend_flush_threshold        | 128            |
| proxy_hint_enabled                    | false          |
| proxy_backend_query_fetch_size        | -1             |
| proxy_frontend_executor_size          | 0              |
| proxy_backend_executor_suitable       | OLAP           |
| proxy_frontend_max_connections        | 0              |
| proxy_backend_driver_type             | JDBC           |
| proxy_mysql_default_version           | 5.7.22         |
| proxy_default_port                    | 3307           |
| proxy_netty_backlog                   | 1024           |
| proxy_instance_type                   | Proxy          |
| agent_plugins_enabled                 | true           |
| cached_connections                    | 0              |
| transaction_type                      | LOCAL          |
+---------------------------------------+----------------+
21 rows in set (0.01 sec)

  1. The application initiates a request and confirms the SQL through Proxy logs

  2. Disable SQL display in Proxy

mysql> SET VARIABLE sql_show = false;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW ALL VARIABLES;
+---------------------------------------+----------------+
| variable_name                         | variable_value |
+---------------------------------------+----------------+
| sql_show                              | false          |
| sql_simple                            | false          |
| kernel_executor_size                  | 0              |
| max_connections_size_per_query        | 1              |
| check_table_metadata_enabled          | false          |
| sql_federation_enabled                | true           |
| proxy_frontend_database_protocol_type |                |
| proxy_frontend_flush_threshold        | 128            |
| proxy_hint_enabled                    | false          |
| proxy_backend_query_fetch_size        | -1             |
| proxy_frontend_executor_size          | 0              |
| proxy_backend_executor_suitable       | OLAP           |
| proxy_frontend_max_connections        | 0              |
| proxy_backend_driver_type             | JDBC           |
| proxy_mysql_default_version           | 5.7.22         |
| proxy_default_port                    | 3307           |
| proxy_netty_backlog                   | 1024           |
| proxy_instance_type                   | Proxy          |
| agent_plugins_enabled                 | true           |
| cached_connections                    | 0              |
| transaction_type                      | LOCAL          |
+---------------------------------------+----------------+
21 rows in set (0.00 sec)

Syntax and Parameters #

Create & Drop Rules #

Create dual routing rules:

CREATE TRAFFIC RULE sql_match_traffic (
    LABELS(OLTP),
    TRAFFIC_ALGORITHM(TYPE(NAME=SQL_MATCH,PROPERTIES("sql" = "SELECT * FROM t_order WHERE order_id = ?; UPDATE t_order SET order_id = ?;"))),
    LOAD_BALANCER(TYPE(NAME=RANDOM, PROPERTIES("key"="value")))
), sql_hint_traffic_1( 
    TRAFFIC_ALGORITHM(TYPE(NAME= SQL_HINT)),
    LOAD_BALANCER(TYPE(NAME=ROUND_ROBIN, PROPERTIES("key"="value")))
), sql_hint_traffic_2( 
    LABELS(OLAP, order_by),
    TRAFFIC_ALGORITHM(TYPE(NAME= SQL_HINT))
), sql_hint_traffic_3( 
    TRAFFIC_ALGORITHM(TYPE(NAME= SQL_HINT))
);

LABELS and LOAD_BALANCER are optional fields. The omitted fields in the example are as follows.

  • sql_match_traffic no omitted fields
  • sql_hint_traffic_1 omit LABELS field
  • sql_hint_traffic_2 omit LOAD_BALANCER field
  • sql_hint_traffic_3 omit LABELS and LOAD_BALANCER fields

Drop dual routing rules:

DROP TRAFFIC RULE sql_hint_traffic_2, sql_hint_traffic_3;

Show Rules #

 -- Query all rules
 SHOW TRAFFIC RULES;
 -- Query specified rule
 SHOW TRAFFIC RULE sql_match_traffic;

Example: Show rules

SHOW TRAFFIC RULE sql_match_traffic;
+--------------------+---------------+----------------+--------------------------------------------------------------------------------+--------------------+---------------------+
| name               | labels        | algorithm_type | algorithm_props                                                                | load_balancer_type | load_balancer_props |
+--------------------+---------------+----------------+--------------------------------------------------------------------------------+--------------------+---------------------+
| sql_match_traffic  | OLTP          | SQL_MATCH      | sql=SELECT * FROM t_order WHERE order_id = ?; UPDATE t_order SET order_id = ?; | RANDOM             | key=value           |
+--------------------+---------------+----------------+--------------------------------------------------------------------------------+--------------------+---------------------+

Alter Rules #

ALTER TRAFFIC RULE sql_match_traffic (
    LABELS(OLTP,LIMIT),
    TRAFFIC_ALGORITHM(TYPE(NAME=SQL_MATCH,PROPERTIES("sql" = "SELECT * FROM t_order WHERE order_id = ?;"))),
    LOAD_BALANCER(TYPE(NAME=RANDOM, PROPERTIES("key1"="value1")))
), sql_hint_traffic_1( 
    TRAFFIC_ALGORITHM(TYPE(NAME= SQL_HINT))
), sql_hint_traffic_2( 
    LABELS(OLAP, order_by),
    TRAFFIC_ALGORITHM(TYPE(NAME= SQL_HINT))
), sql_hint_traffic_3( 
    TRAFFIC_ALGORITHM(TYPE(NAME= SQL_HINT)),
    LOAD_BALANCER(TYPE(NAME=ROUND_ROBIN, PROPERTIES("key"="value")))
);

LABELS and LOAD_BALANCER are optional fields. The omitted fields in the example are as follows.

  • sql_match_traffic no omitted fields
  • sql_hint_traffic_1 omit LABELS field
  • sql_hint_traffic_2 omit LOAD_BALANCER field
  • sql_hint_traffic_3 omit LABELS and LOAD_BALANCER fields

FAQ #

  1. In the same transaction unit, some statements may be executed on the JDBC access end and some on the Proxy access end. Can the consistency of the transaction still be guaranteed?

Transaction consistency can be guaranteed. The configuration of the transaction forwarding strategy has three built-in transaction matching algorithms, including FIRST_SQL, JDBC, and PROXY.

  • FIRST_SQL will decide the forwarding result of the transaction unit based on the first SQL forwarding result.

  • JDBC will not forward the transaction unit and execute it on JDBC.

  • PROXY will forward the transaction unit to the Proxy instance for execution. To ensure data consistency, the transaction unit will be executed on the same instance.