1

I am using AWS RDS aurora mysql 5.7 serverless but in RDS I have enabled the performance_schema parameter, I have connected my RDS to mysql workbench version 6.3 in mysql workbench when I am checking the parameter performance_schema it is showing disabled and I have also checked performance_schema has no data in it. I want to check the unused indexes in the schema for that I have to use performance schema.enter image description here

1
  • No technique gives you a definitive list of "unused indexes". The best any tool can do is give you a list of "indexes that have not been used during the monitoring period". For that reason, I do not recommend using any such tool.
    – Rick James
    Sep 14, 2023 at 3:29

1 Answer 1

0

Please use the sys schema instead

mysql> show create table sys.schema_unused_indexes\G
*************************** 1. row ***************************
                View: schema_unused_indexes
         Create View: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `schema_unused_indexes` AS select `performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA` AS `object_schema`,`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_NAME` AS `object_name`,`performance_schema`.`table_io_waits_summary_by_index_usage`.`INDEX_NAME` AS `index_name` from `performance_schema`.`table_io_waits_summary_by_index_usage` where ((`performance_schema`.`table_io_waits_summary_by_index_usage`.`INDEX_NAME` is not null) and (`performance_schema`.`table_io_waits_summary_by_index_usage`.`COUNT_STAR` = 0) and (`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA` <> 'mysql') and (`performance_schema`.`table_io_waits_summary_by_index_usage`.`INDEX_NAME` <> 'PRIMARY')) order by `performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA`,`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_NAME`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql>

You would have to restart mysql to have a quick clear picture because it is a view that displays indexes that have no events (i.e. they have not been used since the the most recent restart).

SELECT * FROM sys.schema_unused_indexes\G

If you have the slow query log enabled and you do not want to restart mysql, you could also try pt-index-usage. (You are using Aurora so get the slow query log downloaded first.

NOTE : Please check to see if you have enabled "Performance Insights". It is not available in v1 but might be in v2. It may be useless if the stats reset a lot (After all, you are serverless)

1
  • SELECT * FROM sys.schema_unused_indexes - this query showing no data......but it is not possible that all indexes are being used in production server.
    – Paras Sahu
    Feb 13 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.