Thursday, October 17, 2013

A little-mentioned feature on the interwebs regarding MySQL is the InnoDB monitors.

It has been around for a long time.

http://dev.mysql.com/doc/refman/5.6/en/innodb-monitors.html

You have to create varying tables to enable the monitoring.  You only want to do this when your system can withstand a performance degradation.

When you are trying to improve performance or want a look under the hood, the monitors are valuable.

Monitors provide rich information about InnoDB such as File I/O, Locks, and Cardinality of indexes.  They can help to provide a shape of the overall activity on your InnoDB tables.

The amount of information can be overwhelming when you are in a scenario where performance is degrading rapidly and you are on-call to remedy the degradation of service.

The most common issue is lock contention where more than 2 clients attempt to update and read the same row.  This is sometimes unavoidable, like having a conversation where more than two people are involved.  The natural remedy is to have lock waits timeout at half the time the user expects a result (1.5 seconds), which is configurable in my.cnf (or my.ini on windows).  Then the application must retry the transaction if it faults.  Unfortunately retry-code is not part of many of the standard database libraries and must be incorporated by the developers.  In short time, the frameworks could handle for this case.

You can also replay the binary log on a server with these monitors enabled to dig deeper into a past performance degradation.

The monitors are powerful diagnostics built-in to MySQL.