1-- Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved. 2-- 3-- This program is free software; you can redistribute it and/or modify 4-- it under the terms of the GNU General Public License as published by 5-- the Free Software Foundation; version 2 of the License. 6-- 7-- This program is distributed in the hope that it will be useful, 8-- but WITHOUT ANY WARRANTY; without even the implied warranty of 9-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 10-- GNU General Public License for more details. 11-- 12-- You should have received a copy of the GNU General Public License 13-- along with this program; if not, write to the Free Software 14-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA 15 16-- 17-- View: x$innodb_buffer_stats_by_schema 18-- 19-- Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE 20-- table, aggregating by schema 21-- 22-- mysql> select * from x$innodb_buffer_stats_by_schema; 23-- +--------------------------+-----------+--------+-------+--------------+-----------+-------------+ 24-- | object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached | 25-- +--------------------------+-----------+--------+-------+--------------+-----------+-------------+ 26-- | mem30_trunk__instruments | 1769472 | 522272 | 108 | 108 | 108 | 3885 | 27-- | InnoDB System | 704512 | 360054 | 43 | 43 | 43 | 862 | 28-- | mem30_trunk__events | 81920 | 22125 | 5 | 5 | 5 | 229 | 29-- +--------------------------+-----------+--------+-------+--------------+-----------+-------------+ 30-- 31 32CREATE OR REPLACE 33 ALGORITHM = TEMPTABLE 34 DEFINER = 'root'@'localhost' 35 SQL SECURITY INVOKER 36VIEW x$innodb_buffer_stats_by_schema ( 37 object_schema, 38 allocated, 39 data, 40 pages, 41 pages_hashed, 42 pages_old, 43 rows_cached 44) AS 45SELECT IF(LOCATE('.', ibp.table_name) = 0, 'InnoDB System', REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', 1), '`', '')) AS object_schema, 46 SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) AS allocated, 47 SUM(ibp.data_size) AS data, 48 COUNT(ibp.page_number) AS pages, 49 COUNT(IF(ibp.is_hashed = 'YES', 1, 0)) AS pages_hashed, 50 COUNT(IF(ibp.is_old = 'YES', 1, 0)) AS pages_old, 51 ROUND(IFNULL(SUM(ibp.number_records)/NULLIF(COUNT(DISTINCT ibp.index_name), 0), 0)) AS rows_cached 52 FROM information_schema.innodb_buffer_page ibp 53 WHERE table_name IS NOT NULL 54 GROUP BY object_schema 55 ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC; 56