Can't use performance_schema in my mysql server

Question

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.

Answer 1

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)