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.

Advertisements

2 Responses to “oh for the wait interface”

  1. Paul M Says:

    Historical stats would be a start. Most people have to roll their own.
    Of course you could just go and hack the code yourself and publish the patch…

    You might be interested in this article about instrumenting MySQL via DTrace

  2. ben Says:

    Hi Paul –

    missed your link. I did see this post by Brian “Krow” Aker on adding DTrace support to MySQL. (And I did conceive of trying to do this myself, but I don’t think it’s within the realm of my abilities.)

    Thanks,

    Ben

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: