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/03/02 02:16:30 UTC
[GitHub] [shardingsphere] wsg96321 opened a new issue #15727: How to auto discover all resource in one schema ?
wsg96321 opened a new issue #15727:
URL: https://github.com/apache/shardingsphere/issues/15727
version: sharding proxy v5.1.0
Hi, I have many resource in a schema like test, it names ds_1,ds_2....ds_100 and other ds_500..ds_600. When I use distSQL with my own defined algorithm , I should use the sql like
```
CREATE SHARDING ALGORITHM hint_common (
TYPE(NAME=COMMON_SHARD)
);
CREATE SHARDING TABLE RULE hero (
DATANODES("ds_${1..100}.hero,ds_${500..600}"),
DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=id,SHARDING_ALGORITHM=hint_common)
);
```
Besides, the table `hero` is in all resource . So I want to now , how to auto discover the datanodes ? like below, though it is wrong:
```
CREATE SHARDING TABLE RULE hero (
DATANODES("*"),
DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=id,SHARDING_ALGORITHM=hint_common)
);
or
CREATE SHARDING TABLE RULE hero (
DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=id,SHARDING_ALGORITHM=hint_common)
);
```
I think it is very useful for people when one table in all resources , especially when I have to delete some dataresource in my schema. Eg. I have to delete ds_50 and ds_550 in this case , so I have to execute this sql (when I have to delete many nodes it will be so complex , though I can also use enumerate with many numbers ) :
```
ALTER SHARDING TABLE RULE hero(
DATANODES("ds_${1..49}.hero,ds_${51..100}.hero,ds_${500..549}.hero,ds_${551..600}.hero")
);
```
--
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] wsg96321 edited a comment on issue #15727: How to auto discover all resource in one schema ?
Posted by GitBox <gi...@apache.org>.
wsg96321 edited a comment on issue #15727:
URL: https://github.com/apache/shardingsphere/issues/15727#issuecomment-1056115813
Maybe it is a good idea for this Exception to deal like "mysql connection refuse " ? We use this in game's mysql meta data, so I think it is normal for game to "start a new server " or "merge two server " or "close a old server ". In this case , I have to often to `ADD RESROUCE` and `DROP RESOURCE` , and then execute SQL `ALTER SHARDING TABLE RULE xxxx` for every table like `hero/vip/user/account` and so on . Besides, every time when I execute `ADD RESOURCE` will cost about 3 mins for one time(just in commad tell me success, it really can use maybe need 11 mins [It maybe flush connection in backgroud] ) and `ALTER SHARDING TABLE RULE xxxx` cost about 4mins for one table , so I even can't not flush all data in an hour.
--
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] wsg96321 commented on issue #15727: How to auto discover all resource in one schema ?
Posted by GitBox <gi...@apache.org>.
wsg96321 commented on issue #15727:
URL: https://github.com/apache/shardingsphere/issues/15727#issuecomment-1056115813
Maybe it is a good idea for this Exception to deal like "mysql connection refuse " ? We use this in game's mysql meta data, so I think it is normal for game to "start a new server " or "merge two server " or "close a old server ". In this case , I have to often to `ADD RESROUCE` and `DROP RESOURCE` , and then execute SQL `ALTER SHARDING TABLE RULE xxxx` for every table like `hero/vip/user/account` and so on .
--
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] RaigorJiang commented on issue #15727: How to auto discover all resource in one schema ?
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #15727:
URL: https://github.com/apache/shardingsphere/issues/15727#issuecomment-1056106547
Hi @wsg96321
I understand what you're talking, but doing so will cause some other problems, such as configuring DATANODES("*"):
If I use `ADD RESOURCE` to add a new resource, the new resource may not have the table `hero`, which will cause SQL execution errors.
--
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] RaigorJiang commented on issue #15727: How to auto discover all resource in one schema ?
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #15727:
URL: https://github.com/apache/shardingsphere/issues/15727#issuecomment-1057590868
Hi @tuichenchuxin
Please take a look, is our understanding of metadata loading correct?
--
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] tuichenchuxin commented on issue #15727: How to auto discover all resource in one schema ?
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #15727:
URL: https://github.com/apache/shardingsphere/issues/15727#issuecomment-1057748898
@RaigorJiang @wsg96321 Yes, your understanding is correct.
For now, we didn't support auto dscover all resource. And I think it's a good idea to auto discover all resource.
--
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] wsg96321 commented on issue #15727: How to auto discover all resource in one schema ?
Posted by GitBox <gi...@apache.org>.
wsg96321 commented on issue #15727:
URL: https://github.com/apache/shardingsphere/issues/15727#issuecomment-1056724498
Maybe we can do it in a batch ? like
```
ALTER SHARDING TABLE RULE hero1,hero2,hero3,hero4,hero5...hero100(
DATANODES("ds_${1..49}.hero,ds_${51..100},ds_${500..549},ds_${551..600}"),
DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=id,SHARDING_ALGORITHM=hint_common)
);
or
ALTER SHARDING TABLE RULE hero1,hero2,hero3,hero4,hero5...hero100(
RESOURCES("ds_${1..49},ds_${51..100},ds_${500..549},ds_${551..600}"),
DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=id,SHARDING_ALGORITHM=hint_common)
);
```
At first we have to support this grammar which does not specify table . The purpose of this is to reuse one connection to process different meta of tables , and then we just reflush metadata X times (the resources we have) instead of Y times (the Cartesian Product of resource's number and table's number ).
eg. We have 100 datasource and 200 tables in every dataresource(which in different LAN) , we need to reflush 100*200=2000 times , surpose every time we need 1s , it will cost us 2000s (about 33.3 min). But if we can reuse connection with batch sql , we just reflush 100 times which cost us 100s (about 1.7min) , and it must cost more than 1s in really operate because of the different of processing one table and processing 200 tables . But I think maybe it is a good direction of optimization .
--
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] RaigorJiang commented on issue #15727: How to auto discover all resource in one schema ?
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #15727:
URL: https://github.com/apache/shardingsphere/issues/15727#issuecomment-1056394317
Changes in resources will trigger refresh metadata, which is really time-consuming when there are too many tables.
There is currently no good way to do this, we can think more about it.
--
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