[GitHub] [incubator-shardingsphere] NatsuSai commented on issue #3740: In query when the number of parameters reaches a certain number, SS exhausts the number of MySQL connections

[GitBox <gi...@apache.org>]

NatsuSai commented on issue #3740: In query when the number of parameters reaches a certain number, SS exhausts the number of MySQL connections
URL: https://github.com/apache/incubator-shardingsphere/issues/3740#issuecomment-565976179
 
 
   > 
   >
   > It seems like you need have the knowledge of database connection pool. ShardingSphere just reuse your configuration of max connection number of database connection pool.
   > So how do you consider about the database connection pool?
   
   Could you tell me how to calculate the maximum number of connections for a sharded database or other suggestion. I encountered a database connection pool full exception last week, but now is fine. I don't know why.
   
   
   
   I was use 50 threads to request an API who is query ss like 'select * from table where id in (xxx,xxx,xxx)', and 500 ids per request.
   
   
   
   There is my config:
   - application.yml
   ```
   spring:
     application:
       name: ss-test
     datasource:
       driver-class-name: com.mysql.jdbc.Driver
       url: jdbc:mysql://localhost:13307/product_data?defaultRowFetchSize=1&characterEncoding=utf8&cachePrepStmts=true&useServerPrepStmts=true&rewriteBatchedStatements=true&useSSL=false
       username: root
       password: root
       type: com.zaxxer.hikari.HikariDataSource
       hikari:
         auto-commit: false
         maximum-pool-size: 50
         minimum-idle: 5
   ```
   - server.yml
   ```
   ...
   props:
     max.connections.size.per.query: 16
     acceptor.size: 12  # The default value is available processors count * 2.
     executor.size: 200  # Infinite by default.
     proxy.transaction.type: LOCAL
     proxy.transaction.enabled: false
     proxy.opentracing.enabled: false
     sql.show: false
   ```
   - config-sharding.yml
   ```
   schemaName: product_data
   
   dataSources:
     product_data_0:
       url: jdbc:mysql://localhost:3306/product_data?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
       username: root
       password: root
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 200
       
   ...
   
     product_data_9:
       url: jdbc:mysql://localhost:3307/product_data?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
       username: root
       password: root
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 200
   
   shardingRule:
     tables:
       brand:
         actualDataNodes: product_data_${0..9}.brand
         databaseStrategy:
           inline:
             shardingColumn: id
             algorithmExpression: product_data_${(id >> 22) % 10}
     defaultDataSourceName: product_data_0
     defaultKeyGenerator:
       type: SNOWFLAKE
       column: id
   ```

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


With regards,
Apache Git Services