MySQL Forums
Forum List  »  General

Re: How to Find the Least Accessed Tables?
Posted by: Peter Brawley
Date: March 14, 2022 09:15AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: How to Find the Least Accessed Tables?
March 14, 2022 09:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.