Skip to content

metrics

Jump to bottom
xiaoboluo768 edited this page Jun 9, 2020 · 2 revisions

  • server的计数指标，包含innodb内部的一些度量、全局状态变量、当前系统时间，默认按照变量类型和名称进行排序，数据来源：performance_schema的global_status、memory_summary_global_by_event_name，information_schema.innodb_metrics、NOW()和UNIX_TIMESTAMP()函数

    • 该视图包含以下几个部分的信息
      • 来自performance_schema.global_status表中的全局状态变量名称及其统计值
      • 来自information_schema.innodb_metrics表中的innodb指标变量和统计值
      • 来自performance_schema内存监控中的当前分配的和总的历史分配内存统计值
      • 来自系统当前时间（使用可读格式的unix时间戳）
      • PS：global_status表和innodb_metrics表之间存在一些重复的统计值，在metrics视图中去进行去重
    • 该视图在MySQL 5.7.9中新增

  • metrics视图字段含义如下：

    • Variable_name：度量变量名称，度量变量的类型决定了该数据的来源
      • 对于全局状态变量，该字段值对应global_status表的 VARIABLE_NAME列
      • 对于innodb指标变量，该字段值对应innodb_metrics表的NAME列
      • 对于来自performance_schema中的内存监控指标，使用metrics视图提供的memory_current_allocated代表当前内存使用量，memory_total_allocated代表总历史内存分配量
      • 对于系统时间戳度量，使用now()和unix_timestamp(now())生成的unix格式时间和时间戳
    • Variable_value：度量变量值。度量变量的类型确定了该数据的来源：
      • 对于全局状态变量：该字段对应global_status表的VARIABLE_VALUE列
      • 对于InnoDB指标变量：该字段对应INNODB_METRICS表的COUNT列
      • 对于来自performance_schema中的内存监控指标，当前内存使用量和总历史内存分配量分别对memory_summary_global_by_event_name表的CURRENT_NUMBER_OF_BYTES_USED和SUM_NUMBER_OF_BYTES_ALLOC做求和操作得来
      • 对于当前时间值：使用now()和unix_timestamp(now())生成的unix格式时间和时间戳
    • Type：度量变量类型：
      • 对于全局状态变量：该列值为 'Global Status'
      • 对于InnoDB指标：该列值为 ' InnoDB Metrics - %'，其中％号在输出对应的度量变量指标时，使用INNODB_METRICS表的SUBSYSTEM列值替换再输出（转换函数： CONCAT('InnoDB Metrics - ', SUBSYSTEM) AS Type）
      • 对于performance_schema中监控的内存指标：该列值为 'Performance Schema'
      • 对于当前系统时间：该列值为 'System Time'
    • Enabled：度量变量是否启用
      • 对于全局状态变量：该列值总是显示为 'Yes'
      • 对于InnoDB指标：如果INNODB_METRICS表的STATUS列已启用，则该列值显示为'Yes'，否则为 'No'
      • 对于内存度量：该列值可能的值有NO、YES、PARTIAL（目前，PARTIAL仅用于内存指标，表示未启用所有的内存监控指标，对于performance_schema开头的内存监控指标默认全部启用，无法关闭）
      • 对于当前系统时间：该列值总是显示为 'Yes'

  • 视图定义语句

CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW metrics (
  Variable_name,  Variable_value,  Type,  Enabled
) AS
(
SELECT LOWER(VARIABLE_NAME) AS Variable_name, VARIABLE_VALUE AS Variable_value, 'Global Status' AS Type, 'YES' AS Enabled
  FROM performance_schema.global_status
) UNION ALL (
SELECT NAME AS Variable_name, COUNT AS Variable_value,
      CONCAT('InnoDB Metrics - ', SUBSYSTEM) AS Type,
      IF(STATUS = 'enabled', 'YES', 'NO') AS Enabled
  FROM information_schema.INNODB_METRICS
  -- Deduplication - some variables exists both in GLOBAL_STATUS and INNODB_METRICS
  -- Keep the one from GLOBAL_STATUS as it is always enabled and it's more likely to be used for existing tools.
WHERE NAME NOT IN (
    'lock_row_lock_time', 'lock_row_lock_time_avg', 'lock_row_lock_time_max', 'lock_row_lock_waits',
    'buffer_pool_reads', 'buffer_pool_read_requests', 'buffer_pool_write_requests', 'buffer_pool_wait_free',
    'buffer_pool_read_ahead', 'buffer_pool_read_ahead_evicted', 'buffer_pool_pages_total', 'buffer_pool_pages_misc',
    'buffer_pool_pages_data', 'buffer_pool_bytes_data', 'buffer_pool_pages_dirty', 'buffer_pool_bytes_dirty',
    'buffer_pool_pages_free', 'buffer_pages_created', 'buffer_pages_written', 'buffer_pages_read',
    'buffer_data_reads', 'buffer_data_written', 'file_num_open_files',
    'os_log_bytes_written', 'os_log_fsyncs', 'os_log_pending_fsyncs', 'os_log_pending_writes',
    'log_waits', 'log_write_requests', 'log_writes', 'innodb_dblwr_writes', 'innodb_dblwr_pages_written', 'innodb_page_size')
)
  UNION ALL (
SELECT 'memory_current_allocated' AS Variable_name, SUM(CURRENT_NUMBER_OF_BYTES_USED) AS Variable_value, 'Performance Schema' AS Type,
        IF((SELECT COUNT(*) FROM performance_schema.setup_instruments WHERE NAME LIKE 'memory/%' AND ENABLED = 'YES') = 0, 'NO',
        IF((SELECT COUNT(*) FROM performance_schema.setup_instruments WHERE NAME LIKE 'memory/%' AND ENABLED = 'YES') = (SELECT COUNT(*) FROM performance_schema.setup_instruments WHERE NAME LIKE 'memory/%'), 'YES',
            'PARTIAL')) AS Enabled
  FROM performance_schema.memory_summary_global_by_event_name
) UNION ALL (
SELECT 'memory_total_allocated' AS Variable_name, SUM(SUM_NUMBER_OF_BYTES_ALLOC) AS Variable_value, 'Performance Schema' AS Type,
        IF((SELECT COUNT(*) FROM performance_schema.setup_instruments WHERE NAME LIKE 'memory/%' AND ENABLED = 'YES') = 0, 'NO',
        IF((SELECT COUNT(*) FROM performance_schema.setup_instruments WHERE NAME LIKE 'memory/%' AND ENABLED = 'YES') = (SELECT COUNT(*) FROM performance_schema.setup_instruments WHERE NAME LIKE 'memory/%'), 'YES',
            'PARTIAL')) AS Enabled
  FROM performance_schema.memory_summary_global_by_event_name
) 
  UNION ALL (
SELECT 'NOW()' AS Variable_name, NOW(3) AS Variable_value, 'System Time' AS Type, 'YES' AS Enabled
) UNION ALL (
SELECT 'UNIX_TIMESTAMP()' AS Variable_name, ROUND(UNIX_TIMESTAMP(NOW(3)), 3) AS Variable_value, 'System Time' AS Type, 'YES' AS Enabled
)
ORDER BY Type, Variable_name;
  • 视图查询信息示例
admin@localhost : sys 10:49:59> select * from metrics where type='global status' limit 5;
+----------------------------+----------------+---------------+---------+
| Variable_name              | Variable_value | Type          | Enabled |
+----------------------------+----------------+---------------+---------+
| aborted_clients            | 0              | Global Status | YES    |
| aborted_connects          | 0              | Global Status | YES    |
| binlog_cache_disk_use      | 0              | Global Status | YES    |
| binlog_cache_use          | 1159          | Global Status | YES    |
| binlog_stmt_cache_disk_use | 0              | Global Status | YES    |
+----------------------------+----------------+---------------+---------+
5 rows in set (0.17 sec)

admin@localhost : sys 11:04:01> select * from metrics where type='InnoDB Metrics - transaction' limit 5;
+---------------------------+----------------+------------------------------+---------+
| Variable_name            | Variable_value | Type                        | Enabled |
+---------------------------+----------------+------------------------------+---------+
| trx_active_transactions  | 0              | InnoDB Metrics - transaction | NO      |
| trx_commits_insert_update | 0              | InnoDB Metrics - transaction | NO      |
| trx_nl_ro_commits        | 0              | InnoDB Metrics - transaction | NO      |
| trx_rollbacks            | 0              | InnoDB Metrics - transaction | NO      |
| trx_rollbacks_savepoint  | 0              | InnoDB Metrics - transaction | NO      |
+---------------------------+----------------+------------------------------+---------+
5 rows in set (0.02 sec)

admin@localhost : sys 11:04:21> select * from metrics where type='InnoDB Metrics - server' limit 5;
+-----------------------------------+----------------+-------------------------+---------+
| Variable_name                    | Variable_value | Type                    | Enabled |
+-----------------------------------+----------------+-------------------------+---------+
| buffer_pool_size                  | 2147483648    | InnoDB Metrics - server | YES    |
| innodb_activity_count            | 1330          | InnoDB Metrics - server | YES    |
| innodb_background_drop_table_usec | 0              | InnoDB Metrics - server | NO      |
| innodb_checkpoint_usec            | 0              | InnoDB Metrics - server | NO      |
| innodb_dict_lru_count            | 0              | InnoDB Metrics - server | NO      |
+-----------------------------------+----------------+-------------------------+---------+
5 rows in set (0.02 sec)

admin@localhost : sys 11:04:48> select * from metrics where type='InnoDB Metrics - recovery' limit 5;
+-------------------------+----------------+---------------------------+---------+
| Variable_name          | Variable_value | Type                      | Enabled |
+-------------------------+----------------+---------------------------+---------+
| log_checkpoints        | 0              | InnoDB Metrics - recovery | NO      |
| log_lsn_buf_pool_oldest | 0              | InnoDB Metrics - recovery | NO      |
| log_lsn_checkpoint_age  | 0              | InnoDB Metrics - recovery | NO      |
| log_lsn_current        | 0              | InnoDB Metrics - recovery | NO      |
| log_lsn_last_checkpoint | 0              | InnoDB Metrics - recovery | NO      |
+-------------------------+----------------+---------------------------+---------+
5 rows in set (0.01 sec)

admin@localhost : sys 11:05:12> select * from metrics where type='InnoDB Metrics - os' limit 5;
+-------------------+----------------+---------------------+---------+
| Variable_name    | Variable_value | Type                | Enabled |
+-------------------+----------------+---------------------+---------+
| os_data_fsyncs    | 522            | InnoDB Metrics - os | YES    |
| os_data_reads    | 103070        | InnoDB Metrics - os | YES    |
| os_data_writes    | 833            | InnoDB Metrics - os | YES    |
| os_pending_reads  | 0              | InnoDB Metrics - os | NO      |
| os_pending_writes | 0              | InnoDB Metrics - os | NO      |
+-------------------+----------------+---------------------+---------+
5 rows in set (0.01 sec)

admin@localhost : sys 11:05:31> select * from metrics where type='InnoDB Metrics - buffer_page_io' limit 5;
+----------------------------------+----------------+---------------------------------+---------+
| Variable_name                    | Variable_value | Type                            | Enabled |
+----------------------------------+----------------+---------------------------------+---------+
| buffer_page_read_blob            | 0              | InnoDB Metrics - buffer_page_io | NO      |
| buffer_page_read_fsp_hdr        | 0              | InnoDB Metrics - buffer_page_io | NO      |
| buffer_page_read_ibuf_bitmap    | 0              | InnoDB Metrics - buffer_page_io | NO      |
| buffer_page_read_ibuf_free_list  | 0              | InnoDB Metrics - buffer_page_io | NO      |
| buffer_page_read_index_ibuf_leaf | 0              | InnoDB Metrics - buffer_page_io | NO      |
+----------------------------------+----------------+---------------------------------+---------+
5 rows in set (0.01 sec)

上一篇： memory_global_total,x$memory_global_total视图 |

下一篇： processlist,x$processlist视图

  • 验证、测试、整理：罗小波
  • QQ：309969177
  • 提示：本系列文章的主体结构遵循Oracle MySQL 官方 5.7 手册中，关于information_schema、mysql schema、performance_schema、sys schema的章节结构体系，并额外添加了一些验证、测试数据。鉴于本人精力和能力有限，难免出现一些纰漏，欢迎大家踊跃指正！
Clone this wiki locally