sys.schema_table_statistics View returns up-to-the-minute statistics on table I/O ...
CREATE
ALGORITHM=TEMPTABLE
DEFINER=`mysql.sys`@localhost
SQL SECURITY INVOKER
VIEW schema_table_statistics AS
SELECT
pst.OBJECT_SCHEMA AS table_schema,pst.OBJECT_NAME AS `table_name`,
sys.format_time(pst.SUM_TIMER_WAIT) AS total_latency,
pst.COUNT_FETCH AS rows_fetched,
sys.format_time(pst.SUM_TIMER_FETCH) AS fetch_latency,
pst.COUNT_INSERT AS rows_inserted,
sys.format_time(pst.SUM_TIMER_INSERT) AS insert_latency,
pst.COUNT_UPDATE AS rows_updated,
sys.format_time(pst.SUM_TIMER_UPDATE) AS update_latency,
pst.COUNT_DELETE AS rows_deleted,
sys.format_time(pst.SUM_TIMER_DELETE) AS delete_latency,
fsbi.count_read AS io_read_requests,
sys.format_bytes(fsbi.sum_number_of_bytes_read) AS io_read,
sys.format_time(fsbi.sum_timer_read) AS io_read_latency,
fsbi.count_write AS io_write_requests,
sys.format_bytes(fsbi.sum_number_of_bytes_write) AS io_write,
sys.format_time(fsbi.sum_timer_write) AS io_write_latency,
fsbi.count_misc AS io_misc_requests,
sys.format_time(fsbi.sum_timer_misc) AS io_misc_latency
FROM performance_schema.table_io_waits_summary_by_table pst
LEFT JOIN sys.x$ps_schema_table_statistics_io fsbi
ON pst.OBJECT_SCHEMA = fsbi.table_schema)
AND pst.OBJECT_NAME = fsbi.`table_name`
ORDER BY pst.SUM_TIMER_WAIT desc
For more sys schema Views, see https://dev.mysql.com/doc/refman/5.7/en/sys-schema-views.html.Last updated 12 Mar 2025
