Hack MySQL News

mysqlsla v2 is finally “done” and released. About 3 months ago, when v1.8 was released, I said it would be coming “soon,” but time just flew by and here we are. Oh well. In any case, the v1 branch is dead to me and v2 is all the rave (at least for me). If you don’t care about the differences and all you want is your default top 10 report from a slow log, for example, then all you need to know is: mysqlsla -lt slow SLOW_LOG

For those interested in what has changed to warrant a new major version number, here’s the briefing of changes/overhauls:

1. Almost ALL new command line options (–log-type a.k.a. -lt is the most important); see the documentation
2. Customizable reports. In v1 the report was hard-coded. Now with -rf FILE you can format your own report. See the doc on reports.
3. FULL filtering and sorting. Somewhere around v1.7 (if I remember correctly) filtering based on things like the connection ID, etc. was introduced. v2 is more abstracted and allows more complex filtering on any “meta-property” available in the log (and sorting by most of them, too). See the doc on filters.
4. Replays to compact fat text log files into super-compact binary files.
5. Slightly better query abstraction.
6. Full support for microslow patched slow logs.
7. User-defined logs so that, one day, we may begin parsing, filtering, analyzing and sorting custom logs from things like MySQL Proxy.
8. And in general, what I called the “mysqlsla v2 Library: 6 long, detailed pages documenting just about single, tiny aspect of mysqlsla (plus with the “installer” you get a mysqlsla man page, too).

As this is pretty much an entire code overhaul, please please report bugs, problems, suggestions and whatever. Please also send me your log files: slows, generals, binaries. They help me tremendously because I cannot sit around inventing up all the weird kinds of stuff I’ve seen in other people’s logs.

And as always, thank you all for your support, and thanks to those people who provided feedback, patches and such.

mysqlsla v1.8 is available at:
http://hackmysql.com/scripts/mysqlsla-1.8-DEBUG
I am releasing it publicly without updating the mysqlsla web page or documentation because, instead, I am waiting until I finish mysqlsla v2. After working with v1.8 I realized the code needed a major re-think and overhaul. v2 will reflect this and will be a far superior log hacking and analyzing tool, capable of far more than v1.8 is now.

But for now, 1.8 fixes several good (or bad?) bugs:

• –only-hosts did not work for general logs
• Multi-line comments using /* */ caused everything after the first line to be ignored in raw logs
• “Change user” commands were not handled in general logs
• CHANGE, DROP and RESET statements were filtered out
• A certain variant of the Connect command in general logs was not handled
• A few more SQL capitalization/beautifications were added

If you use mysqlsla, you should really get v1.8 due to these bug fixes. And if you have used mysqlsla before and found it didn’t do what you needed: tell me because v2 open to suggestions. My goal for v2 is to make it the MySQL log hacking tool, capable of parsing, ordering, analyzing, sorting, etc. a MySQL log in any way possible and then to serve as a liaison to other scripts, such as Baron’s mk-query-profiler; e.g. have mysqlsla process and filter a log and then feed SQL statements to mk-query-profiler or any other script. Currently, v1.8 is too stuck in its own ways to help other scripts. Such selfishness shall be expunged.

mysqlsla v1.7a has been released. It fixes a strange bug for general logs with connection IDs greater than 10 million.

mysqlsla v1.7 is ready because v1.6 (and v1.5) had a bug: numbers greater than 999 were printed wrong; internally, though, they were still used correctly. Therefore: do not use v1.6.

Also, v1.7 has a –milliseconds option to make time values less than 1 second print as millisecond values. This option is necessary for slow logs from servers patched with the slow query log millisecond patch.

mysqlsla v1.6 is ready. It has four new options to allow better isolation/filtering of queries: –only-databases, –only-users, –only-hosts, –only-ids. Each option limits the queries under analysis to their respective properties (i.e., “–only-users root” only analyzes queries executed by root). See the documentation for which options work with which kinds of logs.

It’s strange what articles on one’s site become “popular.” I wrote Non-technical Guide to Isolating Slow MySQL Queries some time ago when I was an internal system admin at a data center. I wasn’t “customer-facing” but I still got requests from customers who wanted me to look at their MySQL server. Since that wasn’t my primary responsibility, I had to enlist their help to save me time. Hence, I wrote this article to help not-technically-inclined customers through the first few steps.

My webstats have shown this page to be hit more often than I would have expected. Consequently, I felt that I should update it, so I did.

mysqlsla (MySQL Statement Log Analyzer) v1.5 is finally done. Some people may recall a semi-release of v1.4 a few months ago, but that version wasn’t documented or otherwise mentioned at hackmysql.com.

Now, the real next release of mysqlsla is finished, and with documentation too! (However, I have removed the guide for now; it still needs to be completely rewritten). From the old v1.3 to the present, a lot has changed due to a lot of feedback, suggestions, problems, and bugs reported from users. Essentially, the script has been completely written, but here is a brief list of the changes:

• COMPLETE redesign from v1.3: more simple to use and more informative
• Log types are now mutually exclusive (but multiple logs of same type can still be combined)
• Captures USE statements for querys when possible (to make EXPLAIN work)
• –time-all-queries only works with general and slow logs
• Nothing is filtered by default
• Auto-filter -*,+SELECT,+USE enabled for –time-all-queries and –time-each-query unless overriden by –filter
• Handles compound statements in slow logs
• Mimicking of mysqldumpslow removed; slow log values now “automatic” when parsing a slow log
• Removed options: –order, –correlate, –hide, –rows-read, –mysqldumpslow, –frequency (now called Count)
• Added options: –debug, –flat, –examples, –sort, –databases, –distribution, –mp, –no-report, –np, –nth-percent
• Improved log parsing routines
• Handles DBI/MySQL errors cleanly instead of dumping them to stderr
• Greatly minimized memory usage (can now parse multi-gig logs without running out of memory)

Thank you to all those who helped to improve this script. Please, keep submitting suggestions, bugs, problems, etc.

Although I have not updated the website, documentation, or how to for mysqlsla, I am tossing mysqlsla v1.4 “out there” to view or wget at:

http://hackmysql.com/scripts/mysqlsla-1.4

I don’t like to release new versions without having everything else updated too, but for various reasons I’m making an exception this time.

mysqlsla v1.4 is much better than v1.3 and a lot has changed. So much has changed that the entire script was essentially re-written. Without an updated doc or how to, I suggest starting with the –help command line op, then contact me if you have specific questions or issues. I will, eventually, update the website, doc, how to, and officially package this new version.