Using PERFORMANCE_SCHEMA in MySQL 5.6

About PERFORMANCE_SCHEMA

MySQL 5.6 includes some new features in the performance schema, specifically for collecting query performance data. This creates new opportunities to inspect queries, but itself it's very useful, as described in this blog post. But lets face it -- running those queries by hand is bothersome. Anemometer can now read those tables directly, and report on them from its web interface! You can do this in one of two ways. Either directly, or by building a history of events.

How to Use It

To use these new features, you'll first need to make sure you have the most up to date code from Anemometer. Fetch and rebase changes through git, or download the tar distribution. In particular you'll need the newest sample.config.inc.php. Merge any changes you have to your config file.

Query events_statements_summary_by_digest Directly

To set up Anemometer to query any MySQL 5.6 directly, just add a new datasource like the following

$conf['datasources']['<your datasource name>'] = array(
'host'	=> '<your server hostname>',
'port'	=> 3306,
'db'	=> 'performance_schema',
'user'	=> '<your mysql user>',
'password' => '<your mysql password>',
'tables' => array(
	'events_statements_summary_by_digest' => 'fact',
),
'source_type' => 'performance_schema'
); 

There are a few values to change there: your datasource name, the server name and port, and the mysql user and password. Once you have set those up, you should be able to pick the new datasource from the drop down list at the top of the interface.

You'll notice that there is no graph search, or statement checksum search -- this is because you can only generate this data if you keep a history of events. MySQL's performance_schema tables do not do this. They only track the current performance of queries, and are updated in real time. You are able to see the current performance statistics up to the present moment.

The section below will explain how you can use this data to set up tracking history and save the data you need to create the graphs and query detail pages that you're used to in Anemometer.

Creating History Tables

There are N steps required to set this up:

1. Create the history tables
2. Install the collection script
3. Set up the datasource