Thread blocked

[zhaoyvli]

When I execute an SQL query that takes a long time, I get thread blocked,
and creating a new query will fail

[zhicwu]

Hi @MrZhao1024B, it's a warning from Vert.x but it should not block your next query. Are you using latest JDBC bridge?

[zhaoyvli]

Hi@zhicwu, I'm using the latest JDBC bridge. Only execute a query that takes a long time to return results, Creating a new query will fail, and I need to wait for the task to finish before executing a new query.
My guess is that the task has not received any data from the database, causing the new task to wait.

[zhicwu]

There was an issue(#113) like this but it's been fixed. Just want to double confirm with you: 1) by latest version you meant 2.1.0, right? and 2) if connection pool is used, its maximum size is greater than one.

Will try if I can reproduce the issue on 2.1.0.

[zhaoyvli]

My JDBC bridge version is 2.1.0, clickhouse version is 21.12.3.32.

"maximumPoolSize": 5

[zhicwu]

Thanks for the extra information. What's your query looks like? How many jdbc table functions appear in your query? 3 connections will be used for one query like below:

select * from jdbc('ds1', 'query1')
union all
select * from jdbc('ds1', 'query2')
union all
select * from jdbc('ds1', 'query3')

[zhaoyvli]

My query has only one JDBC table function.
select * from jdbc ('ds1', 'query1')

Tests have found that.

1. First, a query is executed:
   select * from jdbc('ds1', 'select * from table_01')
   Second, execute the query:
   select * from jdbc('ds1', 'select * from table_01')
   If table_01 has 100 million entries, I can query two SQL processes in the Clickhouse and table_01 databases

2. First, a query is executed:
   select cnt from jdbc('ds1', 'select count(1) as cnt from table_01')
   Second, execute the query:
   select * from jdbc('ds1', 'select * from table_01')
   If table_01 has 100 million entries. I can query two SQL processes in Clickhouse, but I can only query the first SQL process in table_01.

[zhicwu]

I was not able to reproduce the issue. What's the target database? It looks like the first query leads to a table lock?

[zhaoyvli]

My target database is Oracle. But my first query using oracle, and my second query using sqlserver will have the same problem. So the reason is not because the table is locked.

[zhicwu]

I see what's going on there. By design, JDBC bridge will perform scheme inferring for each query. This is to help Clickhouse to understand the table schema. Usually it simply issue the original query with fetch size and max rows set to 1. This works well for the first case but not the second(not sure why count query is so slow in your environment). To avoid the extra query, you have to either use named query(query in json config file), or issue a query like select * from jdbc('ds1', 'a String, b Int32, ...', 'your query').

[zhaoyvli]

Thank you for your advice. When I execute query select cnt from jdbc('ds1','a String, b Int32', 'select count(1) as cnt from table_01'), creating a new query is successful.

My count query was so slow. A new query should not be blocked because of this.

[lichenglin]

It seems that the schema query is a serial method?

when there is a long query such as select * from jdbc('source','select count(1) from huge_table')

all the query is blocked.

but when running select * from jdbc('source','c Int64','select count(1) as c from huge_table')

It work well .

[lichenglin]

I think this is maybe the root cause.

In NamedDataSource

I found this
columnsCache.get(query, k -> { return inferTypes(schema, query, this.loadSavedQueryAsNeeded(k, params), params); })

When running a long sql. get may block the processs.

It's metioned in caffeine docs , see get javadoc

If the specified key is not already associated with a value, attempts to compute its value using the given mapping function and enters it into this cache unless null. The entire method invocation is performed atomically, so the function is applied at most once per key. **Some attempted update operations on this cache by other threads may be blocked while the computation is in progress, so the computation should be short and simple, and must not attempt to update any other mappings of this cache.**

[peder1001]

Any progress on making get stop blocking the process?

[lichenglin]

I modify JdbcBridgeVerticle
from

router.post("/columns_info").produces(RESPONSE_CONTENT_TYPE).handler(queryTimeoutHandler) .blockingHandler(this::handleColumnsInfo,SERIAL_MODE);

to

router.post("/columns_info").produces(RESPONSE_CONTENT_TYPE).handler(queryTimeoutHandler) .handler(this::handleColumnsInfo);