Easier Overview of Current Performance Schema Setting

While I prepared for my Hands-On Lab about the Performance Schema at MySQL Connect last year, one of the things that occurred to me was how difficult it was quickly getting an overview of which consumers, instruments, actors, etc. are actually enabled. For the consumers things are made more complicated as the effective setting also depends on parents in the hierarchy. So my thought was: “How difficult can it be to write a stored procedure that outputs a tree of the hierarchies.” Well, simple enough in principle, but trying to be general ended up making it into a lengthy project and as it was a hobby project, it often ended up being put aside for more urgent tasks.

However here around eight months later, it is starting to shape up. While there definitely still is work to be done, e.g. creating the full tree and outputting it in text mode (more on modes later) takes around one minute on my test system – granted I am using a standard laptop and MySQL is running in a VM, so it is nothing sophisticated.

The current routines can be found in ps_tools.sql.gz – it may later be merged into Mark Leith's ps_helper to try to keep the Performance Schema tools collected in one place.

Description of the ps_tools Routines

The current status are two views, four stored procedure, and four functions – not including several functions and procedures that does all the hard work:

• Views:
  • setup_consumers – Displays whether each consumer is enabled and whether the consumer actually will be collected based on the hierarchy rules described in Pre-Filtering by Consumer in the Reference Manual.
  • accounts_enabled – Displays whether each account defined in the mysql.user table has instrumentation enabled based on the rows in performance_schema.setup_actors.
• Procedures:
  • setup_tree_consumers(format, color) – Create a tree based on setup_consumers displaying whether each consumer is effectively enabled. The arguments are:
    • format is the output format and can be either (see also below).:
      • Text: Left-Right
      • Text: Top-Bottom
      • Dot: Left-Right
      • Dot: Top-Bottom
    • color is whether to add bash color escapes sequences around the consumer names when used a Text format (ignored for Dot outputs).
  • setup_tree_instruments(format, color, only_enabled, regex_filter) – Create a tree based on setup_instruments displaying whether each instrument is enabled. The tree is creating by splitting the instrument names at each /. The arguments are:
    • format is the output format and can be either:
      • Text: Left-Right
      • Dot: Left-Right
      • Dot: Top-Bottom
    • color is whether to add bash color escapes sequences around the instrument names when used a Text format (ignored for Dot outputs).
    • type – whether to base the tree on the ENABLED or TIMED column of setup_instruments.
    • only_enabled – if TRUE only the enabled instruments are included.
    • regex_filter – if set to a non-empty string only instruments that match the regex will be included.
  • setup_tree_actors_by_host(format, color) – Create a tree of each account defined in mysql.user and whether they are enabled; grouped by host. The arguments are:
    • format is the output format and can be either:
      • Text: Left-Right
      • Dot: Left-Right
      • Dot: Top-Bottom
    • color is whether to add bash color escape sequences around the names when used a Text format (ignored for Dot outputs).
  • setup_tree_actors_by_user – Create a tree of each account defined in mysql.user and whether they are enabled; grouped by username. The arguments are:
    • format is the output format and can be either:
      • Text: Left-Right
      • Dot: Left-Right
      • Dot: Top-Bottom
    • color is whether to add bash color escape sequences around the names when used a Text format (ignored for Dot outputs).
• Functions:
  • is_consumer_enabled(consumer_name) – Returns whether a given consumer is effectively enabled.
  • is_account_enabled(host, user) – Returns whether a given account (host, user) is enabled according to setup_actors.
  • substr_count(haystack, needle, offset, length) – The number of times a given substring occurs in a string. A port of the PHP function of the same name.
  • substr_by_delim(set, delimiter, pos) – Returns the Nth element from a delimiter string.

The two functions substr_count() and substr_by_delim() was also described in an earlier blog.

The formats for the four stored procedures consists of two parts: whether it is Text or Dot and the direction. Text is a tree that can be viewed directly either in the mysql command line client (coloured output not supported) or the shell (colours supported for bash). Dot will output a DOT graph file in the same way as dump_thread_stack() in ps_helper. The direction is as defined in the DOT language, so e.g. Left-Right will have the first level furthest to the left, then add each new level to the right of the parent level.

Examples

As the source code – including comments – is more than 1600 lines, I will not discuss it here, but rather go through some examples.

setup_tree_consumers

Using the coloured output:

or the same using a non-coloured output:

setup_tree_instruments

Here a small part of the tree is selected using a regex.

setup_tree_actors_%

With only root@localhost and root@127.0.0.1 enabled, the outputs of setup_tree_actors_by_host and setup_tree_actors_by_user gives respectively:

DOT Graph of setup_instruments

The full tree of setup_instruments can be created using the following sequence of steps (I am using graphviz to get support for dot files):

MySQL 5.6.11$ echo -e "$(mysql -NBe "CALL ps_tools.setup_tree_instruments('Dot: Left-Right', FALSE, 'Enabled', FALSE, '')")" > /tmp/setup_instruments.dot
MySQL 5.6.11$ dot -Tpng /tmp/setup_instruments.dot -o /tmp/setup_instruments.png

The full output is rather large (6.7M). If you want to see if you can get to it at http://mysql.wisborg.dk/wp-content/uploads/2013/05/setup_tree_instruments_dot_lr.png.

Views

mysql> SELECT * FROM ps_tools.setup_consumers;
+--------------------------------+---------+----------+
| NAME                           | ENABLED | COLLECTS |
+--------------------------------+---------+----------+
| events_stages_current          | NO      | NO       |
| events_stages_history          | NO      | NO       |
| events_stages_history_long     | NO      | NO       |
| events_statements_current      | YES     | YES      |
| events_statements_history      | NO      | NO       |
| events_statements_history_long | NO      | NO       |
| events_waits_current           | NO      | NO       |
| events_waits_history           | NO      | NO       |
| events_waits_history_long      | NO      | NO       |
| global_instrumentation         | YES     | YES      |
| thread_instrumentation         | YES     | YES      |
| statements_digest              | YES     | YES      |
+--------------------------------+---------+----------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM ps_tools.accounts_enabled;
+-------------+-----------+---------+
| User        | Host      | Enabled |
+-------------+-----------+---------+
| replication | 127.0.0.1 | NO      |
| root        | 127.0.0.1 | YES     |
| root        | ::1       | NO      |
| meb         | localhost | NO      |
| memagent    | localhost | NO      |
| root        | localhost | YES     |
+-------------+-----------+---------+
6 rows in set (0.00 sec)

Conclusion

There is definitely more work to do on making the Performance Schema easier to access. ps_helper and ps_tools are a great start to what I am sure will be an extensive library of useful diagnostic queries and tools.