Justin Swanhart (swanhart) wrote,
Justin Swanhart
swanhart

Take the long view on the MySQL PERFORMANCE_SCHEMA with ps_history and sys_history

The performance_schema is a powerful tool for analyzing MySQL performance and behavior. One aspect of the performance_schema is that the view of the data is "right now", and very little historical information is present. You can see that there are 10 threads connected right now, but what about five minutes ago?

ps_history
ps_history is a set of stored routines and events for periodically collecting the data in the performance_schema into another schema called ps_history. The ps_history schema contains a copy of each performance_schema view as a real table, and timestamp and server_id columns have been added to each table. Periodically (by default every 30 seconds) the performance_schema data is written into the history tables.

ps_history comes as one script (setup.sql) which will create the ps_history schema, the tables within it, and create an event for collecting data. To collect data into the history tables you must either enable the event scheduler (set global event_scheduler=1) or you must periodically run the ps_history.collect() stored routine.

ps_history automatically removes old data from the history tables. By default 1 week of history will be retained. You can change the retention period with ps_history.set_retention_period(), for example to keep 2 weeks of history use ps_history.set_retention_period('2 WEEK'); to keep 10 days, ps_history.set_retention_period('10 DAY');

Here is an example from the hosts table:
mysql> select * from ps_history.hosts limit 4\G
*************************** 1. row ***************************
               HOST: localhost
CURRENT_CONNECTIONS: 2
  TOTAL_CONNECTIONS: 18588
          server_id: 33
                 ts: 2015-02-09 13:17:16.68339
*************************** 2. row ***************************
               HOST: NULL
CURRENT_CONNECTIONS: 0
  TOTAL_CONNECTIONS: 6
          server_id: 33
                 ts: 2015-02-09 13:17:16.68339
*************************** 3. row ***************************
               HOST: localhost
CURRENT_CONNECTIONS: 2
  TOTAL_CONNECTIONS: 18619
          server_id: 33
                 ts: 2015-02-09 13:17:47.69614
*************************** 4. row ***************************
               HOST: NULL
CURRENT_CONNECTIONS: 0
  TOTAL_CONNECTIONS: 6
          server_id: 33
                 ts: 2015-02-09 13:17:47.69614
4 rows in set (0.00 sec)


sys_history
sys_history is a modified version of the SYS schema to use the data from the ps_history tables instead of the performance_schema. Each of the views in the SYS schema have been modified to include the timestamp (ts) field from the ps_history tables, and joins have been modified to include ts in the join.

Here is an example of one of the aggregated views:
mysql> select * from io_by_thread_by_latency limit 4\G
*************************** 1. row ***************************
            ts: 2015-02-09 13:17:16.68339
          user: page_cleaner_thread
         total: 4809257
 total_latency: 00:08:13.93
   min_latency: 66.98 ns
   avg_latency: 1.02 ms
   max_latency: 99.29 ms
     thread_id: 18
processlist_id: NULL
*************************** 2. row ***************************
            ts: 2015-02-09 13:17:16.68339
          user: io_handler_thread
         total: 1081585
 total_latency: 00:04:44.69
   min_latency: 432.51 ns
   avg_latency: 263.22 us
   max_latency: 36.52 ms
     thread_id: 8
processlist_id: NULL
*************************** 3. row ***************************
            ts: 2015-02-09 13:17:16.68339
          user: io_handler_thread
         total: 412302
 total_latency: 00:01:34.81
   min_latency: 604.82 ns
   avg_latency: 229.94 us
   max_latency: 69.59 ms
     thread_id: 11
processlist_id: NULL
*************************** 4. row ***************************
            ts: 2015-02-09 13:17:16.68339
          user: io_handler_thread
         total: 368534
 total_latency: 00:01:32.18
   min_latency: 589.63 ns
   avg_latency: 250.12 us
   max_latency: 37.70 ms
     thread_id: 10
processlist_id: NULL
4 rows in set (5.91 sec)
Tags: mysql, performance_schema, stored procedure, sys schema
Subscribe
  • Post a new comment

    Error

    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.
  • 0 comments