Ok, so I knew about innodb_table_monitor and innodb_tablespace_monitor. I’ve tried them before, looked at the output and given up, partly because it didn’t serve the purpose I wanted it to at the time, and also because it’s format was a little cryptic.
What I didn’t know was there are actually 4 monitors via this “create table functionality”. You can also do innodb_monitor which is the same as SHOW INNODB STATUS, and you can also do innodb_lock_monitor .
Another thing I didn’t know is that these commands don’t send the output just once, it’s on a timer. I’ve found the timers to be different. For innodb_monitor you get every 15 sec, as well as a nice line given time of averages which seems to always say 16 seconds.
===================================== 070601 15:11:25 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds
innodb_table_monitor was in this case 65 seconds??
=========================================== 070601 15:13:33 INNODB TABLE MONITOR OUTPUT =========================================== ... =========================================== 070601 15:14:37 INNODB TABLE MONITOR OUTPUT ===========================================
The fact it goes to the MySQL error log is annoying, rather then a log for Innodb Monitoring. It’s much easier to script when you know the content coming into a file and can control it, with the MySQL error log you can’t.
More info at SHOW ENGINE INNODB STATUS and the InnoDB Monitors .
So you have to read the fine print in the MySQL Manual to get these things. If you also read the manual page to completion, which I did after I wrote this initial post you find yet another option innodb_status_file. If this is set for the server, it writes the output of SHOW ENGINE INNODB STATUS to a file every 15 seconds. What about the other outputs to file? Seems a need to be consistent here.
Things to ponder more.
Now were are all those useful tools that parse this output?
Is this output worth parsing and monitoring at any regularity?
Updated: 06/09
Two additional references that help in the understanding of these monitors are:
- SHOW INNODB STATUS walk through – July 2006
- InnoDB Table and Tablespace Monitors – Jan 2009
