Tackling Slow Queries: A Practical Approach to Prevention and Correction
summary
Shopify reports peaks like $4.6M per minute and serves 10.5 trillion queries so slow SQL causes about one in five merchant‑impacting incidents. The build is two parts: a correction pipeline that reads MySQL slow‑query logs with Vector, ships them to Observe, autocreates and autoassigns audit action items and a Grafana dashboard to prioritize worst offenders, and a prevention flow that hooks CI via an ActiveRecord subscriber to run EXPLAIN, comment on PRs for full table scans and block overdue fixes. The system shortens feedback loops and catches many regressions before production.
AI-written summary · about
chapters (19)
- 0:00Slow query challenges at Shopify
- 3:48Shopify’s mission and database resilience
- 5:12Short-term database scaling
- 6:35Long-term database resiliency
- 8:21Extracting services for reliability
- 10:30Better query writing for developers
- 11:33Gaps in slow query prevention
- 12:39Incident remediation lifecycle
- 14:24Gaps in previous slow query system
- 14:45Automated slow query detection
- 16:33Reducing incident dependency
- 17:56Ownership and accountability
- 19:44Debugging resources for developers
- 22:31Preventing slow queries in CI
- 23:15Detecting full table scans
- 25:47Slow query prevention in PRs
- 27:59Shifting left with VS Code
- 28:41Key takeaways
- 31:54Closing remarks and Q&A
related talks
55mLet's Talk About MySQL Query Tuning
22mFrom Firefighting to Proactive Work: ...
53mBanishing Slow Queries with Intelligent Query Processing
32mManaging Databases in the Cloud in Broken
31m"Optimizing SQL Queries"
14mDatabase Performance at Scale for RoR Applications
22mImproving the efficiency of more than 800 databases with o11y
05mAfter analyzing 600,000 SQL queries, these are the top mistakes developers make