At a glance
A row-by-row table of MariaDB slow queries (statements exceeding long_query_time) that fired inside the same 5-minute window as a measured drop in checkout completion on the connected storefront. This is the database-side smoking gun for a checkout that is “slow for no obvious reason”. Each row pairs a slow query digest with the order, cart, or inventory tables it touched, so a DBA can tell at a glance whether a checkout dip is a database problem or something further up the stack. When more than 5 slow queries co-occur with a checkout drop, the card fires: the database is the bottleneck and the storefront is bleeding conversions while it stays that way.
| What it tracks | MariaDB slow queries captured in the slow query log (or performance_schema.events_statements_summary_by_digest) during a 5-minute window, correlated against a concurrent fall in checkout completion rate on the linked ecommerce connector. Broken down by row: one row per slow query digest. |
| Data source | The MariaDB slow query log / performance_schema digest tables for the slow-query side; the linked storefront connector (Shopify, BigCommerce, or Adobe Commerce) for the checkout-completion side. Vortex IQ aligns the two on a shared UTC 5-minute bucket. |
| Detail | ”Slow Queries During Checkout Window (5m), broken down by row.” Each row carries the normalised digest, count, mean and max execution time, rows examined, and the storefront checkout delta for the same window. |
| Time window | 5m rolling, refreshed each minute. The window is short by design: checkout pain is acute, and a 5-minute window keeps the correlation tight enough to be actionable. |
| Alert trigger | >5 slow queries co-occur with checkout drop. More than 5 slow query events landing in the same 5-minute bucket as a measurable checkout-completion drop. |
| What counts as “slow” | Any statement whose execution time exceeds the instance long_query_time (commonly 1s to 2s for OLTP). Statements not using indexes are also captured if log_queries_not_using_indexes is on. |
| What counts as a “checkout drop” | A 5-minute checkout-completion rate that falls below the trailing baseline by more than the sensitivity threshold for the linked storefront. The drop must be measured, not inferred, so the card needs a connected storefront to populate the cross-channel half. |
| What does NOT count | (1) Slow queries outside the checkout window; (2) slow queries during a window with no checkout drop (those surface on Slow-Query Rate instead); (3) administrative or backup statements tagged as maintenance; (4) replica-side slow queries (this card reads the primary that serves checkout writes). |
| Roles | owner, engineering, operations |
Calculation
The card is the intersection of two independent measurements over the same 5-minute UTC bucket. The MariaDB side. Every statement that exceedslong_query_time is recorded. Vortex IQ reads either the slow query log (when slow_query_log = ON and log_output includes TABLE or FILE) or, preferred on busy instances, the digest summary in performance_schema.events_statements_summary_by_digest, which aggregates by normalised statement so a single hot query does not flood the table with thousands of literal-by-literal rows. For each digest the engine carries:
count_star: how many times the digest executed in the window.avg_timer_waitandmax_timer_wait: mean and worst execution time, converted from picoseconds to milliseconds.sum_rows_examinedvssum_rows_sent: a high examined-to-sent ratio is the classic signature of a missing or unused index.
count_star × avg_timer_wait). The alert condition is purely a count: more than 5 slow query events landing inside a drop window. The card does not claim causation, it surfaces co-occurrence so a human can confirm it in seconds.
Worked example
A mid-market homeware brand runs Adobe Commerce on a 3-node MariaDB 10.6 Galera cluster, with checkout writes served by the primary writer node. The snapshot is taken on 14 Apr 26 at 20:05 BST, during the evening traffic peak. The storefront connector reports checkout completion has fallen from a trailing baseline of 41 completions per 5 minutes to 18, a 56% drop, which crosses the sensitivity threshold and marks the window 20:00 to 20:05 as a checkout drop. The card lists the slow queries that fired in the same bucket:| Digest | Count | Avg (ms) | Max (ms) | Rows examined (avg) | Tables |
|---|---|---|---|---|---|
SELECT ... FROM cataloginventory_stock_item WHERE product_id IN (...) | 14 | 2,180 | 6,400 | 1,240,000 | cataloginventory_stock_item |
SELECT ... FROM quote_item WHERE quote_id = ? | 11 | 1,640 | 3,900 | 88,000 | quote_item, quote |
UPDATE sales_order_grid SET ... WHERE entity_id = ? | 7 | 1,510 | 4,200 | 1 | sales_order_grid |
SELECT ... FROM customer_grid_flat WHERE ... | 6 | 1,120 | 2,300 | 420,000 | customer_grid_flat |
- The inventory check is the prime suspect.
cataloginventory_stock_itemis examining 1.24 million rows per execution to answer a multi-SKU stock lookup. That examined count against a table that should be a primary-key fetch screams a missing or unusable index, almost certainly because theIN (...)list lost its index path after a recent schema change or statistics drift. At 14 executions averaging 2.18s, this digest alone consumed roughly 30s of the 5-minute window on the writer node. - Checkout serialises behind it. The
quote_itemreads andsales_order_gridwrites are the checkout’s own statements, slowing because they queue behind the inventory scan competing for the same buffer pool and row locks. This is why completions halved: customers are not abandoning by choice, they are timing out on a spinner. - It is the database, not the storefront. Because the card pairs the storefront drop with concrete slow query rows, the DBA can rule out a marketing surge, a payment-gateway issue, or a frontend deploy in seconds. The evidence points at one table.
cataloginventory_stock_item with EXPLAIN, run ANALYZE TABLE to refresh statistics if the optimiser has gone off-plan, and if a recent migration dropped the index, restore it. The DBA also pins Slow-Query Rate and Query Latency p95 (ms) to confirm the fix lands.
Three takeaways:
- Co-occurrence is the value, not the count. “38 slow queries” on its own is a Slow-Query Rate story. The reason this card is a Hero is that the 38 landed exactly when checkout fell. That pairing turns a vague “the site felt slow” into a specific table and a specific index.
- Read the rows-examined column first. A high examined-to-sent ratio is the fastest tell for a missing index, which is the single most common cause of a sudden checkout-window slowdown after a deploy or a stats drift.
- The writer node is the one that matters. Galera replicates writes synchronously, but checkout statements execute on the node the application points at. Slow queries on a read replica do not appear here; this card watches the node that serves the checkout path.
Sibling cards to reference together
| Card | Why pair it with Slow Queries During Checkout Window | What the combination tells you |
|---|---|---|
| Slow-Query Rate % | The instance-wide slow-query view, not gated on checkout. | A high rate here but no rows on this card equals slow queries that are not hitting the checkout path; a low rate but rows here equals a focused checkout-table problem. |
| Top 10 Slowest Queries (digest) | The 24h ranked digest list. | If the digest topping this card also tops the 24h list, the problem is chronic, not a one-off spike. |
| Query Latency p95 (ms) | The tail-latency view across all statements. | p95 climbing in the same window confirms the slowdown is broad, not isolated to the listed digests. |
| Query Latency p99 (ms) | The worst-case latency tail. | A p99 spike alongside these rows means a few statements are catastrophically slow, the ones most likely to time out a checkout. |
| Pool Saturation Across Galera Nodes vs Traffic | The connection-capacity cross-channel peer. | Slow queries plus pool saturation equals checkout starved of both query speed and connection slots, the worst combination. |
| MariaDB QPS Spike vs Ecom Order Rate | The volume-mismatch cross-channel peer. | Rules out a bot or scraper: if QPS spiked without an order spike, the slow queries may be driven by scraper traffic, not real checkout load. |
| Query Error Rate % | The failure-rate view. | Slow queries plus a rising error rate equals statements timing out, not just running slow, which abandons carts hard. |
| MariaDB Health Score | The composite that folds slow-query pressure into one number. | A drop in the score concurrent with these rows confirms the slow queries are degrading overall instance health. |
Reconciling against the source
Where to look in MariaDB’s own tooling:Slow query log withWhy our number may legitimately differ:SHOW VARIABLES LIKE 'slow_query_log%';andSHOW VARIABLES LIKE 'long_query_time';to confirm capture is on and the threshold matches expectations. Iflog_outputincludesTABLE, inspectmysql.slow_logdirectly. Performance Schema digest summary withSELECT digest_text, count_star, avg_timer_wait/1e9 AS avg_ms, sum_rows_examined FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 20;for the same ranked view this card builds.SHOW FULL PROCESSLIST;during a live drop to see statements in flight, andEXPLAINon the suspect digest to confirm the index path. Managed-service consoles (Amazon RDS for MariaDB Performance Insights, Azure Database for MariaDB Query Performance Insight, SkySQL monitoring) surface the same slow-statement ranking under their query-performance views; confirm the time range matches the 5-minute drop window.
| Reason | Direction | Why |
|---|---|---|
| Digest normalisation | Vortex IQ count lower | The slow query log records every literal variant; events_statements_summary_by_digest collapses them to one digest. We report digests, so a raw log scan will show more individual rows. |
| Threshold drift | Either way | If long_query_time was changed mid-window, the log captures fewer or more statements than the card expects. Confirm the live value. |
| Time zone | ”Window” timestamps shift | MariaDB logs in the server’s time_zone; Vortex IQ buckets in UTC and renders in your profile timezone. |
| Maintenance filtering | Vortex IQ count lower | Backup, ANALYZE, and OPTIMIZE statements tagged as maintenance are excluded; a raw log includes them. |
| Replica vs primary | Either way | The card reads the checkout-serving primary; a SHOW PROCESSLIST run on a replica shows different statements. |
| Card | Expected relationship | What causes divergence |
|---|---|---|
shopify.checkout-completion-rate / BigCommerce / Adobe Commerce equivalents | The storefront-side drop that gates this card should match the storefront connector’s own checkout metric for the same window. | A drop here but not on the storefront card equals a sensitivity-threshold mismatch; align the thresholds. |
| MariaDB QPS Spike vs Ecom Order Rate | A genuine checkout slowdown shows slow queries with stable QPS. | Slow queries with a QPS spike and no order spike points at scraper or bot load driving the database, not real shoppers. |
Known limitations / FAQs
The card shows slow queries but my checkout looks fine. Why did it fire? The alert fires on co-occurrence, not causation. If more than 5 slow queries land in a window where checkout also dipped, the card lists them so you can confirm. A small dip can be normal noise at low traffic (going from 3 completions to 1 looks like a 67% drop). Read the absolute counts in the worked-example style, not just the percentage, and pair with MariaDB QPS Spike vs Ecom Order Rate to confirm real load. My slow query log is off. Does the card still work? Partly. Withslow_query_log = OFF, Vortex IQ falls back to performance_schema.events_statements_summary_by_digest, which is enabled by default on MariaDB 10.x and captures statement timing without the log overhead. If Performance Schema is also disabled, this card cannot populate the slow-query half; enable one of the two. The digest summary is preferred on busy instances because it does not write a log row per statement.
What long_query_time should I set so this card is useful?
For an OLTP checkout workload, 1s to 2s is typical. Lower (for example 0.5s) captures more and surfaces marginal queries earlier, at the cost of a noisier log. Setting it too high (10s) means a query has to be catastrophically slow before it appears, by which point checkout has already failed. Tune it so the card lights up before customers feel pain, not after.
Why are replica slow queries not shown?
Checkout writes (cart updates, order inserts, stock decrements) execute on the node the application points at, which is the primary writer in a Galera or primary-replica topology. Slow reads on a replica do not slow the checkout write path, so including them would add noise. Replica slow queries surface on the replica’s own Slow-Query Rate instead.
The same digest appears every evening at peak. Is that an alert I can ignore?
No, that is the most actionable signal there is: a chronic, predictable slow query on the checkout path. Cross-reference Top 10 Slowest Queries (digest) to confirm it is a recurring offender, then fix it once (usually an index, a query rewrite, or ANALYZE TABLE to refresh optimiser statistics). A recurring alert that you suppress instead of fixing will eventually become a peak-traffic outage.
Can a Galera flow-control pause look like slow queries here?
Yes, and it is worth knowing the difference. If one node falls behind, Galera throttles the whole cluster’s writes via flow control, which makes checkout statements slow without any single bad query. If the rows on this card show fast individual digests but checkout still dropped, check Galera Flow Control Paused %. The fix there is a node, not an index.
Does the card distinguish a missing index from genuine high load?
It gives you the evidence to distinguish them yourself. The rows-examined column is the key: a digest examining millions of rows to return a handful is a missing-index signature, fixable with an index or ANALYZE TABLE. A digest examining roughly as many rows as it returns, but appearing many times, is genuine load, fixable with caching, read replicas, or capacity. The card reports both numbers so you can tell which one you are looking at.