Hack MySQL

Archive for the ‘MySQL’ Category

mysqlreport v3.2 released

without comments

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.

Written by Daniel Nichter

May 26th, 2007 at 2:41 pm

Posted in MySQL,mysqlreport

mysqlreport v3.1 released

without comments

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.

Written by Daniel Nichter

April 26th, 2007 at 11:11 am

Posted in MySQL,mysqlreport

Updated: Non-technical Guide to Isolating Slow MySQL Queries

without comments

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.

Written by Daniel Nichter

April 11th, 2007 at 2:43 pm

MySQL log analyzer – mysqlsla v1.5 finally done

without comments

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.

Written by Daniel Nichter

April 6th, 2007 at 11:34 am

Posted in MySQL,mysqlsla

Checking MySQL databases/tables for unused indexes: mysqlidxchk

without comments

Peter Zaitsev was kind enough to share with me an idea for a script which I have now finished. mysqlidxchk checks MySQL databases/tables for unused indexes. Version 1.0 with documentation and guide is available for download: mysqlidxchk v1.0 (Linux or Windows).

Please report any bugs or problems you experience.

Written by Daniel Nichter

March 31st, 2007 at 2:16 pm

Posted in MySQL,mysqlidxchk