hi
we think the count query from 16databases , in the end ,it seems every
database result to sum by sharding-proxy in memory ,we think it so fast.
for example , db0 count query cost 5s, by the sharding-proxy count
query cost 25s with 16databases, to deal with the count result need 20s
(25s-5s)?
we really want to know what the proxy do after the count query ? why cost
another 20s+?
thanks
________________________________
发件人: Kai Yu <[email protected]>
发送时间: 2020年6月3日 17:23
收件人: [email protected] <[email protected]>
主题: 回复: About count query in sharding-proxy 4.0.0-RC2
Hi:
before i send last email ,i didn't add this props,
after I add props in server.yml , by the config like this:
max.connections.size.per.query: 64
acceptor.size: 64
executor.size: 64
the count query sql cost 25s
I changed props ,increase to 128 or 256 ,the count query sql cost still between
24-26s
Is this situation normal ?
Is there a way to continue improve this ? thanks
________________________________
发件人: Kai Yu <[email protected]>
发送时间: 2020年6月3日 15:16
收件人: [email protected] <[email protected]>
主题: About count query in sharding-proxy 4.0.0-RC2
Hi ALL :
we have a project used 16 databases (db0 ...db15)，we used sharding-proxy
4.0.0-RC2 ,
and config the sharding.xml like this:
schemaName: ty-basic-member
dataSources:
db0:
url: jdbc:mysql://xxx:3306/db0?useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 65
...
db15:
url: jdbc:mysql://xxx:3306/db15?useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 65
shardingRule:
tables:
member:
actualDataNodes:
dwj|13.member,dwj|12.member,dwj|11.member,dwj|10.member,dwj|5.member,dwj|6.member,dwj|7.member,dwj|15.member,dwj|8.member,dwj|14.member,dwj|9.member,dwj|0.member,dwj|0.member,dwj|1.member,dwj|2.member,dwj|3.member,dwj|4.member
databaseStrategy:
complex:
algorithmClassName:
com.toonyoo.app.sharding.proxy.ProxyDBShardingAlgorithm
shardingColumns: merchant_id,member_id
logicTable: member
there is a table named member, it has 3kw rows, then we test the query like
this :
case 1
we connect to the sharding-proxy application (like ip:3307 ) on server ,
execute count sql:
MySQL [ty-basic-member]> select count(*) from member where merchant_id = 'dwj';
+----------+
| count(*) |
+----------+
| 34202355 |
+----------+
1 row in set (37.59 sec)
case 2
we connect to db0 on server , executecount sql:
MySQL [db0]> select count(*) from member where merchant_id = 'dwj';
+----------+
| count(*) |
+----------+
| 2217826 |
+----------+
1 row in set (5.49 sec)
we found the count query cost 37s with 16 database ,
we think the proxy component deal the 16database's query may cost 5s like
count query on db0 ，looks like Parallel ,we dont know why.
Can you provide some technical details underlying principle for us to
understand?
thanks
