The MySQL Performance Schema is an extremely valuable feature in MySQL 5.6 for finding queries which have a performance impact on the database. However, the performance schema is not enabled by default. The performance schema can be enabled through several different methods. Possible reasons why enabling the performance schema is a bad thing includes performance impact on the server. Percona has a good write up on the performance impact of enabling the Performance Schema. Therefore be careful when enabling the performance schema in a production environment as it can add a significant amount of overhead.
Setting the performance_schema directives in the mn.cnf file will ensure that the performance_schema is enabled at startup. One can also enable the performance_schema by running SQL queries after MySQL is up and running. This can be useful if you want to disable and enable the performance_schema to troubleshoot performance issues then turn them back off without having to restart MySQL.
vim /etc/my.cnf performance_schema_consumer_events_statements_history_long=ON performance_schema_consumer_events_statements_history=ON performance_schema_consumer_events_stages_current=ON performance_schema_consumer_events_stages_history=ON performance_schema_consumer_events_stages_history_long=ON performance_schema_consumer_events_waits_current=ON performance_schema_consumer_events_waits_history=ON performance_schema_consumer_events_waits_history_long=ON performance_schema=ON performance_schema_instrument='%=ON'
Consumers are setup in the setup_consumers table this specified where the data will go.
select * from performance_schema.setup_consumers;
View which objects have the performance schema setup on them.
The screen shot below shows that performance stats will be collected for all databases except for mysql, Performance_schema and information_schema.
To view the current instruments which are enabled run the following query.
select * from setup_instruments
Instruments can be enabled by running an UPDATE statement on the table.
update setup_instruments set enabled = 'yes', timed = 'yes' where name = 'statement/sql/lock_tables'
After the performance_schema has been configured you will be able to view the sys tables for statistics collected on queries.
select * from sys.`x$statements_with_full_table_scans`
Here it is important to note the difference between the average rows sent and the average rows examined. This means that the query optimizer is examining many rows but only sending a a small number. If there is a large difference between the two. This may indicate that their is a need for an index.
Additional performance tables can be viewed which present statistics on number of temp tables created.
select * from sys.`x$statements_with_temp_tables`
select * from sys.`x$statement_analysis`