转自:

INNODB监控开关: 

标准监控:

 打开标准的innodb监控

    CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

 关闭标准的innodb监控  

    DROP TABLE innodb_monitor;

 通过建表的方式会有一个告警信息:

 mysql> CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

 Query OK, 0 rows affected, 1 warning (0.04 sec)

 mysql> show warnings;

 +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level   | Code | Message                                                                                                                                                                                                        |

+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Warning |  131 | Using the table name innodb_monitor to enable diagnostic output is deprecated and may be removed in future releases. Use INFORMATION_SCHEMA or PERFORMANCE_SCHEMA tables or SET GLOBAL innodb_status_output=ON. |

+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

如果是5.6.16可以使用

    set GLOBAL innodb_status_output=ON;打开标准监控,会把信息写入到日志文件

锁监控:

 打开innodb的锁监控:

    CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;    

 关闭锁监控:

    DROP TABLE innodb_lock_monitor;

   

    5.6.16可以使用:  --两个都需要打开

    set GLOBAL innodb_status_output=ON;

    set GLOBAL innodb_status_output_locks=ON;

   

 

表空间监控:  

  打开innodb表空间监控:

  CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;

  关闭:

  DROP TABLE innodb_tablespace_monitor;

表监控:

     打开innodb表监控:

     CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;

     关闭:

     DROP TABLE innodb_table_monitor;