The purpose of these scripts is to provide a mechanism for periodically snapshotting the contents of the MySQL Performance_Schema. This will allow you to look at historical trends and changes in the Performance_Schema information, something that you can't do out of the box.
The setup process will create a copy of almost all of the sys views, with the exception primirily those that use the information_schema. The sys_history schema contains views that have been modified to utilize the ps_history tables to
prevent a historical view of the sys schema of ps_history data.
To install the ps_history schema, use the provided setup.sql script. It will create the ps_history database and run the ps_history.setup() script to create history tables that automatically match the installed performance_schema tables.
If you are running 5.7 you'll automatically get history into the 5.7 tables, such as the memory tables as the setup will copy all available performance_schema tables.
You should turn the event scheduler on to collect data. There is a stored routine called ps_history.collect() which snapshots the performance schema table data into the history tables in a transactionally consistent manner. If you don't want to use the event that comes with ps_history, you can run ps_history.collect() manually at any time.
By default data is collected every 30 seconds. You do not need to modify the event to change the collection interval. Instead, use the ps_history.set_collection_interval() procedure. For example, to collect data every fifteen seconds: CALL ps_history.set_collection_interval(15);
You can also update the psh_settings table directly: UPDATE ps_history.psh_settings set value = 15 where variable = 'interval';
By default, ps_history will automatically remove history more than one week old. Depending on your needs, this interval may be too short or too long.
Use ps_history.set_retenion_period(X); to set the retention period.
For example to retain two weeks of history: CALL ps_history.set_retention_period('2 WEEK');. Use the SQL INTERVAL which is appropriate. The function will error out if you give an invalid SQL INTERVAL.
There are two stored routines you can use to clean up ps_history data.
You can remove all data with CALL ps_history.truncate_tables();.
You can remove only a subset of older data, for example, using CALL ps_history.cleanup_history('1 DAY'); which will delete all records older than 1 days old.
When cleanup up data you can use any valid argument to the INTERVAL keyword, so to delete data 1 week old you could do CALL ps_history.cleanup_history('1 WEEK');.