StyleFeeder Tech Blog

I’ve been having some odd performance issues with some of my MySQL queries since moving over to InnoDB.  I picked up the new O’Reilly title High Performance MySQL to try to track down the problem.  The book in turn recommends a couple of pretty cool monitoring/reporting tools that summarize a lot of the MySQL variable displays in a more friendly format.

Innotop (more info here) is sort of like the friendly unix top command, but instead for database status.  There are pages to show buffer statuses, deadlocks, i/o status, current queries, and lots more.  They all update on screen, at configurable increments.

MySQLReport from hackmysql.com runs a few status commands and formats them nicely on screen in a nice grouped format.  This guide summarizes the sections, which include more detail on many of the same things that innotop covered.  I find the sections on SELECT types, and InnoDB Buffer Pool use, are especially useful to me.

Using the command type summary, we discovered an inordinate number of com_rollback calls in our main database, which we were able to reduce by using this technique.  The root cause was Hibernate‘s love of transactions, combined with connection pooling.  A simple driver parameter seems to clear it up.