Hack MySQL Blog

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.