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)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31480688/viewspace-2671963/，如需轉載，請註明出處，否則將追究法律責任。