You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "fyeeme (via GitHub)" <gi...@apache.org> on 2025/06/07 06:23:17 UTC
[I] Critical Performance Bottleneck: Optimize DataNode Creation in Table Sharding with Time-Based Strategy [shardingsphere]
fyeeme opened a new issue, #35625:
URL: https://github.com/apache/shardingsphere/issues/35625
## Performance Issue
In a time-based sharding scenario with hour-level granularity, we've identified a severe performance bottleneck in `ShardingStandardRouteEngine.routeTables()`. Performance drops from 10,000 TPS to 1-3 TPS when using sharding keys.
### Configuration Context
```yaml
tables:
zns_simple_delay_message:
actualDataNodes: ds_0.zns_simple_delay_message_2025${['06'..'12']}${['01'..'31']}${['00'..'23']}
tableStrategy:
standard:
shardingColumn: scheduled_time
shardingAlgorithmName: zns_simple_delay_message_inline
shardingAlgorithms:
zns_simple_delay_message_inline:
type: INTERVAL
props:
datetime-interval-unit: HOURS
datetime-interval-amount: 1
```

## Root Cause
The performance degradation occurs in the following loop where a new DataNode is created for each routed table:
```java
for (String each : routedTables) {
result.add(new DataNode(routedDataSource, each));
}
```
With hour-level sharding, this creates thousands of DataNode objects (24 hours * 31 days * 7 months = 5,208 potential objects) for a single routing operation.
## Impact
- Single insert performance: 1-3 TPS (with sharding) vs 10,000 TPS (without sharding)
- Excessive object creation causing GC pressure
- Significant latency in high-throughput scenarios
## Proposed Solutions
1. **Immediate Fix**: Implement DataNode object pooling
```java
private static final Map<String, DataNode> DATA_NODE_CACHE = new ConcurrentHashMap<>();
private DataNode getOrCreateDataNode(String dataSource, String table) {
String key = dataSource + "." + table;
return DATA_NODE_CACHE.computeIfAbsent(key, k -> new DataNode(dataSource, table));
}
```
2. **Long-term Optimization**:
- Pre-calculate and cache DataNode objects for time-based sharding
- Implement batch DataNode creation for time-interval strategies
- Consider lazy DataNode instantiation pattern
## Success Metrics
- Restore TPS to at least 50% of non-sharding
--
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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Critical Performance Bottleneck: Optimize DataNode Creation in Table Sharding with Time-Based Strategy [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #35625:
URL: https://github.com/apache/shardingsphere/issues/35625#issuecomment-3642817232
## Issue Understanding
In an hourly INTERVAL sharding setup (~7 months * 31 days * 24 hours ≈ 5,208 tables), TPS drops from ~10k to single digits when routing with the sharding key, suspected to be caused by DataNode creation in routeTables().
## Root Cause
The heavy cost is not DataNode construction but the INTERVAL sharding algorithm’s time-slice iteration plus full table-suffix matching: getMatchedTables iterates from datetime-lower to datetime-upper, and for each time slice filters all available table names with
endsWith (see features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/algorithm/sharding/datetime/IntervalShardingAlgorithm.java lines 139-158). Time-slice count × table count yields ~5,208 × 5,208 ≈ 27M string matches for this configuration; if
datetime-upper defaults to now() or spans more, iterations increase. DataNode is just a value object; its cost is negligible compared to the string matching.
## Analysis
- Official docs (INTERVAL algorithm: docs/document/content/user-manual/common-config/builtin-algorithm/sharding.en.md#interval-sharding-algorithm; algorithm list: docs/document/content/dev-manual/sharding.en.md) show range routing scans the time window and does not
index table suffixes.
- If the SQL lacks a sharding key or the format mismatches, tableShardingValues becomes empty and routes to all tables, inflating matches.
- Changing step to DAYS only reduces the number of time slices but keeps the “time slices × all tables” complexity.
- Generic optimization ideas (design-level) include building a suffix index over available tables, formatting suffix directly for precise sharding, and pruning time slices/table coverage for range sharding; these are design evolutions, not current defects.
## Conclusion
This is the intended behavior of the INTERVAL algorithm, not a DataNode bug. Recommended usage: explicitly narrow datetime-lower/datetime-upper to the actual table coverage, increase granularity or shorten the time span to fit throughput needs, and ensure SQL carries a sharding key matching datetime-pattern to avoid full-table routing.
--
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
Re: [I] Critical Performance Bottleneck: Optimize DataNode Creation in Table Sharding with Time-Based Strategy [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #35625: Critical Performance Bottleneck: Optimize DataNode Creation in Table Sharding with Time-Based Strategy
URL: https://github.com/apache/shardingsphere/issues/35625
--
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
Re: [I] Critical Performance Bottleneck: Optimize DataNode Creation in Table Sharding with Time-Based Strategy [shardingsphere]
Posted by "fyeeme (via GitHub)" <gi...@apache.org>.
fyeeme commented on issue #35625:
URL: https://github.com/apache/shardingsphere/issues/35625#issuecomment-2952600577
even if datetime-interval-unit: HOURS set **DAYS** ,TPS is still much lower than normal mysql jdbc
--
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