Skip to content

Active Queries

The hdx.active_queries system table stores query information for queries executed in the last five minutes. Unlike Hydrolix tables, it's a ClickHouse system table and can't be configured or managed. Rows older than five minutes are automatically removed. It can be queried like any other table in the hdx project.

Use hdx.active_queries to:

  • Identify long-running or resource-intensive queries on the cluster.
  • Track which users or services are generating query load.
  • Tag queries with hdx_query_comment or hdx_query_admin_comment to label scheduled or automated queries.
  • Diagnose query failures using exception_code and exception_string.

This example query lists recent queries with their duration, host, and peak memory usage:

List Recent Queries
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT
  query_start,
  query_end,
  query,
  host_addr,
  active,
  peak_memory_usage,
  (query_end - query_start) AS query_duration
FROM hdx.active_queries
ORDER BY query_start DESC

Column reference⚓︎

Query identification⚓︎

Column Type Description
query_id String Identifier for this specific execution of the query
initial_query_id String Identifier assigned when the query was first submitted
host_addr String Address of the query head that processed the request

Timing⚓︎

Column Type Description
query_start DateTime Timestamp when the query was received
query_end Nullable(DateTime) Timestamp when the response was returned (null while active)

Query details⚓︎

Column Type Description
query String SQL body of the query
user String User who submitted the query
comment String Text provided by hdx_query_comment
admin_comment String Text provided by hdx_query_admin_comment
mode String Execution mode of the query
query_settings String JSON-encoded query settings

Status and performance⚓︎

Column Type Description
active Int8 Whether the query is running (1) or finished (0)
peak_memory_usage Int64 Peak memory consumed by the query, in bytes
memory_usage Int64 Current memory consumed by the query, resets to 0 when finished
num_partitions UInt64 Number of partitions scanned (0 if unavailable)
num_peers UInt64 Number of query peers involved (0 if unavailable)
query_stats String JSON-encoded query statistics ({} if unavailable)

Errors⚓︎

Column Type Description
exception_code Nullable(Int32) Error code if the query failed (null otherwise)
exception_string String Error message if the query failed

Full table schema⚓︎

Full table schema
hdx.active_queries Schema
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE hdx.active_queries (
    `initial_query_id` String,
    `query_id` String,
    `query_start` DateTime,
    `query_end` Nullable(DateTime),
    `query` String,
    `user` String,
    `comment` String,
    `admin_comment` String,
    `peak_memory_usage` Int64,
    `memory_usage` Int64,
    `active` Int8,
    `mode` String,
    `query_settings` String,
    `num_partitions` UInt64,
    `num_peers` UInt64,
    `query_stats` String,
    `exception_code` Nullable(Int32),
    `exception_string` String,
    `host_addr` String
)

Useful Queries⚓︎

Metadata on running queries⚓︎

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT
  initial_query_id,
  query_start,
  (now() - query_start) AS exec_time_secs,
  formatReadableSize(memory_usage) AS mem_usage,
  formatReadableSize(peak_memory_usage) AS peak_mem_usage
FROM hdx.active_queries
WHERE active = 1
AND mode = 'head'
order by query_start

Metadata on long running queries⚓︎

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT
  initial_query_id,
  query_start,
  (now() - query_start) AS exec_time_secs,
  JSONExtractInt(query_settings, 'query_timeout_secs') AS query_timeout_secs,
  formatReadableSize(memory_usage) AS mem_usage,
  formatReadableSize(peak_memory_usage) AS peak_mem_usage
FROM hdx.active_queries
WHERE active = 1
AND mode = 'head'
AND exec_time_secs > query_timeout_secs
ORDER BY exec_time_secs DESC

Inspect a specific query⚓︎

Replace the <initial_query_id> in this query with a UUID discovered from another diagnostic query.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SELECT
  initial_query_id,
  mode,
  query_start,
  query_end,
  num_peers,
  num_partitions,
  formatReadableSize(memory_usage) AS mem_usage,
  formatReadableSize(peak_memory_usage) AS peak_mem_usage,
  JSONExtractInt(query_settings, 'query_timeout_secs') AS query_timeout_secs,
  JSONExtractInt(query_settings, 'query_max_timerange_secs') AS query_max_timerange_secs,
  query
FROM hdx.active_queries
WHERE initial_query_id = '<initial_query_id>'
ORDER BY query_start

Monitor total memory usage at node level⚓︎

1
2
3
4
5
6
7
SELECT
  mode,
  formatReadableSize(sum(memory_usage)) AS total_memory_usage,
  formatReadableSize(max(peak_memory_usage)) AS max_memory_usage
FROM hdx.active_queries
WHERE active = 1
GROUP by mode

Monitor current running query count⚓︎

1
2
3
4
SELECT mode, count(distinct initial_query_id)
FROM hdx.active_queries
WHERE active=true
GROUP BY mode

1
2
3
4
SELECT mode, host_addr, count(query_id)
FROM hdx.active_queries
WHERE active=true
GROUP BY mode, host_addr