MySQL Shell 8.0.16: Built-in Reports
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.
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 the
watchcommand 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.
\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
-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
\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.
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.