hi: thx for your replay, our machine'environment is aliyun RDS 4C8G The 16 machines have same environment, it means one RDS one database,We have 16 RDS. Every databases have one table named 'member', the table have almost two million rows, it means 16databases total have thirty million rows .
we connect to every server（from db0 to db15） , execute count sql is “select count(*) from member where merchant_id = 'dwj'” the cost is : > db0 (4.97 sec) > db1 (4.83 sec) > db2 (5.23 sec) > db3 (4.94 sec) > db4 (5.12 sec) > db5 (5.86 sec) > db6 (5.31 sec) > db7 (5.03 sec) > db8 (5.37 sec) > db9 (5.66 sec) > db9 (5.75 sec) > db10 (5.49 sec) > db11 (5.11 sec) > db12 (4.98 sec) > db13 (5.72 sec) > db14 (5.33 sec) > db15 (5.15 sec) ________________________________ 发件人: Zhang Yonglun <[email protected]> 发送时间: 2020年6月4日 14:03 收件人: dev <[email protected]> 主题: Re: About count query in sharding-proxy 4.0.0-RC2 If so, I suggest you connect all 16 databases and record the time cost for each. Also, please show your hardware environment. -- Zhang Yonglun Apache ShardingSphere Kai Yu <[email protected]> 于2020年6月3日周三 下午5:23写道： > 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 >
