Posted on 7 Comments

microslow patch – backwards compatibility

Years ago Percona created the microslow patch, allowing greater granularity as well as additional detail to be captured in the slow query log. Brilliantly useful!

Just one problem, and for OurDelta we’ve been pondering how it can best be resolved: the original patch changes long_query_time to mean microseconds (millionths of a second) rather than seconds, so an existing config file would have to be modified.

One possible solution, which is currently in a branch preparing for the next OurDelta (d7) build, checks the input value and if it’s <600 it presumes seconds were specified and multiplies accordingly. This works both from cmdline/my.cnf as well as when using SET. The SET actually also accepts fractional seconds (i.e., a float). So the allowable range becomes 600 microseconds upward, with 1-599 seconds specified in two possible ways. I understand that companies like eBay wanted to check for queries taking 50 milliseconds; that'd be 50000. Ok. What I (personally) like about this method is that it's backward compatible with both stock MySQL builds as well as Percona builds, while automatically resolving the backward compatibility issue if you drop in an OurDelta build. An existing configuration will work as expected. However, it's still a bit of a hack. So this is a call for input... how do you think it should work, or do you have a fabulously neat solution to the whole problem? Just a few points that have already been brought up: We could have the cmdline/my.cnf accept fractional seconds also. We could (also) allow unit identifiers like ms and us/mu to indicate milliseconds and microseconds. Both ideas would break (or rather, not resolve) backward compatibility with some builds out there; it would be backward compatible with stock MySQL builds though. Finally, a related issue is how the setting should get displayed in SHOW VARIABLES. The original patch shows microseconds. Opinions and ideas, please! Thanks!

Posted on 7 Comments

7 thoughts on “microslow patch – backwards compatibility

  1. I hate config changes that “just happen” but as a developer I also hate keeping hacks around and other things for legacy purposes.

    To me, I would make the change and try to highlight it in a quick “MUST READ” document somehow. Perhaps on “make install” or next to a download link… also in the changelog/NEWS file. But that’s the developer in me speaking.

    The daemon administrator in me would want some intelligent thing to ask if I want to update my configuration or seamlessly upgrade. At the same time I don’t want to keep around legacy options that will eventually get deprecated anyway. That’s a pet peeve too.

    So I’ve gone around in a circle here now.

    I think ultimately I’d say “make the change” and hope people read/understand the changes.

  2. What’s wrong with making it seconds as a float?

  3. But the code doesn’t deal with this right now… some quirky stuff going on in there.

  4. We just got hit by this actually.

    50GB slow query log on an out of production MySQL box running a new percona build 🙂

    It should be a float.

    Existing mysql users receive priority, and the value is assumed to be seconds by default.

    If you want 300 ms you would just use 0.300 .

    If you’re on 5.1 you can at least change this stuff on the fly.

    I HATE that it has taken to 5.1 to fix some of this stuff :-/

    I wish there were a 5.0 patch or this bug.

  5. Kevin: it’s done, it’s a float, fully runtime configurable, and in the 5.0.67.d7 OurDelta build. I spent some time coding on that, most enjoyable.

    I did find another issue, slow queries get logged regardless of whether they’re executed; so if you set the time very low, it will even catch syntax errors. I intend to fix that as I don’t think it’s desirable… again it clutters. See https://bugs.launchpad.net/ourdelta/+bug/297060

  6. Cool. I saw that after I posted this.

    Actually, the ability to log slow queries AND errors seems like a good feature.

    This way you could see if your application is generating invalid SQL without turning on full logging.

  7. […] to everybody who responded to microslow patch – backwards compatibility. After some more poking around in the code, I’ve managed to resolve the issue and it now both […]

Comments are closed.