Using PERFORMANCE_SCHEMA in MySQL 5.6
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.
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.
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', // this must stay the same
'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.
There are 3 steps required to set this up:
- Create the history tables
- Install the collection script
- Set up the datasource
There's a different install sql file for these new tables. Just run mysql56-install.sql against your database:
mysql -u<user> -p < mysql56-install.sql
This will create a database called slow_query_log and two tables called events_statements and events_statements_history. These tables are analogous to the global_query_review and global_query_review_history tables respectively.
There are a series of SQL statements needed to capture the current data from performance_schema, save it to the events_statements and events_statements_history tables, and the reset the statistics. These will need to be run at regular intervals to create the history of data. It can be as simple as a cron job:
*/10 * * * * mysql < mysql56-save_history.sql
Since this script is pure SQL, you could also set it up using the event scheduler built into mysql. If you wish to use Event Scheduler, check if is turned on, because default state in MySQL 5.6 is OFF (http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_event_scheduler).
select @@global.event_scheduler;
Must be executed as root, because:
grant DELETE ON performance_schema.events_statements_summary_by_digest to 'anemometr_isp_pr';
ERROR 1142 (42000): DELETE, GRANT command denied to user 'root'@'localhost' for table 'events_statements_summary_by_digest'
The datasource looks similar to the others; you can set it up in the exact same way as the others -- by only changing a few values specific to your system.
$conf['datasources']['<your datasource name>'] = array(
'host' => '<your mysql host name>',
'port' => 3306,
'db' => 'slow_query_log',
'user' => '<your mysql user name>',
'password' => '<your mysql password>',
'tables' => array(
'events_statements' => 'fact',
'events_statements_history' => 'dimension'
),
'source_type' => 'performance_schema_history'
);