Traffic Management #

As data sizes continue to expand, distributed approaches using multi-node clusters have become a prevailing trend. In modern database systems, which separate storage and computing, the ability to manage the cluster as a whole and to control individual components at a fine-grained level is crucial. One of the challenges in managing and controlling a cluster is the ability to centrally manage it and operate at a fine-grained level in the event of a single point of failure.

The challenge of centralized management is to unify the status of the database storage nodes and middleware compute nodes, and to detect real-time changes in a distributed environment, providing a basis for controlling and scheduling the cluster. In controlling a single node in a distributed system, the challenge is to fuse and limit traffic flow to a single node in order to keep the entire database cluster running during overloads.

Circuit Breaker #

Blocking the connection between a node and the database can be necessary in certain circumstances. For instance, when a SphereEx-DBPlusEngine node exceeds its load or when other nodes need to stop accessing the database, the link to the database can be halted with a command. This action allows the database to secure enough resources to serve other nodes, thus improving the overall performance of the system. If needed, the database can be reconnected to the external service by restoring its state.

Related operations #

sql> DISABLE COMPUTE NODE IP=127.0.0.1,PORT=3306;
sql> SHOW COMPUTE NODES;

See Enable/Disable Proxy for details.

Traffic Limiting #

To protect high-priority requests from being compromised by overloaded requests, flow limiting can be enabled. This can be achieved by shadowing libraries and directing some traffic to other libraries, thus indirectly controlling the flow of requests. By doing so, the system can ensure that the high-priority requests receive the necessary resources and are responded to in a timely and efficient manner.

Related Operations #

具体参见Shadow

Read/Write Splitting #

Read/write splitting is a technique that directs access traffic to the database primary or secondary depending on whether the statement modifies the data. This approach can significantly increase the throughput of the database, making it ideal for scenarios where there are many reads and few writes. By routing read-only queries to the secondary, the system can distribute the load and improve overall performance. Meanwhile, write queries are directed to the primary, ensuring data consistency and integrity.

Related Operations #

See Readwrite Splitting for details.

Intelligent Routing #

Complex business scenarios can be effectively addressed by combining SphereEx-DBPlusEngine with SphereEx-DBPlusEngine-Driver, a lightweight, programmatic product designed to support the deployment of user-side Java applications. This product can be considered an enhancement to the standard JDBC Driver. When used together, these two products allow for the forwarding of business access traffic from the SphereEx-DBPlusEngine-Driver side to the SphereEx-DBPlusEngine side for processing. This approach enables the utilization of exclusive resources for heavy workloads, thereby significantly improving performance. This powerful feature is known as Traffic Route, see Intelligent Routing .

Product Features #

• SphereEx-DBPlus Driver uses a decentralized architecture and shares resources with the application, making it ideal for high-performance, lightweight OLTP applications developed in Java. However, as SphereEx-DBPlus Driver shares resources with the application, executing resource-intensive SQL queries can negatively impact application stability and performance. Additionally, the driver consumes more connections, and the impact of network latency is more noticeable when the application and database are deployed on different network partitions.
• SphereEx-DBPlusEngine is a powerful tool that provides a unified static entry point and can be deployed independently of the application. This makes it suitable for OLAP applications and scenarios where a sharded database is managed and maintained. By using SphereEx-DBPlusEngine, users can execute resource-intensive SQL queries without impacting the application. To further improve query performance and application stability, it is possible to forward resource-consuming SQL from the SphereEx-DBPlus Driver access side to the SphereEx-DBPlusEngine access side, which is located in the same network area as the database. This allows the SphereEx-DBPlusEngine access side to calculate the query results and return them to the application in one place. This approach significantly reduces the impact of network latency and improves query performance, resulting in a better user experience.

Deployment Architecture #

Related Concepts #

• Tags:

SphereEx-DBPlusEngine instances can be configured with a tag attribute to distinguish between them. This is useful when SphereEx-DBPlusEngine enables the Traffic feature, as the target of forwarding is the SphereEx-DBPlusEngine instance corresponding to the tag.

• Forwarding Strategy:

The strategy for forwarding SQL on the incoming side of SphereEx-DBPlus Driver includes the target Proxy instance label, forwarding algorithm, and load balancing algorithm. If the algorithm in the policy is configured as TransactionTrafficAlgorithm, the forwarding policy is a transaction forwarding policy. If the algorithm in the policy is configured as another algorithm, the forwarding policy is a normal forwarding policy.

• Forwarding Algorithm:

The forwarding algorithm is used to determine whether the current SQL needs to be forwarded or not. It includes HintTrafficAlgorithm, SegmentTrafficAlgorithm, and TransactionTrafficAlgorithm.

• Load Balancing Algorithm:

The load balancing algorithm is used to forward SQL statements to different SphereEx-DBPlusEngineProxy instances corresponding to the tag. This helps to distribute the load across multiple instances and ensure high availability and scalability of the database.

Usage Specifications #

• Support
  • All commonly used SQL is supported for forwarding.
• Unsupported
  • SQL that is not supported by user-configured kernel functions is still not supported after forwarding.
  • SQL forwarding to different accesses or SphereEx-DBPlusEngine-Proxy instances for execution after enabling transactions is not supported.

Usage #

While forwarding SQL to SphereEx-DBPlusEngine-Proxy can improve the performance and stability of the application, it does add complexity to the deployment architecture. Users must determine which statements should be forwarded and implement the necessary business logic at the DAO layer to control SQL forwarding. Additionally, in transaction-enabled scenarios, forwarding SQL to the proxy for execution can affect transaction consistency and visibility, which can impact the business system’s use of transactions. Therefore, careful consideration and testing should be performed before implementing SQL forwarding.

Forced Routing #

Data Sharding #

SQL can be forced to run on a specified slice by means of SQL Hint. The comment format for this function is currently only supported /* */ and the content needs to start with SHARDINGSPHERE_HINT:. Optional attributes include:

• {table}.SHARDING_DATABASE_VALUE: Used to add the sharded key value of the data source corresponding to the {table} table, with multiple attributes separated by commas.
• {table}.SHARDING_TABLE_VALUE: Add the key value of the table slice corresponding to the {table} table, with multiple attributes separated by commas.

SHARDING_DATABASE_VALUE: Use SHARDING_DATABASE_VALUE to set the sharding without specifying {table} when forcing a route to a shard when the shard is not a table.

/* SHARDINGSPHERE_HINT: t_order.SHARDING_DATABASE_VALUE=1, t_order.SHARDING_TABLE_VALUE=1 */
SELECT * FROM t_order;

Read/Write Splitting #

SQL Hint can be used to force SQL to run on the main database. The comment format for the SQL Hint function is currently only /* */ and the content needs to start with SHARDINGSPHERE_HINT: with the attribute WRITE_ROUTE_ONLY.

/* SHARDINGSPHERE_HINT: WRITE_ROUTE_ONLY=true */
SELECT * FROM t_order;

SQL can also be forced to run on a data source via SQL Hint. Comments are currently only supported in /* */ and need to start with SHARDINGSPHERE_HINT: and have the attribute DATA_SOURCE_NAME. If you are using a MySQL client connection, you need to add the -c option to preserve comments. The client defaults to -skip-comments to filter comments.

/* SHARDINGSPHERE_HINT: DATA_SOURCE_NAME=ds_0 */
SELECT * FROM t_order;

Session Management #

SphereEx-DBPlusEngine supports session management, allowing you to view the current session or kill it via DistSQL. This feature is currently limited to cases where the storage node is MySQL.

Related Operations #

• Viewing Sessions

• SphereEx-DBPlusEngine automatically generates a unique UUID identifier as an ID and stores the SQL execution information in each instance. When this command is executed, SphereEx-DBPlusEngine collects and synchronizes SQL execution information for each compute node through the Governance Center and then aggregates it back to the user.

mysql> show processlist;
+--------------------------------------+------+-----------+-------------+---------+------+---------------+-------------------+
| Id                                   | User | Host      | db          | Command | Time | State         | Info              |
+--------------------------------------+------+-----------+-------------+---------+------+---------------+-------------------+
| 3de4ff8d-2b06-abd0-46d0-124857919943 | root | 127.0.0.1 | sharding_db | Execute | 6    | Executing 0/1 | select sleep(100) |
| f9e5c97431567415fe10badc5fa46378     | root | 127.0.0.1 | sharding_db | Sleep   | 690  |               |                   |
+--------------------------------------+------+-----------+-------------+---------+------+---------------+-------------------+

• Output Description

  The output emulates native MySQL output, but the Id field is a special random string.

• Prerequisites

  The show-process-list-enabled parameter needs to be turned on. Seeparameter list for details.

• Kill Sessions

  SphereEx-DBPlusEngine will cancel the executing SQL based on the Id in the kill statement.

  mysql> kill 3de4ff8d-2b06-abd0-46d0-124857919943;
Query OK，0 rows affected (0.01 sec)

mysql> show processlist;
Empty set (0.02 sec)