Slow Analytics Queries During Checkout Window, ClickHouse

​

At a glance

A table that lists the slow ClickHouse queries running during a live checkout window, so you can see whether heavy analytics work is colliding with the moment shoppers are paying. Many ecommerce stacks run their order database and their analytics warehouse close together: shared instance, shared connection pool, or a checkout flow that reads from ClickHouse for personalisation, fraud scoring, or inventory checks. When a fat analytics query (a month-end report, an ad-hoc BI scan, a backfill job) runs long during a peak checkout minute, it can starve the resources checkout depends on. This card pairs the slow-query list with the storefront checkout rate: when more than five slow queries co-occur with a checkout drop, it flags the collision so the on-call DBA can act before the dip becomes lost revenue.

​

Calculation

SELECT
    event_time,
    user,
    query_duration_ms,
    formatReadableSize(memory_usage) AS memory,
    normalizeQuery(query) AS query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 1000
  AND event_time > now() - INTERVAL 5 MINUTE
ORDER BY query_duration_ms DESC

slow_query_count > 5         (more than five slow queries in the 5-minute window)
AND checkout_rate dropping    (checkout/order rate below its recent normal band)

​

Worked example

1. The collision is real, not coincidental. Six slow queries landed inside the same minute that checkout fell by a third. The top two alone held nearly 17 GiB of memory between them. On a shared instance, that is exactly the kind of pressure that slows the small, fast queries checkout’s fraud and inventory calls depend on.
2. The source is concentrated. Five of the six rows belong to two users, bi_reporting and analyst_adhoc. This is not broad organic load, it is a month-end report run plus an analyst exploring ad-hoc, both unluckily timed for the lunch peak.
3. The fix is immediate and the prevention is structural. Right now: throttle or kill the two heaviest bi_reporting queries to give checkout its headroom back. Afterwards: schedule heavy reporting outside peak windows, or isolate it onto a separate ClickHouse read replica / resource-group so analytics can never again contend with the checkout path.

Acting on the collision in the moment:
  - Find the live offenders (these may still be running):
      SELECT query_id, user, elapsed, formatReadableSize(memory_usage)
      FROM system.processes
      WHERE elapsed > 5 ORDER BY memory_usage DESC
  - Kill the worst, give checkout its resources back:
      KILL QUERY WHERE query_id = '<id>'
  - Confirm checkout recovers on the storefront admin
  - Prevention: move bi_reporting to a workload/resource group with a
    memory and concurrency cap, or to a dedicated read replica

1. Co-occurrence is the signal, not the slow query alone. Slow analytics queries are normal; slow analytics queries while checkout is dropping is a revenue event. The card joins the two so you do not have to correlate by eye across two tools.
2. Shared infrastructure is the root cause. Every one of these collisions traces back to analytics and checkout drawing from the same pool, memory, or CPU. Isolating heavy reporting (resource groups or a dedicated replica) removes the entire class of incident.
3. The query text names the owner. The normalised query and user columns tell you immediately who to throttle and who to talk to. A collision is usually two or three known heavy users, not a mystery.

​

Sibling cards

​

Reconciling against the source

Pull the same slow-query list directly from clickhouse-client:

SELECT event_time, user, query_duration_ms, memory_usage, normalizeQuery(query)
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 1000
  AND event_time > now() - INTERVAL 5 MINUTE
ORDER BY query_duration_ms DESC;

For queries still running, system.processes (or SHOW PROCESSLIST) shows live duration and memory so you can KILL QUERY the worst offender. On ClickHouse Cloud, the same system.query_log and system.processes queries run in the SQL console, and the managed query-insights view lists slow queries over time. For the checkout side, reconcile against the ecommerce connector’s native admin (Shopify, BigCommerce, or Adobe Commerce order/checkout reports) for the order rate in the same window.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre