Tuesday, May 30, 2017

Short guide on using performance_schema for user & table stats

It took me too long to figure this out while reading the chapter on PS in the MySQL manual. Hopefully this saves me time the next time I need to figure it out. I think it gives me the equivalent of the data I get from IS.user_statistics and IS.table_statistics when using FB MySQL. From a few tests I ran the overhead from the PS was small, maybe less than 5%, while collecting this data.
  1. Add performance_schema=1 to my.cnf
  2. For table stats: select * from table_io_waits_summary_by_table
  3. For user stats: select * from events_statements_summary_by_account_by_event_name
Update - great advice from Mark Leith for MySQL 5.7 and newer
For table stats see the docs and run: SELECT * FROM sys.schema_table_statistics
For user stats see the docs and run: SELECT * FROM sys.user_summary

2 comments:

  1. Wow! I think this short guide was 5 years in the making! :)

    ReplyDelete
    Replies
    1. Maybe closer to 10. User & table stats were in the Google patch prior to the Riga meeting (2008).

      Delete