MySQL Performance Schema

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;

MySQL Workbench_2015-08-11_07-12-25

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.
perf_schema_setup_objects

The setup_actors table determines who to monitor, you can filter by host, user or role.
perf_schema_who_to_monitor

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'

MySQLinstruments

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.

MySQL Workbench_2015-08-11_08-06-52

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`

Reference: For more in depth informaiton on the the performance schema and A Visual Guide to the MySQL Performance Schema

Advertisements

One thought on “MySQL Performance Schema

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s