You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/12/24 01:49:10 UTC
[GitHub] [shardingsphere] strongduanmu opened a new issue #14282: [New feature] Add new traffic rule to implement JDBC request forwarding to Proxy
strongduanmu opened a new issue #14282:
URL: https://github.com/apache/shardingsphere/issues/14282
## Feature Request
Add new traffic rule to implement JDBC request forwarding to Proxy.
### Is your feature request related to a problem?
No.
### Describe the feature you would like.
In some scenarios, the application and the database may be deployed in different network regions. The application uses the JDBC adapter to connect to the database in another network region. Because the JDBC adapter consumes a lot of connections and more cross-network region connections will inevitably lead to a decrease in query performance. In order to improve the query performance in this scenario, we consider forwarding the request from the JDBC adapter to the Proxy adapter located in the same network area as the database, and the Proxy calculates the query results, and then return them to the JDBC adapter, and finally to the application program.

Traffic rule is used to determine whether the currently SQL statement needs to be forwarded. The following shows a configuration example of traffic rule. `TrafficStrategies` is used to declare the request forwarding strategy. When the user configures multiple forwarding strategies, they will be matched in sequence according to the configuration order, and the first successfully matched strategy will be used for request forwarding processing. Take `group_by_traffic` as an example to look at the forwarding strategy in detail, `group_by_traffic` is the name of the forwarding strategy, and it contains three attributes:
- `labels`: the label corresponding to the Proxy instance;
- `algorithmName`: request forwarding matching algorithm, for example, the matching operation type is select, and it contains a group by statement. If the matching is successful, it will find the forwarded Proxy instance according to labels;
- `loadBalancerName`: used to specify the load balancing strategy for forwarding, because the Proxy instance corresponding to labels may contain multiple instances, and the load balance strategy needs to be used to determine which instance to forward to;
```yaml
- !TRAFFIC
trafficStrategies:
group_by_traffic:
labels: OLAP, OLTP
algorithmName: group_by_match_algorithm
loadBalancerName: random_load_balancer
simple_hint_traffic:
labels: ORDER_BY, GROUP_BY
algorithmName: simple_hint_algorithm
loadBalancerName: random_load_balancer
trafficAlgorithms:
group_by_match_algorithm:
type: REGEX_MATCH
props:
operation: select
column: user_id
regex: "[1]"
simple_hint_algorithm:
type: SIMPLE_HINT
props:
condition: xxx
trafficLoadBalancers:
random_load_balancer:
type: RANDOM
round_robin_load_balancer:
type: ROUND_ROBIN
```
In order to cooperate with the labels attribute in traffic rule, the Proxy adapter needs to support the label attribute setting and can store the ip and port information corresponding to the Proxy instance. Proxy server.yaml adds the following configuration.
```yaml
labels: OLTP, GROUP_BY
```
We will add the `shardingsphere-traffic` module under the kernel.
```
shardingsphere-traffic
├── shardingsphere-traffic-api
└── shardingsphere-traffic-core
```
From an overall point of view, the general flow is as follows.

--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #14282: [New feature] Add new traffic rule to implement JDBC request forwarding to Proxy
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #14282:
URL: https://github.com/apache/shardingsphere/issues/14282
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #14282: [New feature] Add new traffic rule to implement JDBC request forwarding to Proxy
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #14282:
URL: https://github.com/apache/shardingsphere/issues/14282#issuecomment-1038869570
The traffic rule development task has been completed, so this issue can be closed.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #14282: [New feature] Add new traffic rule to implement JDBC request forwarding to Proxy
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #14282:
URL: https://github.com/apache/shardingsphere/issues/14282#issuecomment-1036016069
When I use follow unit test to test FirstSQLTrafficAlgorithm, I found the result is wrong.
```java
@Test
public void testFirstSQLTrafficWhenUsePreparedStatementAlgorithmMatch() throws SQLException {
String sql1 = "/* shardingsphere hint:use_traffic=true */SELECT * FROM t_order WHERE content IN (?, ?) AND user_id = 1";
String sql2 = "SELECT * FROM t_order WHERE content IN (?, ?) AND user_id = 1";
setAutoCommit(false);
getPreparedStatement(sql1, Arrays.asList("test1", "test10")).executeQuery();
getPreparedStatement(sql2, Arrays.asList("test1", "test10")).executeQuery();
commit();
}
```
The executed sql is not same with actual sql.
<img width="1210" alt="image" src="https://user-images.githubusercontent.com/10829171/153566892-259651fa-25e9-4115-a94e-ce01dd26e2a2.png">
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org