ITPub博客

首页 > 数据库 > MySQL > information_schema.innodb_metrics表

information_schema.innodb_metrics表

原创 MySQL 作者:水逸冰 时间:2020-01-04 20:13:22 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

全部评论
精通oracle,mysql和linux,热衷于研究数据库,擅长shell和Python自动化运维。VX:18302174682

注册时间:2017-08-05

  • 博文量
    100
  • 访问量
    106143