JDBC: max_open_connections doesn't limit the connections count

[f1llon]

Describe the bug

It seems that max_open_connections config option doesn't work as expected - it doesn't limit the number of opened connections.

As far from I see from the driver codebase, a new instance of the ApacheHttpConnectionImpl is created for each connection and the config option is used inside. Thus it has no impact on the real number of opened connections.

Steps to reproduce

1. create ClickHouseDataSource with MAX_OPEN_CONNECTIONS property
2. execute multiple (more than MAX_OPEN_CONNECTIONS value) select statements in parallel
3. all the requests are executed in parallel, MAX_OPEN_CONNECTIONS has no impact on it

Expected behavior

the number of parallel requests is limited by MAX_OPEN_CONNECTIONS, other requests are waiting in a queue

Code example

see full example here

Error log

see log here

take a look at messages like - "[total available: 0; route allocated: 1 of 3; total allocated: 1 of 3]" it's always 1 of N

Dependencies

see project dependencies here

[chernser]

Good day, @f1llon ! This option sets apache client pool configuration:

• setDefaultMaxPerRoute
• setMaxTotal See https://hc.apache.org/httpcomponents-client-5.3.x/current/httpclient5/apidocs/org/apache/hc/client5/http/impl/io/PoolingHttpClientConnectionManager.html

I agree that documentation doesn't explain it.

[f1llon]

Hey, @chernser. Thank you for your attention to this issue!

As I mentioned, a new instance of the ApacheHttpConnectionImpl is created for each connection. A new PoolingHttpClientConnectionManager is created under the hood. The MAX_OPEN_CONNECTIONS property is used to setMaxTotal and setDefaultMaxPerRoute, see sources.

Thus, we have the same number of PoolingHttpClientConnectionManagers as many connections we have.

It seems more logical to me to set up a singleton PoolingHttpClientConnectionManager and reuse it for each connection. Or maybe I misunderstood something.

[chernser]

@f1llon you are right. I just wanted to mention that initial purpose of the setting MAX_OPEN_CONNECTIONS is to pass configuration to the Apache HC. Current behavior of the CH Client is not correct and we will fix it.

[quartex]

Hi @chernser, I wanto to report another problem on Pool when settings the pool more than 10 max connections (im my test become more evident from 16 or more ) and create more concurrent requests to stress the pool (double of max connections), an errors like this become frequent :

[HikariPool-917 connection adder] ERROR com.clickhouse.client.http.ApacheHttpConnectionImpl -- HTTP request failed java.net.SocketException: No buffer space available (maximum connections reached?): connect

and using netstat I see many fewer active connections than requested. I'm using java 21.0.4 , Ch jdbc driver 0.6.4 Thanks a lot

[chernser]

Hi, @quartex! what socket settings do you have? what is max connections? what is hikari pool settings? do you use hikari pool because use of JDBC?

Thanks!

[emili4ciccone]

Hi @chernser, I wrote and performed the tests @quartex has mentioned. To answer you, I tried my benchmark stress tests using Hikari CP because we use Clickhouse in a microservice which already uses Hikari CP to manage its own connection pool, and we wanted to see the difference in performances between the default connection pool configuration (which unfortunately leads to the bug described in this issue) and a configuration that uses the already existent Hikari CP.

So I tried to execute parallel readings from a CH table and I had more issues when the size of my connection pool exceeded the max default value of 10 connections; below that value, even when the number of parallel readings was greater or equal to the Hikari maximumPoolSize property, my test was executed correctly. These are the results of my stress test with 8 parallel readings: While with 16 or 12 as my connection pool size and executing 12 parallel readings I noticed the error message @quartex reported.

I set the connection pool size with statement:

HikariConfig config = new HikariConfig();
[..]
config.setMaximumPoolSize(Integer.parseInt(properties.getProperty("max_open_connections")));

where max_open_connections is the connectionPoolSize number you can see in the image. And even when I tried to execute 8 parallel readings with a pool of 5 connections, all 30 my tests where executed and the results registered by the benchmark.

The only Hikari properties I have are: being maximumPoolSize the property I try to override.

And I don't have any explicit custom configuration for sockets, so I presume I am just using the default one for Spring Cloud 2023.0.2 + Jetty.

Thank you in advance!

[emili4ciccone]

Hi @chernser ! Are there any news about the fixing of this bug? :)