oh for the wait interface

We purchased MySQL support, and I installed the advisor software that comes with it, and checked out the tuning advisors. I feel like I’m back in the days before the Oracle wait interface, tuning everything with hit ratios, etc, and basically guessing which changes might help what how much. (Tune, cross your fingers, start over, try again. Repeat.) Like before I had ever read “Optimizing Oracle Performance” by Cary Millsap, or “Oracle Insights:Tales of the Oak Table”.

My #1 wish for MySQL at this point would be that version 6 would be instrumented, so that we could tune using wait events, to identify actual wait times and what contributed to them by how much.

For now, I get recommendations like “Query Cache Has Sub-Optimal Hit Rate“, which is giving me flashbacks… (In our case, I don’t have to worry much about this in particular, and the advisors are good enough to let me know that I might “Evaluate whether the query cache is suitable for your application. If you have a high rate of INSERT / UPDATE / DELETE statements compared to SELECT statements, then there may be little benefit to enabling the query cache.“)

Anyway, it’s hit ratios all over again, making me pine for Oracle’s Automatic Workload Repository. Where I get recommendations about specific queries or indexes, and estimates for what the performance improvements might be if I change them, based on actual times spent doing operations. (And also, eg, how much time is spent waiting for things outside of the database, too, so you might avoid spending 80% of your time tuning something that will give your application an improvement of 2%.)

I think that, for now, the slow query log is my best friend. It gives me actual times spent doing which queries, and from there I can tune those queries. For the database itself, I’m back to comparing various ratios over time and trying to improve “bad” ones and seeing whether it makes a noticeable difference.