How to get current queries to max_concurrent_queries

Question

I encountered this error：

clickhouse_driver.errors.ServerException: Code: 202. DB::Exception: Too many simultaneous queries. Maximum: 200. Stack trace:

but I have adjusted the max_concurrent_queries from 100 to 200.

I need to know the current number of queries. If it is about to exceed the max_concurrent_queries, the program will no longer search but wait. But I have looked at the indicators in the system.metrics, system.events, and system.asynchronous_metrics tables and no suitable monitoring indicators were found.

Please help me, I want to achieve the maximum number of searches within the current max_concurrent_queries allowed range of clickhouse.

Answer 1

To get the number of queries currently running, run this:

select count() from system.processes

If you want to track the Number of executing queries metrics, you can use the ClickHouseMetrics_Query gauge, or you can directly select the value of the metric from metrics table:

select value from system.metrics where metric = 'Query'

Answer 2

Some queries may trigger multiple sub-queries. Once a query triggers multiple sub-queries, it is hard to track them over system.processes table, since they will start to fail once the limit is reached.

I would recommend to use system.query_log table to check those queries. It may help to understand why queries are spiking.

https://clickhouse.com/docs/en/operations/system-tables/query_log

with time_range as (
  select now() - interval number second time_to_check
  from numbers(3600)
),
query_log as (
  select hostName() host_name, query_start_time, event_time, type, query, query_kind
  from clusterAllReplicas('{cluster}', system.query_log)
  where event_date=today()
  and type != 'QueryStart'
)
select
  host_name,
  time_to_check, 
  count()
from query_log
cross join time_range
where time_range.time_to_check >= query_log.query_start_time
  and time_range.time_to_check <  query_log.event_time
group by time_to_check
order by time_to_check;