Posted on 12 Comments

Instrumentation inside MySQL – your wishes?

Augmenting Baron‘s excellent post about existing monitoring/analysis info available through the enhanced builds by Percona and OurDelta… developing these capabilities further is a matter of what as well as how.

So let’s discuss the what: let us know what kind of information you would like to get out of the server, and why. Please be as specific, detailed and descriptive as possible, so we get the best possible overview of your needs. To discuss publicly, simple comment on this post – but you may also contact me directly to discuss if you wish. Thanks for your input!

Posted on 12 Comments

12 thoughts on “Instrumentation inside MySQL – your wishes?

  1. Hey Arjen

    A few of my own wishes, to get things going – tracked within the server, not in logs :

    Temporary table monitoring for all threads (sizes, current open temporary tables, memory or disk based, etc.)
    Lock monitoring (for all storage engines, in a single place, be it table, row or page level)
    Contents of the binary log cache for currently running transaction(s)
    Query Cache contents
    Database file IO

    Thinking of feeding these back to MySQL, or is this just for OurDelta/Percona?

    Regards

    Mark Leith

  2. Thanks for your input Mark, very useful!

    And patches are patches, my friend – it’s up to MySQL to eat them.
    But as Baron has noted, we can’t wait for it.
    With OurDelta, we are now able to somewhat immunise our clients against political and business decisions at the Sun Database Group which may be perfectly valid, but don’t help those clients NOW – if you have pain somewhere, you won’t give a stuff about reasons why certain doctor’s can’t or won’t offer particular treatments – you want to be helped with whatever is or can be made available.

    For many companies, it’s a matter of survival, not luxory.
    Their assessment will be one of risk versus trust.

  3. How about improving explain:

    * should work for INSERTS/UPDATES/DELETES too
    * cpu and/or io estimate, join size estimate or similar, sort of a total score

  4. My pleasure, instrumentation is my “pet interest”! 🙂

    I do agree with the “NOW” comments (just see the comments on my own recent blog), and understand them.

    However, patches are not just patches that MySQL can eat – as you know, there is a legal process with signing joint ownership over to Sun, using the CDDL now (a great improvement imho).. If that’s not signed however, we can’t really touch it.

    I do believe that extra instrumentation is of great benefit to *all* MySQL users though, so would love to see any efforts in this arena fed back to us if at all possible.. Even if it doesn’t help people straight away, and you guys continue “fighting that fight” on that front, getting it in to the main line in later versions as well will eventually ease the “merge hell” pain for you guys when we can include them too (as well as reaching a far wider audience eventually)..

    Anyway, look forward to anything new you guys can come up with! 🙂

    Mark

  5. > My pleasure, instrumentation is my “pet interest”! 🙂

    I know.

    Re patch acceptance upstream… most authors of the major stuff have signed, and that’s nothing new. Like Google signed ages ago. And I actually ascribed to the original mysql contributor agreement, borked as it was.

    You’re right, if Sun/MySQL were to adopt patches, that would easy the maintenance; however, the timeline is key. With the timeline as it is now, I really think it’s pretty much irrelevant whether or not Sun/MySQL accepts anything. “eventually” is very close to “never”, in this sense. So why should we spend much time on it… been there, done that – as you know.

    So we’re just getting on with things now. It doesn’t help to keep looking up at the supposed leader, if they don’t actually lead. Looking away from where you’re going is a serious distraction, it can make you stumble.
    You’ve probably heard the joke “wait for us, we’re the leader!”

    Bending over backwards, that far, for that long, hurts ones’ back 😉
    If MySQL wants to deal with the real world and modify its acceptance process and timeline, I don’t think anyone in the community would object to that. It might even get applauded, if people still give a stuff – there’ve been too many “announcements”. Actual real action is the only thing that will do anything now.

    On a related note, have you noticed how empty the internal@ mailing list is these days?

  6. EXPLAIN-like info for commands other than SELECT. Yep.

  7. Yep, we’re looking pretty hard at both the release cycles and contribution processes to try and solve this – Giuseppe being one of the people leading the effort there (maybe chat with him about it?), whilst the upper management have put together a specific team now to make sure that this happens.

    I don’t believe there is any silver bullet though, it’s something right now that is going to have to be done on a step by step basis. I do believe that 6.0 simply will not suffer the same long run out process that 5.1 did though.

    Development themselves are overwhelmed with just day to day development (bug fixing), which I believe has a big impact on this. I’d love to see more *bug fixes* coming from the community, myself – there are *some*, but the overwhelming majority is new features.

    If we *all* took a little time to solve a bug (at least, all of us who are able to), I think we could get on track much faster. That would be *true* community contribution, imho. 🙂 Pulling together to help make sure that their favorite project succeeds.

    Of course, we need to fix the communication channels for these kinds of contributions as well, I don’t necessarily think that internals@ is the best resource for that either (whilst I think things like jira, or something that can provide a much more structured review of bug fixes *and* contributions are the way to go). Hopefully the above project team will help resolve this as well – I know they’re looking closely at it.

    Just out of interest – what do you think an acceptable acceptance time frame is?

    For instance, 5.1 is just reaching GA, we have an obligation to all of our customers that we do not just add features to it willy nilly, for maximum stability, so things *must* go in to 6.0. But 6.0 is perhaps a year out (I don’t really know when it’s planned to be released so this is not a release statement :)), is that acceptable? Or would you want a release every six months?

    Just trying to get a feel for what people think the acceptable “patch to released GA binary” is for their contributions..

  8. I want details on temp tables open over all connections including bytes, rows, ddl (maybe). SHOW INDEXES for all of them would be nice too. Sometimes I want to know whether index cardinality stats were collected for them and in some cases index stats are not collected unless ANALYZE is run. Users don’t really know this and get many lousy query plans.

    I also want to see query plans for all running queries. It doesn’t have to be the full EXPLAIN output. The join order and indexes used would be sufficient.

    I will do this eventually as I want to deploy it next year.

  9. Ubuntu does a release every 6 months, and whatever is ready gets in, whatever is not waits until next time. I think this is a very sane and quite agile approach. What MySQL currently does is a classic waterfall model with all the dramas and delays.
    Apart from the external predictability, it makes developers feel better (=motivated) because there’s real visible progress regularly.

    I don’t think new teams and step-by-step foo is going to do anything – fundamentally I’m an optimist, but it’s trying to ignore gravity. Business processes work in a certain way, and that defines what is possible within that framework. If you want to do something else, you need to make sure that it’s not subject to the old ruleset. This is why, for instance, Drizzle can’t exist within the Sun Database Group. Think about it. This is well researched and documented stuff.
    Ignoring it and/or pretending that this one time it’ll be different is, IMHO, silly – afaik there hasn’t been a single case in history where this has been proven wrong. Might as well set it up in the way that has been proven to work. I can give you the book refs if you want.

  10. noted, thanks!

  11. Well this is the model I’ve seen talked about – with feature trees outside of the mainline, and whatever is ready getting pushed to main line before the (beta I guess) releases. If it ain’t done, then it won’t go in.

    And I meant that we have to take changes internally (with the way we do things) in a step by step manner. Changing everything in one huge swoop will not help either if you ask me, and could have just as dire consequences whilst everybody runs around doing the headless chicken dance.

    Those changes are already starting to take place as well, we now have program managers for each of the *specific* releases, 5.1, 6.0, 6.x (yes 6.x is being planned and even worked on right now, so that we can bring things forwards) – the program managers are mostly old school Sun people, who know how to meet dates, and are *very * committed to reaching them from what I’ve seen, even at the cost of cutting major features. It’s already one step towards the model you talk about.

    So we’re not really ignoring it all, but maybe you have be on the inside to see the change that is happening. I for one am really optimistic about it. I hope that we’ll communicate to the outside world what we’re doing once the “Grand Plans” are finalized, but I can assure you that we are not standing still, or looking back (other than to see what I mess we’ve been in the past, to change it).

    Again, chat with Giuseppe, he can give you a better idea of what’s happening than I probably.

Comments are closed.