1-- Copyright (c) 2014, 2021, Oracle and/or its affiliates. 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, version 2.0, 5-- as published by the Free Software Foundation. 6-- 7-- This program is also distributed with certain software (including 8-- but not limited to OpenSSL) that is licensed under separate terms, 9-- as designated in a particular file or component or in included license 10-- documentation. The authors of MySQL hereby grant you an additional 11-- permission to link the program and your derivative works with the 12-- separately licensed software that they have included with MySQL. 13-- 14-- This program is distributed in the hope that it will be useful, 15-- but WITHOUT ANY WARRANTY; without even the implied warranty of 16-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 17-- GNU General Public License, version 2.0, for more details. 18-- 19-- You should have received a copy of the GNU General Public License 20-- along with this program; if not, write to the Free Software 21-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA 22 23-- 24-- View: schema_table_statistics 25-- 26-- Statistics around tables. 27-- 28-- Ordered by the total wait time descending - top tables are most contended. 29-- 30-- mysql> SELECT * FROM schema_table_statistics\G 31-- *************************** 1. row *************************** 32-- table_schema: sys 33-- table_name: sys_config 34-- total_latency: 0 ps 35-- rows_fetched: 0 36-- fetch_latency: 0 ps 37-- rows_inserted: 0 38-- insert_latency: 0 ps 39-- rows_updated: 0 40-- update_latency: 0 ps 41-- rows_deleted: 0 42-- delete_latency: 0 ps 43-- io_read_requests: 8 44-- io_read: 2.28 KiB 45-- io_read_latency: 727.32 us 46-- io_write_requests: 0 47-- io_write: 0 bytes 48-- io_write_latency: 0 ps 49-- io_misc_requests: 10 50-- io_misc_latency: 126.88 us 51-- 52 53CREATE OR REPLACE 54 ALGORITHM = TEMPTABLE 55 DEFINER = 'mysql.sys'@'localhost' 56 SQL SECURITY INVOKER 57VIEW schema_table_statistics ( 58 table_schema, 59 table_name, 60 total_latency, 61 rows_fetched, 62 fetch_latency, 63 rows_inserted, 64 insert_latency, 65 rows_updated, 66 update_latency, 67 rows_deleted, 68 delete_latency, 69 io_read_requests, 70 io_read, 71 io_read_latency, 72 io_write_requests, 73 io_write, 74 io_write_latency, 75 io_misc_requests, 76 io_misc_latency 77) AS 78SELECT pst.object_schema AS table_schema, 79 pst.object_name AS table_name, 80 sys.format_time(pst.sum_timer_wait) AS total_latency, 81 pst.count_fetch AS rows_fetched, 82 sys.format_time(pst.sum_timer_fetch) AS fetch_latency, 83 pst.count_insert AS rows_inserted, 84 sys.format_time(pst.sum_timer_insert) AS insert_latency, 85 pst.count_update AS rows_updated, 86 sys.format_time(pst.sum_timer_update) AS update_latency, 87 pst.count_delete AS rows_deleted, 88 sys.format_time(pst.sum_timer_delete) AS delete_latency, 89 fsbi.count_read AS io_read_requests, 90 sys.format_bytes(fsbi.sum_number_of_bytes_read) AS io_read, 91 sys.format_time(fsbi.sum_timer_read) AS io_read_latency, 92 fsbi.count_write AS io_write_requests, 93 sys.format_bytes(fsbi.sum_number_of_bytes_write) AS io_write, 94 sys.format_time(fsbi.sum_timer_write) AS io_write_latency, 95 fsbi.count_misc AS io_misc_requests, 96 sys.format_time(fsbi.sum_timer_misc) AS io_misc_latency 97 FROM performance_schema.table_io_waits_summary_by_table AS pst 98 LEFT JOIN x$ps_schema_table_statistics_io AS fsbi 99 ON pst.object_schema = fsbi.table_schema 100 AND pst.object_name = fsbi.table_name 101 ORDER BY pst.sum_timer_wait DESC; 102