Auto-Refreshing Reports in MySQL Shell

MySQL Shell makes it easy to develop tools you can use for example to generate reports. In a previous blog, I showed how to use external modules in MySQL Shell. In this blog, I will take it one step further and use the curses Python module to create auto-refreshing reports. The first example will be kept very simple to show the idea, then a more realistic example will be shown where the top N files sorted by I/O will be returned.

Basic Example

As a basic example, consider the query SELECT NOW(). This returns the date and time. Let's query that every second for 10 seconds, then return to the prompt. The easiest is to look at the example and then discuss what it does:

import curses
from time import sleep

def clock():
    # Setup curses
    stdscr = curses.initscr()
    curses.noecho()
    curses.cbreak()
    stdscr.keypad(True)
    
    # Define the query
    query = session.sql("SELECT NOW()")
    
    # Clear screen
    stdscr.clear()
    
    # Run the clock for 10 seconds
    i = 0
    while i < 10:
        i = i + 1
        result = query.execute()
        now = result.fetch_one()
        stdscr.addstr(0, 0, "Now")
        stdscr.addstr(1, 0, "-------------------")
        stdscr.addstr(2, 0, str(now[0]))
        stdscr.refresh()
        sleep(1)
    
    # Reset the cures behaviour and finish
    curses.nocbreak()
    stdscr.keypad(False)
    curses.echo()
    curses.endwin()

clock()

Inside the clock() function, curses is first set up to initialize the screen, not to echo key inputs, and to react to keys instantly (don't wait for enter to be hit). The latter is not needed here as there is nothing checking for key inputs, however in many cases (like the iotop example later in the blog), you want to listen for single key inputs, for example to stop the execution. The counter part of these steps are done at the end of the function to clean up.

Next the query that will be executed is defined. Here I take advantage of the X DevAPI's support for preparing a query and use and re-use it later. This way, the query is only defined in a single spot. Then the screen is cleared and everything is ready for the loop that will do the main part of the job.

The loop in this example is very simple. It just goes through 10 iterations with a one second sleep at the end of each loop. In a real case, you may want to make this more resilient to delays or add another kinds of logic. The query is executed and the single row in the result is fetched. Finally, the addstr() curses method is used to print the output in the desired location (upper left corner in this case).

When you execute the function, you will get a result like in the following screen shot:

This is all fine, but using MySQL Shell to show a clock is hardly the most interesting use of MySQL Shell. Let's look at an example that is more usable in the real World.

MySQL iotop

If you are a Linux user, you may know the iotop utility which allows you to monitor the I/O activity in a similar way to what the top command does CPU and memory usage for processes. Let's implement a basic MySQL my_iotop module with the by_file_by_bytes function that displays the N (default 10) MySQL files that have had the most I/O, refreshes with a specified interval (default 10 seconds), and continues until you hit the q key.

This may sound like a complex task, however most of the steps are the same as in the previous example. The top N files with respect to I/O can be found using the global_io_global_by_file_by_bytes view from the sys schema. This sorts by the total number of bytes read and written for the file in descending order. So, only a simple single view query is needed. For this example to avoid the output handling to be too complex, fixed width columns will be used and file paths longer than 50 characters are truncated.

The only thing that really is required that was not part of the previous example is to add the limit to the number of files to include and to detect when q is entered. The limit is however easy to add when using the select() CRUD method.

Another thing that is worth doing is to include the feature in an external module. This makes it easier to reuse. This requires you to add the directory where you will save your modules to the ~/.mysqlsh/mysqlshrc.py file, for example:

import sys
sys.path.append("/home/jesper/.mysqlsh/lib")

See also my previous blog on using external modules for more information.

In this example the file with the source code is called my_iotop.py stored in the directory added to the mysqlshrc.py file.

The complete source is:

import curses
from datetime import datetime
from time import sleep

def global_by_file_by_bytes(session, max_files=10, delay=10):
    # Define the output format
    fmt_header = "| {0:50s} | {1:12s} | {2:13s} | {3:13s} | {4:12s} " \
               + "| {5:13s} | {6:13s} | {7:13s} | {8:8s} |"
    header = fmt_header.format("File", "# Reads", "Bytes Read", "Avg. Read",
                               "# Writes", "Bytes Write", "Avg. Write",
                               "Bytes Total", "Write %")
    bar = "+" + "-" * 52 + "+" + "-" * 14 + "+" + "-" * 15 + "+" + "-" * 15 \
        + "+" + "-" * 14 + "+" + "-" * 15 + "+" + "-" * 15 + "+" + "-" * 15 \
        + "+" + "-" * 10 + "+"
    fmt_row = "| {0:50.50s} | {1:12d} | {2:13s} | {3:13s} | {4:12d} " \
            + "| {5:13s} | {6:13s} | {7:13s} | {8:8s} |"
    
    # Setup curses
    stdscr = curses.initscr()
    curses.start_color()
    curses.init_pair(1, curses.COLOR_RED, curses.COLOR_WHITE)
    curses.noecho()
    curses.cbreak()
    # Listing for 1/10th of second at a time
    curses.halfdelay(1)
    stdscr.keypad(True)

    # Define the query
    sys_schema = session.get_schema("sys")
    table = sys_schema.get_table("io_global_by_file_by_bytes")
    query = table.select().limit(max_files)
    
    # Clear screen
    stdscr.clear()
    
    # Run the query and generate the report
    keep_running = True
    while keep_running:
        time = datetime.now()
        result = query.execute()

        stdscr.addstr(0, 0, time.strftime('%A %-d %B %H:%M:%S'), 
                      curses.color_pair(1))
        stdscr.addstr(2, 0, bar)
        stdscr.addstr(3, 0, header)
        stdscr.addstr(4, 0, bar)

        # Print the rows in the result
        line = 5
        for row in result.fetch_all():
            stdscr.addstr(line, 0, fmt_row.format(*row))
            line = line + 1

        stdscr.addstr(line, 0, bar)
        stdscr.refresh()

        # Wait until delay seconds have passed while listening for the q key
        while (datetime.now() - time).total_seconds() < delay:
            c = stdscr.getch()
            if c == ord("q"):
                keep_running = False
                break

    # Reset the cures behaviour and finish
    curses.nocbreak()
    stdscr.keypad(False)
    curses.echo()
    curses.endwin()

The example are using a few more of the curses features which I will not go into detail with. I will recommend you to read the Python documentation and the Curses Programming with Python tutorial, if you are interested in learning more about using curses.

You start the report by calling the global_by_file_by_bytes() function. The session for the connection is required as an argument. Optional arguments are the delay between each iteration (delay) and the maximum number of files to include in the report (max_files). An example using a delay of 5 seconds is:

mysql-py> import my_iotop
mysql-py> my_iotop.global_by_file_by_bytes(session, delay=5)

While the implementation shown here is quite rough in its edges, it does show the potential. And remember you have the full Python language available for manipulating the data. Click on the video below to see an example of the report.