Justin Swanhart (swanhart) wrote,
Justin Swanhart

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
Tags: history, mysql, performance, performance_schema, ps_history, sys, views
  • Post a new comment


    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.