内存分配统计视图 | 全方位认识 sys 系统库

在上一篇《按 file 分组统计视图 | 全方位认识 sys 系统库》中，我们介绍了sys 系统库中按 file 分组统计的视图，本期的内容将为大家介绍内存事件和innodb buffer pool内存分配的统计视图。下面请跟随我们一起开始 sys 系统库的系统学习之旅吧。

PS：

• 对innodb buffer pool的统计视图对数据库的性能影响较大（可能会导致性能陡降），它主要是提供给专业DBA人员做问题分析排查使用，一般情况下不要随意使用

• 对innodb buffer pool的统计视图数据来源于information_schema系统库，考虑到大家可能有MySQL 5.7之前的版本中使用需求，所以本文中特意列出了对innodb buffer pool的统计视图的select语句文本

01.innodb_buffer_stats_by_schema,x$innodb_buffer_stats_by_schema

按照schema分组的 InnoDB buffer pool统计信息，默认按照分配的buffer size大小降序排序--allocated字段。数据来源：information_schema.innodb_buffer_page

视图select语句文本如下：

# 不带x$前缀的视图select语句文本
SELECT IF(LOCATE('.', ibp.table_name) = 0, 'InnoDB System', REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', 1), '`', '')) AS object_schema,
  sys.format_bytes(SUM(IF(ibp.compressed_size = 0, 16384, compressed_size))) AS allocated,
  sys.format_bytes(SUM(ibp.data_size)) AS data,
  COUNT(ibp.page_number) AS pages,
  COUNT(IF(ibp.is_hashed = 'YES', 1, NULL)) AS pages_hashed,
  COUNT(IF(ibp.is_old = 'YES', 1, NULL)) AS pages_old,
  ROUND(SUM(ibp.number_records)/COUNT(DISTINCT ibp.index_name)) AS rows_cached
FROM information_schema.innodb_buffer_page ibp
WHERE table_name IS NOT NULL
GROUP BY object_schema
ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC;

# 带x$前缀的视图select语句文本
SELECT IF(LOCATE('.', ibp.table_name) = 0, 'InnoDB System', REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', 1), '`', '')) AS object_schema,
  SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) AS allocated,
  SUM(ibp.data_size) AS data,
  COUNT(ibp.page_number) AS pages,
  COUNT(IF(ibp.is_hashed = 'YES', 1, NULL)) AS pages_hashed,
  COUNT(IF(ibp.is_old = 'YES', 1, NULL)) AS pages_old,
  ROUND(IFNULL(SUM(ibp.number_records)/NULLIF(COUNT(DISTINCT ibp.index_name), 0), 0)) AS rows_cached
FROM information_schema.innodb_buffer_page ibp
WHERE table_name IS NOT NULL
GROUP BY object_schema
ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC;

下面我们看看使用该视图查询返回的结果。

# 不带x$前缀的视图
admin@localhost : sys 06:15:41> select * from innodb_buffer_stats_by_schema;
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| object_schema | allocated  | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| InnoDB System | 23.73 MiB  | 21.76 MiB |  1519 |            0 |        24 |      21474 |
| mysql        | 240.00 KiB | 14.57 KiB |    15 |            0 |        15 |        179 |
| xiaoboluo    | 128.00 KiB | 38.93 KiB |    8 |            0 |        5 |        982 |
| sys          | 16.00 KiB  | 354 bytes |    1 |            0 |        1 |          6 |
| 小萝卜        | 16.00 KiB  | 135 bytes |    1 |            0 |        1 |          3 |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
5 rows in set (0.43 sec)

# 带x$前缀的视图
admin@localhost : sys 06:15:54> select * from x$innodb_buffer_stats_by_schema;
+---------------+-----------+----------+-------+--------------+-----------+-------------+
| object_schema | allocated | data    | pages | pages_hashed | pages_old | rows_cached |
+---------------+-----------+----------+-------+--------------+-----------+-------------+
| InnoDB System |  24887296 | 22809628 |  1519 |            0 |        24 |      21498 |
| mysql        |    245760 |    14917 |    15 |            0 |        15 |        179 |
| xiaoboluo    |    131072 |    39865 |    8 |            0 |        5 |        982 |
| sys          |    16384 |      354 |    1 |            0 |        1 |          6 |
| 小萝卜        |    16384 |      135 |    1 |            0 |        1 |          3 |
+---------------+-----------+----------+-------+--------------+-----------+-------------+
5 rows in set (0.42 sec)

 视图字段含义如下：

• object_schema：schema级别对象的名称，如果该表属于Innodb存储引擎，则该字段显示为InnoDB System，如果是其他引擎，则该字段显示为每个schema name.<