Sharding-JDBC Startup Optimization: Analyzing and Tuning max.connections.size.per.query

During local debugging the author discovered that Sharding-JDBC spent 116 seconds loading metadata, accounting for roughly half of the application startup time.

Log analysis shows the bottleneck occurs in SchemaMetaDataLoader#load , where tables are grouped based on the maxConnectionCount derived from the configuration property max.connections.size.per.query , which defaults to 1.

[org.apache.shardingsphere.core.log.ConfigurationLogger:104] : [0||0] Properties:
max.connections.size.per.query: '1'

[org.apache.shardingsphere.core.metadata.ShardingMetaDataLoader:131] : [0||0] Loading 2 logic tables 'meta data.
[org.apache.shardingsphere.sql.parser.binder.metadata.schema.SchemaMetaDataLoader:70] : [0||0] Loading 401 tables' meta data.
[org.apache.shardingsphere.shardingjdbc.jdbc.core.context.MultipleDataSourcesRuntimeContext:59] : [0||0] Meta data load finished, cost 115930 milliseconds.

The code partitions table names according to the connection count:

List
> tableGroups = Lists.partition(tableNames, Math.max(tableNames.size() / maxConnectionCount, 1));
Map
tableMetaDataMap = 1 == tableGroups.size()
        ? load(dataSource.getConnection(), tableGroups.get(0), databaseType)
        : asyncLoad(dataSource, maxConnectionCount, tableNames, tableGroups, databaseType);

The enum ConfigurationPropertyKey defines the default value:

public enum ConfigurationPropertyKey implements TypedPropertyKey {
    // ...
    /**
     * Max opened connection size for each query.
     */
    MAX_CONNECTIONS_SIZE_PER_QUERY("max.connections.size.per.query", String.valueOf(1), int.class)
    // ...
}

Increasing the property to 8 reduced startup time from 116 s to 15 s, confirming the performance impact. However, the default of 1 is chosen to avoid broader side effects.

The Sharding-JDBC query flow shows that the value influences the execution mode: CONNECTION_STRICTLY (limit connections per data source) or MEMORY_STRICTLY (no limit, creates a connection per shard). The relevant method is:

private List
> getSQLExecuteGroups(...){
    int desiredPartitionSize = Math.max(...);
    List
> sqlUnitPartitions = Lists.partition(sqlUnits, desiredPartitionSize);
    ConnectionMode connectionMode = maxConnectionsSizePerQuery < sqlUnits.size() ? ConnectionMode.CONNECTION_STRICTLY : ConnectionMode.MEMORY_STRICTLY;
    // ...
}

When max.connections.size.per.query > 1, both SELECT and UPDATE statements go through the same preparation logic, which can lead to deadlocks in UPDATE operations if multiple connections are opened for the same data source.

Therefore, the recommendation is to raise the setting in test or pre‑release environments to speed up startup, while keeping it at the default value of 1 in production to ensure stability.

Reference: Sharding‑JDBC official documentation.