Skip to content
chmonitor chmonitor

Query History

Open as Markdown

The query history pages (/history-queries, /failed-queries, /expensive-queries, /slow-queries) read from system.query_log. This table is built into ClickHouse and records every completed query.

Requirements

Section titled “Requirements”

system.query_log must be enabled in your ClickHouse configuration. It is enabled by default in most installations. If the table is empty or missing, history pages show no data.

The configured CLICKHOUSE_USER needs SELECT on system.query_log:

GRANT SELECT ON system.query_log TO monitoring_user;

Retention

Section titled “Retention”

ClickHouse flushes query log entries every 7.5 seconds by default and retains them for 30 days. To change retention, set flush_interval_milliseconds and partition_by / ttl in system.query_log config:

<!-- config.xml or conf.d/query_log.xml -->
<query_log>
    <database>system</database>
    <table>query_log</table>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <ttl>event_date + INTERVAL 14 DAY DELETE</ttl>
</query_log>

Shorter retention reduces disk usage but limits how far back history pages can look.

Filtering out monitoring users

Section titled “Filtering out monitoring users”

By default, history pages show queries from all users including the monitoring user itself. This creates noise. Filter out specific users using CLICKHOUSE_EXCLUDE_USER_DEFAULT:

Terminal window
CLICKHOUSE_EXCLUDE_USER_DEFAULT=monitoring,healthcheck

Set this to a comma-separated list of usernames to exclude from history page defaults. Users can still remove the filter in the UI.

Docker:

Terminal window
docker run -d \
  -e CLICKHOUSE_HOST='http://ch-1:8123' \
  -e CLICKHOUSE_USER='monitoring' \
  -e CLICKHOUSE_PASSWORD='' \
  -e CLICKHOUSE_EXCLUDE_USER_DEFAULT='monitoring,healthcheck' \
  -p 3000:3000 \
  ghcr.io/duyet/chmonitor:vX.Y.Z

Kubernetes / Helm:

env:
  - name: CLICKHOUSE_HOST
    value: "http://ch-1:8123"
  - name: CLICKHOUSE_USER
    value: "monitoring"
  - name: CLICKHOUSE_PASSWORD
    value: ""
  - name: CLICKHOUSE_EXCLUDE_USER_DEFAULT
    value: "monitoring,healthcheck"

What each history page shows

Section titled “What each history page shows”
PageRouteSourceDescription
History/history-queriessystem.query_logAll completed queries, filterable by user, database, and time.
Failed/failed-queriessystem.query_logQueries that ended with an exception.
Expensive/expensive-queriessystem.query_logQueries ranked by memory usage or read bytes.
Slow/slow-queriessystem.query_logQueries ranked by elapsed time.

Notes

Section titled “Notes”
  • History pages respect the global time range selector.
  • If system.query_log is partitioned by day, queries crossing midnight may appear split across rows.
  • The AI agent can analyze query history and surface patterns or anomalies — see AI Agent.
Section titled “Related”