MySQL Shell 8.0.16: Built-in Reports
Readers of my blog know that I like how MySQL Shell allows you to customize it and use it's Python and JavaScript support to create custom libraries with tools that help with your daily tasks and even creating auto-refreshing reports. Lefred has even taken this a step further and started to port Innotop to MySQL Shell.
One disadvantage of my example of auto-refreshing reports and the Innotop port is they both rely on the curses Python module to refresh the screen. While avoiding to reinvent the wheel is usually a good thing, and the curses library is both powerful and easy to use, it is not well supported on Microsoft Windows. The good news is that in MySQL 8.0.16 and later, you can also get auto-refreshing reports with a new built-in reporting framework in MySQL Shell. This blog shows how this framework works.
Built-In Features
The great thing with the built-in framework is that you can start using it even without coding as it comes with a pre-configured report. The framework consists of three parts:
\show
: This is the most basic command which runs a report once and displays the result.\watch
: This is similar to thewatch
command on Linux, where a command (report in this case) is repeatedly executed with the screen refreshed to show the new result.shell.registerReport()
: This method can be used to register custom reports. The details of custom reports are discussed in the blog MySQL Shell 8.0.16: User Defined Reports.
The \show
command is a good place to start.
The \show Command
You can get more information about how the \show
command works and reports in general using the built-in help system:
mysql-js> \h \show
NAME
\show - Executes the given report with provided options and arguments.
SYNTAX
\show <report_name> [options] [arguments]
DESCRIPTION
The report name accepted by the \show command is case-insensitive, '-'
and '_' characters can be used interchangeably.
Common options:
- --help - Display help of the given report.
- --vertical, -E - For 'list' type reports, display records vertically.
The output format of \show command depends on the type of report:
- 'list' - displays records in tabular form (or vertically, if --vertical
is used),
- 'report' - displays a YAML text report,
- 'print' - does not display anything, report is responsible for text
output.
If executed without the report name, lists available reports.
Note: user-defined reports can be registered with shell.registerReport()
method.
EXAMPLES
\show
Lists available reports, both built-in and user-defined.
\show query show session status like 'Uptime%'
Executes 'query' report with the provided SQL statement.
\show query --vertical show session status like 'Uptime%'
As above, but results are displayed in vertical form.
\show query --help
Displays help for the 'query' report.
SEE ALSO
Additional entries were found matching \show
The following topics were found at the SQL Syntax category:
- SHOW
For help on a specific topic use: \? <topic>
e.g.: \? SHOW
This already gives a lot of information, not only about the \show
command, but also about reports. Reports can be in one of three formats (more on that in a later blog), if they are using the list format (which the query report discussed below uses), you can get the output in tabular format (the default) or vertical using the --vertical
or -E
option. And finally, you can get more information about known reports by running the report with the --help
option, and you can get a list of known reports running \show
without arguments:
mysql-js> \show
Available reports: query.
Let's take a closer look at the query report.
The Query Report
The query report is a very simple report that take a query and runs it. You can get the help text for it by executing \show query --help
:
mysql-js> \show query --help
query - Executes the SQL statement given as arguments.
Usage:
\show query [OPTIONS] [ARGUMENTS]
\watch query [OPTIONS] [ARGUMENTS]
Options:
--help Display this help and exit.
--vertical, -E Display records vertically.
Arguments:
This report accepts 1-* arguments.
So, to run it, you simply provide the query as an argument – you can do this either just providing the query as is or as a quoted string. Let's say you want to use the following query for the report:
SELECT conn_id,
sys.format_statement(current_statement) AS statement,
format_pico_time(statement_latency) AS latency
FROM sys.x$session
ORDER BY statement_latency DESC
LIMIT 10
This will show the longest running queries limited to 10 queries. Note that is uses the new format_pico_time()
function that replaces the sys.format_time()
function in MySQL 8.0.16. Newlines are not allowed in the query when generating the report, so the command becomes:
mysql-js> \show query SELECT conn_id, sys.format_statement(current_statement) AS statement, format_pico_time(statement_latency) AS latency FROM sys.x$session ORDER BY statement_latency DESC LIMIT 10
+---------+-------------------------------------------------------------------+----------+
| conn_id | statement | latency |
+---------+-------------------------------------------------------------------+----------+
| 8 | SELECT conn_id, sys.format_sta ... tatement_latency DESC LIMIT 10 | 33.34 ms |
| 4 | NULL | 0 ps |
+---------+-------------------------------------------------------------------+----------+
Granted, this is not particular useful – you could just have executed the query on its own. However, if you consider the \watch
command instead, it become more useful.
The \watch Command
The \watch
the command supports two additional arguments on its own:
--interval=float, -i float
: The amount of time in seconds to wait between displaying the result of the report until the report is run again. Valid values are 0.1 second to 86400 seconds (one day).--nocls
: Do not clear the screen between iterations of the report. This will make the subsequent output be displayed below the previous output. This can for example be useful for reports returning a single line of output and you that way have the history of the report up the screen.
Report may also add options of their own. The query report for example accepts one argument, which is the query to execute. Other reports may accept other arguments.
Otherwise, you start the report the same way as when using \show
. For example, to run the query every five seconds:
mysql-js> \watch query --interval=5 SELECT conn_id, sys.format_statement(current_statement) AS statement, format_pico_time(statement_latency) AS latency FROM sys.x$session ORDER BY statement_latency DESC LIMIT 10
That's it. If you want to stop the report again, use CTRL+c and the report will stop at the next refresh.
Conclusion
The report framework in MySQL Shell 8.0.16 gives a nice starting point for generating reports. The built-in query function may not be the most fancy you can think of, but it is very easy way to quickly make a query run repeatedly at set intervals. However, the real power of the report framework is that you now have a framework to create cross-platform custom reports. That is the topic of a later blog.
0 Comments on “MySQL Shell 8.0.16: Built-in Reports”