Optimizing Oracle Performance by Cary Millsap and Jeff Holt uses Oracle to make its points, but these points apply also to MySQL. The primary lesson I took away from this book is: all else aside, optimize/fix the user-action that provides the most economic benefit to the company; do this by profiling just that action and optimizing/fixing the most time-consuming events even if they are “idle” or “wait” events.
The authors call the aforementioned approach to performance optimization “Method R”. It’s meant to be deterministic and teachable unlike “Method C”–the conventional method–whereby one uses their best judgment and experience to find the cause(s) of problems and fix them. I agree, and Method R is fundamentally, imho, just the scientific method in practice. Therefore, I like Method R because it puts “science” back into “computer science.”
The book also discusses queueing theory. It’s a whirlwind tour (~60 pages) but the authors provide everything you need to get started, including helper scripts and Excel worksheets. I’m pretty sure that I’ll be working with this theory more in my job; when I do, I’ll begin with what the authors have given me (“stand on the shoulders of giants“).
One criticism/clarification comes to mind: Method R is reactive. Let’s say your MySQL configuration is terrible so you’re not getting the most from your server as you could. Method R may indirectly expose this only if the configuration is the root cause of a slow user-action. So the configuration is only examined and fixed if the user deems their action unacceptably slow. However, users don’t always complain; sometimes they just “live with it” because they don’t care or they don’t think it can be fixed or they’re afraid to complain or it’s always been that way so they’re not even aware that things could be better. Thus, I think a more holistic view of performance optimization requires both a proactive method and a reactive method. Method R is a great reactive method, but someone should be checking stuff even when there doesn’t seem to be a problem. The authors don’t say “Method R is all you ever need to do”–I’m just making a clarification here.
Oracle extended SQL traces are used throughout the book to investigate performance issues. Does MySQL have anything similar? Nothing as cohesive comes to my mind (correct me if I’m wrong). I think we can achieve the same thing via microslow logs, the community PROFILE feature, session status values, and scripts to glue it all together. That’s a lot a of disparate pieces. I’d rather have MySQL extended SQL traces (in a format more easily parsable than Oracle’s).
In summary, Optimizing Oracle Performance is a must-read for any database professional. I think its emphases on providing the business the most “bang for its buck” and the deterministic nature of Method R are timeless and timely lessons for those of us who earn our livings by engaging in the science of computing.