MySQL Connect 2013: ps_tools

During my talk and hands on labs at MySQL Connect 2013 I used several views, functions, and procedures from ps_helper and ps_tools.

The slides from the two talks can be downloaded from Thanks For Attending MySQL Connect or from the MySQL Connect 2013 Content Catalogue.

You can read more about ps_helper on Mark Leith's blog and you can download ps_helper from github.

To get ps_tools, download ps_tools.tgz from here. Once unpacked there is a ps_tools_5x.sql for each of the versions supported. The tools presented at MySQL Connect were all based on MySQL 5.6 and 5.7. Note that several of the included tools are not particularly useful on their own but are more meant as utility functions for some of the other tools. The actual implementations are organised so they are in the subdirectory corresponding to the earliest version it applies to. For a few tools, such as ps_reset_setup(), there are multiple versions depending on the MySQL version it applies to.

Several of the tools have a help text at the top of the source file.

The main tools are:

Function ps_thread_id():

Returns the thread id for a given connection. Specify NULL to get the thread id for the current connection. For example:

mysql> SELECT ps_tools.ps_thread_id(8063);
+-----------------------------+
| ps_tools.ps_thread_id(8063) |
+-----------------------------+
|                        8113 |
+-----------------------------+
1 row in set (0.00 sec)

View ps_setup_consumers:

Similar to performance_schema.setup_consumers, but includes a column to display whether the consumer is effectively enabled. See also slide 10 from CON 5282.

Function substr_count():

See also A Couple of Substring Functions: substr_count() and substr_by_delim().

Function substr_by_delim():

See also A Couple of Substring Functions: substr_count() and substr_by_delim().

Function ps_account_enabled():

Check whether a given account is enabled according to performance_schema.setup_actors. For example:

mysql> SELECT ps_tools.ps_account_enabled('localhost', 'root');
+--------------------------------------------------+
| ps_tools.ps_account_enabled('localhost', 'root') |
+--------------------------------------------------+
| YES                                              |
+--------------------------------------------------+
1 row in set (0.03 sec)

View ps_accounts_enabled:

Lists each account and whether it is enabled according to performance_schema.setup_actors.

Procedure ps_setup_tree_instruments():

Creates a tree displaying whether instruments are enabled. See Easier Overview of Current Performance Schema Setting for more details.

Procedure ps_setup_tree_actors_by_host():

Creates a tree displaying whether instruments are enabled. See Easier Overview of Current Performance Schema Setting for more details.

Procedure ps_setup_tree_actors_by_user():

Creates a tree displaying whether instruments are enabled. See Easier Overview of Current Performance Schema Setting for more details.

Procedure ps_setup_tree_consumers():

Creates a tree displaying whether instruments are enabled. See Easier Overview of Current Performance Schema Setting and slide 11 from CON 5282 for more details.

Procedure ps_dump_thread_stack():

This is the same as in ps_helper with one bug fix. See also slides 30-31 from CON 5282.

Procedure ps_enable_all():

Enable all consumers and instruments.

Procedure ps_reset_setup():

Reset consumers, instruments, actors, and threads to the defaults (without taking my.cnf into consideration).

View is_innodb_lock_waits:

Show information about waiting locks for InnoDB.

View is_engine_data:

Summary of the amount of data and indexes grouped by storage engine.

View ps_memory_by_thread_by_event_raw:

The amount of memory used grouped by thread and event without any formatting and ordering.

View ps_memory_current_by_thread_by_event:

The same as above but formatted output and ordered by current usage.

View ps_memory_high_by_thread_by_event:

The same as above but formatted output and ordered by the high watermark.

Procedure schema_unused_routines():

Lists unused stored procedures and functions.