You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "huntkalio (via GitHub)" <gi...@apache.org> on 2025/08/21 11:01:58 UTC
[I] Aggregate data sources improve shardingproxy latecy and Reduced performance [shardingsphere]
huntkalio opened a new issue, #36379:
URL: https://github.com/apache/shardingsphere/issues/36379
I upgrade shardingproxy version from 5.3.2 to 5.5.2.After that I found the shardingproxy latency rise（about doube）.Then I found shardingproxy connection with mysql switch schema frequently.
From: https://github.com/apache/shardingsphere/pull/26761/files I found that: new shardingproxy will aggregate data sources and this may cause switch schema ,and then improve latency .
Is there a solution? Can data sources aggregation be turned off?
Below is my config:
```
schemaName: test_db
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/test_0?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://127.0.0.1:3306/test_1?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_2:
url: jdbc:mysql://127.0.0.1:3306/test_2?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_3:
url: jdbc:mysql://127.0.0.1:3306/test_3?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_4:
url: jdbc:mysql://127.0.0.1:3306/test_4?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_5:
url: jdbc:mysql://127.0.0.1:3306/test_5?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_6:
url: jdbc:mysql://127.0.0.1:3306/test_6?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_7:
url: jdbc:mysql://127.0.0.1:3306/test_7?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_8:
url: jdbc:mysql://127.0.0.1:3306/test_8?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_9:
url: jdbc:mysql://127.0.0.1:3306/test_9?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_10:
url: jdbc:mysql://127.0.0.1:3306/test_10?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_11:
url: jdbc:mysql://127.0.0.1:3306/test_11?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_12:
url: jdbc:mysql://127.0.0.1:3306/test_12?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_13:
url: jdbc:mysql://127.0.0.1:3306/test_13?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_14:
url: jdbc:mysql://127.0.0.1:3306/test_14?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_15:
url: jdbc:mysql://127.0.0.1:3306/test_15?characterEncoding=utf8&useSSL=false&useUnicode=true&allowMultiQueries=true
username: test
password: yyy
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
pcs_user_device_relation_did:
actualDataNodes: ds_${0..15}.test_table_${0..15}
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_user_id_inline
tableStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: test_table_inline
defaultKeyGenerateStrategy:
column: id
keyGeneratorName: snowflake
shardingAlgorithms:
database_user_id_inline:
type: INLINE
props:
algorithm-expression: ds_${Math.abs(user_id ? user_id.hashCode() % 16:user_id.hashCode() % 16)}
test_table_inline:
type: INLINE
props:
algorithm-expression: test_table_${Math.abs(user_id ? user_id.hashCode().intdiv(16) % 16:user_id.hashCode().intdiv(16) % 16)}
```
--
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] Aggregate data sources increase shardingproxy latecy and reduced performance [shardingsphere]
Posted by "elulis (via GitHub)" <gi...@apache.org>.
elulis commented on issue #36379:
URL: https://github.com/apache/shardingsphere/issues/36379#issuecomment-3257328295
Or else you can modify org.apache.shardingsphere.infra.database.mysql.metadata.database.MySQLDatabaseMetaData#isInstanceConnectionAvailable **returning false instead true** and replace the jar file.
--
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] Aggregate data sources increase shardingproxy latecy and reduced performance [shardingsphere]
Posted by "elulis (via GitHub)" <gi...@apache.org>.
elulis commented on issue #36379:
URL: https://github.com/apache/shardingsphere/issues/36379#issuecomment-3257283429
It is possible to bypass this aggregation by **using different usernames** for each data source. The aggregation logic uses a combination of hostname, port, and username to distinguish between physical database instances.
--
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] Aggregate data sources increase shardingproxy latecy and reduced performance [shardingsphere]
Posted by "zhaojinchao95 (via GitHub)" <gi...@apache.org>.
zhaojinchao95 commented on issue #36379:
URL: https://github.com/apache/shardingsphere/issues/36379#issuecomment-3218231899
@RaigorJiang Hi, please cc
--
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] Aggregate data sources increase shardingproxy latecy and reduced performance [shardingsphere]
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #36379:
URL: https://github.com/apache/shardingsphere/issues/36379#issuecomment-3283530168
> > Analysis shows that `setCatalog` may brings performance loss. We can add a switch to control whether to enable instance connection.
> > can this switch be added to version 5.5.3?
@huntkalio We can try this.
--
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] Aggregate data sources increase shardingproxy latecy and reduced performance [shardingsphere]
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #36379:
URL: https://github.com/apache/shardingsphere/issues/36379#issuecomment-3218233792
Analysis shows that setCatalog brings performance loss. We can add a switch to control whether to enable instance connection.
--
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] Aggregate data sources increase shardingproxy latecy and reduced performance [shardingsphere]
Posted by "huntkalio (via GitHub)" <gi...@apache.org>.
huntkalio commented on issue #36379:
URL: https://github.com/apache/shardingsphere/issues/36379#issuecomment-3222942750
> Analysis shows that `setCatalog` may brings performance loss. We can add a switch to control whether to enable instance connection.
can this switch be added to version 5.5.3?
--
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] Aggregate data sources increase shardingproxy latecy and reduced performance [shardingsphere]
Posted by "jiangML (via GitHub)" <gi...@apache.org>.
jiangML closed issue #36379: Aggregate data sources increase shardingproxy latecy and reduced performance
URL: https://github.com/apache/shardingsphere/issues/36379
--
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