SLIDE 1
A Proactive approach to Monitoring Slow Queries
Shashank Sahni ThousandEyesShashank Sahni
Principal SRE @ThousandEyes Reach out to shashank@thousandeyes.com Twitter @shredder12
@ThousandEyes @shredder12A Proactive approach to Monitoring Slow Queries Shashank Sahni - - PowerPoint PPT Presentation
A Proactive approach to Monitoring Slow Queries Shashank Sahni - - PowerPoint PPT Presentation
A Proactive approach to Monitoring Slow Queries Shashank Sahni ThousandEyes Shashank Sahni Principal SRE @ThousandEyes Reach out to shashank@thousandeyes.com Twitter @shredder12 @shredder12 @ThousandEyes 2 MySQL @ThousandEyes Percona
SLIDE 2 2
SLIDE 3 3
MySQL @ThousandEyes
Percona Xtradb Clusters Low Latency Transactional store 7k tx/sec
@ThousandEyes @shredder12 SLIDE 4
What is a Slow Query?
SLIDE 5
If exec_time > threshold
Poorly Designed Dealing with a lot of data Victims of Database Congestion
@ThousandEyes @shredder12 5 SLIDE 6
Performance Impact
SLIDE 7
Database Performance Impact
- Slow by Design
- High latency for
applications issuing them
- Steal resources from
- ther queries
- DB congestion due to
extended locks.
- Thrashing System Resources
- Leaving DB unresponsive.
Outage!!
WORST WORSE BAD
@ThousandEyes @shredder12 7 SLIDE 8
Slow Queries Always Sneak in
Bad Design
Human Mistake
Deleted Index Increasing table size
Schema Changes Performance Degrades Over Time
@ThousandEyes @shredder12 8 SLIDE 9
Proactively dealing with Slow Queries
SLIDE 10
Catch Early and Minimize Impact
New changes are pushed everyday Faster analysis Notify application
- wner for faster
resolution
Continuous Process Automation Notification
@ThousandEyes @shredder12 10 SLIDE 11
Slow Query Pipeline @ThousandEyes
SLIDE 12 12 12
20 min
Detects new queries in under
@ThousandEyes @shredder12 SLIDE 13
Notifications
- Notifies the application owner
– MySQL user associated to app
- JIRA Issues
– Creates Issue for new queries – Raises priority if performance worsens – Reopens Issue if query shows up again
@ThousandEyes @shredder12 13 SLIDE 14
Select Top Offenders
Occurrence Total Exec Time Avg Exec Time
- Avoids one off queries
- Captures Recurring queries
- Queries with high total exec_time
- Filters queries which have small
count > C && (time_sum > S || time_avg > A)
- Queries with high avg exec_time
- Filters queries which have high
SLIDE 15
Select Poorly Designed Queries
Full Table Scan Queued or Executing
- Not using Indexes
- Poor Joins
- Rarely genuine
- Filters query whose exec_time wasn’t spend in Storage
- Set T to just under your slow query threshold
full_scan_sum > 0 && Innodb_queue_wait_max < T
@ThousandEyes @shredder12 15 SLIDE 16
Architecture
pt-query-digest pushing analyzed logs from databases Slow Query Database Anemometer Slow Query Notifier SQN Jira
@ThousandEyes @shredder12 16 SLIDE 17
pt-query-digest
MySQL Query Analyzer from Percona
- Great tool for ad hoc or batch analysis
- Invaluable Insights
Our Setup
- Runs periodically on all DB nodes
- Analyzes slow query logs and stores the
data in a central slow query database.
@ThousandEyes @shredder12 17 SLIDE 18
Anemometer
Slow Query Monitor from Box
- Web utility to visualize and search through analyzed slow query data
- Good Search and Deepdive features
- Not under active development.
- Github - box/anemometer
SLIDE 19
Slow Query Notifier
SQN
From ThousandEyes
- In-house tool to monitor slow query events.
- Configurable query properties to catch top offenders
– JIRA workflow for notifications
- create/reopen/prioritize
- To be open-sourced
SLIDE 20
Lessons Learned
SLIDE 21
Target Top Offenders
Less Noise More Impact Happy Developers!
21 @ThousandEyes @shredder12 SLIDE 22
Share Every Win
Result of resolving Top 4 queries
@ThousandEyes @shredder12 22 SLIDE 23
Future Works
SLIDE 24 @ThousandEyes @shredder12
24
Future Works
Support for notification backends – email etc. Support for Mongodb Evaluate PMM for Slow Query visualization Open source Slow-Query-Notifier
SLIDE 25 25
@ThousandEyes @shredder12
SLIDE 26