Shardingsphere: Initiating datasource too slow when defaultDataSource has big amount of tables

[joooohnli]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.
If no response more than 7 days and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

3.1.0

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Sharding-JDBC

Expected behavior

init datasource faster

Actual behavior

init datasource too slow.

Reason analyze (If you can)

my database has 8000 tables and most of them are sharding tables. it is reasonable to take so long to load metadata of all tables.

io.shardingsphere.core.metadata.table.executor.TableMetaDataInitializer

I wonder if metadata of sharding tables that belongs to a same logic table can be load only one time because they are supposed to have same table structure, it would be faster if do so.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1. database A has big amount of tables.(in my case, it has 8000 tables)
2. set defaultDataSourceName=A
3. start application
4. initiating lag.(in my case, it took about 10 minutes)

Example codes for reproduce this issue (such as a github link).

[terrymanu]

ShardingSphere need load tables's metadata.
Your default table have big amount of tables, I think same problem occur if other framework need to load metadata, such hibernate.
I tend to don't make this kind of problem as ShardingSphere's problem, what do you think?

[joooohnli]

No it's not a problem. I just request if an optimization or option can be considered:
metadata of sharding tables that belongs to a same logic table can be loaded only one time because they are supposed to have same table structure

[daizf]

I hava the same trouble.
And I think "DefaultTables" should not include ShardingTables. In case default database includes ShardingTables.

[terrymanu]

And I think "DefaultTables" should not include ShardingTables. In case default database includes ShardingTables.

If not include "DefaultTables", use input show tables will not display "DefaultTables"

[daizf]

I think I have solved it in another way.
Before this, the "ShardingTables" and "DefaultTables" have shared the same database(such as demodb_00). Now, I separate them with different database.

[joooohnli]

Maybe I didn't explain it clearly. let me put it in this way:
Logic table t binds sharding tablest_${0..99}. while loading metadata, we hope just to load metadata of t_1, and set that metadata to rest sharding tables t_${1..99}.

[daizf]

@joooohnli

isCheckingMetaData = false，then just fetch metadata for t_1;
And the default value of isCheckingMetaData is false.

[joooohnli]

@daizf but it will still be load 100 times, so it does not help save time.

[terrymanu]

fixed at #1688

[joooohnli]

@terrymanu #1688 does not fix my issues.

Time is mainly consumed on metadata loading, not checking.

Based on #1688, the behavior will be like this: metadata of t_1 will be loaded 100 times. it does not save time at all.

[yjjqrqqq]

This proplem should not be related to #1688, every boot shardingsphere will scall all tables metadata. too slow~~~~

[yjjqrqqq]

finally,, i reloved like this .

copy TableMetaDataInitializer.java to my project, and removed loadDefaultTables function. then,, times fly~~~

It's not a good idea, but i have no others ways~~

[linxux]

@terrymanu #1688 does not fix my issues.

Time is mainly consumed on metadata loading, not checking.

Based on #1688, the behavior will be like this: metadata of t_1 will be loaded 100 times. it does not save time at all.

Agree.
The loadDefaultTables still load the table metadata in n times for the same table.

[MickMo]

finally,, i reloved like this .

copy TableMetaDataInitializer.java to my project, and removed loadDefaultTables function. then,, times fly~~~

It's not a good idea, but i have no others ways~~

Same problem here ，same way to fix it.
But is there any side effect？

[yanjunlu]

finally,, i reloved like this .

copy TableMetaDataInitializer.java to my project, and removed loadDefaultTables function. then,, times fly~~~

It's not a good idea, but i have no others ways~~

Have you found any side effects?

[jxxyan]

finally,, i reloved like this .
copy TableMetaDataInitializer.java to my project, and removed loadDefaultTables function. then,, times fly~
It's not a good idea, but i have no others ways

Same problem here ，same way to fix it.
But is there any side effect？

finally,, i reloved like this .

copy TableMetaDataInitializer.java to my project, and removed loadDefaultTables function. then,, times fly~~~

It's not a good idea, but i have no others ways~~

how did you do that ,have some codes?

[Allkoman]

https://shardingsphere.apache.org/document/current/en/faq/#18-how-to-speed-up-the-metadata-loading-when-service-starts-up

upgrade to 4.1.0 it's still very slow when I hava 3000 tables

[Allkoman]

when I set max.connections.size.per.query = 10000, it's much faster, but it take too much mysql resources as well . neither is ok for me