ps_helper and its migration to sys

MySQL’s Performance Schema is an incredibly rich and versatile instrumentation engine, but apparently, with great power, comes increased complexity for the user to understand up front.

I think this is pretty natural:

  • The more flexibility you provide to the user on what to monitor (and this is one of the major goals of Performance Schema) – the more there is to twiddle from a configuration perspective.
  • The more data we provide in as generic a ways as possible – the more data there is.

Performance Schema is also only going to grow over time. There are many many missing bits of instrumentation within MySQL that users want us to add, and for the most part, most of those new things are now going in to Performance Schema. Things such as memory instrumentation, stored programs, transactions, prepared statements, better replication monitoring … the list goes on, and the things I mention here are just the things that have gone in to MySQL 5.7 so far.

When I started ps_helper it was nothing more than a little playground for me to research both the breadth of the data that Performance Schema provides, as well as to try and nail down exactly the kinds of views that users want and need, based around various different use cases.

Judging by the feedback that I’ve gotten on ps_helper so far, it turns out that I was actually pretty successful with the latter goal. I’ve heard about it being used in all sorts of situations – from large websites, to small installations, everybody seems to have found something useful with it. MySQL Support also regularly directs customers to install it, and return the output from many of the views, to help customers tune and debug what their MySQL instances are doing.

One of the biggest pieces of feedback that I (and others) have heard is that users really want something like ps_helper to ship with the server itself – there is even a bug to have it included opened by my old colleague Valerii Kravchuk.

Getting something like ps_helper in to the server is not a small task. We need to know that what we are providing makes sense to users, commit to maintaining it “forever”, make sure that it is properly tested, is as bug free as possible, that it follows the right procedures for licenses and that it is properly structured for any future plans that we may have (of which there are many).

Recently, the MySQL Workbench team announced that they would be shipping a new schema that was something like ps_helper:

Additionally Workbench 6.1 is leveraging work from various teammates in MySQL Engineering in introducing a schema called “SYS” that provides simplified views on Performance Schema, Information Schema, and other areas.

This is the first step towards moving the kinds of views and procedures that ps_helper provides in to a shipping “MySQL product”. It started with the Workbench team taking ps_helper, performing a bunch of post processing on it (to rename schema etc.), and then integrating the resulting set of objects. In discussion with them after their first try at this, we decided instead to start a new branch, that didn’t need post-processing, and that starts following some of the other strict guidelines for getting a product shipped within MySQL/Oracle.

And so we get to the point of this blog finally, the mysql-sys repository was born on Github.

Why the name “sys“?

Firstly, and more importantly for some people – it is shorter and easier to type than “ps_helper”.

When you look at other database systems though, and at other similar things that people have done within the MySQL Community, it’s clear that there is room for a schema that can more generically provide a set of objects to administrators that help them with their day to day work.

SQLServer for example has a sys catalog, which comes with a number of dynamic management views, functions and procedures for administrators to use for various tasks.

DB2 has SYSIBM, which is a mixture of some DD and some runtime info. They also have syscat and sysstat that can be used too.

Oracle has a SYS catalog, owned by the SYS user. It’s a little different to our goals here (it’s more the DD owner). When comparing our goals with sys to Oracle, you could compare it to the V$ tables in many ways. However in Oracle SYS also includes a number of packages (routines) that can be used for administration tasks – the SYS.DBMS_* packages.

Looking a little closer to home, one only has to look at the excellent common_schema maintained by Shlomi Noach, which provides a whole host of views, procedures and functions to help manage and interact with MySQL, around objects, runtime metadata, and more (QueryScript for example is incredibly powerful).

With all of these in mind, we also wanted something a little more generic than ps_helper, which was created to make Performance Schema itself a little easier to understand. Something that could contain routines and views that are not necessarily Performance Schema specific (although that is what the bulk of the objects are for at the moment).

So what are the changes that have been made when migrating ps_helper to sys?

A quick summary would be:

  • mysql-sys now uses the GPLv2 license.
    • This is an important change, it aligns it with other MySQL products, and will follow the same procedures as other Oracle products. If you want to submit patches, you should follow the OCA. Unfortunately, this means that merges from mysql-sys to ps_helper can not happen. More on this shortly
  • All objects now fully specify all characteristic clauses
    • For instance, they all have a “root@localhost” DEFINER with SQL SECURITY INVOKER, and fully specify other things such as whether a routine is DETERMINISTIC, how it interacts (i.e. READS SQL DATA), or the ALGORITHM a view should use.
  • All [name]_raw views have been renamed to x$[name]
    • Within ps_helper, the “foo” and “foo_raw” tables all sort together, which makes it a little harder to scan the list of user friendly views from the output of SHOW TABLES. By renaming them all to x$foo, all of the views with some form of raw output sort at the end, and give the nice user friendly list individually first. Oracle also has something like this with their X$ vs V$ views, although X$ is “hidden” in that case.
  • A lot of the objects have had their names changed, as well as some columns
    • Many of the routines are now prefixed with ps_*, many of them have had their names changed for a little more clarity etc.
    • All “count” or “count_star” columns now use “total”, which was also used a lot. There was a lot of inconsistency there.
  • All stored routines (functions and procedures) now have a little documentation in their COMMENT clauses.
  • A number of new views were added, particularly around summarizing user activity, and making sure that each formatted view had an x$ counterpart.
  • A WHOLE BUNCH of bugs were fixed along the way.

Unfortunately, I can not really merge those bug fixes back to ps_helper – they happened as I was going through each of the objects for the clean up, but their commits were kind of mixed in with all these other changes (including the license header changes). If I try to merge this back to ps_helper, I have to re-license ps_helper as GPLv2. I’d still like to keep ps_helper as my little playground though, to try out views and routines that could be useful one day to the sys schema.

I’m not sure what to do with the dbahelper (ps_helper) repository at the moment. I am tempted to remove all of the objects that are within mysql-sys itself, and do a similar rename of the remaining objects to “sys” (so they would build on each other). I do still want to use it as my “hey this could be an interesting object to add” playground. Your thoughts on this from the community would be appreciated.

I’ve now updated the ps_helper page to remove the outdated examples, and to simply point at the Github pages directly instead. The README for the mysql-sys repository now contains the most up to date examples and documentation, and should be used instead.

I hope to blog some more about the changes, and the new things that came along in the near future, but this blog is long enough for now.

Finally, I’d like to take the opportunity to thank all in the community for their great feedback on ps_helper. It helped to turn it in to a much more mature project.

I’m looking forward to its new future as sys!