summaryrefslogtreecommitdiffstats
path: root/scripts/sys_schema/views/i_s
diff options
context:
space:
mode:
Diffstat (limited to 'scripts/sys_schema/views/i_s')
-rw-r--r--scripts/sys_schema/views/i_s/innodb_buffer_stats_by_schema.sql65
-rw-r--r--scripts/sys_schema/views/i_s/innodb_buffer_stats_by_table.sql67
-rw-r--r--scripts/sys_schema/views/i_s/innodb_lock_waits.sql124
-rw-r--r--scripts/sys_schema/views/i_s/schema_auto_increment_columns.sql66
-rw-r--r--scripts/sys_schema/views/i_s/schema_object_overview.sql58
-rw-r--r--scripts/sys_schema/views/i_s/schema_redundant_indexes.sql92
-rw-r--r--scripts/sys_schema/views/i_s/x_innodb_buffer_stats_by_schema.sql62
-rw-r--r--scripts/sys_schema/views/i_s/x_innodb_buffer_stats_by_table.sql66
-rw-r--r--scripts/sys_schema/views/i_s/x_innodb_lock_waits.sql124
-rw-r--r--scripts/sys_schema/views/i_s/x_schema_flattened_keys.sql42
10 files changed, 766 insertions, 0 deletions
diff --git a/scripts/sys_schema/views/i_s/innodb_buffer_stats_by_schema.sql b/scripts/sys_schema/views/i_s/innodb_buffer_stats_by_schema.sql
new file mode 100644
index 00000000..46f7cdd0
--- /dev/null
+++ b/scripts/sys_schema/views/i_s/innodb_buffer_stats_by_schema.sql
@@ -0,0 +1,65 @@
+-- Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
+--
+-- This program is free software; you can redistribute it and/or modify
+-- it under the terms of the GNU General Public License as published by
+-- the Free Software Foundation; version 2 of the License.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+
+--
+-- View: innodb_buffer_stats_by_schema
+--
+-- Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
+-- table, aggregating by schema
+--
+--
+-- mysql> select * from innodb_buffer_stats_by_schema;
+-- +--------------------------+------------+------------+-------+--------------+-----------+-------------+
+-- | object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+-- +--------------------------+------------+------------+-------+--------------+-----------+-------------+
+-- | mem30_trunk__instruments | 1.69 MiB | 510.03 KiB | 108 | 108 | 108 | 3885 |
+-- | InnoDB System | 688.00 KiB | 351.62 KiB | 43 | 43 | 43 | 862 |
+-- | mem30_trunk__events | 80.00 KiB | 21.61 KiB | 5 | 5 | 5 | 229 |
+-- +--------------------------+------------+------------+-------+--------------+-----------+-------------+
+--
+
+DELIMITER $$
+
+BEGIN NOT ATOMIC
+ DECLARE EXIT HANDLER FOR SQLEXCEPTION
+ BEGIN
+ END;
+CREATE OR REPLACE
+ ALGORITHM = TEMPTABLE
+ DEFINER = 'mariadb.sys'@'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;
+END$$
+DELIMITER ;
+
diff --git a/scripts/sys_schema/views/i_s/innodb_buffer_stats_by_table.sql b/scripts/sys_schema/views/i_s/innodb_buffer_stats_by_table.sql
new file mode 100644
index 00000000..be104fb5
--- /dev/null
+++ b/scripts/sys_schema/views/i_s/innodb_buffer_stats_by_table.sql
@@ -0,0 +1,67 @@
+-- Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
+--
+-- This program is free software; you can redistribute it and/or modify
+-- it under the terms of the GNU General Public License as published by
+-- the Free Software Foundation; version 2 of the License.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+
+--
+-- View: innodb_buffer_stats_by_table
+--
+-- Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
+-- table, aggregating by schema and table name
+--
+-- mysql> select * from innodb_buffer_stats_by_table;
+-- +--------------------------+------------------------------------+------------+-----------+-------+--------------+-----------+-------------+
+-- | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+-- +--------------------------+------------------------------------+------------+-----------+-------+--------------+-----------+-------------+
+-- | InnoDB System | SYS_COLUMNS | 128.00 KiB | 98.97 KiB | 8 | 8 | 8 | 1532 |
+-- | InnoDB System | SYS_FOREIGN | 128.00 KiB | 55.48 KiB | 8 | 8 | 8 | 172 |
+-- | InnoDB System | SYS_TABLES | 128.00 KiB | 56.18 KiB | 8 | 8 | 8 | 365 |
+-- | InnoDB System | SYS_INDEXES | 112.00 KiB | 76.16 KiB | 7 | 7 | 7 | 1046 |
+-- | mem30_trunk__instruments | agentlatencytime | 96.00 KiB | 28.83 KiB | 6 | 6 | 6 | 252 |
+-- | mem30_trunk__instruments | binlogspaceusagedata | 96.00 KiB | 22.54 KiB | 6 | 6 | 6 | 196 |
+-- | mem30_trunk__instruments | connectionsdata | 96.00 KiB | 36.68 KiB | 6 | 6 | 6 | 276 |
+-- ...
+-- +--------------------------+------------------------------------+------------+-----------+-------+--------------+-----------+-------------+
+--
+
+DELIMITER $$
+BEGIN NOT ATOMIC
+DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
+CREATE OR REPLACE
+ ALGORITHM = TEMPTABLE
+ DEFINER = 'mariadb.sys'@'localhost'
+ SQL SECURITY INVOKER
+VIEW innodb_buffer_stats_by_table (
+ object_schema,
+ object_name,
+ 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,
+ REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', -1), '`', '') AS object_name,
+ 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, object_name
+ ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC;
+END$$
+DELIMITER ; \ No newline at end of file
diff --git a/scripts/sys_schema/views/i_s/innodb_lock_waits.sql b/scripts/sys_schema/views/i_s/innodb_lock_waits.sql
new file mode 100644
index 00000000..493eacfc
--- /dev/null
+++ b/scripts/sys_schema/views/i_s/innodb_lock_waits.sql
@@ -0,0 +1,124 @@
+-- Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved.
+--
+-- This program is free software; you can redistribute it and/or modify
+-- it under the terms of the GNU General Public License as published by
+-- the Free Software Foundation; version 2 of the License.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+
+--
+-- View: innodb_lock_waits
+--
+-- Give a snapshot of which InnoDB locks transactions are waiting for.
+-- The lock waits are ordered by the age of the lock descending.
+--
+-- Versions: 5.1+ (5.1 requires InnoDB Plugin with I_S tables)
+--
+-- mysql> SELECT * FROM x$innodb_lock_waits\G
+-- *************************** 1. row ***************************
+-- wait_started: 2014-11-11 13:39:20
+-- wait_age: 00:00:07
+-- wait_age_secs: 7
+-- locked_table: `db1`.`t1`
+-- locked_index: PRIMARY
+-- locked_type: RECORD
+-- waiting_trx_id: 867158
+-- waiting_trx_started: 2014-11-11 13:39:15
+-- waiting_trx_age: 00:00:12
+-- waiting_trx_rows_locked: 0
+-- waiting_trx_rows_modified: 0
+-- waiting_pid: 3
+-- waiting_query: UPDATE t1 SET val = val + 1 WHERE id = 2
+-- waiting_lock_id: 867158:2363:3:3
+-- waiting_lock_mode: X
+-- blocking_trx_id: 867157
+-- blocking_pid: 4
+-- blocking_query: UPDATE t1 SET val = val + 1 + SLEEP(10) WHERE id = 2
+-- blocking_lock_id: 867157:2363:3:3
+-- blocking_lock_mode: X
+-- blocking_trx_started: 2014-11-11 13:39:11
+-- blocking_trx_age: 00:00:16
+-- blocking_trx_rows_locked: 1
+-- blocking_trx_rows_modified: 1
+-- sql_kill_blocking_query: KILL QUERY 4
+-- sql_kill_blocking_connection: KILL 4
+-- 1 row in set (0.01 sec)
+--
+DELIMITER $$
+BEGIN NOT ATOMIC
+ DECLARE EXIT HANDLER FOR SQLEXCEPTION
+ BEGIN
+ END;
+CREATE OR REPLACE
+ ALGORITHM = TEMPTABLE
+ DEFINER = 'mariadb.sys'@'localhost'
+ SQL SECURITY INVOKER
+VIEW innodb_lock_waits (
+ wait_started,
+ wait_age,
+ wait_age_secs,
+ locked_table,
+ locked_index,
+ locked_type,
+ waiting_trx_id,
+ waiting_trx_started,
+ waiting_trx_age,
+ waiting_trx_rows_locked,
+ waiting_trx_rows_modified,
+ waiting_pid,
+ waiting_query,
+ waiting_lock_id,
+ waiting_lock_mode,
+ blocking_trx_id,
+ blocking_pid,
+ blocking_query,
+ blocking_lock_id,
+ blocking_lock_mode,
+ blocking_trx_started,
+ blocking_trx_age,
+ blocking_trx_rows_locked,
+ blocking_trx_rows_modified,
+ sql_kill_blocking_query,
+ sql_kill_blocking_connection
+) AS
+SELECT r.trx_wait_started AS wait_started,
+ TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
+ TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
+ rl.lock_table AS locked_table,
+ rl.lock_index AS locked_index,
+ rl.lock_type AS locked_type,
+ r.trx_id AS waiting_trx_id,
+ r.trx_started as waiting_trx_started,
+ TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
+ r.trx_rows_locked AS waiting_trx_rows_locked,
+ r.trx_rows_modified AS waiting_trx_rows_modified,
+ r.trx_mysql_thread_id AS waiting_pid,
+ sys.format_statement(r.trx_query) AS waiting_query,
+ rl.lock_id AS waiting_lock_id,
+ rl.lock_mode AS waiting_lock_mode,
+ b.trx_id AS blocking_trx_id,
+ b.trx_mysql_thread_id AS blocking_pid,
+ sys.format_statement(b.trx_query) AS blocking_query,
+ bl.lock_id AS blocking_lock_id,
+ bl.lock_mode AS blocking_lock_mode,
+ b.trx_started AS blocking_trx_started,
+ TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
+ b.trx_rows_locked AS blocking_trx_rows_locked,
+ b.trx_rows_modified AS blocking_trx_rows_modified,
+ CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
+ CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
+ FROM information_schema.innodb_lock_waits w
+ INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
+ INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
+ INNER JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
+ INNER JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
+ ORDER BY r.trx_wait_started;
+ END$$
+DELIMITER ;
diff --git a/scripts/sys_schema/views/i_s/schema_auto_increment_columns.sql b/scripts/sys_schema/views/i_s/schema_auto_increment_columns.sql
new file mode 100644
index 00000000..98c95695
--- /dev/null
+++ b/scripts/sys_schema/views/i_s/schema_auto_increment_columns.sql
@@ -0,0 +1,66 @@
+
+--
+-- View: schema_auto_increment_columns
+--
+-- Present current auto_increment usage/capacity in all tables.
+--
+-- mysql> select * from schema_auto_increment_columns limit 5;
+-- +-------------------+-------------------+-------------+-----------+-------------+-----------+-------------+---------------------+----------------+----------------------+
+-- | table_schema | table_name | column_name | data_type | column_type | is_signed | is_unsigned | max_value | auto_increment | auto_increment_ratio |
+-- +-------------------+-------------------+-------------+-----------+-------------+-----------+-------------+---------------------+----------------+----------------------+
+-- | test | t1 | i | tinyint | tinyint(4) | 1 | 0 | 127 | 34 | 0.2677 |
+-- | mem__advisor_text | template_meta | hib_id | int | int(11) | 1 | 0 | 2147483647 | 516 | 0.0000 |
+-- | mem__advisors | advisor_schedules | schedule_id | int | int(11) | 1 | 0 | 2147483647 | 249 | 0.0000 |
+-- | mem__advisors | app_identity_path | hib_id | int | int(11) | 1 | 0 | 2147483647 | 251 | 0.0000 |
+-- | mem__bean_config | plists | id | bigint | bigint(20) | 1 | 0 | 9223372036854775807 | 1 | 0.0000 |
+-- +-------------------+-------------------+-------------+-----------+-------------+-----------+-------------+---------------------+----------------+----------------------+
+--
+
+CREATE OR REPLACE
+ ALGORITHM = MERGE
+ DEFINER = 'mariadb.sys'@'localhost'
+ SQL SECURITY INVOKER
+VIEW schema_auto_increment_columns (
+ table_schema,
+ table_name,
+ column_name,
+ data_type,
+ column_type,
+ is_signed,
+ is_unsigned,
+ max_value,
+ auto_increment,
+ auto_increment_ratio
+) AS
+SELECT TABLE_SCHEMA,
+ TABLE_NAME,
+ COLUMN_NAME,
+ DATA_TYPE,
+ COLUMN_TYPE,
+ (LOCATE('unsigned', COLUMN_TYPE) = 0) AS is_signed,
+ (LOCATE('unsigned', COLUMN_TYPE) > 0) AS is_unsigned,
+ (
+ CASE DATA_TYPE
+ WHEN 'tinyint' THEN 255
+ WHEN 'smallint' THEN 65535
+ WHEN 'mediumint' THEN 16777215
+ WHEN 'int' THEN 4294967295
+ WHEN 'bigint' THEN 18446744073709551615
+ END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
+ ) AS max_value,
+ AUTO_INCREMENT,
+ AUTO_INCREMENT / (
+ CASE DATA_TYPE
+ WHEN 'tinyint' THEN 255
+ WHEN 'smallint' THEN 65535
+ WHEN 'mediumint' THEN 16777215
+ WHEN 'int' THEN 4294967295
+ WHEN 'bigint' THEN 18446744073709551615
+ END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
+ ) AS auto_increment_ratio
+ FROM INFORMATION_SCHEMA.COLUMNS
+ INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME)
+ WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys', 'INFORMATION_SCHEMA', 'performance_schema')
+ AND TABLE_TYPE='BASE TABLE'
+ AND EXTRA='auto_increment'
+ ORDER BY auto_increment_ratio DESC, max_value;
diff --git a/scripts/sys_schema/views/i_s/schema_object_overview.sql b/scripts/sys_schema/views/i_s/schema_object_overview.sql
new file mode 100644
index 00000000..9da11254
--- /dev/null
+++ b/scripts/sys_schema/views/i_s/schema_object_overview.sql
@@ -0,0 +1,58 @@
+-- Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved.
+--
+-- This program is free software; you can redistribute it and/or modify
+-- it under the terms of the GNU General Public License as published by
+-- the Free Software Foundation; version 2 of the License.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+
+--
+-- View: schema_object_overview
+--
+-- Shows an overview of the types of objects within each schema
+--
+-- Note: On instances with a large number of objects, this could take
+-- some time to execute, and is not recommended.
+--
+-- mysql> select * from schema_object_overview;
+-- +---------------------------------+---------------+-------+
+-- | db | object_type | count |
+-- +---------------------------------+---------------+-------+
+-- | information_schema | SYSTEM VIEW | 59 |
+-- | mem30_test__instruments | BASE TABLE | 1 |
+-- | mem30_test__instruments | INDEX (BTREE) | 2 |
+-- | mem30_test__test | BASE TABLE | 9 |
+-- | mem30_test__test | INDEX (BTREE) | 19 |
+-- ...
+-- | sys | FUNCTION | 8 |
+-- | sys | PROCEDURE | 16 |
+-- | sys | VIEW | 59 |
+-- +---------------------------------+---------------+-------+
+--
+
+CREATE OR REPLACE
+ ALGORITHM = TEMPTABLE
+ DEFINER = 'mariadb.sys'@'localhost'
+ SQL SECURITY INVOKER
+VIEW schema_object_overview (
+ db,
+ object_type,
+ count
+) AS
+SELECT ROUTINE_SCHEMA AS db, ROUTINE_TYPE AS object_type, COUNT(*) AS count FROM information_schema.routines GROUP BY ROUTINE_SCHEMA, ROUTINE_TYPE
+ UNION
+SELECT TABLE_SCHEMA, TABLE_TYPE, COUNT(*) FROM information_schema.tables GROUP BY TABLE_SCHEMA, TABLE_TYPE
+ UNION
+SELECT TABLE_SCHEMA, CONCAT('INDEX (', INDEX_TYPE, ')'), COUNT(*) FROM information_schema.statistics GROUP BY TABLE_SCHEMA, INDEX_TYPE
+ UNION
+SELECT TRIGGER_SCHEMA, 'TRIGGER', COUNT(*) FROM information_schema.triggers GROUP BY TRIGGER_SCHEMA
+ UNION
+SELECT EVENT_SCHEMA, 'EVENT', COUNT(*) FROM information_schema.events GROUP BY EVENT_SCHEMA
+ORDER BY DB, OBJECT_TYPE;
diff --git a/scripts/sys_schema/views/i_s/schema_redundant_indexes.sql b/scripts/sys_schema/views/i_s/schema_redundant_indexes.sql
new file mode 100644
index 00000000..8ad8bb82
--- /dev/null
+++ b/scripts/sys_schema/views/i_s/schema_redundant_indexes.sql
@@ -0,0 +1,92 @@
+--
+-- View: schema_redundant_keys
+--
+-- Shows indexes which are made redundant (or duplicate) by other (dominant) keys.
+--
+-- mysql> select * from sys.schema_redundant_indexes\G
+-- *************************** 1. row ***************************
+-- table_schema: test
+-- table_name: rkey
+-- redundant_index_name: j
+-- redundant_index_columns: j
+-- redundant_index_non_unique: 1
+-- dominant_index_name: j_2
+-- dominant_index_columns: j,k
+-- dominant_index_non_unique: 1
+-- subpart_exists: 0
+-- sql_drop_index: ALTER TABLE `test`.`rkey` DROP INDEX `j`
+-- 1 row in set (0.20 sec)
+--
+-- mysql> SHOW CREATE TABLE test.rkey\G
+-- *************************** 1. row ***************************
+-- Table: rkey
+-- Create Table: CREATE TABLE `rkey` (
+-- `i` int(11) NOT NULL,
+-- `j` int(11) DEFAULT NULL,
+-- `k` int(11) DEFAULT NULL,
+-- PRIMARY KEY (`i`),
+-- KEY `j` (`j`),
+-- KEY `j_2` (`j`,`k`)
+-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+-- 1 row in set (0.06 sec)
+--
+
+CREATE OR REPLACE
+ ALGORITHM = TEMPTABLE
+ DEFINER = 'mariadb.sys'@'localhost'
+ SQL SECURITY INVOKER
+VIEW schema_redundant_indexes (
+ table_schema,
+ table_name,
+ redundant_index_name,
+ redundant_index_columns,
+ redundant_index_non_unique,
+ dominant_index_name,
+ dominant_index_columns,
+ dominant_index_non_unique,
+ subpart_exists,
+ sql_drop_index
+) AS
+ SELECT
+ redundant_keys.table_schema,
+ redundant_keys.table_name,
+ redundant_keys.index_name AS redundant_index_name,
+ redundant_keys.index_columns AS redundant_index_columns,
+ redundant_keys.non_unique AS redundant_index_non_unique,
+ dominant_keys.index_name AS dominant_index_name,
+ dominant_keys.index_columns AS dominant_index_columns,
+ dominant_keys.non_unique AS dominant_index_non_unique,
+ IF(redundant_keys.subpart_exists OR dominant_keys.subpart_exists, 1 ,0) AS subpart_exists,
+ CONCAT(
+ 'ALTER TABLE `', redundant_keys.table_schema, '`.`', redundant_keys.table_name, '` DROP INDEX `', redundant_keys.index_name, '`'
+ ) AS sql_drop_index
+ FROM
+ x$schema_flattened_keys AS redundant_keys
+ INNER JOIN x$schema_flattened_keys AS dominant_keys
+ USING (TABLE_SCHEMA, TABLE_NAME)
+ WHERE
+ redundant_keys.index_name != dominant_keys.index_name
+ AND (
+ (
+ /* Identical columns */
+ (redundant_keys.index_columns = dominant_keys.index_columns)
+ AND (
+ (redundant_keys.non_unique > dominant_keys.non_unique)
+ OR (redundant_keys.non_unique = dominant_keys.non_unique
+ AND IF(redundant_keys.index_name='PRIMARY', '', redundant_keys.index_name) > IF(dominant_keys.index_name='PRIMARY', '', dominant_keys.index_name)
+ )
+ )
+ )
+ OR
+ (
+ /* Non-unique prefix columns */
+ LOCATE(CONCAT(redundant_keys.index_columns, ','), dominant_keys.index_columns) = 1
+ AND redundant_keys.non_unique = 1
+ )
+ OR
+ (
+ /* Unique prefix columns */
+ LOCATE(CONCAT(dominant_keys.index_columns, ','), redundant_keys.index_columns) = 1
+ AND dominant_keys.non_unique = 0
+ )
+ );
diff --git a/scripts/sys_schema/views/i_s/x_innodb_buffer_stats_by_schema.sql b/scripts/sys_schema/views/i_s/x_innodb_buffer_stats_by_schema.sql
new file mode 100644
index 00000000..6637f509
--- /dev/null
+++ b/scripts/sys_schema/views/i_s/x_innodb_buffer_stats_by_schema.sql
@@ -0,0 +1,62 @@
+-- Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
+--
+-- This program is free software; you can redistribute it and/or modify
+-- it under the terms of the GNU General Public License as published by
+-- the Free Software Foundation; version 2 of the License.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+
+--
+-- View: x$innodb_buffer_stats_by_schema
+--
+-- Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
+-- table, aggregating by schema
+--
+-- mysql> select * from x$innodb_buffer_stats_by_schema;
+-- +--------------------------+-----------+--------+-------+--------------+-----------+-------------+
+-- | object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+-- +--------------------------+-----------+--------+-------+--------------+-----------+-------------+
+-- | mem30_trunk__instruments | 1769472 | 522272 | 108 | 108 | 108 | 3885 |
+-- | InnoDB System | 704512 | 360054 | 43 | 43 | 43 | 862 |
+-- | mem30_trunk__events | 81920 | 22125 | 5 | 5 | 5 | 229 |
+-- +--------------------------+-----------+--------+-------+--------------+-----------+-------------+
+--
+
+DELIMITER $$
+BEGIN NOT ATOMIC
+ DECLARE EXIT HANDLER FOR SQLEXCEPTION
+ BEGIN
+ END;
+CREATE OR REPLACE
+ ALGORITHM = TEMPTABLE
+ DEFINER = 'mariadb.sys'@'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, 1, NULL)) AS pages_hashed,
+ COUNT(IF(ibp.is_old, 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;
+END$$
+DELIMITER ;
diff --git a/scripts/sys_schema/views/i_s/x_innodb_buffer_stats_by_table.sql b/scripts/sys_schema/views/i_s/x_innodb_buffer_stats_by_table.sql
new file mode 100644
index 00000000..39d19b05
--- /dev/null
+++ b/scripts/sys_schema/views/i_s/x_innodb_buffer_stats_by_table.sql
@@ -0,0 +1,66 @@
+-- Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
+--
+-- This program is free software; you can redistribute it and/or modify
+-- it under the terms of the GNU General Public License as published by
+-- the Free Software Foundation; version 2 of the License.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+
+--
+-- View: x$innodb_buffer_stats_by_table
+--
+-- Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
+-- table, aggregating by schema and table name
+--
+-- mysql> select * from x$innodb_buffer_stats_by_table;
+-- +--------------------------+------------------------------------+-----------+--------+-------+--------------+-----------+-------------+
+-- | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+-- +--------------------------+------------------------------------+-----------+--------+-------+--------------+-----------+-------------+
+-- | InnoDB System | SYS_COLUMNS | 131072 | 101350 | 8 | 8 | 8 | 1532 |
+-- | InnoDB System | SYS_FOREIGN | 131072 | 56808 | 8 | 8 | 8 | 172 |
+-- | InnoDB System | SYS_TABLES | 131072 | 57529 | 8 | 8 | 8 | 365 |
+-- | InnoDB System | SYS_INDEXES | 114688 | 77984 | 7 | 7 | 7 | 1046 |
+-- | mem30_trunk__instruments | agentlatencytime | 98304 | 29517 | 6 | 6 | 6 | 252 |
+-- | mem30_trunk__instruments | binlogspaceusagedata | 98304 | 23076 | 6 | 6 | 6 | 196 |
+-- | mem30_trunk__instruments | connectionsdata | 98304 | 37563 | 6 | 6 | 6 | 276 |
+-- ...
+-- +--------------------------+------------------------------------+-----------+--------+-------+--------------+-----------+-------------+
+--
+DELIMITER $$
+BEGIN NOT ATOMIC
+DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
+CREATE OR REPLACE
+ ALGORITHM = TEMPTABLE
+ DEFINER = 'mariadb.sys'@'localhost'
+ SQL SECURITY INVOKER
+VIEW x$innodb_buffer_stats_by_table (
+ object_schema,
+ object_name,
+ 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,
+ REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', -1), '`', '') AS object_name,
+ 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, 1, NULL)) AS pages_hashed,
+ COUNT(IF(ibp.is_old, 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, object_name
+ ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC;
+END$$
+DELIMITER ; \ No newline at end of file
diff --git a/scripts/sys_schema/views/i_s/x_innodb_lock_waits.sql b/scripts/sys_schema/views/i_s/x_innodb_lock_waits.sql
new file mode 100644
index 00000000..a36bc4d7
--- /dev/null
+++ b/scripts/sys_schema/views/i_s/x_innodb_lock_waits.sql
@@ -0,0 +1,124 @@
+-- Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved.
+--
+-- This program is free software; you can redistribute it and/or modify
+-- it under the terms of the GNU General Public License as published by
+-- the Free Software Foundation; version 2 of the License.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+
+--
+-- View: x$innodb_lock_waits
+--
+-- Give a snapshot of which InnoDB locks transactions are waiting for.
+-- The lock waits are ordered by the age of the lock descending.
+--
+-- Versions: 5.1+ (5.1 requires InnoDB Plugin with I_S tables)
+--
+-- mysql> SELECT * FROM x$innodb_lock_waits\G
+-- *************************** 1. row ***************************
+-- wait_started: 2014-11-11 13:39:20
+-- wait_age: 00:00:07
+-- wait_age_secs: 7
+-- locked_table: `db1`.`t1`
+-- locked_index: PRIMARY
+-- locked_type: RECORD
+-- waiting_trx_id: 867158
+-- waiting_trx_started: 2014-11-11 13:39:15
+-- waiting_trx_age: 00:00:12
+-- waiting_trx_rows_locked: 0
+-- waiting_trx_rows_modified: 0
+-- waiting_pid: 3
+-- waiting_query: UPDATE t1 SET val = val + 1 WHERE id = 2
+-- waiting_lock_id: 867158:2363:3:3
+-- waiting_lock_mode: X
+-- blocking_trx_id: 867157
+-- blocking_pid: 4
+-- blocking_query: UPDATE t1 SET val = val + 1 + SLEEP(10) WHERE id = 2
+-- blocking_lock_id: 867157:2363:3:3
+-- blocking_lock_mode: X
+-- blocking_trx_started: 2014-11-11 13:39:11
+-- blocking_trx_age: 00:00:16
+-- blocking_trx_rows_locked: 1
+-- blocking_trx_rows_modified: 1
+-- sql_kill_blocking_query: KILL QUERY 4
+-- sql_kill_blocking_connection: KILL 4
+-- 1 row in set (0.01 sec)
+--
+
+DELIMITER $$
+BEGIN NOT ATOMIC
+DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
+
+CREATE OR REPLACE
+ ALGORITHM = TEMPTABLE
+ DEFINER = 'mariadb.sys'@'localhost'
+ SQL SECURITY INVOKER
+VIEW x$innodb_lock_waits (
+ wait_started,
+ wait_age,
+ wait_age_secs,
+ locked_table,
+ locked_index,
+ locked_type,
+ waiting_trx_id,
+ waiting_trx_started,
+ waiting_trx_age,
+ waiting_trx_rows_locked,
+ waiting_trx_rows_modified,
+ waiting_pid,
+ waiting_query,
+ waiting_lock_id,
+ waiting_lock_mode,
+ blocking_trx_id,
+ blocking_pid,
+ blocking_query,
+ blocking_lock_id,
+ blocking_lock_mode,
+ blocking_trx_started,
+ blocking_trx_age,
+ blocking_trx_rows_locked,
+ blocking_trx_rows_modified,
+ sql_kill_blocking_query,
+ sql_kill_blocking_connection
+) AS
+SELECT r.trx_wait_started AS wait_started,
+ TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
+ TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
+ rl.lock_table AS locked_table,
+ rl.lock_index AS locked_index,
+ rl.lock_type AS locked_type,
+ r.trx_id AS waiting_trx_id,
+ r.trx_started as waiting_trx_started,
+ TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
+ r.trx_rows_locked AS waiting_trx_rows_locked,
+ r.trx_rows_modified AS waiting_trx_rows_modified,
+ r.trx_mysql_thread_id AS waiting_pid,
+ r.trx_query AS waiting_query,
+ rl.lock_id AS waiting_lock_id,
+ rl.lock_mode AS waiting_lock_mode,
+ b.trx_id AS blocking_trx_id,
+ b.trx_mysql_thread_id AS blocking_pid,
+ b.trx_query AS blocking_query,
+ bl.lock_id AS blocking_lock_id,
+ bl.lock_mode AS blocking_lock_mode,
+ b.trx_started AS blocking_trx_started,
+ TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
+ b.trx_rows_locked AS blocking_trx_rows_locked,
+ b.trx_rows_modified AS blocking_trx_rows_modified,
+ CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
+ CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
+ FROM information_schema.innodb_lock_waits w
+ INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
+ INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
+ INNER JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
+ INNER JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
+ ORDER BY r.trx_wait_started;
+END$$
+DELIMITER ;
diff --git a/scripts/sys_schema/views/i_s/x_schema_flattened_keys.sql b/scripts/sys_schema/views/i_s/x_schema_flattened_keys.sql
new file mode 100644
index 00000000..35001158
--- /dev/null
+++ b/scripts/sys_schema/views/i_s/x_schema_flattened_keys.sql
@@ -0,0 +1,42 @@
+--
+-- View: x$schema_flattened_keys
+--
+-- Helper view for the schema_redundant_keys view.
+--
+-- mysql> select * from sys.x$schema_flattened_keys;
+-- +---------------+---------------------+------------------------------+------------+----------------+-----------------+
+-- | table_schema | table_name | index_name | non_unique | subpart_exists | index_columns |
+-- +---------------+---------------------+------------------------------+------------+----------------+-----------------+
+-- | mem__advisors | advisor_initialized | PRIMARY | 0 | 0 | advisorClassId |
+-- | mem__advisors | advisor_schedules | advisorClassIdIdx | 1 | 0 | advisorClassId |
+-- | mem__advisors | advisor_schedules | PRIMARY | 0 | 0 | schedule_id |
+-- | mem__advisors | app_identity_path | FK_7xbq2i81hgo0xlvnb6rr77s21 | 1 | 0 | for_schedule_id |
+-- | mem__advisors | app_identity_path | PRIMARY | 0 | 0 | hib_id |
+-- ...
+--
+
+CREATE OR REPLACE
+ ALGORITHM = TEMPTABLE
+ DEFINER = 'mariadb.sys'@'localhost'
+ SQL SECURITY INVOKER
+VIEW x$schema_flattened_keys (
+ table_schema,
+ table_name,
+ index_name,
+ non_unique,
+ subpart_exists,
+ index_columns
+) AS
+ SELECT
+ TABLE_SCHEMA,
+ TABLE_NAME,
+ INDEX_NAME,
+ MAX(NON_UNIQUE) AS non_unique,
+ MAX(IF(SUB_PART IS NULL, 0, 1)) AS subpart_exists,
+ GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS index_columns
+ FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE
+ INDEX_TYPE='BTREE'
+ AND TABLE_SCHEMA NOT IN ('mysql', 'sys', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')
+ GROUP BY
+ TABLE_SCHEMA, TABLE_NAME, INDEX_NAME;