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:

1

2

3

4

5

6

7

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.

A problem that sometimes when writing queries or stored routines is the need to use strings to do basic manipulation of the data. While from a performance perspective it is generally faster to do these manipulations inside the application, for various reasons it may be desirably to keep things inside MySQL.

This post lists two stored functions that can be used for simple manipulation of strings.

The function as it stands here, has the following limitations and behaviours:

As the MySQL convention is to use 1 as the offset, so is the MySQL port of substr_count().

As MySQL stored functions do not support optional arguments, all arguments must be specified. For in_offset and in_length use NULL or 0 to use the default values.

The maximum length supported for the needle is 255 characters.

I am using the LOCATE() function as that returns the first occurrence of a substring after a given offset. This will particularly benefit the performance in cases where the needle is only sparsely present in the search string.