At a glance
A correlated table that lists every slow query (a statement whose execution exceeded long_query_time) recorded on the MySQL instance during a rolling 5-minute window, lined up against the storefront’s live checkout volume for the same minutes. For a platform or SRE team, this answers one blunt question: “are slow database queries happening at the exact moment shoppers are trying to pay?” A slow query during a quiet hour is a tuning task. The same slow query co-occurring with a checkout drop is an active revenue leak, which is why this card crosses the database connector with the storefront connector rather than living in either alone.
| Data source | Slow_queries delta from SHOW GLOBAL STATUS plus the per-statement rows from performance_schema.events_statements_summary_by_digest (or the slow query log / mysql.slow_log table when log_output = TABLE). Each row is a slow statement: digest, schema, mean and max latency, rows examined, and a count for the window. The storefront side is the checkout/order rate pulled from the connected ecommerce connector for the identical minutes. |
| Metric basis | Statement-level slow events, NOT a single scalar. The card is a table broken down by row (one row per slow digest seen in the window), joined in time to checkout volume. A query only appears if it crossed long_query_time inside the window. |
| Aggregation window | Rolling 5 minutes (5m), refreshed on the live cadence. The window is short on purpose: checkout impact is a now problem, not a daily-average problem. |
| What counts as a “slow query” | Any statement whose wall-clock execution time exceeded the instance’s long_query_time threshold (default 10s, but most production instances run it at 1s or below). Statements that were slow only because they waited on a lock still count, the shopper waited regardless of the cause. |
| What does NOT count | (1) Fast queries, even high-volume ones; (2) Slow queries outside the 5-minute window; (3) Administrative statements excluded by log_slow_admin_statements = OFF; (4) Queries below min_examined_row_limit if that filter is set; (5) Replica-side apply latency (that lives on the Replication Lag card). |
| The checkout join | The storefront side is sourced from the connected commerce connector (Shopify, BigCommerce, or Adobe Commerce) order/checkout rate for the same 5 minutes. The “co-occur” logic compares the slow-query burst against a dip in that checkout rate. |
| Time zone | Statements are timestamped in the instance’s system_time_zone; Vortex IQ normalises to UTC for cross-connector windowing and renders in your display time zone. |
| Time window | 5m (rolling 5-minute window, live refresh) |
| Alert trigger | >5 slow queries co-occur with checkout drop. More than five slow statements inside the window AND a measurable fall in checkout/order rate over the same minutes pages the on-call. |
| Roles | owner, engineering, operations |
Calculation
The card is built in two passes that are then joined on time. Pass one, the database side. Every refresh, the engine reads theSlow_queries counter from SHOW GLOBAL STATUS and computes the delta against the previous sample. A non-zero delta means at least one statement crossed long_query_time since the last read. To turn that count into rows, the engine reads performance_schema.events_statements_summary_by_digest filtered to statements whose MAX_TIMER_WAIT (or last-seen timer) lands inside the 5-minute window, ordered by latency descending. Where Performance Schema is unavailable, it falls back to the slow query log table (mysql.slow_log, requires log_output = TABLE). Each surviving statement becomes one row: normalised digest, default schema, count in window, mean latency, max latency, and rows examined.
Pass two, the checkout side. For the identical 5 minutes, the engine pulls the checkout or order rate from the connected commerce connector and compares it to the trailing baseline (the same weekday-and-hour pattern). A “checkout drop” is a statistically meaningful fall below that baseline, not just any minute-to-minute wobble.
The join and the alert. The two passes are aligned minute by minute. The headline counts the slow statements in the window; the alert fires only when both conditions are true at once: more than five slow statements present, and a checkout drop detected over the same window. That AND is the whole point. Five slow queries with healthy checkout is a backlog tuning ticket; five slow queries with a checkout dip is money walking out of the door right now.
Worked example
A UK homeware brand runs Magento (Adobe Commerce) on a primary MySQL 8.0 instance withlong_query_time = 1. Snapshot taken on 14 Apr 26 at 20:05 BST, in the middle of an evening email-campaign traffic peak.
| Digest | Schema | Count (5m) | Mean | Max | Rows examined |
|---|---|---|---|---|---|
SELECT ... FROM sales_flat_quote WHERE ... | magento | 9 | 2.4s | 6.1s | 1.9M |
SELECT ... FROM catalog_product_index_price ... | magento | 6 | 1.8s | 3.3s | 740k |
UPDATE inventory_stock_item SET qty = ... | magento | 4 | 1.3s | 2.0s | 12k |
SELECT ... FROM core_session WHERE session_id = ... | magento | 3 | 1.1s | 1.4s | 88k |
- The quote-table query is the bleeder.
sales_flat_quoteis on the hot path: every “add to basket” and every checkout step touches it. At 1.9M rows examined per execution and a 6.1s max, it is a full or near-full scan, almost always a missing or stale index after a recent deploy or a statistics drift. This is the digest to attack first. - The price-index query is collateral.
catalog_product_index_pricegoing slow at the same instant points at a shared root cause: the InnoDB buffer pool is thrashing under the traffic peak, so even normally-cached pages are hitting disk. Confirm with InnoDB Buffer Pool Hit Rate %; if it has dropped below 95%, the buffer pool is the systemic cause, not each query individually. - The checkout dip is real, not noise. 38 to 21 is a 45% fall during the exact minutes the database is slow. That is the difference between “tune this later” and “act now”.
KILL QUERY if a single rogue statement is starving the pool, and (c) decide on a storefront mitigation (throttle the email-campaign send rate so traffic stops outrunning the database) while the index fix is prepared and deployed.
Three takeaways the team should remember:
- The count alone is not the signal; the co-occurrence is. Twenty slow queries during a 03:00 batch window is expected and harmless. The same twenty during a checkout dip is the alertable event. Always read the checkout column next to the count.
- Repeated digests beat one-off spikes. A single slow statement that ran once may have been an unlucky lock wait. The same digest appearing nine times in five minutes is a structural problem (bad plan, missing index, buffer-pool pressure) that will keep firing until it is fixed.
- Rows examined is the smoking gun. A slow query that examined 1.9M rows to return a handful is an index problem you can fix. A slow query that examined few rows but still took seconds is a locking or resource-contention problem, a different fix entirely. Read the rows-examined column to tell them apart.
Sibling cards platform teams should reference together
| Card | Why pair it with Slow Queries During Checkout Window | What the combination tells you |
|---|---|---|
| Slow-Query Rate % | The instance-wide rate, not the checkout-windowed view. | A high overall rate with this card quiet means the slow queries are off the checkout path; both high means the slow load is landing on shoppers. |
| Top 10 Slowest Queries (digest) | The 24h digest leaderboard that names the worst statements. | The digest at the top of this card during the incident should appear in the 24h leaderboard, that is your permanent fix target. |
| Query Latency p95 (ms) | The percentile view of overall query health. | p95 spiking in the same window confirms the slowdown is broad, not confined to one digest. |
| InnoDB Buffer Pool Hit Rate % | The most common systemic cause of correlated slow queries. | Hit rate falling below 95% as this card fires equals buffer-pool thrashing, fix capacity not individual queries. |
| InnoDB Deadlocks (last 5m) | Lock contention as an alternative cause of slow checkout statements. | Deadlocks plus slow checkout queries means a locking problem, not an indexing one. |
| Connection Pool Saturation % | Slow queries hold connections open longer, draining the pool. | Both high means a feedback loop: slow queries saturate the pool, the pool starves new checkouts. |
| MySQL QPS Spike vs Ecom Order Rate | The volume-side cross-channel peer. | A QPS spike with no order spike points at a bot or scraper generating the slow load, not real shoppers. |
| MySQL Pool Saturation vs Traffic Burst | The capacity-side cross-channel peer. | If the burst saturated the pool first, the slow queries are a symptom of capacity, not query plans. |
Reconciling against the source
Where to look in MySQL’s own tooling:Why our number may legitimately differ from MySQL’s own view:SHOW GLOBAL STATUS LIKE 'Slow_queries';for the running counter; take two readings a few minutes apart and subtract to get the window delta.performance_schema.events_statements_summary_by_digestordered byMAX_TIMER_WAITfor the per-digest latency leaderboard that feeds the table rows. The slow query log (file atslow_query_log_file, or themysql.slow_logtable whenlog_output = TABLE) for the raw statements with timestamps, parse withmysqldumpsloworpt-query-digest. Managed services: on Amazon RDS / Aurora use Performance Insights and the slow query log in the console; on Google Cloud SQL use Query Insights; on Azure Database for MySQL use the Query Performance Insight blade. These expose the same counters with their own retention.
| Reason | Direction | Why |
|---|---|---|
long_query_time setting | Count varies | The card counts whatever the instance defines as slow. If your long_query_time is 10s but you expected 1s, the count will look low until you tune the threshold. |
| Window boundaries | Edge statements shift | A statement straddling the 5-minute boundary is attributed to the window its completion timestamp falls in; manual Slow_queries deltas over a different interval will not match exactly. |
| Performance Schema vs slow log | Slight row differences | The digest summary aggregates by normalised statement; the raw slow log keeps each execution. Counts agree, but the slow log may show parameter values the digest view hides. |
| Counter reset on restart | Sudden drop | Slow_queries resets to zero on instance restart or FLUSH STATUS. A restart inside the window deflates the delta. |
| Admin statement filtering | Count lower | If log_slow_admin_statements = OFF, slow ALTER/ANALYZE statements are not counted here even though they ran slowly. |
| Time zone | Timestamps shift | The instance logs in system_time_zone; Vortex IQ renders in your display time zone. |
| Card | Expected relationship | What causes the divergence |
|---|---|---|
shopify.orders-over-time / shopify.total-revenue | The checkout drop on this card should show as a dip in storefront orders for the same minutes. | If orders are flat while this card shows a drop, the join is reading a different store or time zone, check the connector mapping. |
bigcommerce.abandoned-cart-spike-alert | Slow checkout queries should correlate with a rise in abandoned carts. | Abandonment up with this card quiet means the friction is front-end (JS, payment iframe), not the database. |
datadog.checkout-service-health-sales | The APM view of the same checkout slowness. | Datadog shows the application latency; this card shows the database cause underneath it. They should rise together when the DB is the bottleneck. |
Known limitations / FAQs
The card shows slow queries but checkout looks fine. Why is it not alerting? By design. The alert requires both conditions: more than five slow statements AND a measurable checkout drop in the same window. Slow queries alone, without a checkout dip, are a tuning ticket rather than an incident, so the card stays amber/quiet and feeds the Slow-Query Rate % and Top 10 Slowest Queries (digest) cards instead. The whole reason this card exists is to separate “slow but harmless” from “slow and costing money”. Mylong_query_time is 10s. Should I lower it?
For an OLTP storefront, almost certainly yes. A 10s threshold means a query has to be catastrophically slow before it is counted, by which point shoppers have already abandoned. Most production ecommerce instances run long_query_time between 0.5s and 2s so the card catches the queries that actually hurt checkout. Lower it during a maintenance window, watch the slow-query volume, and tune to a level that surfaces real problems without flooding the log.
Performance Schema is disabled on my instance. Does the card still work?
Partly. With Performance Schema off, the engine falls back to the slow query log (you need slow_query_log = ON, ideally log_output = TABLE so it can read mysql.slow_log over SQL). You still get the rows and counts, but you lose some of the per-digest aggregation that Performance Schema provides for free. Enabling Performance Schema (it is on by default in MySQL 8.0) gives the richest table.
Why does the same digest appear multiple times in one window?
Each row’s count is how many times that normalised statement crossed long_query_time in the 5 minutes. A count of nine means the same query plan ran slowly nine times, which is the strongest possible signal that it is a structural problem (missing index, stale statistics, buffer-pool pressure) and not a one-off unlucky lock wait. Repeated digests are your priority fix targets.
How does the checkout side know which store to join against?
The card reads the commerce connector linked to the same Vortex IQ profile (Shopify, BigCommerce, or Adobe Commerce). If you run multiple storefronts off one database, confirm the connector mapping in the profile so the join lines up the right store’s checkout rate. A mismatched mapping is the most common cause of “the numbers do not agree”.
A slow query examined very few rows but still took seconds. Is the card wrong?
No. Wall-clock time, not rows examined, is what makes a query slow and what the shopper feels. A statement that examined few rows but took seconds was almost certainly waiting on a lock or starved of a resource (CPU, I/O, or a saturated buffer pool). Read the rows-examined column to classify it: high rows examined means an indexing problem; low rows examined with high latency means contention. Pair with InnoDB Deadlocks (last 5m) to confirm locking.
Can the card distinguish a real shopper slowdown from a bot-driven one?
Indirectly, by pairing. If queries-per-second spikes but the storefront order rate does not, the load is likely a scraper or bot rather than buying shoppers; that pattern is exactly what MySQL QPS Spike vs Ecom Order Rate surfaces. This card tells you checkout is hurting; that sibling tells you whether the cause is real demand or automated abuse.
Does a restart or FLUSH STATUS affect the reading?
Yes. The Slow_queries counter is cumulative and resets to zero on instance restart or an explicit FLUSH STATUS. If either happens inside the window, the delta will under-report. The per-digest Performance Schema view is more resilient because it carries its own timers, but a full restart clears that too. After a restart, give the card one full window to re-establish a baseline before trusting the count.