How to reduce the rate of 'ping' sql

[chenglinli0]

Describe the bug

we have some spring-boot project that use clichouse-jdbc-driver query data.

but in system.query table we find a lot of query like this.

SELECT 1 FORMAT TabSeparatedWithNamesAndTypes SELECT version(), timezone() FORMAT TSV select currentUser() user, timezone() timezone, version() version, toInt8(ifnull((select value from system.settings where name = 'readonly'), '0')) as readonly, toInt8(ifnull((select value from system.settings where name = 'throw_on_unsupported_query_inside_transaction'), '-1')) as non_transational_query, lower(ifnull((select value from system.settings where name = 'wait_changes_become_visible_after_commit_mode'), '')) as commit_wait_mode, toInt8(ifnull((select value from system.settings where name = 'implicit_transaction'), '-1')) as implicit_transaction FORMAT RowBinaryWithNamesAndTypes

I thinks they are created by driver itself.

my question is how to reduce the amount of these kind of query?

As we know clickhouse is not good at concurrent query.

Steps to reproduce

1.create jdbc connection with http in spring boot.

Expected behaviour

Environment

• Client version: 0.3.2 - 0.6.0
• Language version: JAVA
• OS: LINUX

[xiaolei565]

we also encountered the same problem.

[mbaksheev]

@chenglinli0 , @xiaolei565 Hi can you please provide more details about your connection properties? It looks like queries like SELECT 1 are spawned by health check mechanism What is your jdbc connection string? Do you use node auto discovery or health checking? If yes you can increase intervals for them. E.g.

node_discovery_interval=60000
health_check_interval=60000

For me these settings helped to reduce number of SELECT 1 queries.

Also changing health_check_method property to PING. (SELECT_ONE method is used by default) should help.

Regarding queries like select currentUser() user, ... I'm not sure, but it looks like this query executed for each any other query executed throgh JDBC driver, since it is executed during connect code. Not sure there is an easy way to reduce it.

[lichenglin]

@chenglinli0 , @xiaolei565 Hi can you please provide more details about your connection properties? It looks like queries like SELECT 1 are spawned by health check mechanism What is your jdbc connection string? Do you use node auto discovery or health checking? If yes you can increase intervals for them. E.g.

node_discovery_interval=60000
health_check_interval=60000

For me these settings helped to reduce number of SELECT 1 queries.

Also changing health_check_method property to PING. (SELECT_ONE method is used by default) should help.

Regarding queries like select currentUser() user, ... I'm not sure, but it looks like this query executed for each any other query executed throgh JDBC driver, since it is executed during connect code. Not sure there is an easy way to reduce it.

Noop, we are using a AWS ELB as a gateway .

Is there any other setting for non-discovery mode?