Hack MySQL Blog

Yesterday I released mysqlreport v3.1. No large changes:

• Added support for table_open_cache system var (MySQL 5.1.3+).
• Removed default assignments for $mycnf{’host’}, $mycnf{’port’}, and $mycnf{’socket’}.
• Connecting to MySQL now tries socket if one is given and actually a socket, else TCP/IP host if one is given, else generic “host=localhost” which relies on the MySQL client lib to do something useful.
• Updated help for clarity.
• Fixed bug: spelled “fsync” wrong.

I also updated The Guide To Understanding mysqlreport to include InnoDB reports and the mysqlreport documentation.

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.