innodb_buffer_stats_by_schema,x$innodb_buffer_stats_by_schema - xiaoboluo768/mysql-system-schema GitHub Wiki
-
按照schema分组的 InnoDB缓冲区统计信息，默认按照分配的buffer size大小降序排序--allocated字段。数据来源：information_schema.innodb_buffer_page
-
innodb_buffer_stats_by_schema和x$innodb_buffer_stats_by_schema视图字段含义如下：
- object_schema：schema级别对象的名称，如果该表属于Innodb存储引擎，则该字段显示为InnoDB System，如果是其他引擎，则该字段显示为每个schema name.
- allocated：当前已分配给schema的总字节数
- data：当前已分配给schema的数据部分使用的字节总数
- pages：当前已分配给schema总页数
- pages_hashed：当前已分配给schema的自适应hash索引页总数
- pages_old：当前已分配给schema的旧页总数（位于LRU列表中的旧块子列表中的页数）
- rows_cached：buffer pool中为schema缓冲的总数据行数
-
视图定义语句
# innodb_buffer_stats_by_schema
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW innodb_buffer_stats_by_schema (
object_schema, allocated, data, pages, pages_hashed, pages_old, rows_cached
) AS
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$innodb_buffer_stats_by_schema
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW x$innodb_buffer_stats_by_schema (
object_schema, allocated, data, pages, pages_hashed, pages_old, rows_cached
) AS
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;
- 视图查询信息示例
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)
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)
上一篇： host_summary_by_statement_type,x$host_summary_by_statement_type视图 |
下一篇： innodb_buffer_stats_by_table,x$innodb_buffer_stats_by_table视图