# How to use ClickHouse system.query_log for query observability and performance monitoring [Blog](/blog/)>[Technology](/blog/category/technical/)>How to use ClickHouse system.query\_log for query observability and performance monitoring How to use ClickHouse system.query\_log for query observability and performance monitoring ========================================================================================== September 08, 2026 | By [ Vikas Kumar](https://www.instaclustr.com/blog/author/vkask1/) [ ](https://x.com/intent/tweet?text=How%20to%20use%20ClickHouse%20system.query_log%20for%20query%20observability%20and%20performance%20monitoring&url=https://www.instaclustr.com/blog/how-to-use-clickhouse-system-query_log-for-query-observability-and-performance-monitoring/) [ ](https://www.linkedin.com/shareArticle?mini=true&url=https://www.instaclustr.com/blog/how-to-use-clickhouse-system-query_log-for-query-observability-and-performance-monitoring/&title=&summary=How%20to%20use%20ClickHouse%20system.query_log%20for%20query%20observability%20and%20performance%20monitoring&source=) **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. 2. **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. 3. **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. 4. **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](https://www.instaclustr.com/platform/managed-clickhouse/), gives you enterprise-grade reliability, scalability, and support, so that you can grow without the stress. [Learn how](https://www.netapp.com/video/OlqrH2tY6nM/operata-unlocks-over-6-200-engineering-hours-with-managed-open-source/) Instaclustr manages the ClickHouse clusters that power all of Operata’s observability and analytics workloads [ Add Instaclustr as a preferred source on Google ](https://google.com/preferences/source?q=instaclustr.com) ![mail icon]()#### Get the latest articles for open sourceIn your inbox Sign up now ### Related content [ Zero Downtime Migration to Instaclustr Yes, we can migrate existing Cassandra clusters to Instaclustr without any downtime. Here's what to expect from the process... ](https://www.instaclustr.com/blog/zero-downtime-migration-to-instaclustr/) [ Workflow Comparison: Uber Cadence vs Netflix Conductor When choosing what’s right for your company’s opensource workflow needs it is important to know the difference and similarities ... ](https://www.instaclustr.com/blog/workflow-comparison-uber-cadence-vs-netflix-conductor/) [ Will Your Cassandra Database Project Succeed?: The New Stack Open source Apache Cassandra® continues to stand out as an enterprise-proven solution for organizations seeking high availability... ](https://www.instaclustr.com/blog/will-your-cassandra-database-project-succeed-the-new-stack/) ×Sign upto ourNewsletter -----------------------