I had the pleasure to sit on the Performance Panel at the recent Percona Performance Conference. While the panel contained a number of usual MySQL suspects, one person was not familiar, that being Cary Millsap from Method R.
An expert in optimizing Oracle performance, Cary also gave an session on Day 2 that I attended. While he opened professing not to be an expert in MySQL, his talk provided valuable foundation knowledge irrespective of whether you use MySQL or another database product.
Having come myself from 7 straight years in system architecture and performance tuning in Ingres, then a further 6 years in Oracle again heavily involved in system architecture and performance tuning, a lot of my experience in the 10 years of providing my own MySQL consulting is drawn from my past RDBMS experiences. In addition much of what I actually provide to clients today is common sense that I don’t see applied.
A summary of the excellent content provided by Cary.
The common technology agnostic problem we need to address is:
- Users say that everything is slow, but I don’t know where to begin
- Users are complaining but all the monitoring dials are green
From a user’s perspective, their experience consist simply of two elements.
- Task
- Time
In general, business people simply don’t care about the “system” except thought the specific tasks that make up their pressing business needs. And for these users, performance is all about the time to complete this task.
Throughput can be stated as tasks per time.
Response time is the time taken per task.
Cary also quoted Donald Knuth — “The universal experience of programmers who have been using measurements tools has been that there intuitive guesses fail.”
Performance is easy if you stop guessing where your code is slow. A few best practice tips are:
- You have to insist on seeing where time goes for any task you think is important
- You need to look at the sequence diagram of the task
- What individual part takes the most time, then look at the task before that. The fastest way to do something is don’t.
- To drill down, you need to attack the skew of each part, not the average.
In Summary the closing points were:
- Performance is about time and tasks
- Not all tasks are created equal
- Read “The Goal”
- Don’t guess, your probably wrong
- Measure response time before you optimize anything – Insist on it
Performance is easy when code measures it’s own time and tasks. This closing statement on instrumentation I completely concur with.