information_schema.innodb_metrics表

information_schema.innodb_metrics表，就像是一个InnoDB性能和资源相关项的计数器。 log_lsn_current和log_lsn_last_flush当前状态 mysql>  select name,subsystem,count,type,status,comment from innodb_metrics where name in ('log_lsn_current','log_lsn_last_flush');    +--------------------+-----------+-------+-------+----------+-------------------+ | name               | subsystem | count | type  | status   | comment           | +--------------------+-----------+-------+-------+----------+-------------------+ | log_lsn_last_flush | recovery  |     0 | value | disabled | LSN of Last flush | | log_lsn_current    | recovery  |     0 | value | disabled | Current LSN value | +--------------------+-----------+-------+-------+----------+-------------------+ 2 rows in set (0.00 sec) 可以通过参数来开启监控项： mysql> show variables like '%monitor%'; +--------------------------+-------+ | Variable_name            | Value | +--------------------------+-------+ | innodb_monitor_disable   |       | | innodb_monitor_enable    |       | | innodb_monitor_reset     |       | | innodb_monitor_reset_all |       | +--------------------------+-------+ 4 rows in set (0.00 sec) 开启单个监控项： mysql> set global innodb_monitor_enable=log_lsn_current; Query OK, 0 rows affected (0.00 sec) 开启多个监控项，但是可能不那么精准 mysql> set global innodb_monitor_enable='log_lsn_%'; Query OK, 0 rows affected (0.00 sec) 开启所有监控项 mysql> set global innodb_monitor_enable=all; Query OK, 0 rows affected (0.00 sec) 也可以通过配置文件，多个监控项逗号分隔即可： [mysqld] innodb_monitor_enable=log_lsn_current,log_lsn_last_flush 虽然生效了多个监控项，但是innodb_monitor_enable显示不完全 mysql>  select name,subsystem,count,type,status,comment from innodb_metrics where name in ('log_lsn_current','log_lsn_last_flush'); +--------------------+-----------+-----------+-------+---------+-------------------+ | name               | subsystem | count     | type  | status  | comment           | +--------------------+-----------+-----------+-------+---------+-------------------+ | log_lsn_last_flush | recovery  | 451523543 | value | enabled | LSN of Last flush | | log_lsn_current    | recovery  | 451523543 | value | enabled | Current LSN value | +--------------------+-----------+-----------+-------+---------+-------------------+ 2 rows in set (0.00 sec) mysql> show variables like '%monitor%'; +--------------------------+-----------------+ | Variable_name            | Value           | +--------------------------+-----------------+ | innodb_monitor_disable   |                 | | innodb_monitor_enable    | log_lsn_current | | innodb_monitor_reset     |                 | | innodb_monitor_reset_all |                 | +--------------------------+-----------------+ 4 rows in set (0.00 sec) 关闭监控项 mysql>  select name,subsystem,count,type,status,comment from innodb_metrics where name in ('log_lsn_current','log_lsn_last_flush'); +--------------------+-----------+-----------+-------+---------+-------------------+ | name               | subsystem | count     | type  | status  | comment           | +--------------------+-----------+-----------+-------+---------+-------------------+ | log_lsn_last_flush | recovery  | 495909560 | value | enabled | LSN of Last flush | | log_lsn_current    | recovery  | 495910375 | value | enabled | Current LSN value | +--------------------+-----------+-----------+-------+---------+-------------------+ 2 rows in set (0.06 sec) mysql> set global innodb_monitor_disable=log_lsn_current; Query OK, 0 rows affected (0.00 sec) mysql>  select name,subsystem,count,type,status,comment from innodb_metrics where name in ('log_lsn_current','log_lsn_last_flush'); +--------------------+-----------+-----------+-------+----------+-------------------+ | name               | subsystem | count     | type  | status   | comment           | +--------------------+-----------+-----------+-------+----------+-------------------+ | log_lsn_last_flush | recovery  | 510811720 | value | enabled  | LSN of Last flush | | log_lsn_current    | recovery  | 510811720 | value | disabled | Current LSN value | +--------------------+-----------+-----------+-------+----------+-------------------+ 2 rows in set (0.00 sec) 重置监控项数值，需要先disable监控项，否则不会生效。 innodb_monitor_reset会重置COUNT，MAX_COUNT_RESET，MIN_COUNT_RESET innodb_monitor_reset_all会重置所有值 mysql>  set global innodb_monitor_disable = log_lsn_last_flush; Query OK, 0 rows affected (0.00 sec) mysql> set global innodb_monitor_reset=log_lsn_last_flush; Query OK, 0 rows affected (0.00 sec) mysql> select * from innodb_metrics where name='log_lsn_last_flush'\G *************************** 1. row ***************************            NAME: log_lsn_last_flush       SUBSYSTEM: recovery           COUNT: 0       MAX_COUNT: 605981929       MIN_COUNT: 570811646       AVG_COUNT: NULL     COUNT_RESET: 0 MAX_COUNT_RESET: NULL MIN_COUNT_RESET: NULL AVG_COUNT_RESET: NULL    TIME_ENABLED: 2019-12-06 09:23:06   TIME_DISABLED: 2019-12-06 09:24:22    TIME_ELAPSED: 76      TIME_RESET: NULL          STATUS: disabled            TYPE: value         COMMENT: LSN of Last flush 1 row in set (0.00 sec) mysql> set global innodb_monitor_reset_all = log_lsn_last_flush; Query OK, 0 rows affected (0.00 sec) mysql> select * from innodb_metrics where name='log_lsn_last_flush'\G *************************** 1. row ***************************            NAME: log_lsn_last_flush       SUBSYSTEM: recovery           COUNT: 0       MAX_COUNT: NULL       MIN_COUNT: NULL       AVG_COUNT: NULL     COUNT_RESET: 0 MAX_COUNT_RESET: NULL MIN_COUNT_RESET: NULL AVG_COUNT_RESET: NULL    TIME_ENABLED: NULL   TIME_DISABLED: NULL    TIME_ELAPSED: NULL      TIME_RESET: NULL          STATUS: disabled            TYPE: value         COMMENT: LSN of Last flush 1 row in set (0.00 sec)