Proposal to remove InnoDB Monitor tables

Before there was information_schema and performance_schema, there were InnoDB Monitor tables.

What the monitor tables are, is a way of communicating directly to the InnoDB storage engine by creating a table of a special name. Additional debugging information is then written to MySQL’s error log. For example:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

/* view to the error log */

DROP TABLE innodb_monitor;

There’s a number of problems with this approach:

  1. By using a magic table name, there is no immediate feedback for typos or spelling errors in monitor table names. The user has to wait to see if the error log is written to, and then debug if not.
  2. The monitors will continually write to the error log as long as the magic table still exists. This can reduce the error log readability.
  3. Parsing and using the error log is non-trivial for automated tooling. It also requires local filesystem access, and the log-file path can be changed by configuration.
  4. It is difficult to apply privileges to the creation of magic tables, other than those required to CREATE or DROP tables. This is not specifically a security risk (local file system access is still required to view the log file), but semantically it makes more sense for these to require either the PROCESS or SUPER privilege.
  5. information_schema is the SQL Standard for meta-data access and should be used wherever possible.

Deprecation Plan

The deprecation plan proposed for MySQL 5.7 is:

  • innodb_monitor. To be replaced with SET GLOBAL innodb_monitor=ON|OFF.
  • innodb_lock_monitor. To be replaced with SET GLOBAL innodb_lock_monitor=ON|OFF.
  • innodb_tablespace_monitor. To be removed. information_schema will become the recommended alternative.
  • innodb_table_monitor. To be removed. information_schema will become the recommended alternative.
  • innodb_mem_validate. To be removed. This depends on UNIV_MEM_DEBUG, which is not normally enabled even in debug builds.

So in all cases the magic table name (point #1 above) is removed, but in some cases the monitors remain to offer compatibility where the corresponding information_schema functionality may differ dramatically in meta-data available.

Conclusion

As with our other upcoming changes planned, we are seeing community feedback as to how this change will affect users.

Do you use the InnoDB Monitors?
Will the deprecation plan affect you negatively?

Please leave a comment, or get in touch with me. Thanks!

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.

  • On balance, I see this as a positive. The only thing you lose by replacing innodb_monitor with a global variable is persistence across restarts. And the persistent nature of the monitors was a downside as often as it was useful.

    The other monitor tables (lock, table, tablespace) were poorly documented. We know how to turn them on and off, but the meaning of the output is pretty much a mystery.

    If I recall, the tablespace monitor never worked with file-per-table, it only outputs info about the global tablespace. Given that file-per-table is now the default, this makes the tablespace monitor less useful.

    Side comment: I think there needs to be clarification about which views reside in INFORMATION_SCHEMA and which belong in PERFORMANCE_SCHEMA. There are unexpected cases, like InnoDB locks and buffer pool state, and thread pool state, etc.

    I would think that P_S contains runtime state that changes dynamically based on server usage, whereas I_S contains static metadata that changes only when we issue DDL.

    • Bill: you are correct in your description of the intended divide between the two: I_S is static meta data, P_S is runtime/dynamic.

      I will take your feedback back to the development team and see if they have any plans on moving some of the InnoDB tables currently in I_S. They were introduced in 5.1-plugin and predate P_S.

  • Good thing to modernize these. I seldomly use the monitor tables (partly because it did cause a crash once (Bug #69641)). Afaik the i_s tables didn include the tablespace 0 (Bug #69323), so that’s something which might need to be fixed to make complete solution. Another thing which needs some cleanup/removal is the isam log (Bug 68623).

  • Marko Mäkelä

    Bill Karwin, I think that you may be mistaken in your claim: “The only thing you lose by replacing innodb_monitor with a global variable is persistence across restarts.”

    As far as I understand the InnoDB code, the magic table names are only being checked when executing CREATE TABLE or DROP TABLE. If you create one of the magic tables and restart the server, the magic should be gone.

    With a global variable, you could pass a command-line option or a configuration file parameter to enable the feature from the very beginning. This is how innodb_adaptive_hash_index works today.

    A somewhat related question is whether configuration parameters should live in transactional tables. One major reason against that is that you could be out of luck if the server refuses to start because of an incorrect or inconsistent option. (You would not be able to undo the change if you were unable to edit the configuration before the server has started.)

    • Thanks for the tip! I just tested it to see for myself, and you are correct. The innodb_monitor table doesn’t do anything after a restart.