We are trying to connect to a ClickHouse cluster (containing 4 nodes and 3 ClickHouse-keepers) using the JDBC connector. When I test the performance with 100, 200, or 300 threads (users), after some time (~5 minutes), I encounter connection read timeout errors (in production environment) or "Address already in use" errors (in local environment) although nothing is in use.
Currently, I have three tables:
- main_table
- replication_table (2 shards, 2 replicas per shard)
- buffer_table (to mitigate "too many parts" errors)
Data is first inserted into the buffer_table, which then flushes out to the replicated_table, from where the data is distributed to all the replicas (4 nodes, depending on the shard key).
Here is the configuration used while connecting:
@Value("${clickhouse.datasource.url}")
private String url;
@Value("${clickhouse.datasource.username}")
private String username;
@Value("${clickhouse.datasource.password}")
private String password;
@Value("${clickhouse.datasource.driver-class-name}")
private String driverClass;
@Value("${clickhouse.datasource.connection-timeout}")
private String connectionTimeout;
@Value("${clickhouse.datasource.socket-timeout}")
private String socketTimeout;
@Value("${clickhouse.datasource.ssl}")
private String ssl;
@Value("${clickhouse.datasource.load-balancing-policy}")
private String loadBalancingPolicy;
@Bean
public DataSource dataSource() throws SQLException {
Properties properties = new Properties();
properties.setProperty("user", username.trim());
properties.setProperty("password", password.trim());
properties.setProperty("connection_timeout", connectionTimeout.trim());
properties.setProperty("socket_timeout", socketTimeout.trim());
properties.setProperty("ssl", ssl.trim());
properties.setProperty("load_balancing_policy", loadBalancingPolicy.trim());
ClickHouseDataSource dataSource = new ClickHouseDataSource(url.trim(), properties);
return dataSource;
}
Configuration properties:
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.minimum-idle=200
spring.datasource.hikari.maximum-pool-size=200
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.auto-commit=true
clickhouse.datasource.url=jdbc:clickhouse://server1:8123,server2:8123,server3:8123,server4:8123/dap_dev
clickhouse.datasource.driver-class-name=com.clickhouse.jdbc.ClickHouseDriver
clickhouse.datasource.connection-timeout=30000
clickhouse.datasource.socket-timeout=300000
clickhouse.datasource.ssl=false
clickhouse.datasource.load-balancing-policy=roundRobin
Please let me know if there is any property or something else I am doing wrong that is causing ClickHouse to throw these timeout errors.
