10.1.6.2 Built-in MySQL Shell Report: Threads

The built-in MySQL Shell report threads is available from MySQL Shell 8.0.18. It lists the current threads in the connected MySQL server which belong to the user account that is used to run the report. The report works with servers running all supported MySQL 5.7 and MySQL 8.0 versions. If any item of information is not available in the MySQL Server version of the target server, the report leaves it out.

The threads report provides information for each thread drawn from various sources including MySQL's Performance Schema. Using the report-specific options, you can choose to show foreground threads, background threads, or all threads. You can report a default set of information for each thread, or select specific information to include in the report from a larger number of available choices. You can filter, sort, and limit the output. For details of the report-specific options and the full listing of information that you can include in the report, issue one of the following MySQL Shell commands to view the report help:

\help threads
\show threads --help

In addition to the report-specific options, the threads report accepts the standard options for the \show and \watch commands, as described in Section 10.1.5, “Running MySQL Shell Reports”. The threads report is of the list type, and by default the results are returned as a table, but you can use the --vertical (or -E) option to display them in vertical format.

The threads report uses MySQL Server's format_statement() function (see The format_statement() Function). Any truncated statements displayed in the report are truncated according to the setting for the statement_truncate_len option in MySQL Server's sys_config table, which defaults to 64 characters.

The following list summarizes the capabilities provided by the report-specific options for the threads report. See the report help for full details and the short forms of the options:

--foreground, --background, --all

List foreground threads only, background threads only, or all threads. The report displays a default set of appropriate fields for your thread type selection, unless you use the --format option to specify your own choice of fields instead.

--format

Define your own custom set of information to display for each thread, specified as a comma-separated list of columns (and display names, if you want). The report help lists all of the columns that you can include to customize your report.

--where, --order-by, --desc, --limit

Filter the returned results using logical expressions (--where), sort on selected columns (--order-by), sort in descending instead of ascending order --desc), or limit the number of returned threads (--limit).

For example, the following command runs the threads report to display all foreground threads, with a custom set of information comprising the thread ID, ID of any spawning thread, connection ID, user name and host name, client program name, type of command that the thread is executing, and memory allocated by the thread:

mysql-js> \show threads --foreground -o tid,ptid,cid,user,host,progname,command,memory