Hack MySQL News

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.

The hiatus is over; I am reasonably settled at my new home. Therefore, HackMySQL.com is back to normal operation.

Parlez-vous le français? Je peux parler le français mais pas bien. Si vous voulez, on peut essayer à parler en français. Merci de votre patience.

I am moving halfway across the world. Therefore, for the remainder of July and all of August and September HackMySQL.com will not be actively maintained. This means no updates to scripts or articles and very limited response to feedback.

The Guide To Understanding mysqlreport has been updated. As many people have noticed, since mysqlreport v3.2, Key Write and Read ratio went away, replaced by Write and Read hit percentage. I figured that “99.87%” was more intuitive than “0.0013″.

hackmysql.com is moving to (mt) Media Temple this weekend. Because of Memorial Day, I suspect that site traffic will be really low, so perhaps no one will even notice.

mysqlreport v3.2 has been released. Mark Leith’s post, Aggregating SHOW STATUS Output, reminded me that a user had asked me a few months ago to do this for mysqlreport. I forgot until Mark’s post.

I have finished implementing this feature with mysqlreport. The new version has three new command line options: –relative (-r), –report-count (-c), and –detach. All of these are explained in the doc. This feature required substantial alterations to the script, so please tell me if anything doesn’t work.

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.