ClickHouse is renowned for its high-performance analytical capabilities, not only excelling at speed but also providing remarkable insights into its operations. A key component of this observability is the system.query_log table – an invaluable resource for administrators and developers aiming to monitor, diagnose, and optimize query performance in real-time.

This blog will cover:

  • What is system.query_log?
  • How the ClickHouse query log works
  • Key use cases for query performance monitoring, troubleshooting, auditing and resource management

What is system.query_log in ClickHouse?

The ClickHouse system.query_log table records details about the executed queries on the ClickHouse server. This table is essential for monitoring, auditing, and diagnosing query performance issues. It’s stored as a MergeTree table and updated asynchronously by ClickHouse. It captures:

  • When the query started and ended
  • Who ran the query
  • What resources it consumed
  • If it succeeded or failed

How the ClickHouse query log works

The mechanics behind system.query_log in ClickHouse involve capturing detailed information about queries during their execution, storing these details in a special system table, and providing configuration options to control logging behaviour and retention. Here’s a detailed look at how it works:

1. Query Lifecycle Events

During the execution of a query, ClickHouse generates log entries at different stages of a query lifecycle. These stages typically include:

  • QueryStart: Logged when the query starts executing
  • QueryFinish: Logged when the query finishes executing
  • ExceptionWhileProcessing: Logged when an exception occurs during query execution.

Each record is written as a new row into the system.query_log table.

2. Asynchronous Logging

To ensure optimal performance, ClickHouse uses an asynchronous background thread to periodically flush query logs from memory to disk. This process is controlled by the flush_interval_milliseconds setting in the server configuration.

  • In-Memory Buffers: ClickHouse accumulates log entries in in-memory buffers before writing them to disk. This approach minimizes the overhead of frequent disk writes, thereby enhancing overall performance.
  • Asynchronous Writing: The buffered log data is written to the system.query_log table on disk asynchronously in the background. This means that the execution of queries is not delayed by the logging process, as the log data persisted without blocking query execution.

3. Engine and Partitioning

In ClickHouse, query logs are stored in a table that uses the powerful MergeTree engine. While the schema of this table is defined internally, you can easily view it by running DESCRIBE TABLE system.query_log.

You have the flexibility to configure the table to suit your needs:

  • Partitioning: Use the partition_by setting to organize logs by month, for example, to YYYYMM(event_date).
  • Retention: Set up automatic log cleanup with the ttl setting. For instance, event_date + INTERVAL 14 DAY ensures logs older than 14 days are purged.

These configurations help maintain the performance of the query log table even under heavy load and ensure that old logs are automatically managed and removed.

Key use cases for ClickHouse system.query_log table

The system.query_log table supports various use cases across operations, development, and security. Here are some key examples:

  1. Query performance Analysis and optimization:
    • Identifying Slow Queries: The query_duration_ms column is used to pinpoint slow‑running client queries, not to diagnose overall cluster or service performance issues. It focuses specifically on how long individual queries take to execute on the server, helping identify query‑level inefficiencies and optimization opportunities such as indexing, query rewrites, or parameter tuning.
    • Resource utilization analysis: Monitor metrics like read_rows, read_bytes, and memory_usage to determine which queries are the most resource intensive. This is valuable for capacity planning and identifying patterns that lead to inefficient resource usage.
    • Tracking query performance over time: Leverage historical query_log data to compare query performance across different time intervals. This helps in detecting performance regressions or validating improvements after schema changes, query tuning, or version upgrades.
    • Query profiling using ProfileEvents: Use the ProfileEvents column to drill into the internal behaviour of a specific query. It provides a fine-grained breakdown of resource usage (e.g., CPU cycles, I/O operations, function calls), enabling deep performance profiling at the micro level.
  1. Troubleshooting and debugging ClickHouse queries
    • Identifying error sources: Quickly locate queries that failed by filtering on the type column for values like ExceptionBeforeStart or ExceptionWhileProcessing. The exception_code and exception columns then provide detailed insight into the cause of the failure like memory limits, syntax errors etc.
    • Tracing distributed query execution: Use the initial_query_id field to trace the full execution path of distributed queries across multiple nodes. This is especially useful when debugging inconsistencies or failures in a cluster environment.
    • Provide the full query: The query column stores the full SQL text that was executed. This makes it easy to reproduce problematic queries in a test environment for debugging and analysis.
    • Investigating unexpected behaviour: If your application behaves abnormally, the query_log provides critical context about what queries were running at the time. This can help correlate symptoms with underlying database activity.
  1. Security and audit:
    • Tracking user activity: Use the user column to monitor which users are executing specific queries. This helps in auditing individual actions and attributing responsibility for data access or modifications.
    • Detect potential security incidents: Analyze the query patterns to identify suspicious or unauthorized activity—such as unexpected DROP, ALTER, or data exfiltration attempts. Anomalous behaviour from users can indicate compromised credentials or misconfigurations.
    • Identifying client origins: Leverage the address, client_name, and client_version columns to trace where queries are coming from. This is useful for whitelisting known clients, detecting unknown sources, or correlating activity with application components.
  1. Capacity planning and resource management:
    • Estimating future resource requirements: Analyze historical query patterns, including metrics like read_rows, memory_usage, and query_duration_ms, to forecast future compute and storage needs. This helps in proactive scaling and infrastructure planning.
    • Identify peak load periods: Examine the event_time and resource metrics to pinpoint periods of high activity or stress on the system. Understanding peak usage trends supports better workload distribution and scheduling.
    • Optimizing resource allocation: Use insights from system.query_log to fine-tune server configurations, such as memory limits, thread settings, and disk I/O policies. This ensures efficient utilization of hardware resources and avoids performance bottlenecks.

Final thoughts

The system.query_log table isn’t just a basic log table; it’s a powerful, built-in observability layer for ClickHouse. It offers deep, granular insights into what is happening in your database: from individual query performance and user actions to resource consumption and system-level errors. Best of all, it provides all this data with the native speed and flexibility you expect from ClickHouse itself.

Whether you need to find slow queries, troubleshoot failed queries, monitor resource usage, or strengthen your Clickhouse observability strategy integrating system.query_log into your monitoring and optimization workflow is a single, impactful step that can significantly enhance operational efficiency and database health.

Ready to stop firefighting and start focusing on insights? Instaclustr for ClickHouse, gives you enterprise-grade reliability, scalability, and support, so that you can grow without the stress. Learn how Instaclustr manages the ClickHouse clusters that power all of Operata’s observability and analytics workloads