Profiling MySQL by "Response Time"

Introduction
Being an independent MySQL consultant is fun and equally challenging, I very regularly get the opportunity of doing low-level MySQL Performance Health Check, Diagnostics and Forensics. My customers equally are curious in knowing how optimal is their MySQL infrastructure ? Many times I have heard people defining “Performance” as executing queries optimally using minimum system resources! I contradict this thought by asking in return, If you have bought expensive high processing power infrastructure why not use it to maximum? At MinervaSQL we define performance as total time consumed to complete a task so for us Performance is Response Time . In real life this is very scalable method too because MySQL Server is busy executing queries and how much time consumed by each query leads us to Response Time  and our goal remains reducing the Response Time , Here we measure where the time is consumed.This also brings most powerful principle of performance optimization “Things which cannot be measured can never be maximised !” . Measure where response time is spent and "never ever” aggregate here, target each query independently. Measure every slow query and monitor what is the impact in the server, Calculate percentage of time spend in waiting and executing of each query. 

Profiling MySQL for Response Time   
Accuracy of profiling is key in calculating Response Time, During profiling we measure where MySQL is spending time? In effect here we are measuring how much time is spend in execution ? and how much time spend by waiting ? When we are doing profiling of MySQL, There are two interesting matrices to notice:1. Queries are continuously executing using available infrastructure so this is execution time profiling 2. Queries are waiting for tasks to get completed or for resources , This is called wait time profiling 
Both of these above mentioned matrices are very important for us to measure the time spend by every query and the results will guide us to conclude where we should be spending time during tuning. 
I always list down the queries that can bring down big impact to MySQL performance, If a query consumes only 5% of total time of execution then tuning that specific query can gain only in 5% of improvement (Amdahl’s law)The "lost time” calculation during response time analysis is very important, It is total wall clock time went unaccounted during execution time. Let’s consider overall CPU time for execution of query is 60 seconds and profiling tools subtasks adds upto 50 seconds then “lost time” here is 10 seconds!When doing MySQL profiling two of my favourite tools are SHOW PROFILE (Jeremy Cole) and PT-QUERY-DIGEST of Percona Toolkit developed by Percona  

Conclusion 
I recommend my customers to collect the response time of each query and measure every single activity that consume time which include even application level response time, It is not just MySQL causes performance bottleneck every time and it’s very straightforward to diagnose whether MySQL is the problem. 

No comments: