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 2022/01/25 10:53:05 UTC
[GitHub] [shardingsphere] TeslaCN opened a new issue #13893: Possibly poor performance in SQL rewrite
TeslaCN opened a new issue #13893:
URL: https://github.com/apache/shardingsphere/issues/13893
## Performance Issue
### Which version of ShardingSphere did you use?
ShardingSphere master 753995d257d5803cc61e4abda2870978f506c3af
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere JDBC
## Environments
- 128 cores (aarch64) running BenchmarkSQL + ShardingSphere JDBC
- 128 cores (aarch64) openGauss
- 128 cores (aarch64) openGauss
- 128 cores (aarch64) openGauss
While benching, the CPU usage of the BenchmarkSQL + ShardingSphere JDBC is about 12000%. But the average CPU usage of databases are 30~50%.
## JFR
Extract the following zip and open with JFR or IntelliJ IDEA with ShardingSphere project.
[shardingsphere_jdbc_cpu_lock_1638414632.zip](https://github.com/apache/shardingsphere/files/7638806/shardingsphere_jdbc_cpu_lock_1638414632.zip)
There are about **47% samples are SQL rewriting**:
## Configurations
```yaml
dataSources:
ds_0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: org.opengauss.Driver
jdbcUrl: jdbc:opengauss://og0:14000/test?loggerLevel=OFF
username: username
password: password
maximumPoolSize: 4096
minimumIdle: 400
ds_1:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: org.opengauss.Driver
jdbcUrl: jdbc:opengauss://og1:14000/test?loggerLevel=OFF
username: username
password: password
maximumPoolSize: 4096
minimumIdle: 400
ds_2:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: org.opengauss.Driver
jdbcUrl: jdbc:opengauss://og2:14000/test?loggerLevel=OFF
username: username
password: password
maximumPoolSize: 4096
minimumIdle: 400
rules:
- !SHARDING
bindingTables:
- bmsql_warehouse, bmsql_customer
- bmsql_stock, bmsql_district, bmsql_order_line
broadcastTables:
- bmsql_item
defaultDatabaseStrategy:
standard:
shardingAlgorithmName: database_inline
shardingColumn: ds_id
defaultTableStrategy:
none: null
shardingAlgorithms:
ds_bmsql_config_inline:
props:
algorithm-expression: ds_${cfg_id % 3}
type: INLINE
ds_bmsql_customer_inline:
props:
algorithm-expression: ds_${c_w_id % 3}
type: INLINE
ds_bmsql_district_inline:
props:
algorithm-expression: ds_${d_w_id % 3}
type: INLINE
ds_bmsql_history_inline:
props:
algorithm-expression: ds_${h_w_id % 3}
type: INLINE
ds_bmsql_new_order_inline:
props:
algorithm-expression: ds_${no_w_id % 3}
type: INLINE
ds_bmsql_oorder_inline:
props:
algorithm-expression: ds_${o_w_id % 3}
type: INLINE
ds_bmsql_order_line_inline:
props:
algorithm-expression: ds_${ol_w_id % 3}
type: INLINE
ds_bmsql_stock_inline:
props:
algorithm-expression: ds_${s_w_id % 3}
type: INLINE
ds_bmsql_warehouse_inline:
props:
algorithm-expression: ds_${w_id % 3}
type: INLINE
tables:
bmsql_config:
actualDataNodes: ds_${0..2}.bmsql_config
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_config_inline
shardingColumn: cfg_id
bmsql_customer:
actualDataNodes: ds_${0..2}.bmsql_customer
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_customer_inline
shardingColumn: c_w_id
bmsql_district:
actualDataNodes: ds_${0..2}.bmsql_district
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_district_inline
shardingColumn: d_w_id
bmsql_history:
actualDataNodes: ds_${0..2}.bmsql_history
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_history_inline
shardingColumn: h_w_id
bmsql_new_order:
actualDataNodes: ds_${0..2}.bmsql_new_order
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_new_order_inline
shardingColumn: no_w_id
bmsql_oorder:
actualDataNodes: ds_${0..2}.bmsql_oorder
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_oorder_inline
shardingColumn: o_w_id
bmsql_order_line:
actualDataNodes: ds_${0..2}.bmsql_order_line
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_order_line_inline
shardingColumn: ol_w_id
bmsql_stock:
actualDataNodes: ds_${0..2}.bmsql_stock
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_stock_inline
shardingColumn: s_w_id
bmsql_warehouse:
actualDataNodes: ds_${0..2}.bmsql_warehouse
databaseStrategy:
standard:
shardingAlgorithmName: ds_bmsql_warehouse_inline
shardingColumn: w_id
schemaName: sharding_db
props:
max-connections-size-per-query: 10
```
--
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 #13893: Possibly poor performance in SQL rewrite
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13893:
URL: https://github.com/apache/shardingsphere/issues/13893#issuecomment-1067510799
Already optimize.
--
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 #13893: Possibly poor performance in SQL rewrite
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13893:
URL: https://github.com/apache/shardingsphere/issues/13893#issuecomment-1021057157
After performance test, we still need to do lots of optimize for sql rewrite, so I will reopen this issue.
--
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 #13893: Possibly poor performance in SQL rewrite
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13893:
URL: https://github.com/apache/shardingsphere/issues/13893#issuecomment-1018059331
Already optimize.
--
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 #13893: Possibly poor performance in SQL rewrite
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #13893:
URL: https://github.com/apache/shardingsphere/issues/13893
--
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 #13893: Possibly poor performance in SQL rewrite
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #13893:
URL: https://github.com/apache/shardingsphere/issues/13893
--
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 #13893: Possibly poor performance in SQL rewrite
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13893:
URL: https://github.com/apache/shardingsphere/issues/13893#issuecomment-1021057157
After performance test, we still need to do lots of optimize for sql rewrite, so I will reopen this issue.
--
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