Skip to main content

Fetch query statistics for Aiven for ClickHouse®

In ClickHouse®, the system.query_log table stores statistics for each executed query, including memory usage and duration. This table is available in Aiven for ClickHouse.

You can fetch query statistics in Aiven for ClickHouse in the following ways:

Use system.query_log​

system.query_log is non-replicated, so each node contains only queries executed on that node. To get statistics from all service nodes, use clusterAllReplicas.

Run the following query in the query editor in the Aiven Console or using a ClickHouse client. It returns the 10 most recently finished queries across all nodes, including their duration, memory usage, and amount of data read:

SELECT
    query_id,
    query,
    query_duration_ms,
    memory_usage,
    read_rows,
    read_bytes
FROM clusterAllReplicas(default, system.query_log)
WHERE type = 'QueryFinish'
ORDER BY event_time DESC
LIMIT 10

Use the Aiven Console dashboard​

  1. Log in to the Aiven Console and choose your Aiven for ClickHouse service.
  2. In the service sidebar, click Observe > Query statistics.
  3. View and analyze query statistics in the dashboard.

Use the Aiven API​

To retrieve query statistics programmatically, use the ServiceClickHouseQueryStats endpoint in the Aiven API.

GET /project/PROJECT/service/SERVICE_NAME/clickhouse/query/stats

Replace the following:

  • PROJECT: the name of your project.
  • SERVICE_NAME: the name of your Aiven for ClickHouse service.

Related pages