October 11th, 2016

ss - oscar

PS_history 2.0 was released last week with MySQL 8 support and bundled sys_history

PS_history is a tool which collects historical snapshots of the PERFORMANCE_SCHEMA (P_S). This allows you to trend P_S values over time, for example, it is possible to look at the 95 th percentile response time for a query over time.

PS_history is stored procedure and event based, and thus it resides entirely inside of the database with no external dependencies. It uses a clever technique to capture all of the P_S data in one consistent snapshot. This ensures that all of the sys_history views (bundled now with PS_history) have a consistent set of data.

By default, as long as the event_schedule is enabled, PS_history will collect data every 30 seconds. If a snapshot takes 30 seconds, there will be a 30 second delay before the next snapshot starts. This value can be changed by calling the `ps_history`.`set_collection_interval`(N) where N is the number of seconds between samples.

The `sys_history` schema is automatically created by rewriting queries in the `sys` schema, excluding the views that access INFORMATION_SCHEMA.

The tables in the `ps_history` schema are now indexed identically to the P_S indexes added in MySQL 8. These indexes support the sys_history views and can be used to quickly look up data in the history tables as well. You can of course add any additional indexes that you like to any PS_history table.

To setup ps_history you can download the git repostory, or the zip file here:

Then execute the setup script:
\. setup.sql


mysql -f < setup.sql