You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@shardingsphere.apache.org by Kai Yu <fr...@outlook.com> on 2020/06/03 07:16:41 UTC
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
Re: About count query in sharding-proxy 4.0.0-RC2
Posted by Zhang Yonglun <zh...@apache.org>.
In server.yaml, followling props effect the concurrency, you can have a try.
max.connections.size.per.query: 64
acceptor.size: 64
executor.size: 64
--
Zhang Yonglun
Apache ShardingSphere
Kai Yu <fr...@outlook.com> 于2020年6月3日周三 下午3:16写道：
> 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
>
Re: About count query in sharding-proxy 4.0.0-RC2
Posted by Zhang Yonglun <zh...@apache.org>.
Please refer to the docement of Execute Engine:
https://shardingsphere.apache.org/document/current/en/features/sharding/principle/execute/
and Merge Engine:
https://shardingsphere.apache.org/document/current/en/features/sharding/principle/merge/
--
Zhang Yonglun
Apache ShardingSphere
Kai Yu <fr...@outlook.com> 于2020年6月3日周三 下午6:05写道：
> 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 <fr...@outlook.com>
> 发送时间: 2020年6月3日 17:23
> 收件人: dev@shardingsphere.apache.org <de...@shardingsphere.apache.org>
> 主题: 回复: 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 <fr...@outlook.com>
> 发送时间: 2020年6月3日 15:16
> 收件人: dev@shardingsphere.apache.org <de...@shardingsphere.apache.org>
> 主题: 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
>
回复: About count query in sharding-proxy 4.0.0-RC2
Posted by Kai Yu <fr...@outlook.com>.
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 <fr...@outlook.com>
发送时间: 2020年6月3日 17:23
收件人: dev@shardingsphere.apache.org <de...@shardingsphere.apache.org>
主题: 回复: 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 <fr...@outlook.com>
发送时间: 2020年6月3日 15:16
收件人: dev@shardingsphere.apache.org <de...@shardingsphere.apache.org>
主题: 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
Re: About count query in sharding-proxy 4.0.0-RC2
Posted by Zhang Yonglun <zh...@apache.org>.
4.0.0-RC2 is too old. Can you change ther Proxy version to 4.1.1 just
released? You can download from
https://shardingsphere.apache.org/document/current/en/downloads/ .
--
Zhang Yonglun
Apache ShardingSphere
Kai Yu <fr...@outlook.com> 于2020年6月11日周四 下午4:19写道：
> Hi:
> We test again, the cost use Proxy to route to only one database :
> > “select count(*) from member where merchant_id = 'dwj'”
> > the cost is :
> 4.32 sec
>
> its looks like the same cost of count query to one server（ db0 or db1）
> ,like this.
>
> so , the same question
> query cost 25s with 16databases, why?
> ________________________________
> 发件人: Zhang Yonglun <zh...@apache.org>
> 发送时间: 2020年6月4日 17:27
> 收件人: dev <de...@shardingsphere.apache.org>
> 主题: Re: About count query in sharding-proxy 4.0.0-RC2
>
> Get it. What about the cost use Proxy to route to only one database?
> Actually merging just consume little time.
>
> --
>
> Zhang Yonglun
> Apache ShardingSphere
>
>
> Kai Yu <fr...@outlook.com> 于2020年6月4日周四 下午4:56写道：
>
> > 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 <zh...@apache.org>
> > 发送时间: 2020年6月4日 14:03
> > 收件人: dev <de...@shardingsphere.apache.org>
> > 主题: 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 <fr...@outlook.com> 于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 <fr...@outlook.com>
> > > 发送时间: 2020年6月3日 15:16
> > > 收件人: dev@shardingsphere.apache.org <de...@shardingsphere.apache.org>
> > > 主题: 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
> > >
> >
>
回复: About count query in sharding-proxy 4.0.0-RC2
Posted by Kai Yu <fr...@outlook.com>.
Hi:
We test again, the cost use Proxy to route to only one database :
> “select count(*) from member where merchant_id = 'dwj'”
> the cost is :
4.32 sec
its looks like the same cost of count query to one server（ db0 or db1） ,like this.
so , the same question
query cost 25s with 16databases, why?
________________________________
发件人: Zhang Yonglun <zh...@apache.org>
发送时间: 2020年6月4日 17:27
收件人: dev <de...@shardingsphere.apache.org>
主题: Re: About count query in sharding-proxy 4.0.0-RC2
Get it. What about the cost use Proxy to route to only one database?
Actually merging just consume little time.
--
Zhang Yonglun
Apache ShardingSphere
Kai Yu <fr...@outlook.com> 于2020年6月4日周四 下午4:56写道：
> 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 <zh...@apache.org>
> 发送时间: 2020年6月4日 14:03
> 收件人: dev <de...@shardingsphere.apache.org>
> 主题: 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 <fr...@outlook.com> 于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 <fr...@outlook.com>
> > 发送时间: 2020年6月3日 15:16
> > 收件人: dev@shardingsphere.apache.org <de...@shardingsphere.apache.org>
> > 主题: 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
> >
>
Re: About count query in sharding-proxy 4.0.0-RC2
Posted by Zhang Yonglun <zh...@apache.org>.
Get it. What about the cost use Proxy to route to only one database?
Actually merging just consume little time.
--
Zhang Yonglun
Apache ShardingSphere
Kai Yu <fr...@outlook.com> 于2020年6月4日周四 下午4:56写道：
> 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 <zh...@apache.org>
> 发送时间: 2020年6月4日 14:03
> 收件人: dev <de...@shardingsphere.apache.org>
> 主题: 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 <fr...@outlook.com> 于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 <fr...@outlook.com>
> > 发送时间: 2020年6月3日 15:16
> > 收件人: dev@shardingsphere.apache.org <de...@shardingsphere.apache.org>
> > 主题: 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
> >
>
回复: About count query in sharding-proxy 4.0.0-RC2
Posted by Kai Yu <fr...@outlook.com>.
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 <zh...@apache.org>
发送时间: 2020年6月4日 14:03
收件人: dev <de...@shardingsphere.apache.org>
主题: 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 <fr...@outlook.com> 于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 <fr...@outlook.com>
> 发送时间: 2020年6月3日 15:16
> 收件人: dev@shardingsphere.apache.org <de...@shardingsphere.apache.org>
> 主题: 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
>
Re: About count query in sharding-proxy 4.0.0-RC2
Posted by Zhang Yonglun <zh...@apache.org>.
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 <fr...@outlook.com> 于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 <fr...@outlook.com>
> 发送时间: 2020年6月3日 15:16
> 收件人: dev@shardingsphere.apache.org <de...@shardingsphere.apache.org>
> 主题: 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
>
回复: About count query in sharding-proxy 4.0.0-RC2
Posted by Kai Yu <fr...@outlook.com>.
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 <fr...@outlook.com>
发送时间: 2020年6月3日 15:16
收件人: dev@shardingsphere.apache.org <de...@shardingsphere.apache.org>
主题: 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