Finding slow and failed ClickHouse queries in system.query_log
system.query_log is the server's own record of query execution, and teams need it when queries are slow or failing and they must find out which ones, how often and why before changing anything. It is one of the primary tools for query performance analysis in self-managed ClickHouse, because each row captures what a query read, how long it ran, how much memory it used and whether it finished or raised an error. The table only helps if three things are in place: query logging is enabled on the servers, the log is retained long enough to cover the incident window, and the analysis runs against every replica rather than the one node an engineer happens to connect to. A query that ran on replica two leaves no trace on replica one.
Start by confirming the logging configuration on each node, including the logging settings for query_log and the flushing interval, since a short-lived incident can be missing from the table if logging was off or the log was truncated. Ask your platform team to write down the retention policy for this table and the date range it currently covers. Those two facts decide whether your analysis can answer questions about last Tuesday's spike or only about the last few hours. Because the table is local to each server, a cluster-wide question needs a plan for reading every replica and tying the results back to the same time window and host.
Logging options and when each fits
This table compares the four controls that decide how much of the query history you keep: enabling query logging, sampling with log_queries_probability, TTL retention on the log table, and full versus sampled capture. Read each row against your cluster's query volume and how far back your investigations reach. The main takeaway is that sampling lowers storage but can hide the rare failing query you are looking for.
| Option | When it fits | Trade-off in storage and visibility | What to confirm |
|---|---|---|---|
| Full query logging | Small clusters or low query volume, where every failed and slow query should be visible | Highest storage and write load on the log table; every query leaves a row, so retention must be planned alongside it | Confirm the query_log settings and flush behavior for your server version in the official docs |
| Sampled logging with log_queries_probability | High-volume clusters where full logging adds noticeable load or storage cost | Lower volume, but a sampled log can miss the one expensive or failing query under investigation; trends remain usable, single events may not | Confirm the setting's accepted range and scope (session, profile or server) in your version's documentation |
| Retention through TTL on the log table | Teams that need a defined window for investigations and want old rows removed automatically | Shorter windows cut storage but limit how far back incidents can be analyzed; longer windows raise disk use on every replica | Ask your security and compliance owners how long query text may be kept, since queries can contain sensitive values |
| Longer retention for long investigations | Incident reviews, capacity reviews or audits that need months of query history | Requires disk planned for the extra rows and possibly a copy of the log outside the cluster; query text may hold personal or regulated data | Decide the retention period with the data owners before enabling it, and document where the copies live |
| Keeping logs on the same cluster | Teams that want to query history with the same tools as production data | Log writes and investigative queries share resources with workloads, so heavy log analysis can affect production | Confirm resource limits or a separate environment for analysis queries |
| Exporting to a separate analysis cluster | Organizations that need heavy log analysis without affecting production | Adds a second system to secure, back up and monitor, and requires a copy process for the log data | Confirm the copy method, its lag and who operates the second cluster |
| Logging only in a pre-production environment | Teams validating a query pattern or upgrade before production | Shows behavior on representative data but not production concurrency or data volume | Confirm the pre-production dataset is representative before relying on its results |
Setting up query_log analysis step by step
This sequence turns system.query_log from a default setting into a tested monitoring source, with confirmed logging on every node, bounded retention, and a baseline set of queries your team can run against live traffic.
Rehearse the setup in pre-production
Before touching production, the platform owner builds a pre-production cluster with data volumes and query shapes close to production, as the ClickHouse production FAQ advises. Collect the statements your analysts and applications actually run so the later checks reflect real workload. The step is done when the team has a written list of representative queries and a pre-production cluster that accepts them. Confirm the ClickHouse version you plan to run against the stable release guidance in the production FAQ, and read the changelog for the version you choose, because minor backward-incompatible changes can occur.
Confirm query logging on every node
The DBA or platform engineer checks the server configuration on each node, not just one, and confirms that query logging is enabled and that the query_log table is defined in the server configuration. Nodes often differ because configuration was applied by hand or through a different deployment path. The check is a query such as selecting from system.query_log on each host, run individually rather than through a distributed table, returning a row count that is above zero after a few test statements. Record the result per node so any gap is visible before analysis begins.
Set the sampling probability
The platform owner decides whether every query needs to be logged or whether a sample is enough. Full logging gives complete history but adds write volume to the log table on busy clusters. Where volume matters, set the logging sampling through the log_queries_probability setting for the profile that applies, and document the value and the profile it belongs to. The check is to run a fixed number of test statements and confirm the logged count matches the expected proportion. Note that sampling reduces what you can see, so failed and slow statements may be missed on a low setting, and the team should agree that trade-off in writing.
Set TTL retention on the log tables
Without a retention rule, the log tables grow until someone notices. The DBA defines a TTL on the query log tables so old rows expire on a schedule your compliance and capacity requirements allow, and confirms the retention period with whoever owns audit obligations. The check is to read the table definition and confirm the TTL expression is present on each node, then verify that the expiry rule is applied on a test copy by inspecting the oldest timestamp in the table after the expected interval. Keep a note of which logs are needed longer for investigations, because TTL removal is not reversible without a backup.
Run a controlled test workload
The engineer runs the representative workload from the first step, including a few deliberately slow statements, one statement that fails, and one that consumes significant memory. This gives each category a known row to find later. The check is that each test statement appears in system.query_log with the expected status, and that the exception text is captured for the failed statement. Run the same workload through each replica or shard that analysts will query, since a query may be routed to any node. A missing test row on one node means logging or routing needs investigation before the baseline is written.
Confirm recent rows on each replica
On a replicated cluster, the engineer queries the query log on each replica to confirm that rows from the test workload appear locally, not only on the node that handled the connection. This check matters because each node records only the queries it executes. The result should show the same test statements on every node that ran them, with timestamps inside the test window. If a replica shows no rows, check its logging configuration and whether the replica is healthy before continuing. Document the replicas checked and the time of the check, so the later cluster-wide queries have a known baseline.
Write the baseline queries
The engineer writes a short, versioned set of queries for three categories: slow statements ordered by duration, failed statements grouped by exception code, and high-memory statements ordered by peak memory usage. Each query filters on a time range and on the test user or application where needed. Store them in a shared repository with a short description of what each one answers. The check is that each query returns the known test rows from the controlled workload with the expected values, and that the results are readable by the people who will run them during incidents.
Validate the baseline before handover
The platform owner runs the baseline queries against the pre-production cluster and then compares the results with the expected test rows. They confirm that retention and sampling behave as documented, and that permissions allow the analysts who need access to read the log tables without exposing more than they should. The step closes when the team signs off on the settings and the baseline, and the same configuration is scheduled for production through the normal change process. Confirm access rules with your security owner, since query text can contain sensitive values.
Queries that find slow, failed and expensive work
Each pattern is a different query against system.query_log, and each answers a different question about the workload. Run them on a schedule against the same time window so results are comparable across days. A query that is slow, failing, heavy on memory or new since the last release usually points to a different owner and a different fix.
Slowest queries by duration
Group rows by a normalized form of the query text, then sort by total and average query_duration_ms over the window. Total time shows which pattern costs the most cluster time, while average time shows which individual queries a user feels. Check whether slow runs cluster at certain hours, which often points to merges or ingestion competing for the same resources. The usual next step is a schema or ordering-key review, or a dashboard that should read a pre-aggregated table.
Failed queries grouped by exception
Filter on the exception_code and exception columns for rows where the query did not finish, then group by exception code. A cluster of one code usually reveals a single root cause, such as memory limits, missing columns after a schema change, or permission errors after an access change. Compare the count of failures against the count of successful runs of the same pattern to see whether failures are rare or systemic. Each group should be routed to an owner, since many of these failures are caused by the application, not the server.
Queries reading the most rows or bytes
Sort by read_rows and read_bytes to find queries that scan far more data than they return. The gap between data read and result_rows is the signal: a large gap with a selective filter often means the primary key or partition filter is not being used. Confirm that the table's ordering key matches the filter columns before changing the query. The fix is usually a better ordering key, a projection or a materialized view rather than a larger server.
Heavy memory consumers
Sort by memory_usage and peak_memory_usage to find queries that come closest to the server's limits. Peak values matter more than averages, because a single large aggregation or join can cause memory errors for unrelated users on the same node. Look for GROUP BY and JOIN queries over high-cardinality columns, and check whether they run during merge or ingestion peaks. Pair the findings with the memory troubleshooting topic before setting per-user limits.
Queries by user or application
Group by user, and by the http_user_agent or client_name fields where your clients set them, to see which service or person generates each part of the load. This view answers ownership questions: which team's dashboards, which pipeline and which ad hoc analysts account for the most CPU time or failures. Use it to decide where quotas, settings profiles or row policies should apply. If many queries arrive under the default user, that is a finding in itself, because the default user starts with broad permissions.
Queries that changed after a deployment
Compare the normalized query fingerprints and their durations before and after a release, a schema change or a configuration change, using the time of the change as the split point. New fingerprints, or known ones with a clear rise in read volume, indicate a regression that the change introduced. Keep a record of change times, because system.query_log alone does not say why a query changed. Confirm the comparison on a pre-production system with representative data before drawing conclusions about production.
Querying system.query_log across replicas
system.query_log only holds the queries that ran on the server you connected to. Each replica writes its own log, so a query against one node misses everything that ran on the others. On a replicated cluster, that means slow or failed queries can be invisible unless you read the log from every node. Plan the cluster-wide view before you build dashboards or alerts on top of it, or you will chase problems that are actually happening on a node you are not looking at.
The usual pattern is to read the local log on all nodes in one query. Depending on your version and topology, this is done with a cluster-aware table function such as clusterAllReplicas or with a Distributed table that points at the local system.query_log on each shard and replica. Both return a hostName() or equivalent host column, which you should keep in your output so you can tell which node ran each query. Also check whether a shard-level query returns each row once or once per replica, because a replicated shard can make the same logical activity appear more than once if you do not filter by host or by the replica you intend to count.
Before you rely on any cluster query, confirm the exact syntax for your ClickHouse version in the official documentation, and confirm that the cluster name in your configuration includes every replica you expect. Run a known test query on each node, then check that its rows appear exactly as expected in the combined result. Record which hosts were missing, if any, since a node that is down or not listed in the cluster definition will silently drop out of your analysis.
Protecting query logs and the data they expose
Query logs record what was asked of the database, and the query text often carries the sensitive values. The cards below cover who can read the log, how literals are kept out of it, how long it lives and how it stays bounded, so the log helps operations without becoming a second copy of regulated data.
Restrict who reads the log
Treat system.query_log as sensitive data, because it holds the text of every query, including literal values in WHERE clauses. Grant read access only to a named operations or security role through ClickHouse access control, not to general application users. Use row policies if an on-call group should see only some users or databases. Confirm the grants with a test user that has only the intended role.
Restrict the default user
The default user starts with all permissions, so a cluster that leaves it enabled gives any client that can reach the server full read access to the log. Disable or restrict the default user, create named administrative accounts through SQL, and confirm that application accounts cannot query system tables they do not need. Record the change in your hardening baseline so it survives rebuilds.
Decide how literals are handled
Query text can contain customer identifiers, card fragments, email addresses or health codes typed directly into a filter. Decide before go-live whether the team accepts those values in the log, asks developers to pass sensitive values as bound parameters, or runs a masking step on log output before it leaves the cluster. Ask your security team which fields count as regulated, because the answer drives the design.
Set retention to a compliance decision
Retention for query logs is a policy question, not a tuning setting. Work with compliance and legal to find how long the organization must keep query activity for audit, and how long it may keep query text that contains personal data. Apply a TTL to the log table that matches that answer, and document the reason so an auditor can trace the setting back to the policy.
Bound the log table size
Query logging writes a row for each logged query, so a busy cluster can grow the table faster than expected. Set a TTL, sample with log_queries_probability if full capture is not needed, and monitor the table's size on each replica. An unbounded log also widens the backup scope, so confirm that the backup plan covers it on purpose, not by accident.
Protect the log during backups and copies
Anyone who copies system.query_log output into tickets, chat or dashboards extends its exposure beyond the access controls on the cluster. Define where exports may go, who approves them and how they are deleted. Include the log in the access review cycle, so role membership is checked on the same schedule as other sensitive tables.
Questions teams ask before analyzing query_log
How much effort does query log analysis take to set up?
Setup is usually modest for one node and grows with the cluster. The main drivers are the number of shards and replicas, because each node writes its own system.query_log and the team must decide how to combine them, and whether the team needs dashboards, alerts or a shared query library, which adds design and review work. Retention settings, log sampling and access rights on the logs also need decisions before the first query runs. Ask any partner to break the effort into node access, configuration, query design, dashboards and handover, and to state which items are one-time and which recur, such as reviewing slow-query reports after each upgrade.
Can our team analyze query_log without outside help?
Yes, many teams can, provided they have someone comfortable with SQL, ClickHouse system tables and the basics of MergeTree tables. The work needs an operator who understands how queries are logged, which fields record duration, memory, read volume and exception text, and how to filter by query kind and user. Teams without that experience often benefit from a short review of their first queries and their retention setup. DeployOpen can plan and implement this work as a fixed-scope engagement, or support it through a retainer, and the written scope will name the skills each part needs.
How long does a query log analysis project take?
Duration depends on scope rather than on the logging feature itself. A single-node review that confirms logging, sets retention and produces a handful of saved queries is much smaller than a cluster-wide design with dashboards, alert thresholds and integration with a monitoring stack. Schedule is also affected by change windows, access approvals and how many workloads need representative testing. Ask for milestones in the written scope, including when the team must provide access and when sign-off happens. Any timeline should be tied to a named deliverable rather than quoted as a generic figure.
What do we need to provide for query log work?
Most of the work depends on access and representative workloads. Typically the client provides:
- Access to the nodes or a test cluster, with the privileges to read system tables and change server configuration
- Named owners for configuration changes and a change window for any restart
- Examples of the workloads that matter, such as dashboards, ingestion jobs and ad hoc analyst queries
- Agreement on who may read query text, since logged queries can contain sensitive literals
A partner can help define these items, but the client must confirm which environments and accounts are in scope before work begins.
How does log sampling affect the reliability of findings?
Sampling lowers the volume of logged queries but can hide rare failures and short slow queries. The log_queries_probability setting controls how often a query is written to the log, so a value below 1 means some queries are never recorded. For trend analysis, such as the top query patterns by total read volume, sampling is usually acceptable. For investigating a specific incident or a rare error, sampling can miss the query you need. Confirm the setting's behavior in your ClickHouse version, and consider full logging for short windows during investigations. Record the sampling rate alongside any report so that totals are read correctly.
How should we choose retention for query_log?
Choose retention from the questions you need to answer. Recent history supports incident review and regression checks after an upgrade, while longer history supports capacity planning and trend reports. The log table is itself a MergeTree table, so a TTL clause controls how long rows remain. Longer retention increases disk use and the cost of queries against the log, so retention should be decided with the storage and tiering design. Confirm the retention policy with your compliance owner if query text may contain personal data, because the log then becomes a data store in its own right.
How do we analyze a slow query that runs across all replicas?
A cluster-wide query is usually a distributed query, so it should be analyzed in both places it ran. Start with the initiating node's log, then check the logs on the shards and replicas that did the work, because each node records only its own part of the execution. Matching by initial_query_id helps link these records. Look at read rows and bytes, memory use and whether one replica is consistently slower, which can point to a lagging replica or uneven data placement. If the answer is not clear from the logs, a trace or a targeted test run can confirm the cause before any tuning begins.
What should we ask for in a query log quote?
Ask for a written scope that lists the environments, nodes and replicas covered, the logging and retention settings to be changed, the saved queries or dashboards to be delivered, and the testing approach. Ask which items are one-time and which recur, such as retainer hours for reviewing reports after upgrades. Request the assumptions behind the effort, including the number of nodes, the access model and the workloads used for validation. Also ask who signs off the deliverables and what happens if access is delayed. DeployOpen does not publish prices, and a quote follows from that written scope.
How DeployOpen scopes query log analysis work
DeployOpen fixes the scope of a query log engagement in writing before any work starts. The first step is a review of the current logging configuration on each node: whether log_queries is enabled, which query_log settings are in force, how the system.query_log table is partitioned and how long rows are kept under its TTL. The review also checks whether the same settings are applied on every replica, because a replica with different logging behavior produces an incomplete picture. The output is a short list of gaps that the client approves or defers, so nobody discovers later that the data they expected was never recorded.
Next, the client and DeployOpen agree on the two choices that shape cost and accuracy: retention and sampling. Retention decides how many days of query history the cluster keeps locally, weighed against disk use on the nodes. Sampling, controlled through log_queries_probability, decides what share of queries is logged. Sampling reduces load on busy clusters, but it means counts and percentiles describe a sample rather than every query, so the reader must know which figure they are looking at. DeployOpen also builds the cluster-wide queries that read system.query_log across replicas, using the clusterAllReplicas function, and produces a baseline report of slow, failed and expensive queries. Confirm with your own team which dashboards and owners will receive that baseline.
Handover covers the query definitions, the retention and sampling settings with their reasons, the access rights needed to read the log, and a runbook for re-running the analysis. Ongoing monitoring of the log, periodic reviews after upgrades and changes to the cluster are available as retainers, scoped separately from the initial fixed-scope work. Ask any provider to state in the quote what is included in the initial build, what is delivered as documentation, and what recurs, and confirm how the client's own staff will be trained to run the queries after handover.
