MySQL sys schema / ps_helper

The MySQL sys and ps_helper schemas contain a collection of views, functions and procedures that can be used by administrators to analyze and manage MySQL Servers and their activity.

Many of the objects are targeted against the performance_schema database within MySQL, and provide extra summary data around everyday DBA/Developer/operations use cases.

Both repositories are available on Github:

See the MySQL sys schema README file for examples and usage for all objects.

19 thoughts on “MySQL sys schema / ps_helper”

  1. Ah! Looks like great work, and a much needed one!

    Also means a feature-not-needed in common_schema (was planning similar content for a while, but with no real action and no real comprehension of the scope of work on this).

    Just to be an the official side: are these files licensed under some particular license? (none that I would see)

    Reply

  3. That’s really nice, especially with 5.6.

    Shouldn’t there be a cleaner way of converting between different time units than ROUND(somevalue/1000000, 2) and betwen KB/MB/GB/TB than ROUND(some_bytes/1024/1024,2) ? a BYTES_TO_MB(some_bytes) would be better readable and less error prone.

    A bounch of functions for unit conversions would be nice. And then those can be converted to a MySQL fuction or UDF if more performance is needed.

    For PostgreSQL there is PGXN (PostgreSQL Extension Network) but for MySQL there isn’t some nice way of managing extensions like common_schema, ps_helper, etc. Yes there is forge.mysql.com, but that’s different.

    Reply

  5. Hi Mark,

    These views are very helpful.

    It would be nice to have views with both formatted and unformatted numbers.

    Would you consider adding extra “raw” fields to the views, or add extra views with unformatted (ie, “raw”) numbers? For example perhaps have:
    top_global_io_consumers_by_latency
    and
    top_global_io_consumers_by_latency_raw

    The _raw version would have the same columns but without human readable formatting. Or, add _raw columns like avg_read_raw to top_global_io_consumers_by_latency, while also keeping the avg_read column in the current human readable form.

    This would make it easy to “snapshot” these views periodically and compare the contents over time without having to deal with the numbers changing from MB -> GB or vice versa when numbers are near a boundary between units.

    Reply

      1. I would like to suggest an alternative implementation that may be cleaner to implement.

        How about a procedure called enable_batch_mode() / disable_batch_mode() which sets a session variable that the helpers can use?

        Reply

        1. Hrm, I hadn’t considered that – you mean something that the formatting functions would check, and conditionally return either raw or formatted?

          In any case, I’ve now added _raw views to ps_helper in the latest version on Github, so this is a little late..

          I’m interested in what other users of ps_helper think of this idea however, if you’re interested in this approach, rather than the _raw views, vote here!

          Reply

  7. Great work!
    Only one remark (a small little bug):
    the COMMENT for procedure analyze_statement_digest uses double quotes (“…”) as opposed to standard single quotes (‘…’). Because of this, the script wouldn’t execute to completion on my server because I use ansi quotes (set sql_mode = ‘ANSI_QUOTES,…’).
    Again, great work.

    Reply

  8. I would suggest you update unused indexes to exclude mysql

    select * from schema_unused_indexes where object_schema !=’mysql’;

    Also, in MySQL 5.6, the 5.5. query select * from latest_file_io limit 10; returns zero rows. The underlying table events_waits_history_long is empty. Is this a specific metric that has to be enabled on top of the 5.6 defaults?

    Reply

  9. Good morning,
    These comments are 4+ years old and the GiT files are also 4+ years old. Is this a dead project or is it still maintained?
    Thanks
    Joe

    Reply

Leave a Reply to Mark Leith Cancel reply