Re: How to Find the Least Accessed Tables?

[Peter Brawley]

Since 5.7, the 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 sys schema Views, see <url>https://dev.mysql.com/doc/refman/5.7/en/sys-schema-views.html</url>;.



Edited 2 time(s). Last edit at 03/14/2022 09:53AM by Peter Brawley.