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 2021/03/30 10:10:49 UTC

[GitHub] [shardingsphere] viclemon opened a new issue #9879: Optimize startup time: to mach tables in DB, control same logic table only load once

viclemon opened a new issue #9879:
URL: https://github.com/apache/shardingsphere/issues/9879


   I find the startup time is too slow due to the database full tables metadata scan,
   for our case. the database has 5133 tables, we are using micro service,  about 80 apps.
   when we deploying the app. This cause two problems
   
   1. Each app will load all tables metadata, in our project, many tables is same, but all table need to be load, so it's slow to start up.
   2. So many app load tables in the same time makes the Database high load and CPU utilization
   
   I debug the code find 
   ```java
   // SchemaMetaDataLoader:84  version:4.1.1
       private static Map<String, TableMetaData> load(final Connection connection, final Collection<String> tables, final String databaseType) throws SQLException {
           try (Connection con = connection) {
               Map<String, TableMetaData> result = new LinkedHashMap<>();
               for (String each : tables) {
                   result.put(each, new TableMetaData(ColumnMetaDataLoader.load(con, each, databaseType), IndexMetaDataLoader.load(con, each, databaseType)));
               }
               return result;
           }
       }
   
   ```
   The for loop load all table.  Every logic table like test `(test_${0..1023})`  which need load 1024 times in all.
   Sharding table have the same metadata, why not use memory to load only once for logic table
   I tried like this
   ```java
             for (String each : tables) {
                   String logicTable= StrUtils.getLogicTableName(each);
                   if(!resultMeta.containsKey(logicTable)){
                       resultMeta.put(logicTable,new TableMetaData(ColumnMetaDataLoader.load(con, each, databaseType), IndexMetaDataLoader.load(con, each, databaseType)));
                   }
                   result.put(each, resultMeta.get(logicTable));
               }
               return result;
   ```
   ```java
   public static final String SEPARATOR="_";
   public static String getLogicTableName(String tableName){
           if(StringUtils.isNumeric(getSuffix(tableName))){
               return tableName.substring(0,tableName.lastIndexOf(SEPARATOR));
           }
           return tableName;
   
       }
   
   public static String getSuffix(String tableName){
           return tableName.substring(tableName.lastIndexOf(SEPARATOR)+1);
       }
   ```
   ### logs
   *before:*
   >[INFO] -[main] ShardingMetaDataLoader.loadShardingSchemaMetaData(131) - Loading 5 logic tables' meta data.
   >[INFO] -[main] SchemaMetaDataLoader.load(70) - Loading 5133 tables' meta data.
   >[INFO] -[main] MultipleDataSourcesRuntimeContext.createMetaData(59) - Meta data load finished, cost 184055 milliseconds.
   
   *after:*
   >[INFO] -[main] ShardingMetaDataLoader.loadShardingSchemaMetaData(131) - Loading 5 logic tables' meta data.
   >[INFO] -[main] SchemaMetaDataLoader.load(79) - MyLoading 5133 tables' meta data.
   >[INFO] -[main] MultipleDataSourcesRuntimeContext.createMetaData(59) - Meta data load finished, cost 827 milliseconds.
   
   Only use single thread, the load  time reduce almost 200 times.
   So, why not use memory when loading the metadata?


-- 
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.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] terrymanu commented on issue #9879: Optimize startup time: to mach tables in DB, control same logic table only load once

Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #9879:
URL: https://github.com/apache/shardingsphere/issues/9879#issuecomment-927253312


   The problem has resolved on 5.x


-- 
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] terrymanu closed issue #9879: Optimize startup time: to mach tables in DB, control same logic table only load once

Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #9879:
URL: https://github.com/apache/shardingsphere/issues/9879


   


-- 
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] zhujunxxxxx commented on issue #9879: Optimize startup time: to mach tables in DB, control same logic table only load once

Posted by GitBox <gi...@apache.org>.
zhujunxxxxx commented on issue #9879:
URL: https://github.com/apache/shardingsphere/issues/9879#issuecomment-810147548


   Hi, I am very aware of this problem in 4.1.1, this problem appears to have been fixed in the latest version.


-- 
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.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] viclemon commented on issue #9879: Optimize startup time: to mach tables in DB, control same logic table only load once

Posted by GitBox <gi...@apache.org>.
viclemon commented on issue #9879:
URL: https://github.com/apache/shardingsphere/issues/9879#issuecomment-810735695


   Grate!  I'll try 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.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] viclemon commented on issue #9879: Optimize startup time: to mach tables in DB, control same logic table only load once

Posted by GitBox <gi...@apache.org>.
viclemon commented on issue #9879:
URL: https://github.com/apache/shardingsphere/issues/9879#issuecomment-816407122


   Unfortunately, the latest version is not stable version
   The two versions are quite different, and upgrade cost is relatively high.
   I'll use my own code


-- 
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.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org