Monitoring Long Running MySQL Queries with Nagios

November 15, 2010

We’ve been using Nagios as part of our monitoring infrastructure from some time now and it has been very useful to monitor our databases.

To look at long running queries (those whose running time is measured in seconds) we use the show processlist command. Here is what the output looks like on an empty machine.

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 65 | root | localhost | NULL | Query   |    0 | NULL  | show processlist | 
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.02 sec)

Writing a script to read this result set, filter for the slow ones (see the Time column), is easy enough. That was version 1.

However, we all have the occasional maintenance queries which take minutes or hours to run. And with our first solution we would get emergency pages every so often. Annoying; Especially since those run at night.

We opted to add meta-data to queries which we’re expected to be slow using the nifty MySQL comment syntax. From the documentation.

These [comments] enable you to write code that includes MySQL extensions, but is still portable, by using comments of the following form /*! MySQL-specific code */. […] If you add a version number after the “!” character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number.

Take the SQL snippet.

CREATE /*!32302 TEMPORARY */ TABLE t (a INT);

The TEMPORARY keyword will be viewable only to MySQL version 3.23.02 and up. All previous versions of MySQL would see a simple create table.

And since those comments are available at run-time via the show [full] processlist command this feature becomes a simple and light-weight way to add meta-data to queries.

Plugin number 2 looks for a given tag passed via a comment and our maintenance queries now look like.

update huge_table set ... where some_crazy_condition /*!99999 long_running */

To put this to work in your system, simply install the check_mysql_long_running_queries plugin.