sharding-jdbc 5.1.2 startup very slow

[boot-vue]

sharding-jdbc : 5.1.2

      <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.2</version>
        </dependency>

tables : 200+

same config, with version 5.1.0 , application startup very fast

change to version 5.1.2 , application start very slow......

[shanrenxj]

yes, rolling back to 5.1.1 is very fast

[terrymanu]

What is your configuration and database type?

[boot-vue]

What is your configuration and database type?

mysql 8

spring:
  shardingsphere:
    datasource:
      names: node1,node2
      node1:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://xxxxxxx:3307/oooo?characterEncoding=utf8&useSSL=false&autoReconnect=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
        username: root
        password: vvvv
        driver-class-name: com.mysql.cj.jdbc.Driver
        hikari:
          pool-name: ccc1
          maximum-pool-size: 25
          max-lifetime: 900000
          validation-timeout: 2000
          idle-timeout: 60000
          minimum-idle: 15
      node2:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://oooooo:3308/oooo?characterEncoding=utf8&useSSL=false&autoReconnect=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
        username: root
        password: cccccc
        driver-class-name: com.mysql.cj.jdbc.Driver
        hikari:
          pool-name: vvvvv2
          maximum-pool-size: 25
          max-lifetime: 900000
          validation-timeout: 2000
          idle-timeout: 60000
          minimum-idle: 15
    rules:
      readwrite-splitting:
        data-sources:
          oooo:
            type: Static
            props:
              write-data-source-name: node1
              read-data-source-names: node2
              load-balancer-name: random
        load-balancers:
          random:
            type: RANDOM

[linghengqian]

What if you could exclude JOOQ or specify a later version of JOOQ under JDK11+? New perspectives from #18691 .

[boot-vue]

What if you could exclude JOOQ or specify a later version of JOOQ under JDK11+? New perspectives from #18691 .

upgrade jooq version from 3.12.3 to 3.17.1 ✔

or

exclude jooq dependence , start application both perform well ✔

don't know if there are other effects @linghengqian

[linghengqian]

• JOOQ is actually related to Add SQL dialect translator module #17619 and has not been raised to a higher version because the new version of JOOQ requires a JDK baseline version of 11.

• I looked at the Add shardingsphere-sql-translator-jooq-provider module #17655 and noticed that it was using an older version of the 3.14.x branch.

• @boot-vue If we adjusted its jooq.version to 3.14.16 (in line with Upgrade to jOOQ 3.14.16 spring-projects/spring-boot#31429 ), would such a workable strategy in the master branch help this problem? I'm not sure how to define how fast or slow startup speed is.

upgrade jooq version from 3.12.3 to 3.17.1 ✔

• Update: I'm only in https://github.com/apache/shardingsphere/blob/5.1.2/shardingsphere-kernel/shardingsphere-sql-translator/shardingsphere-sql-translator-provider/shardingsphere-sql-translator-jooq-provider/pom.xml , seeing 3.14.15. Where did 3.12.3 come from?

[boot-vue]

yes , version is 3.14.16 , I don't know why it becomes 3.12.3 , didn't find any conflict 😂😂😂

exclude 3.12.3 and add 3.14.16 , it still slow

200+ tables, application start cost about 197.448 seconds

---

previous change to version 3.17.1 , I still use jdk 1.8 and application start cost about 61.109 seconds

[boot-vue]

---

I know , I use spring boot 2.2.1 , it specify jooq version 3.12.3

[linghengqian]

previous change to version 3.17.1 , I still use jdk 1.8 and application start cost about 61.109 seconds

• This is logically problematic because the minimum JDK baseline version for JOOQ 3.17.x is 17, which is in https://blog.jooq.org/3-17-0-release-with-computed-columns-audit-columns-pattern-matching-reactive-transactions-and-kotlin-coroutine-support/ 's blog has been explained.

• ShardingSphere's own SQL dialect translation module I remember was not finished in ShardingSphere 5.1.2, so the default implementation of SQL dialect translation SPI is JOOQ, which is a really tricky problem. 🌝🌝 If there is a self-implement, it should actually be able to replace the default SPI implementation.

• Or, add a switch within the Config configuration file to this module? Similar to sql-translator-enabled. But this module is part of the database gateway, and I'm not sure how to change it.

• update in 2022.06.30: I think it's better to wait until Add SQL dialect translator module #17619 is turned off before discussing this switch.

[newchendb]

I have the same problem. It starts slowly after upgrading from 5.1.1 to 5.1.2

[terrymanu]

It looks like JooQ problem. I'd like to resolve this problem.
I just remove JooQ from default translator.

[terrymanu]

I just update the doc to config SQL translator:

https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/java-api/rules/sql-translator/
https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/yaml-config/rules/sql-translator/

[linghengqian]

@terrymanu After I checked locally, I noticed that spring.shardingsphere.rules of ShardingSphere 5.1.2 does not have a similar module. Is this a specific configuration of the master branch? Also refer to https://shardingsphere.apache.org/document/5.1.2/en/user-manual/shardingsphere-jdbc/spring-boot-starter/rules/ .

update: Update required https://github.com/apache/shardingsphere/blob/master/shardingsphere-jdbc/shardingsphere-jdbc-spring/shardingsphere-jdbc-core-spring/shardingsphere-jdbc-core-spring-boot-starter/src/main/resources/META-INF/additional-spring-configuration-metadata.json ?

[terrymanu]

@zoujianchao Hi, could you have a look for spring boot starter?

[terrymanu]

@terrymanu After I checked locally, I noticed that spring.shardingsphere.rules of ShardingSphere 5.1.2 does not have a similar module. Is this a specific configuration of the master branch? Also refer to https://shardingsphere.apache.org/document/5.1.2/en/user-manual/shardingsphere-jdbc/spring-boot-starter/rules/ .

update: Update required https://github.com/apache/shardingsphere/blob/master/shardingsphere-jdbc/shardingsphere-jdbc-spring/shardingsphere-jdbc-core-spring/shardingsphere-jdbc-core-spring-boot-starter/src/main/resources/META-INF/additional-spring-configuration-metadata.json ?

If new issue, is it better to create a new one?

[linghengqian]

A new SpringBoot Starter related issue is located at #18889 .

[liuwenliang0632]

@boot-vue do u reslove the promblem?

[linghengqian]

At the heart of this issue is actually a documentation issue, and I think this issue can be closed.

[boot-vue]

A new SpringBoot Starter related issue is located at #18889 .

🎃🎃