在上一篇《按 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.<