Description:
Thank you for providing SYS schema on MySQL.
It is very helpful for managing the instance.
PS: It seems many users like "statement_analysis" and "schema_unused_indexes".
This request is adding "schema_unused_tables" on the SYS schema.
If customer want to check if tables are used or not, it is not so easy for user to figure it out by using query. Sometime, user might be checking timestamp of *.ibd files.
If user can use SYS.schema_unused_tables, it is very helpful for users who is thinking about droping old and unused tables from the instance.
How to repeat:
1) Making sure if instrument is enabled
mysql> select * from performance_schema.setup_instruments where name like 'wait/io/table/%';
+---------------------------+---------+-------+------------+------------+---------------+
| NAME | ENABLED | TIMED | PROPERTIES | VOLATILITY | DOCUMENTATION |
+---------------------------+---------+-------+------------+------------+---------------+
| wait/io/table/sql/handler | YES | YES | | 0 | NULL |
+---------------------------+---------+-------+------------+------------+---------------+
1 row in set (0.00 sec)
2) Run Query for checking unused tables.
SQL> select * from sys.schema_unused_tables;
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| test | t1 |
| test | t1_json |
+--------------+------------+
【EX: Query】
select table_schema, table_name from sys.schema_table_statistics where total_latency = 0 and table_schema not in ('information_schema','mysql','performance_schema','sys');
Maybe there might be some pre-requirement.
- Instance has been up long enough to accept all forms of traffic.
- Might be difference between Single Instance, Replication (Master, Slave), Group Replication.