diff options
Diffstat (limited to 'scripts/sys_schema/views/i_s')
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; |